From 524bd473150d94dd3594679cfeca3e4231d5bca9 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sat, 28 May 2016 18:18:18 +0200 Subject: [PATCH] fill place and osmline independently for interpolations on update interpolation lines may be missing in osmline when the interpolation is broken, so we cannot conclude that a way is not in place, just because there are no entries in location_property_osmline. --- sql/functions.sql | 101 +++++++++--------- .../features/db/update/interpolation.feature | 25 +++++ 2 files changed, 77 insertions(+), 49 deletions(-) diff --git a/sql/functions.sql b/sql/functions.sql index 3403dfab..dc5c754b 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -1258,6 +1258,7 @@ BEGIN --RAISE WARNING 'before low level% %', NEW.place_id, NEW.rank_search; + -- --------------------------------------------------------------------------- -- For low level elements we inherit from our parent road IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN @@ -1322,47 +1323,30 @@ BEGIN END IF; END IF; + -- Is this node part of an interpolation? + IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN + FOR location IN + SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x + WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes) + LIMIT 1 + LOOP + NEW.parent_place_id := location.parent_place_id; + END LOOP; + END IF; + + -- Is this node part of a way? IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN ---RAISE WARNING 'x1'; - -- Is this node part of a way? search for the way in placex AND location_property_osmline (for interpolation lines) - FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.class, p.type, p.rank_search, p.street, p.addr_place from placex p, planet_osm_ways w + FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.street, p.addr_place from placex p, planet_osm_ways w where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes) - UNION - select q.place_id, q.osm_id, q.parent_place_id, 'place' as class, 'houses' as type, 30 as rank_search, null as street, - null as addr_place from location_property_osmline q, planet_osm_ways x - where q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes) LOOP - ---RAISE WARNING '%', location; + -- Way IS a road then we are on it - that must be our road - IF location.rank_search = 26 AND NEW.parent_place_id IS NULL THEN + IF location.rank_search < 28 AND NEW.parent_place_id IS NULL THEN --RAISE WARNING 'node in way that is a street %',location; 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] - 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 'node in 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; - END IF; - END LOOP; - END IF; - -- 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)); @@ -1382,6 +1366,23 @@ BEGIN END IF; 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] + 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 'node in 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; + END IF; + END LOOP; + END IF; + END LOOP; END IF; @@ -1452,6 +1453,9 @@ BEGIN -- RAISE WARNING ' INDEXING Started:'; -- RAISE WARNING ' INDEXING: %',NEW; + -- --------------------------------------------------------------------------- + -- Full indexing + IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN -- see if we have any special relation members @@ -1936,22 +1940,29 @@ BEGIN DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id; DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id; - -- If there isn't an existing item in location_property_osmline, just add it - IF existingline.osm_id IS NULL THEN - -- insert new line into location_property_osmline, use function insert_osmline + -- update method for interpolation lines: delete all old interpolation lines with same osm_id (update on place) and insert the new one(s) (they can be split up, if they have > 2 nodes) + IF existingline.osm_id IS NOT NULL THEN + delete from location_property_osmline where osm_id = NEW.osm_id; + END IF; + + -- for interpolations invalidate all nodes on the line + 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); + -- insert new line into location_property_osmline, use function insert_osmline + + + IF existing.osm_type IS NULL THEN i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry); - RETURN NEW; + return NEW; 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, '') + IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') OR coalesce(existing.street, '') != coalesce(NEW.street, '') OR coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '') OR coalesce(existing.isin, '') != coalesce(NEW.isin, '') OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '') OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') - OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15) OR existing.geometry::text != NEW.geometry::text THEN @@ -1968,15 +1979,7 @@ BEGIN geometry = NEW.geometry where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type; - -- update method for interpolation lines: delete all old interpolation lines with same osm_id (update on place) and insert the new one(s) (they can be split up, if they have > 2 nodes) - delete from location_property_osmline where osm_id = NEW.osm_id; - i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, - NEW.postcode, NEW.country_code, NEW.geometry); - - -- for interpolations invalidate all nodes on the line - 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); + i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry); END IF; RETURN NULL; diff --git a/tests/features/db/update/interpolation.feature b/tests/features/db/update/interpolation.feature index ccde8e23..66367b10 100644 --- a/tests/features/db/update/interpolation.feature +++ b/tests/features/db/update/interpolation.feature @@ -230,4 +230,29 @@ Feature: Update of address interpolations | object | parent_place_id | W1 | W2 + Scenario: housenumbers added to interpolation + Given the scene building-with-parallel-streets + And the place ways + | osm_id | class | type | name | geometry + | 2 | highway | unclassified | 'name' : 'Cloud Street' | :w-south + And the ways + | id | nodes + | 1 | 1,100,101,102,2 + And the place ways + | osm_id | class | type | housenumber | geometry + | 1 | place | houses | even | :w-north + When importing + Then table location_property_osmline has no entry for W1 + When updating place nodes + | osm_id | class | type | housenumber | geometry + | 1 | place | house | 2 | :n-north-w + | 2 | place | house | 6 | :n-north-e + And updating place ways + | osm_id | class | type | housenumber | street | geometry + | 1 | place | houses | even | Cloud Street| :w-north + Then table location_property_osmline contains + | object | parent_place_id | startnumber | endnumber + | W1 | W2 | 2 | 6 + + -- 2.45.1