From: Sarah Hoffmann <lonvia@denofr.de>
Date: Fri, 19 Dec 2014 19:20:35 +0000 (+0100)
Subject: Merge remote-tracking branch 'upstream/master'
X-Git-Tag: deploy~494
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/c68a8c9f2d4898bbc0fa5a13200b888093c0e808?hp=-c

Merge remote-tracking branch 'upstream/master'

Conflicts:
	sql/indices.src.sql
	sql/partition-tables.src.sql
	sql/tables.sql
	utils/setup.php
---

c68a8c9f2d4898bbc0fa5a13200b888093c0e808
diff --combined settings/settings.php
index 11e450e9,1b5422f9..13239643
--- a/settings/settings.php
+++ b/settings/settings.php
@@@ -5,11 -5,12 +5,12 @@@
  	// General settings
  	@define('CONST_Debug', false);
  	@define('CONST_Database_DSN', 'pgsql://@/nominatim'); // <driver>://<username>:<password>@<host>:<port>/<database>
+ 	@define('CONST_Database_Web_User', 'www-data');
  	@define('CONST_Max_Word_Frequency', '50000');
  	@define('CONST_Limit_Reindexing', true);
  
  	// Software versions
- 	@define('CONST_Postgresql_Version', '9.1'); // values: 8.3, 8.4, 9.0, 9.1, 9.2
+ 	@define('CONST_Postgresql_Version', '9.1'); // values: 9.0, 9.1, 9.2
  	@define('CONST_Postgis_Version', '1.5'); // values: 1.5, 2.0
  
  	// Paths
@@@ -21,6 -22,23 +22,23 @@@
  	// osm2pgsql settings
  	@define('CONST_Osm2pgsql_Flatnode_File', null);
  
+ 	// tablespace settings
+ 	// osm2pgsql caching tables (aka slim mode tables) - update only
+ 	@define('CONST_Tablespace_Osm2pgsql_Data', false);
+ 	@define('CONST_Tablespace_Osm2pgsql_Index', false);
+ 	// osm2pgsql output tables (aka main table) - update only
+ 	@define('CONST_Tablespace_Place_Data', false);
+ 	@define('CONST_Tablespace_Place_Index', false);
+ 	// address computation tables - update only
+ 	@define('CONST_Tablespace_Address_Data', false);
+ 	@define('CONST_Tablespace_Address_Index', false);
+ 	// search tables - needed for lookups
+ 	@define('CONST_Tablespace_Search_Data', false);
+ 	@define('CONST_Tablespace_Search_Index', false);
+ 	// additional data, e.g. TIGER data - needed for lookups
+ 	@define('CONST_Tablespace_Aux_Data', false);
+ 	@define('CONST_Tablespace_Aux_Index', false);
+ 
  	// Replication settings
  	@define('CONST_Replication_Url', 'http://planet.openstreetmap.org/replication/minute');
  	@define('CONST_Replication_MaxInterval', '3600');
@@@ -52,15 -70,13 +70,15 @@@
  
  	// Website settings
  	@define('CONST_NoAccessControl', true);
 -	@define('CONST_ClosedForIndexing', false);
 -	@define('CONST_ClosedForIndexingExceptionIPs', '');
  	@define('CONST_BlockedIPs', '');
 +	@define('CONST_IPBanFile', CONST_BasePath.'/settings/ip_blocks');
 +	@define('CONST_WhitelistedIPs', '');
 +	@define('CONST_BlockedUserAgents', '');
 +	@define('CONST_BlockReverseMaxLoad', 15);
  	@define('CONST_BulkUserIPs', '');
  	@define('CONST_BlockMessage', ''); // additional info to show for blocked IPs
  
 -	@define('CONST_Website_BaseURL', 'http://'.php_uname('n').'/');
 +	@define('CONST_Website_BaseURL', 'http://nominatim.openstreetmap.org/');
  	@define('CONST_Tile_Default', 'Mapnik');
  
  	@define('CONST_Default_Language', false);
diff --combined sql/functions.sql
index 821d44a6,9186c6a3..5e95d0f4
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@@ -548,21 -548,6 +548,6 @@@ BEGI
      RETURN nearcountry.country_code;
    END LOOP;
  
-   -- WorldBoundaries data (second fallback - think there might be something broken in this data)
- --  FOR nearcountry IN select country_code from country where st_covers(geometry, place_centre) limit 1
- --  LOOP
- --    RETURN nearcountry.country_code;
- --  END LOOP;
- 
- --RAISE WARNING 'near country: %', ST_AsText(place_centre);
- 
-   -- Still not in a country - try nearest within ~12 miles of a country
- --  FOR nearcountry IN select country_code from country where st_distance(geometry, place_centre) < 0.5 
- --    order by st_distance(geometry, place) limit 1
- --  LOOP
- --    RETURN nearcountry.country_code;
- --  END LOOP;
- 
    RETURN NULL;
  END;
  $$
@@@ -846,15 -831,13 +831,13 @@@ BEGI
      IF search_place_id IS NOT NULL THEN
        select * from placex where place_id = search_place_id INTO nextnode;
  
-         IF nodeidpos < array_upper(waynodes, 1) THEN
+         IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
            -- Make sure that the point is actually on the line. That might
            -- be a bit paranoid but ensures that the algorithm still works
            -- should osm2pgsql attempt to repair geometries.
            splitline := split_line_on_node(linegeo, nextnode.geometry);
            sectiongeo := ST_GeometryN(splitline, 1);
-           IF ST_GeometryType(ST_GeometryN(splitline, 2)) = 'ST_LineString' THEN
-             linegeo := ST_GeometryN(splitline, 2);
-           END IF;
+           linegeo := ST_GeometryN(splitline, 2);
          ELSE
            sectiongeo = linegeo;
          END IF;
@@@ -862,7 -845,7 +845,7 @@@
  
          IF startnumber IS NOT NULL AND endnumber IS NOT NULL
             AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber
-            AND ST_GeometryType(linegeo) = 'ST_LineString' THEN
+            AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
  
            IF (startnumber > endnumber) THEN
              housenum := endnumber;
@@@ -897,6 -880,12 +880,12 @@@
            END LOOP;
          END IF;
  
+         -- early break if we are out of line string,
+         -- might happen when a line string loops back on itself
+         IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
+             RETURN newpoints;
+         END IF;
+ 
          startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
          prevnode := nextnode;
      END IF;
@@@ -922,11 -911,6 +911,11 @@@ DECLAR
  BEGIN
    --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id;
  
 +  -- remove operator tag for most places, messes too much with search_name indexes
 +  IF NEW.class not in ('amenity', 'shop') THEN
 +    NEW.name := delete(NEW.name, 'operator');
 +  END IF;
 +
    -- just block these
    IF NEW.class in ('landuse','natural') and NEW.name is null THEN
  --    RAISE WARNING 'empty landuse %',NEW.osm_id;
@@@ -1330,7 -1314,6 +1319,6 @@@ BEGI
  
      result := deleteSearchName(NEW.partition, NEW.place_id);
      DELETE FROM place_addressline WHERE place_id = NEW.place_id;
-     DELETE FROM place_boundingbox where place_id = NEW.place_id;
      result := deleteRoad(NEW.partition, NEW.place_id);
      result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
      UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
@@@ -1349,7 -1332,7 +1337,7 @@@
      place_centroid := ST_PointOnSurface(NEW.geometry);
      NEW.centroid := null;
  
-     -- reclaculate country and partition
+     -- recalculate country and partition
      IF NEW.rank_search = 4 THEN
        -- for countries, believe the mapped country code,
        -- so that we remain in the right partition if the boundaries
@@@ -2064,11 -2047,6 +2052,11 @@@ BEGI
      --DEBUG: RAISE WARNING '%', existingplacex;
    END IF;
  
 +  -- remove operator tag for most places, messes too much with search_name indexes
 +  IF NEW.class not in ('amenity', 'shop') THEN
 +    NEW.name := delete(NEW.name, 'operator');
 +  END IF;
 +
    -- Just block these - lots and pointless
    IF NEW.class in ('landuse','natural') and NEW.name is null THEN
      -- if the name tag was removed, older versions might still be lurking in the place table
@@@ -2259,12 -2237,6 +2247,12 @@@
  
    END IF;
  
 +  -- refuse to update multiplpoygons with too many objects, too much of a performance hit
 +  IF ST_NumGeometries(NEW.geometry) > 2000 THEN
 +    RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id;
 +    RETURN NULL;
 +  END IF;
 +
    IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
       OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
       OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
@@@ -2611,86 -2583,10 +2599,10 @@@ END
  $$
  LANGUAGE plpgsql;
  
- CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id BIGINT) RETURNS place_boundingbox
-   AS $$
- DECLARE
-   result place_boundingbox;
-   numfeatures integer;
- BEGIN
-   select * from place_boundingbox into result where place_id = search_place_id;
-   IF result.place_id IS NULL THEN
- -- remove  isaddress = true because if there is a matching polygon it always wins
-     select count(*) from place_addressline where address_place_id = search_place_id into numfeatures;
-     insert into place_boundingbox select place_id,
-              ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)),
-              ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)),
-              numfeatures, ST_Area(geometry),
-              geometry as area from location_area where place_id = search_place_id;
-     select * from place_boundingbox into result where place_id = search_place_id;
-   END IF;
-   IF result.place_id IS NULL THEN
- -- TODO 0.0001
-     insert into place_boundingbox select address_place_id,
-              min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon,
-              min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat,
-              count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area,
-              ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary 
-              from (select * from place_addressline where address_place_id = search_place_id order by cached_rank_address limit 4000) as place_addressline join placex using (place_id) 
-              where address_place_id = search_place_id
- --               and (isaddress = true OR place_id = search_place_id)
-                and (st_length(geometry) < 0.01 or place_id = search_place_id)
-              group by address_place_id limit 1;
-     select * from place_boundingbox into result where place_id = search_place_id;
-   END IF;
-   return result;
- END;
- $$
- LANGUAGE plpgsql;
- 
- -- don't do the operation if it would be slow
- CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id BIGINT) RETURNS place_boundingbox
-   AS $$
- DECLARE
-   result place_boundingbox;
-   numfeatures integer;
-   rank integer;
- BEGIN
-   select * from place_boundingbox into result where place_id = search_place_id;
-   IF result IS NULL AND rank > 14 THEN
-     select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures;
-     insert into place_boundingbox select place_id,
-              ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)),
-              ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)),
-              numfeatures, ST_Area(geometry),
-              geometry as area from location_area where place_id = search_place_id;
-     select * from place_boundingbox into result where place_id = search_place_id;
-   END IF;
-   IF result IS NULL THEN
-     select rank_search from placex where place_id = search_place_id into rank;
-     IF rank > 20 THEN
- -- TODO 0.0001
-       insert into place_boundingbox select address_place_id,
-              min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon,
-              min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat,
-              count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area,
-              ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary 
-              from place_addressline join placex using (place_id) 
-              where address_place_id = search_place_id 
-                and (isaddress = true OR place_id = search_place_id)
-                and (st_length(geometry) < 0.01 or place_id = search_place_id)
-              group by address_place_id limit 1;
-       select * from place_boundingbox into result where place_id = search_place_id;
-     END IF;
-   END IF;
-   return result;
- END;
- $$
- LANGUAGE plpgsql;
  
  CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN
    AS $$
  DECLARE
-   result place_boundingbox;
    numfeatures integer;
  BEGIN
    update placex set 
diff --combined sql/tables.sql
index 32ff5e87,fdd60bfb..8dae06d5
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@@ -1,10 -1,10 +1,10 @@@
- drop table import_status;
+ drop table if exists import_status;
  CREATE TABLE import_status (
    lastimportdate timestamp NOT NULL
    );
- GRANT SELECT ON import_status TO "www-data" ;
+ GRANT SELECT ON import_status TO "{www-user}" ;
  
- drop table import_osmosis_log;
+ drop table if exists import_osmosis_log;
  CREATE TABLE import_osmosis_log (
    batchend timestamp,
    batchsize integer,
@@@ -13,7 -13,7 +13,7 @@@
    event text
    );
  
- drop table import_npi_log;
+ drop table if exists import_npi_log;
  CREATE TABLE import_npi_log (
    npiid integer,
    batchend timestamp,
@@@ -23,6 -23,19 +23,6 @@@
    event text
    );
  
 ---drop table IF EXISTS query_log;
 -CREATE TABLE query_log (
 -  starttime timestamp,
 -  query text,
 -  ipaddress text,
 -  endtime timestamp,
 -  results integer
 -  );
 -CREATE INDEX idx_query_log ON query_log USING BTREE (starttime);
 -GRANT SELECT ON query_log TO "{www-user}" ;
 -GRANT INSERT ON query_log TO "{www-user}" ;
 -GRANT UPDATE ON query_log TO "{www-user}" ;
 -
  CREATE TABLE new_query_log (
    type text,
    starttime timestamp,
@@@ -30,26 -43,18 +30,17 @@@
    useragent text,
    language text,
    query text,
 +  searchterm text,
    endtime timestamp,
    results integer,
    format text,
    secret text
    );
  CREATE INDEX idx_new_query_log_starttime ON new_query_log USING BTREE (starttime);
- GRANT INSERT ON new_query_log TO "www-data" ;
- GRANT UPDATE ON new_query_log TO "www-data" ;
- GRANT SELECT ON new_query_log TO "www-data" ;
+ GRANT INSERT ON new_query_log TO "{www-user}" ;
+ GRANT UPDATE ON new_query_log TO "{www-user}" ;
+ GRANT SELECT ON new_query_log TO "{www-user}" ;
  
- --drop table IF EXISTS report_log;
- CREATE TABLE report_log (
-   starttime timestamp,
-   ipaddress text,
-   query text,
-   description text,
-   email text
-   );
- GRANT INSERT ON report_log TO "www-data" ;
 -create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as 
 -useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC;
  
  drop table IF EXISTS word;
  CREATE TABLE word (
@@@ -61,17 -66,17 +52,17 @@@
    country_code varchar(2),
    search_name_count INTEGER,
    operator TEXT
-   ) TABLESPACE ssd;
- CREATE INDEX idx_word_word_token on word USING BTREE (word_token) TABLESPACE ssd;
- GRANT SELECT ON word TO "www-data" ;
- DROP SEQUENCE seq_word;
+   ) {ts:search-data};
+ CREATE INDEX idx_word_word_token on word USING BTREE (word_token) {ts:search-index};
+ GRANT SELECT ON word TO "{www-user}" ;
+ DROP SEQUENCE IF EXISTS seq_word;
  CREATE SEQUENCE seq_word start 1;
  
  drop table IF EXISTS location_area CASCADE;
  CREATE TABLE location_area (
    partition integer,
    place_id BIGINT,
-   country_code VARCHAR(2), 
+   country_code VARCHAR(2),
    keywords INTEGER[],
    rank_search INTEGER NOT NULL,
    rank_address INTEGER NOT NULL,
@@@ -81,8 -86,6 +72,6 @@@ SELECT AddGeometryColumn('location_area
  SELECT AddGeometryColumn('location_area', 'geometry', 4326, 'GEOMETRY', 2);
  
  CREATE TABLE location_area_large () INHERITS (location_area);
- CREATE TABLE location_area_roadnear () INHERITS (location_area);
- CREATE TABLE location_area_roadfar () INHERITS (location_area);
  
  drop table IF EXISTS location_property CASCADE;
  CREATE TABLE location_property (
@@@ -98,13 -101,13 +87,13 @@@ CREATE TABLE location_property_aux () I
  CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
  CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
  CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
- GRANT SELECT ON location_property_aux TO "www-data";
+ GRANT SELECT ON location_property_aux TO "{www-user}";
  
- CREATE TABLE location_property_tiger () INHERITS (location_property);
- CREATE INDEX idx_location_property_tiger_place_id ON location_property_tiger USING BTREE (place_id);
- CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_tiger USING BTREE (parent_place_id);
- CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber);
- GRANT SELECT ON location_property_tiger TO "www-data";
+ CREATE TABLE location_property_tiger () INHERITS (location_property) {ts:aux-data};
+ CREATE INDEX idx_location_property_tiger_place_id ON location_property_tiger USING BTREE (place_id) {ts:aux-index};
+ CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_tiger USING BTREE (parent_place_id) {ts:aux-index};
+ CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber) {ts:aux-index};
+ GRANT SELECT ON location_property_tiger TO "{www-user}";
  
  drop table IF EXISTS search_name;
  CREATE TABLE search_name (
@@@ -115,9 -118,9 +104,9 @@@
    country_code varchar(2),
    name_vector integer[],
    nameaddress_vector integer[]
-   );
+   ) {ts:search-data};
  SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2);
- CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) TABLESPACE ssd;
+ CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {ts:search-index};
  
  drop table IF EXISTS place_addressline;
  CREATE TABLE place_addressline (
@@@ -127,46 -130,10 +116,10 @@@
    isaddress boolean,
    distance float,
    cached_rank_address integer
-   ) TABLESPACE data;
- CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) TABLESPACE ssd;
+   ) {ts:search-data};
+ CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {ts:search-index};
  
- drop table IF EXISTS place_boundingbox CASCADE;
- CREATE TABLE place_boundingbox (
-   place_id BIGINT,
-   minlat float,
-   maxlat float,
-   minlon float,
-   maxlon float,
-   numfeatures integer,
-   area float
-   );
- SELECT AddGeometryColumn('place_boundingbox', 'outline', 4326, 'GEOMETRY', 2);
- GRANT SELECT on place_boundingbox to "www-data" ;
- GRANT INSERT on place_boundingbox to "www-data" ;
- 
- drop table IF EXISTS reverse_cache;
- CREATE TABLE reverse_cache (
-   latlonzoomid integer,
-   country_code varchar(2),
-   place_id BIGINT
-   );
- GRANT SELECT on reverse_cache to "www-data" ;
- GRANT INSERT on reverse_cache to "www-data" ;
- CREATE INDEX idx_reverse_cache_latlonzoomid ON reverse_cache USING BTREE (latlonzoomid);
- 
- drop table country;
- CREATE TABLE country (
-   country_code varchar(2),
-   country_name hstore,
-   country_default_language_code varchar(2)
-   );
- SELECT AddGeometryColumn('country', 'geometry', 4326, 'POLYGON', 2);
- insert into country select iso3166::varchar(2), 'name:en'->cntry_name, null, 
-   ST_Transform(geometryn(the_geom, generate_series(1, numgeometries(the_geom))), 4326) from worldboundaries;
- CREATE INDEX idx_country_country_code ON country USING BTREE (country_code);
- CREATE INDEX idx_country_geometry ON country USING GIST (geometry);
- 
- drop table placex;
+ drop table if exists placex;
  CREATE TABLE placex (
    place_id BIGINT NOT NULL,
    partition integer,
@@@ -181,29 -148,24 +134,24 @@@
    wikipedia TEXT, -- calculated wikipedia article name (language:title)
    geometry_sector INTEGER,
    calculated_country_code varchar(2)
-   ) TABLESPACE ssd;
+   ) {ts:search-data};
  SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2);
- CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) TABLESPACE ssd;
- CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) TABLESPACE ssd;
- CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) TABLESPACE ssd;
- CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) TABLESPACE ssd;
- CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) TABLESPACE ssd;
- CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) TABLESPACE ssd WHERE osm_type='N' and rank_search < 26;
- 
- --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
- 
- --CREATE INDEX idx_placex_pendingbylatlon ON placex USING BTREE (geometry_index(geometry_sector,indexed,name),rank_search)  where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
- 
- DROP SEQUENCE seq_place;
+ CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {ts:search-index};
+ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {ts:search-index};
+ CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {ts:address-index};
+ CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {ts:address-index};
+ CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {ts:search-index};
+ CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26 {ts:address-index};
+ 
+ DROP SEQUENCE IF EXISTS seq_place;
  CREATE SEQUENCE seq_place start 1;
- GRANT SELECT on placex to "www-data" ;
- GRANT SELECT ON search_name to "www-data" ;
- GRANT SELECT on place_addressline to "www-data" ;
- GRANT SELECT ON seq_word to "www-data" ;
- GRANT SELECT ON planet_osm_ways to "www-data" ;
- GRANT SELECT ON planet_osm_rels to "www-data" ;
- GRANT SELECT on location_area to "www-data" ;
- GRANT SELECT on country to "www-data" ;
+ GRANT SELECT on placex to "{www-user}" ;
+ GRANT SELECT ON search_name to "{www-user}" ;
+ GRANT SELECT on place_addressline to "{www-user}" ;
+ GRANT SELECT ON seq_word to "{www-user}" ;
+ GRANT SELECT ON planet_osm_ways to "{www-user}" ;
+ GRANT SELECT ON planet_osm_rels to "{www-user}" ;
+ GRANT SELECT on location_area to "{www-user}" ;
  
  -- insert creates the location tagbles, creates location indexes if indexed == true
  CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
@@@ -222,7 -184,7 +170,7 @@@ CREATE TRIGGER place_before_insert BEFO
      FOR EACH ROW EXECUTE PROCEDURE place_insert();
  
  drop index idx_placex_sector;
- CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id) TABLESPACE ssd;
+ CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id) {ts:address-index};
  
  DROP SEQUENCE seq_postcodes;
  CREATE SEQUENCE seq_postcodes start 1;
@@@ -240,8 -202,8 +188,8 @@@ CREATE TABLE import_polygon_error 
    );
  SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2);
  SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2);
- CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id) TABLESPACE ssd;
- GRANT SELECT ON import_polygon_error TO "www-data";
+ CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
+ GRANT SELECT ON import_polygon_error TO "{www-user}";
  
  drop table import_polygon_delete;
  CREATE TABLE import_polygon_delete (
@@@ -250,8 -212,8 +198,8 @@@
    class TEXT NOT NULL,
    type TEXT NOT NULL
    );
- CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id) TABLESPACE ssd;
- GRANT SELECT ON import_polygon_delete TO "www-data";
+ CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
+ GRANT SELECT ON import_polygon_delete TO "{www-user}";
  
  drop sequence file;
  CREATE SEQUENCE file start 1;
diff --combined utils/setup.php
index 32b56700,f7ebafa6..a31fbadf
--- a/utils/setup.php
+++ b/utils/setup.php
@@@ -191,9 -191,16 +191,16 @@@
  		{
  			$osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
  		}
- 		$osm2pgsql .= ' --tablespace-slim-index ssd --tablespace-main-index ssd --tablespace-main-data ssd --tablespace-slim-data data';
+ 		if (CONST_Tablespace_Osm2pgsql_Data)
+ 			$osm2pgsql .= ' --tablespace-slim-data '.CONST_Tablespace_Osm2pgsql_Data;
+ 		if (CONST_Tablespace_Osm2pgsql_Index)
+ 			$osm2pgsql .= ' --tablespace-slim-index '.CONST_Tablespace_Osm2pgsql_Index;
+ 		if (CONST_Tablespace_Place_Data)
+ 			$osm2pgsql .= ' --tablespace-main-data '.CONST_Tablespace_Place_Data;
+ 		if (CONST_Tablespace_Place_Index)
+ 			$osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
  		$osm2pgsql .= ' -lsc -O gazetteer --hstore';
 -		$osm2pgsql .= ' -C '.$iCacheMemory;
 +		$osm2pgsql .= ' -C 25000';
  		$osm2pgsql .= ' -P '.$aDSNInfo['port'];
  		$osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
  		passthruCheckReturn($osm2pgsql);
@@@ -217,6 -224,7 +224,7 @@@
  		if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
  		if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
  		pgsqlRunScript($sTemplate);
+ 
  		if ($fPostgisVersion < 2.0) {
  			echo "Helper functions for postgis < 2.0\n";
  			$sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_15_aux.sql');
@@@ -259,13 -267,30 +267,30 @@@
  
  	if ($aCMDResult['create-tables'] || $aCMDResult['all'])
  	{
- 		echo "Tables\n";
  		$bDidSomething = true;
- 		pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
+ 
+ 		echo "Tables\n";
+ 		$sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
+ 		$sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:address-data}',
+ 		                                CONST_Tablespace_Address_Data, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:address-index}',
+ 		                                CONST_Tablespace_Address_Index, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:search-data}',
+ 		                                CONST_Tablespace_Search_Data, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:search-index}',
+ 		                                CONST_Tablespace_Search_Index, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:aux-data}',
+ 		                                CONST_Tablespace_Aux_Data, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:aux-index}',
+ 		                                CONST_Tablespace_Aux_Index, $sTemplate);
+ 		pgsqlRunScript($sTemplate, false);
  
  		// re-run the functions
+ 		echo "Functions\n";
  		$sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
- 		$sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
+ 		$sTemplate = str_replace('{modulepath}',
+ 			                     CONST_BasePath.'/module', $sTemplate);
  		pgsqlRunScript($sTemplate);
  	}
  
@@@ -283,6 -308,18 +308,18 @@@
  		if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
  
  		$sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
+ 		$sTemplate = replace_tablespace('{ts:address-data}',
+ 		                                CONST_Tablespace_Address_Data, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:address-index}',
+ 		                                CONST_Tablespace_Address_Index, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:search-data}',
+ 		                                CONST_Tablespace_Search_Data, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:search-index}',
+ 		                                CONST_Tablespace_Search_Index, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:aux-data}',
+ 		                                CONST_Tablespace_Aux_Data, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:aux-index}',
+ 		                                CONST_Tablespace_Aux_Index, $sTemplate);
  		preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
  		foreach($aMatches as $aMatch)
  		{
@@@ -527,7 -564,7 +564,7 @@@
  		$sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
  		$sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
  		$sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 -		$sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
 +		$sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
  		if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
  
  		$sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
@@@ -574,49 -611,59 +611,59 @@@
  
  			// Search for the correct state file - uses file timestamps so need to sort by date descending
  			$sRepURL = CONST_Replication_Url."/";
- 			$sRep = file_get_contents($sRepURL."?C=M;O=D");
+ 			$sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
  			// download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
- 			// planet.openstreetmap.org: <a href="273/">273/</a>                    22-Mar-2013 07:41    -
- 			preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
- 			$aPrevRepMatch = false;
- 			foreach($aRepMatches as $aRepMatch)
+ 			// planet.openstreetmap.org: <a href="273/">273/</a>                    2013-03-11 07:41    -
+ 			preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
+ 			if ($aRepMatches)
  			{
- 				if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
- 				$aPrevRepMatch = $aRepMatch;
- 			}
- 			if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
+ 				$aPrevRepMatch = false;
+ 				foreach($aRepMatches as $aRepMatch)
+ 				{
+ 					if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
+ 					$aPrevRepMatch = $aRepMatch;
+ 				}
+ 				if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
  
- 			$sRepURL .= $aRepMatch[1];
- 			$sRep = file_get_contents($sRepURL."?C=M;O=D");
- 			preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
- 			$aPrevRepMatch = false;
- 			foreach($aRepMatches as $aRepMatch)
- 			{
- 				if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
- 				$aPrevRepMatch = $aRepMatch;
- 			}
- 			if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
+ 				$sRepURL .= $aRepMatch[1];
+ 				$sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
+ 				preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
+ 				$aPrevRepMatch = false;
+ 				foreach($aRepMatches as $aRepMatch)
+ 				{
+ 					if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
+ 					$aPrevRepMatch = $aRepMatch;
+ 				}
+ 				if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
  
- 			$sRepURL .= $aRepMatch[1];
- 			$sRep = file_get_contents($sRepURL."?C=M;O=D");
- 			preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
- 			$aPrevRepMatch = false;
- 			foreach($aRepMatches as $aRepMatch)
+ 				$sRepURL .= $aRepMatch[1];
+ 				$sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
+ 				preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
+ 				$aPrevRepMatch = false;
+ 				foreach($aRepMatches as $aRepMatch)
+ 				{
+ 					if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
+ 					$aPrevRepMatch = $aRepMatch;
+ 				}
+ 				if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
+ 
+ 				$sRepURL .= $aRepMatch[1].'.state.txt';
+ 				echo "Getting state file: $sRepURL\n";
+ 				$sStateFile = file_get_contents($sRepURL);
+ 				if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
+ 				file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
+ 				echo "Updating DB status\n";
+ 				pg_query($oDB->connection, 'TRUNCATE import_status');
+ 				$sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
+ 				pg_query($oDB->connection, $sSQL);
+ 			}
+ 			else
  			{
- 				if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
- 				$aPrevRepMatch = $aRepMatch;
+ 				if (!$aCMDResult['all'])
+ 				{
+ 					fail("Cannot read state file directory.");
+ 				}
  			}
- 			if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
- 
- 			$sRepURL .= $aRepMatch[1].'.state.txt';
- 			echo "Getting state file: $sRepURL\n";
- 			$sStateFile = file_get_contents($sRepURL);
- 			if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
- 			file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
- 			echo "Updating DB status\n";
- 			pg_query($oDB->connection, 'TRUNCATE import_status');
- 			$sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
- 			pg_query($oDB->connection, $sSQL);
  		}
  	}
  
@@@ -647,6 -694,12 +694,12 @@@
  		if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
  
  		$sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
+ 		$sTemplate = replace_tablespace('{ts:address-index}',
+ 		                                CONST_Tablespace_Address_Index, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:search-index}',
+ 		                                CONST_Tablespace_Search_Index, $sTemplate);
+ 		$sTemplate = replace_tablespace('{ts:aux-index}',
+ 		                                CONST_Tablespace_Aux_Index, $sTemplate);
  		preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
  		foreach($aMatches as $aMatch)
  		{
@@@ -758,14 -811,14 +811,14 @@@
  
  	}
  
- 	function pgsqlRunScript($sScript)
+ 	function pgsqlRunScript($sScript, $bfatal = true)
  	{
  		global $aCMDResult;
  		// Convert database DSN to psql parameters
  		$aDSNInfo = DB::parseDSN(CONST_Database_DSN);
  		if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
  		$sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
- 		if (!$aCMDResult['ignore-errors'])
+ 		if ($bfatal && !$aCMDResult['ignore-errors'])
  			$sCMD .= ' -v ON_ERROR_STOP=1';
  		$aDescriptors = array(
  			0 => array('pipe', 'r'),
@@@ -784,7 -837,7 +837,7 @@@
  		}
  		fclose($ahPipes[0]);
  		$iReturn = proc_close($hProcess);
- 		if ($iReturn > 0)
+ 		if ($bfatal && $iReturn > 0)
  		{
  			fail("pgsql returned with error code ($iReturn)");
  		}
@@@ -852,3 -905,15 +905,15 @@@
  		passthru($cmd, $result);
  		if ($result != 0) fail('Error executing external command: '.$cmd);
  	}
+ 
+ 	function replace_tablespace($sTemplate, $sTablespace, $sSql)
+ 	{
+ 		if ($sTablespace)
+ 			$sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"',
+ 			                    $sSql);
+ 		else
+ 			$sSql = str_replace($sTemplate, '', $sSql);
+ 
+ 		return $sSql;
+ 	}
+