From: Sarah Hoffmann Date: Fri, 6 Mar 2015 06:57:20 +0000 (+0100) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~478 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/cefa7a3c3bba1e072e1518062da7fd1c1facdc00?hp=1f951e863a870854188603b29c8a98e3897dccdd Merge remote-tracking branch 'upstream/master' Conflicts: sql/functions.sql sql/tables.sql --- diff --git a/data/country_name.sql b/data/country_name.sql index eb52cc5d..352337a7 100644 --- a/data/country_name.sql +++ b/data/country_name.sql @@ -295,7 +295,6 @@ CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_ -- REVOKE ALL ON TABLE country_name FROM PUBLIC; -GRANT SELECT ON TABLE country_name TO "www-data"; -- diff --git a/data/gb_postcode_table.sql b/data/gb_postcode_table.sql index 7cd3f694..bee8a964 100644 --- a/data/gb_postcode_table.sql +++ b/data/gb_postcode_table.sql @@ -24,4 +24,3 @@ CREATE TABLE gb_postcode ( CONSTRAINT enforce_srid_geometry CHECK ((st_srid(geometry) = 4326)) ); -GRANT SELECT ON TABLE gb_postcode TO "www-data"; diff --git a/osm2pgsql b/osm2pgsql index 10c274f8..d2afef62 160000 --- a/osm2pgsql +++ b/osm2pgsql @@ -1 +1 @@ -Subproject commit 10c274f851b63547d8e1a194231a67f8f0f8aed6 +Subproject commit d2afef62a4a0d72c547103392cffcdda25687c6e diff --git a/sql/functions.sql b/sql/functions.sql index 29e6544c..c3590c1e 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -747,8 +747,77 @@ END; $$ LANGUAGE plpgsql; +-- find the parant road of an interpolation +CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, place TEXT, + partition INTEGER, centroid GEOMETRY, geom GEOMETRY) +RETURNS BIGINT AS $$ +DECLARE + addr_street TEXT; + addr_place TEXT; + parent_place_id BIGINT; + address_street_word_ids INTEGER[]; + + waynodes BIGINT[]; + + location RECORD; +BEGIN + addr_street = street; + addr_place = place; + + IF addr_street is null and addr_place is null THEN + select nodes from planet_osm_ways where id = wayid INTO waynodes; + FOR location IN SELECT placex.street, placex.addr_place from placex + where osm_type = 'N' and osm_id = ANY(waynodes) + and (placex.street is not null or placex.addr_place is not null) + and indexed_status < 100 + limit 1 LOOP + addr_street = location.street; + addr_place = location.addr_place; + END LOOP; + END IF; + + IF addr_street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(addr_street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT place_id from getNearestNamedRoadFeature(partition, centroid, address_street_word_ids) LOOP + parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF parent_place_id IS NULL AND addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT place_id from getNearestNamedPlaceFeature(partition, centroid, address_street_word_ids) LOOP + parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF parent_place_id is null THEN + FOR location IN SELECT place_id FROM placex + WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26 + ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+ + ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+ + ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1 + LOOP + parent_place_id := location.place_id; + END LOOP; + END IF; + + IF parent_place_id is null THEN + RETURN 0; + END IF; -CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER + RETURN parent_place_id; +END; +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT, + parent_id BIGINT, partition INTEGER, + country_code TEXT, geometry_sector INTEGER, + defpostalcode TEXT, geom GEOMETRY) RETURNS INTEGER AS $$ DECLARE @@ -766,10 +835,12 @@ DECLARE linegeo GEOMETRY; splitline GEOMETRY; sectiongeo GEOMETRY; - search_place_id BIGINT; - defpostalcode TEXT; + pointgeo GEOMETRY; BEGIN + delete from placex where osm_type = 'W' and osm_id = wayid + and class = 'place' and type = 'address'; + IF interpolationtype = 'odd' OR interpolationtype = 'even' THEN stepsize := 2; ELSEIF interpolationtype = 'all' THEN @@ -786,13 +857,7 @@ BEGIN RETURN 0; END IF; - select postcode, geometry from placex where osm_type = 'W' and osm_id = wayid - INTO defpostalcode, linegeo; - - IF ST_GeometryType(linegeo) != 'ST_LineString' THEN - RETURN 0; - END IF; - + linegeo := geom; startnumber := NULL; newpoints := 0; @@ -802,9 +867,10 @@ BEGIN -- it is guaranteed to be the original node. For place/house types use the -- one with the smallest id because the original node was created first. -- Ignore all nodes marked for deletion. (Might happen when the type changes.) - select place_id from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and indexed_status < 100 order by (type = 'house'),place_id limit 1 INTO search_place_id; - IF search_place_id IS NOT NULL THEN - select * from placex where place_id = search_place_id INTO nextnode; + select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT + and indexed_status < 100 + order by (type = 'address'),place_id limit 1 INTO nextnode; + IF nextnode.place_id IS NOT NULL THEN IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN -- Make sure that the point is actually on the line. That might @@ -833,23 +899,30 @@ BEGIN startnumber := startnumber + stepsize; -- correct for odd/even - IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN + IF (interpolationtype = 'odd' AND startnumber%2 = 0) + OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN startnumber := startnumber - 1; END IF; endnumber := endnumber - 1; - delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id and type = 'house' and place_id != prevnode.place_id; + -- keep for compatibility with previous versions + delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id + and place_id != prevnode.place_id and class = 'place' + and type = 'house'; FOR housenum IN startnumber..endnumber BY stepsize LOOP - -- this should really copy postcodes but it puts a huge burden on - -- the system for no big benefit ideally postcodes should move up to the way - insert into placex (osm_type, osm_id, class, type, admin_level, - housenumber, street, addr_place, isin, postcode, - country_code, parent_place_id, rank_address, rank_search, - indexed_status, geometry) - values ('N', prevnode.osm_id, 'place', 'house', prevnode.admin_level, - housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode), - prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, - 1, ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float)); + pointgeo := ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float); + insert into placex (place_id, partition, osm_type, osm_id, + class, type, admin_level, housenumber, + postcode, + country_code, parent_place_id, rank_address, rank_search, + indexed_status, indexed_date, geometry_sector, + calculated_country_code, centroid, geometry) + values (nextval('seq_place'), partition, 'W', wayid, + 'place', 'address', prevnode.admin_level, housenum, + coalesce(prevnode.postcode, defpostalcode), + prevnode.country_code, parent_id, 30, 30, + 0, now(), geometry_sector, country_code, + pointgeo, pointgeo); newpoints := newpoints + 1; --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum; END LOOP; @@ -886,9 +959,9 @@ DECLARE 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'); + -- ignore interpolated addresses + IF NEW.class = 'place' and NEW.type = 'address' THEN + RETURN NEW; END IF; -- just block these @@ -1057,8 +1130,6 @@ BEGIN NEW.rank_address := NEW.rank_search; ELSEIF NEW.type in ('houses') THEN -- can't guarantee all required nodes loaded yet due to caching in osm2pgsql - -- insert new point into place for each derived building - --i := create_interpolation(NEW.osm_id, NEW.housenumber); NEW.rank_search := 28; NEW.rank_address := 0; END IF; @@ -1197,6 +1268,7 @@ BEGIN END IF; END IF; + -- add to tables for special search -- Note: won't work on initial import because the classtype tables -- do not yet exist. It won't hurt either. @@ -1276,6 +1348,11 @@ BEGIN RETURN NEW; END IF; + -- ignore interpolated addresses + IF NEW.class = 'place' and NEW.type = 'address' THEN + RETURN NEW; + END IF; + --DEBUG: RAISE WARNING 'placex_update % %',NEW.osm_type,NEW.osm_id; --RAISE WARNING '%',NEW.place_id; @@ -1301,11 +1378,6 @@ BEGIN RETURN NEW; END IF; - IF NEW.class = 'place' AND NEW.type = 'houses' THEN - i := create_interpolation(NEW.osm_id, NEW.housenumber); - RETURN NEW; - END IF; - -- Speed up searches - just use the centroid of the feature -- cheaper but less acurate place_centroid := ST_PointOnSurface(NEW.geometry); @@ -1334,6 +1406,18 @@ BEGIN END IF; NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid); + -- interpolations + IF NEW.class = 'place' AND NEW.type = 'houses'THEN + IF NEW.osm_type = 'W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN + NEW.parent_place_id := get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place, + NEW.partition, place_centroid, NEW.geometry); + i := create_interpolation(NEW.osm_id, NEW.housenumber, NEW.parent_place_id, + NEW.partition, NEW.calculated_country_code, + NEW.geometry_sector, NEW.postcode, NEW.geometry); + END IF; + RETURN NEW; + END IF; + -- waterway ways are linked when they are part of a relation and have the same class/type IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] @@ -1404,41 +1488,67 @@ BEGIN NEW.parent_place_id := null; - -- to do that we have to find our parent road - -- Copy data from linked items (points on ways, addr:street links, relations) - -- Note that addr:street links can only be indexed once the street itself is indexed - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN + -- if we have a POI and there is no address information, + -- see if we can get it from a surrounding building + IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL + AND NEW.housenumber IS NULL THEN + FOR location IN select * from placex where ST_Covers(geometry, place_centroid) + and (housenumber is not null or street is not null or addr_place is not null) + and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + limit 1 + LOOP + NEW.housenumber := location.housenumber; + NEW.street := location.street; + NEW.addr_place := location.addr_place; + END LOOP; + END IF; - -- if there is no address information, see if we can get it from a surrounding building - IF NEW.street IS NULL AND NEW.addr_place IS NULL AND NEW.housenumber IS NULL THEN - FOR location IN select * from placex where ST_Covers(geometry, place_centroid) and rank_search > 28 and (housenumber is not null or street is not null or addr_place is not null) AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') - LOOP - NEW.housenumber := location.housenumber; - NEW.street := location.street; - NEW.addr_place := location.addr_place; - END LOOP; - END IF; + -- We have to find our parent road. + -- Copy data from linked items (points on ways, addr:street links, relations) - -- Is this node part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id] + -- Is this object part of a relation? + FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id] LOOP -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN + IF relation.tags @> ARRAY['associatedStreet'] THEN FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN --RAISE WARNING 'node in relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint + SELECT place_id from placex where osm_type = 'W' + and osm_id = substring(relation.members[i],2,200)::bigint and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; - END LOOP; + END LOOP; + + + -- Note that addr:street links can only be indexed once the street itself is indexed + IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN --RAISE WARNING 'x1'; -- Is this node part of a way? - FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id] LOOP ---RAISE WARNING '%', way; - FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id + FOR location IN select p.* from placex p, planet_osm_ways w + where p.osm_type = 'W' and p.rank_search >= 26 + and p.geometry && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes) LOOP --RAISE WARNING '%', location; -- Way IS a road then we are on it - that must be our road @@ -1447,6 +1557,11 @@ BEGIN NEW.parent_place_id := location.place_id; END IF; + -- If this way is a street interpolation line then it is probably as good as we are going to get + IF NEW.parent_place_id IS NULL AND location.class = 'place' and location.type='houses' THEN + NEW.parent_place_id := location.parent_place_id; + END IF; + -- Is the WAY part of a relation IF NEW.parent_place_id IS NULL THEN FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id] @@ -1462,73 +1577,29 @@ BEGIN END LOOP; END IF; END LOOP; - END IF; - - -- If the way contains an explicit name of a street copy it - -- Slightly less strict then above because data is copied from any object. - IF NEW.street IS NULL AND NEW.addr_place IS NULL THEN ---RAISE WARNING 'node in way that has a streetname %',location; - NEW.street := location.street; - NEW.addr_place := location.addr_place; END IF; - -- If this way is a street interpolation line then it is probably as good as we are going to get - IF NEW.parent_place_id IS NULL AND NEW.street IS NULL AND NEW.addr_place IS NULL AND location.class = 'place' and location.type='houses' THEN - -- Try and find a way that is close roughly parellel to this line - FOR relation IN SELECT place_id FROM placex - WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26 - and st_geometrytype(location.geometry) in ('ST_LineString') - ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0))+ - ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0.5))+ - ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,1))) ASC limit 1 - LOOP ---RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation; - NEW.parent_place_id := relation.place_id; - END LOOP; + -- If the way mentions a street or place address, try that for parenting. + IF NEW.parent_place_id IS NULL AND location.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(location.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := linkedplacex.place_id; + END LOOP; + END IF; END IF; - END LOOP; - END LOOP; - - END IF; - ---RAISE WARNING 'x2'; - - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN - -- Is this way part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['w'||NEW.osm_id] - LOOP - -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN ---RAISE WARNING 'way that is in a relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 and name is not null INTO NEW.parent_place_id; - END IF; - END LOOP; + IF NEW.parent_place_id IS NULL AND location.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(location.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := linkedplacex.place_id; + END LOOP; + END IF; END IF; - END LOOP; - END IF; - ---RAISE WARNING 'x3 %',NEW.parent_place_id; - IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; + END LOOP; - IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; END IF; --RAISE WARNING 'x4 %',NEW.parent_place_id; @@ -2016,6 +2087,11 @@ BEGIN -- mark for delete UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type; + -- interpolations are special + IF OLD.class = 'place' and OLD.type = 'houses' THEN + UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = 'place' and type = 'address'; + END IF; + RETURN OLD; END; @@ -2295,6 +2371,11 @@ BEGIN END IF; + -- for interpolations invalidate all nodes on the line + IF NEW.class = 'place' and NEW.type = 'houses' and NEW.osm_type = 'W' THEN + update placex p set indexed_status = 2 from planet_osm_ways w where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes); + END IF; + -- Abort the add (we modified the existing place instead) RETURN NULL; diff --git a/sql/tables.sql b/sql/tables.sql index 428eadc2..dcee4070 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -41,6 +41,8 @@ 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}" ; +GRANT SELECT ON TABLE country_name TO "{www-user}"; +GRANT SELECT ON TABLE gb_postcode TO "{www-user}"; drop table IF EXISTS word; CREATE TABLE word ( diff --git a/tests/README.md b/tests/README.md index 0e4c269b..1b1663c3 100644 --- a/tests/README.md +++ b/tests/README.md @@ -15,13 +15,17 @@ Prerequisites * nose (https://nose.readthedocs.org) * pytidylib (http://countergram.com/open-source/pytidylib) * haversine (https://github.com/mapado/haversine) + * shapely (https://github.com/Toblerity/Shapely) Usage ===== * get prerequisites + + # on a fresh Ubuntu LTS 14.04 you'll also need these system-wide packages + [sudo] apt-get install python-dev python-pip python-Levenshtein tidy - [sudo] pip install lettuce nose pytidylib haversine psycopg2 + [sudo] pip install lettuce nose pytidylib haversine psycopg2 shapely * run the tests diff --git a/tests/features/db/import/interpolation.feature b/tests/features/db/import/interpolation.feature index ad4bdf85..d70bf18a 100644 --- a/tests/features/db/import/interpolation.feature +++ b/tests/features/db/import/interpolation.feature @@ -14,13 +14,9 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 2 | 1,1 | 4 | 1,1.0005 - And node 2 expands to housenumbers - | housenumber | centroid - | 6 | 1,1.001 Scenario: Simple even two point interpolation with zero beginning Given the place nodes @@ -34,15 +30,11 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 0 | 1,1 | 2 | 1,1.00025 | 4 | 1,1.0005 | 6 | 1,1.00075 - And node 2 expands to housenumbers - | housenumber | centroid - | 8 | 1,1.001 Scenario: Backwards even two point interpolation Given the place nodes @@ -56,13 +48,9 @@ Feature: Import of address interpolations | id | nodes | 1 | 2,1 When importing - Then node 1 expands to housenumbers - | housenumber | centroid - | 2 | 1,1 - And node 2 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid | 4 | 1,1.0005 - | 6 | 1,1.001 Scenario: Even two point interpolation with odd beginning Given the place nodes @@ -76,14 +64,10 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 11 | 1,1 | 12 | 1,1.0002 | 14 | 1,1.0006 - And node 2 expands to housenumbers - | housenumber | centroid - | 16 | 1,1.001 Scenario: Even two point interpolation with odd end Given the place nodes @@ -97,14 +81,10 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 10 | 1,1 | 12 | 1,1.0004 | 14 | 1,1.0008 - And node 2 expands to housenumbers - | housenumber | centroid - | 15 | 1,1.001 Scenario: Reverse even two point interpolation with odd beginning Given the place nodes @@ -118,14 +98,10 @@ Feature: Import of address interpolations | id | nodes | 1 | 2,1 When importing - Then node 1 expands to housenumbers - | housenumber | centroid - | 11 | 1,1 - And node 2 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid | 12 | 1,1.0002 | 14 | 1,1.0006 - | 16 | 1,1.001 Scenario: Reverse even two point interpolation with odd end Given the place nodes @@ -139,14 +115,10 @@ Feature: Import of address interpolations | id | nodes | 1 | 2,1 When importing - Then node 1 expands to housenumbers - | housenumber | centroid - | 10 | 1,1 - And node 2 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid | 12 | 1,1.0004 | 14 | 1,1.0008 - | 15 | 1,1.001 Scenario: Simple odd two point interpolation Given the place nodes @@ -160,16 +132,12 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 1 | 1,1 | 3 | 1,1.0002 | 5 | 1,1.0004 | 7 | 1,1.0006 | 9 | 1,1.0008 - And node 2 expands to housenumbers - | housenumber | centroid - | 11 | 1,1.001 Scenario: Odd two point interpolation with even beginning Given the place nodes @@ -183,14 +151,10 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 2 | 1,1 | 3 | 1,1.0002 | 5 | 1,1.0006 - And node 2 expands to housenumbers - | housenumber | centroid - | 7 | 1,1.001 Scenario: Simple all two point interpolation Given the place nodes @@ -204,13 +168,9 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 1 | 1,1 | 2 | 1,1.0005 - And node 2 expands to housenumbers - | housenumber | centroid - | 3 | 1,1.001 Scenario: Simple numbered two point interpolation Given the place nodes @@ -224,13 +184,9 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 3 | 1,1 | 6 | 1,1.0005 - And node 2 expands to housenumbers - | housenumber | centroid - | 9 | 1,1.001 Scenario: Even two point interpolation with intermediate empty node Given the place nodes @@ -244,15 +200,11 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,3,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 2 | 1,1 | 4 | 1,1.0005 | 6 | 1,1.001 | 8 | 1.0005,1.001 - And node 2 expands to housenumbers - | housenumber | centroid - | 10 | 1.001,1.001 Scenario: Even two point interpolation with intermediate duplicated empty node @@ -267,15 +219,11 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,3,3,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 2 | 1,1 | 4 | 1,1.0005 | 6 | 1,1.001 | 8 | 1.0005,1.001 - And node 2 expands to housenumbers - | housenumber | centroid - | 10 | 1.001,1.001 Scenario: Simple even three point interpolation Given the place nodes @@ -290,16 +238,9 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,3,2 When importing - Then node 1 expands to housenumbers - | housenumber | centroid - | 2 | 1,1 - Then node 3 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 4 | 1,1.001 | 6 | 1.0005,1.001 - And node 2 expands to housenumbers - | housenumber | centroid - | 8 | 1.001,1.001 Scenario: Even three point interpolation with odd center point Given the place nodes @@ -314,17 +255,10 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,3,2 When importing - Then node 1 expands to housenumbers + Then way 1 expands to housenumbers | housenumber | centroid - | 2 | 1,1 | 4 | 1,1.0004 | 6 | 1,1.0008 - Then node 3 expands to housenumbers - | housenumber | centroid - | 7 | 1,1.001 - And node 2 expands to housenumbers - | housenumber | centroid - | 8 | 1.001,1.001 Scenario: Interpolation on self-intersecting way Given the place nodes @@ -339,17 +273,9 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2,3,2 When importing - Then node 1 expands to housenumbers + Then way 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 @@ -364,12 +290,73 @@ Feature: Import of address interpolations | id | nodes | 1 | 1,2,3,2 When importing - Then node 1 expands to housenumbers + Then way 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 + + + Scenario: addr:street on interpolation way + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-middle-w + | 2 | place | house | 6 | :n-middle-e + | 3 | place | house | 12 | :n-middle-w + | 4 | place | house | 16 | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | street | geometry + | 10 | place | houses | even | | :w-middle + | 11 | place | houses | even | Cloud Street | :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | tertiary | 'name' : 'Sun Way' | :w-north + | 3 | highway | tertiary | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + | 11 | 3,200,201,202,4 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | N3 | W3 + | N4 | W3 + | W10 | W2 + | W11 | W3 + And way 10 expands exactly to housenumbers 4 + And way 11 expands exactly to housenumbers 14 + + Scenario: addr:street on housenumber way + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | street | geometry + | 1 | place | house | 2 | | :n-middle-w + | 2 | place | house | 6 | | :n-middle-e + | 3 | place | house | 12 | Cloud Street | :n-middle-w + | 4 | place | house | 16 | Cloud Street | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | geometry + | 10 | place | houses | even | :w-middle + | 11 | place | houses | even | :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | tertiary | 'name' : 'Sun Way' | :w-north + | 3 | highway | tertiary | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + | 11 | 3,200,201,202,4 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | N3 | W3 + | N4 | W3 + | W10 | W2 + | W11 | W3 + And way 10 expands exactly to housenumbers 4 + And way 11 expands exactly to housenumbers 14 diff --git a/tests/features/db/import/parenting.feature b/tests/features/db/import/parenting.feature index dd155c45..36754f84 100644 --- a/tests/features/db/import/parenting.feature +++ b/tests/features/db/import/parenting.feature @@ -389,7 +389,7 @@ Feature: Parenting of objects | N3 | W2 | None | nowhere | None ### Scenario 20 - Scenario: POIs parent a road if and only if they are attached to it + Scenario: POIs parent a road if they are attached to it Given the scene points-on-roads And the named place nodes | osm_id | class | type | street | geometry @@ -408,8 +408,8 @@ Feature: Parenting of objects When importing Then table placex contains | object | parent_place_id - | N1 | W2 - | N2 | W1 + | N1 | W1 + | N2 | W2 | N3 | W1 | N4 | W2 diff --git a/tests/features/db/update/interpolation.feature b/tests/features/db/update/interpolation.feature new file mode 100644 index 00000000..bfc93b12 --- /dev/null +++ b/tests/features/db/update/interpolation.feature @@ -0,0 +1,167 @@ +@DB +Feature: Update of address interpolations + Test the interpolated address are updated correctly + + Scenario: addr:street added to interpolation + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-middle-w + | 2 | place | house | 6 | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | geometry + | 10 | place | houses | even | :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | unclassified | 'name' : 'Sun Way' | :w-north + | 3 | highway | unclassified | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | W10 | W2 + And way 10 expands exactly to housenumbers 4 + When updating place ways + | osm_id | class | type | housenumber | street | geometry + | 10 | place | houses | even | Cloud Street | :w-middle + Then way 10 expands exactly to housenumbers 4 + And table placex contains + | object | parent_place_id + | N1 | W3 + | N2 | W3 + | W10 | W3 + + @Fail + Scenario: addr:street added to housenumbers + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-middle-w + | 2 | place | house | 6 | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | geometry + | 10 | place | houses | even | :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | unclassified | 'name' : 'Sun Way' | :w-north + | 3 | highway | unclassified | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | W10 | W2 + And way 10 expands exactly to housenumbers 4 + When updating place nodes + | osm_id | class | type | street | housenumber | geometry + | 1 | place | house | Cloud Street| 2 | :n-middle-w + | 2 | place | house | Cloud Street| 6 | :n-middle-e + Then way 10 expands exactly to housenumbers 4 + And table placex contains + | object | parent_place_id + | N1 | W3 + | N2 | W3 + | W10 | W3 + + + Scenario: interpolation tag removed + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-middle-w + | 2 | place | house | 6 | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | geometry + | 10 | place | houses | even | :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | unclassified | 'name' : 'Sun Way' | :w-north + | 3 | highway | unclassified | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | W10 | W2 + And way 10 expands exactly to housenumbers 4 + When marking for delete W10 + Then way 10 expands to no housenumbers + And table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + + + Scenario: referenced road added + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-middle-w + | 2 | place | house | 6 | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | street | geometry + | 10 | place | houses | even | Cloud Street| :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | unclassified | 'name' : 'Sun Way' | :w-north + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | W10 | W2 + And way 10 expands exactly to housenumbers 4 + When updating place ways + | osm_id | class | type | name | geometry + | 3 | highway | unclassified | 'name' : 'Cloud Street' | :w-south + Then way 10 expands exactly to housenumbers 4 + And table placex contains + | object | parent_place_id + | N1 | W3 + | N2 | W3 + | W10 | W3 + + + Scenario: referenced road deleted + Given the scene parallel-road + And the place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-middle-w + | 2 | place | house | 6 | :n-middle-e + And the place ways + | osm_id | class | type | housenumber | street | geometry + | 10 | place | houses | even | Cloud Street| :w-middle + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | unclassified | 'name' : 'Sun Way' | :w-north + | 3 | highway | unclassified | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 10 | 1,100,101,102,2 + When importing + Then table placex contains + | object | parent_place_id + | N1 | W3 + | N2 | W3 + | W10 | W3 + And way 10 expands exactly to housenumbers 4 + When marking for delete W3 + Then way 10 expands exactly to housenumbers 4 + And table placex contains + | object | parent_place_id + | N1 | W2 + | N2 | W2 + | W10 | W2 diff --git a/tests/scenes/data/parallel-road.wkt b/tests/scenes/data/parallel-road.wkt new file mode 100644 index 00000000..355af300 --- /dev/null +++ b/tests/scenes/data/parallel-road.wkt @@ -0,0 +1,5 @@ +n-middle-w | POINT(1.0065316 2.0003381) +n-middle-e | POINT(1.007236 2.0003408) +w-south | LINESTRING(1.0065324 2.0001892,1.006676 2.0002786,1.0068195 2.0002786,1.0069171 2.0002515,1.0070417 2.0001892,1.0072422 2.000173) +w-middle | LINESTRING(1.0065316 2.0003381,1.006686 2.0004248,1.0069 2.0004167,1.007236 2.0003408) +w-north | LINESTRING(1.0065397 2.000418,1.0066833 2.0005074,1.0068269 2.0005074,1.0069244 2.0004803,1.007049 2.000418,1.0072495 2.0004018) diff --git a/tests/scenes/data/roads.osm b/tests/scenes/data/roads.osm index b642fa62..ed7cd62a 100644 --- a/tests/scenes/data/roads.osm +++ b/tests/scenes/data/roads.osm @@ -1,294 +1,347 @@ - + - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + - - + + - + - - + + - + - - + + - - - - - - + + + + + + - - + + - + - + - + - + - + - - - - - - - - + + + + + + + + - + - + - + - + - + - + - + - + - + - + - + - - - - - - - - - - + + + + + + + + + + - + - + - + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - + + + + - - - - - - - + + + + + + + - - - - - - + + + + + + - - - - - - + + + + + + - - - - - + + - + + + + - - - - + + + + - - - - - - - + + + + + + + - - - - - - - - + + + + + + + + - - - + + + - - - - - - - + + + + + + + - - - - - + + + + + - - - - + + + + - - - - + + + + - - - - + + + + - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/tests/steps/db_results.py b/tests/steps/db_results.py index 2566418e..c3ac9445 100644 --- a/tests/steps/db_results.py +++ b/tests/steps/db_results.py @@ -119,10 +119,10 @@ def check_search_name_content(step): else: raise Exception("Cannot handle field %s in search_name table" % (k, )) -@step(u'node (\d+) expands to housenumbers') +@step(u'way (\d+) expands to housenumbers') def check_interpolated_housenumbers(step, nodeid): """Check that the exact set of housenumbers has been entered in - placex for the given source node. Expected are tow columns: + placex for the given source node. Expected are two columns: housenumber and centroid """ numbers = {} @@ -132,7 +132,8 @@ def check_interpolated_housenumbers(step, nodeid): cur = world.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute("""SELECT DISTINCT housenumber, ST_X(centroid) as clat, ST_Y(centroid) as clon - FROM placex WHERE osm_type = 'N' and osm_id = %s""", + FROM placex WHERE osm_type = 'W' and osm_id = %s + and class = 'place' and type = 'address'""", (int(nodeid),)) assert_equals(len(numbers), cur.rowcount) for r in cur: @@ -140,6 +141,32 @@ def check_interpolated_housenumbers(step, nodeid): world.match_geometry((r['clat'], r['clon']), numbers[r["housenumber"]]) del numbers[r["housenumber"]] +@step(u'way (\d+) expands exactly to housenumbers ([0-9,]*)') +def check_interpolated_housenumber_list(step, nodeid, numberlist): + """ Checks that the interpolated house numbers corresponds + to the given list. + """ + expected = numberlist.split(','); + cur = world.conn.cursor() + cur.execute("""SELECT housenumber FROM placex + WHERE osm_type = 'W' and osm_id = %s + and class = 'place' and type = 'address'""", (int(nodeid),)) + for r in cur: + assert_in(r[0], expected, "Unexpected house number %s for node %s." % (r[0], nodeid)) + expected.remove(r[0]) + assert_equals(0, len(expected), "Missing house numbers for way %s: %s" % (nodeid, expected)) + +@step(u'way (\d+) expands to no housenumbers') +def check_no_interpolated_housenumber_list(step, nodeid): + """ Checks that the interpolated house numbers corresponds + to the given list. + """ + cur = world.conn.cursor() + cur.execute("""SELECT housenumber FROM placex + WHERE osm_type = 'W' and osm_id = %s + and class = 'place' and type = 'address'""", (int(nodeid),)) + res = [r[0] for r in cur] + assert_equals(0, len(res), "Unexpected house numbers for way %s: %s" % (nodeid, res)) @step(u'table search_name has no entry for (.*)') def check_placex_missing(step, osmid): diff --git a/tests/steps/db_setup.py b/tests/steps/db_setup.py index 7d1ea8d3..14f17756 100644 --- a/tests/steps/db_setup.py +++ b/tests/steps/db_setup.py @@ -213,8 +213,8 @@ def import_database(step): world.run_nominatim_script('setup', 'create-functions', 'create-partition-functions') cur = world.conn.cursor() cur.execute("""insert into placex (osm_type, osm_id, class, type, name, admin_level, - housenumber, street, addr_place, isin, postcode, country_code, extratags, - geometry) select * from place""") + housenumber, street, addr_place, isin, postcode, country_code, extratags, + geometry) select * from place""") world.conn.commit() world.run_nominatim_script('setup', 'index', 'index-noanalyse') #world.db_dump_table('placex') diff --git a/tests/steps/terrain.py b/tests/steps/terrain.py index 746485ac..e9561d1a 100644 --- a/tests/steps/terrain.py +++ b/tests/steps/terrain.py @@ -94,7 +94,7 @@ def get_placeid(oid): q = 'SELECT place_id FROM placex where osm_type = %s and osm_id = %s and class = %s' params = (osmtype, osmid, cls) cur.execute(q, params) - assert_equals (cur.rowcount, 1) + assert_equals(cur.rowcount, 1, "%d rows found for place %s" % (cur.rowcount, oid)) return cur.fetchone()[0] diff --git a/utils/specialphrases.php b/utils/specialphrases.php index 13480695..97b60dde 100755 --- a/utils/specialphrases.php +++ b/utils/specialphrases.php @@ -105,7 +105,7 @@ echo " tablespace ".CONST_Tablespace_Aux_Index; echo ";\n"; - echo "GRANT SELECT ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." TO \"www-data\";\n"; + echo "GRANT SELECT ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1]).' TO "'.CONST_Database_Web_User."\";\n"; }