From: Sarah Hoffmann 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=9c1abc4b75b941f434d6a120bd1c8fc4f1031c30 Merge remote-tracking branch 'upstream/master' Conflicts: sql/indices.src.sql sql/partition-tables.src.sql sql/tables.sql utils/setup.php --- diff --git a/data/words.sql b/data/words.sql index a9019f8a..b15686d7 100644 --- a/data/words.sql +++ b/data/words.sql @@ -49637,7 +49637,7 @@ kein 100 select count(make_keywords(v)) from (select distinct svals(name) as v from place) as w where v is not null; select count(make_keywords(v)) from (select distinct postcode as v from place) as w where v is not null; -select count(getorcreate_housenumber_id(v)) from (select distinct housenumber as v from place where housenumber is not null) as w; +select count(getorcreate_housenumber_id(make_standard_name(v))) from (select distinct housenumber as v from place where housenumber is not null) as w; -- copy the word frequencies update word set search_name_count = count from word_frequencies wf where wf.word_token = word.word_token; diff --git a/settings/settings.php b/settings/settings.php index 11e450e9..13239643 100644 --- a/settings/settings.php +++ b/settings/settings.php @@ -5,11 +5,12 @@ // General settings @define('CONST_Debug', false); @define('CONST_Database_DSN', 'pgsql://@/nominatim'); // ://:@:/ + @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 @@ // 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'); diff --git a/sql/functions.sql b/sql/functions.sql index 821d44a6..5e95d0f4 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -548,21 +548,6 @@ BEGIN 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 @@ BEGIN 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 @@ BEGIN 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 @@ BEGIN 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; @@ -1330,7 +1319,6 @@ BEGIN 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 +1337,7 @@ BEGIN 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 @@ -2611,86 +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 --git a/sql/indices.src.sql b/sql/indices.src.sql index 4685ae2f..52bfbe35 100644 --- a/sql/indices.src.sql +++ b/sql/indices.src.sql @@ -1,33 +1,30 @@ -- Indices used only during search and update. -- These indices are created only after the indexing process is done. -CREATE INDEX idx_word_word_id on word USING BTREE (word_id) TABLESPACE ssd; +CREATE INDEX idx_word_word_id on word USING BTREE (word_id) {ts:search-index}; -CREATE INDEX idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off) TABLESPACE ssd; -CREATE INDEX idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; -CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid) TABLESPACE ssd; +CREATE INDEX idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off) {ts:search-index}; +CREATE INDEX idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off) {ts:search-index}; +CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid) {ts:search-index}; -CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id) TABLESPACE ssd; - -CREATE INDEX idx_place_boundingbox_place_id on place_boundingbox USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_place_boundingbox_outline ON place_boundingbox USING GIST (outline) TABLESPACE ssd; +CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id) {ts:search-index}; DROP INDEX IF EXISTS idx_placex_rank_search; -CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search) TABLESPACE ssd; -CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address) TABLESPACE ssd; -CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) TABLESPACE ssd where indexed_status > 0; -CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) TABLESPACE ssd where parent_place_id IS NOT NULL; -CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) TABLESPACE ssd where indexed_status > 0 and class='place' and type='houses'; -CREATE INDEX idx_placex_reverse_geometry ON placex USING gist (geometry) TABLESPACE ssd where rank_search != 28 and (name is not null or housenumber is not null) and class not in ('waterway','railway','tunnel','bridge'); -CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search) {ts:search-index}; +CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address) {ts:search-index}; +CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) {ts:address-index} where indexed_status > 0; +CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) {ts:search-index} where parent_place_id IS NOT NULL; +CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) {ts:address-index} where indexed_status > 0 and class='place' and type='houses'; +CREATE INDEX idx_placex_reverse_geometry ON placex USING gist (geometry) {ts:search-index} where rank_search != 28 and (name is not null or housenumber is not null) and class not in ('waterway','railway','tunnel','bridge'); +CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) {ts:address-index}; -CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid) TABLESPACE ssd; +CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid) {ts:address-index}; -- start -CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid) TABLESPACE ssd; +CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid) {ts:address-index}; -- end -CREATE UNIQUE INDEX idx_place_osm_unique on place using btree(osm_id,osm_type,class,type); +CREATE UNIQUE INDEX idx_place_osm_unique on place using btree(osm_id,osm_type,class,type) {ts:address-index}; -CREATE INDEX idx_gb_postcode_postcode ON gb_postcode USING BTREE (postcode); +CREATE INDEX idx_gb_postcode_postcode ON gb_postcode USING BTREE (postcode) {ts:search-index}; diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql index e0b1fae0..3f400c65 100644 --- a/sql/partition-tables.src.sql +++ b/sql/partition-tables.src.sql @@ -34,35 +34,35 @@ CREATE TABLE search_name_blank ( SELECT AddGeometryColumn('search_name_blank', 'centroid', 4326, 'GEOMETRY', 2); -CREATE TABLE location_area_country () INHERITS (location_area_large); -CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) TABLESPACE ssd; +CREATE TABLE location_area_country () INHERITS (location_area_large) {ts:address-data}; +CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {ts:address-index}; -CREATE TABLE search_name_country () INHERITS (search_name_blank); -CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; +CREATE TABLE search_name_country () INHERITS (search_name_blank) {ts:address-data}; +CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id) {ts:address-index}; +CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off) {ts:address-index}; -- start -CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large); -CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry) TABLESPACE ssd; +CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large) {ts:address-data}; +CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) {ts:address-index}; +CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry) {ts:address-index}; -CREATE TABLE search_name_-partition- () INHERITS (search_name_blank); -CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid) TABLESPACE ssd; -CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; +CREATE TABLE search_name_-partition- () INHERITS (search_name_blank) {ts:address-data}; +CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) {ts:address-index}; +CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid) {ts:address-index}; +CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off) {ts:address-index}; -CREATE TABLE location_property_-partition- () INHERITS (location_property); -CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id); -CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id); -CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber); +CREATE TABLE location_property_-partition- () INHERITS (location_property) {ts:aux-data}; +CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id) {ts:aux-index}; +CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id) {ts:aux-index}; +CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber) {ts:aux-index}; CREATE TABLE location_road_-partition- ( partition integer, place_id BIGINT, country_code VARCHAR(2) - ); + ) {ts:address-data}; SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2); -CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry) TABLESPACE ssd; -CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry) {ts:address-index}; +CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id) {ts:address-index}; -- end diff --git a/sql/tables.sql b/sql/tables.sql index 32ff5e87..8dae06d5 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -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 @@ CREATE TABLE import_osmosis_log ( event text ); -drop table import_npi_log; +drop table if exists import_npi_log; CREATE TABLE import_npi_log ( npiid integer, batchend timestamp, @@ -37,19 +37,10 @@ CREATE TABLE new_query_log ( 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" ; drop table IF EXISTS word; CREATE TABLE word ( @@ -61,17 +52,17 @@ CREATE TABLE word ( 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 +72,6 @@ SELECT AddGeometryColumn('location_area', 'centroid', 4326, 'POINT', 2); 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 +87,13 @@ CREATE TABLE location_property_aux () INHERITS (location_property); 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 +104,9 @@ CREATE TABLE search_name ( 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 +116,10 @@ CREATE TABLE place_addressline ( 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 +134,24 @@ CREATE TABLE placex ( 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 +170,7 @@ CREATE TRIGGER place_before_insert BEFORE INSERT ON place 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 +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 +198,8 @@ CREATE TABLE import_polygon_delete ( 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 --git a/tests/features/db/import/interpolation.feature b/tests/features/db/import/interpolation.feature index 9a4856f2..ad4bdf85 100644 --- a/tests/features/db/import/interpolation.feature +++ b/tests/features/db/import/interpolation.feature @@ -326,4 +326,50 @@ Feature: Import of address interpolations | housenumber | centroid | 8 | 1.001,1.001 + Scenario: Interpolation on self-intersecting way + Given the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | 0 0 + | 2 | place | house | 6 | 0 0.001 + | 3 | place | house | 10 | 0 0.002 + And the place ways + | osm_id | class | type | housenumber | geometry + | 1 | place | houses | even | 0 0, 0 0.001, 0 0.002, 0 0.001 + And the ways + | id | nodes + | 1 | 1,2,3,2 + When importing + Then node 1 expands to housenumbers + | housenumber | centroid + | 2 | 0,0 + | 4 | 0,0.0005 + Then node 2 expands to housenumbers + | housenumber | centroid + | 6 | 0,0.001 + | 8 | 0,0.0015 + Then node 3 expands to housenumbers + | housenumber | centroid + | 10 | 0,0.002 + | 8 | 0,0.0015 + + Scenario: Interpolation on self-intersecting way II + Given the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | 0 0 + | 2 | place | house | 6 | 0 0.001 + And the place ways + | osm_id | class | type | housenumber | geometry + | 1 | place | houses | even | 0 0, 0 0.001, 0 0.002, 0 0.001 + And the ways + | id | nodes + | 1 | 1,2,3,2 + When importing + Then node 1 expands to housenumbers + | housenumber | centroid + | 2 | 0,0 + | 4 | 0,0.0005 + Then node 2 expands to housenumbers + | housenumber | centroid + | 6 | 0,0.001 + diff --git a/tests/features/osm2pgsql/import/broken.feature b/tests/features/osm2pgsql/import/broken.feature new file mode 100644 index 00000000..58a45f91 --- /dev/null +++ b/tests/features/osm2pgsql/import/broken.feature @@ -0,0 +1,37 @@ +@DB +Feature: Import of objects with broken geometries by osm2pgsql + + @Fail + Scenario: Import way with double nodes + Given the osm nodes: + | id | geometry + | 100 | 0 0 + | 101 | 0 0.1 + | 102 | 0.1 0.2 + And the osm ways: + | id | tags | nodes + | 1 | 'highway' : 'primary' | 100 101 101 102 + When loading osm data + Then table place contains + | object | class | type | geometry + | W1 | highway | primary | (0 0, 0 0.1, 0.1 0.2) + + Scenario: Import of ballon areas + Given the osm nodes: + | id | geometry + | 1 | 0 0 + | 2 | 0 0.0001 + | 3 | 0.00001 0.0001 + | 4 | 0.00001 0 + | 5 | -0.00001 0 + And the osm ways: + | id | tags | nodes + | 1 | 'highway' : 'unclassified' | 1 2 3 4 1 5 + | 2 | 'highway' : 'unclassified' | 1 2 3 4 1 + | 3 | 'highway' : 'unclassified' | 1 2 3 4 3 + When loading osm data + Then table place contains + | object | geometrytype + | W1 | ST_LineString + | W2 | ST_Polygon + | W3 | ST_LineString diff --git a/tests/steps/db_results.py b/tests/steps/db_results.py index 2b44215e..f65e9924 100644 --- a/tests/steps/db_results.py +++ b/tests/steps/db_results.py @@ -49,6 +49,7 @@ def check_placex_content(step, tablename): q = 'SELECT *' if tablename == 'placex': q = q + ", ST_X(centroid) as clat, ST_Y(centroid) as clon" + q = q + ", ST_GeometryType(geometry) as geometrytype" q = q + ' FROM %s where osm_type = %%s and osm_id = %%s' % (tablename,) if cls is None: params = (osmtype, osmid) diff --git a/utils/setup.php b/utils/setup.php index 32b56700..a31fbadf 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -191,7 +191,14 @@ { $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 25000'; $osm2pgsql .= ' -P '.$aDSNInfo['port']; @@ -217,6 +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 @@ 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 @@ 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) { @@ -574,49 +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: 000/26-Feb-2013 11:53 - // planet.openstreetmap.org: 273/ 22-Mar-2013 07:41 - - preg_match_all('#([0-9]{3}/).*(([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: 273/ 2013-03-11 07:41 - + preg_match_all('#([0-9]{3}/)\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('#([0-9]{3}/).*(([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('#([0-9]{3}/)\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('#([0-9]{3}).state.txt.*(([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('#([0-9]{3}).state.txt\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 @@ 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 @@ } - 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 @@ } 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 @@ 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; + } +