From 638ed15ada6c62d34bf5ac82e99255611b90c7d8 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 25 Jan 2022 10:14:05 +0100 Subject: [PATCH] improve handling von updates on nodes in interpolations Use the same update mechanism as for updates on the interpolations themselves. Updates must solely happen in place_insert as this is the place where actual changes of the data happen. --- lib-sql/functions/interpolation.sql | 44 +++++++++++++------- lib-sql/functions/place_triggers.sql | 58 +++++++++++---------------- lib-sql/functions/placex_triggers.sql | 5 +-- lib-sql/indices.sql | 4 ++ 4 files changed, 58 insertions(+), 53 deletions(-) diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index a8ef3771..098db26c 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -82,24 +82,38 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION osmline_reinsert(node_id BIGINT, geom GEOMETRY) - RETURNS BOOLEAN +CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE, + geom GEOMETRY) + RETURNS INT AS $$ DECLARE - existingline RECORD; + existing BIGINT[]; BEGIN - SELECT w.id FROM planet_osm_ways w, location_property_osmline p - WHERE p.linegeo && geom and p.osm_id = w.id and p.indexed_status = 0 - and node_id = any(w.nodes) INTO existingline; - - IF existingline.id is not NULL THEN - DELETE FROM location_property_osmline WHERE osm_id = existingline.id; - INSERT INTO location_property_osmline (osm_id, address, linegeo) - SELECT osm_id, address, geometry FROM place - WHERE osm_type = 'W' and osm_id = existingline.id; - END IF; - - RETURN true; + -- Get the existing entry from the interpolation table. + SELECT array_agg(place_id) INTO existing + FROM location_property_osmline WHERE osm_id = way_id; + + IF existing IS NULL or array_length(existing, 1) = 0 THEN + INSERT INTO location_property_osmline (osm_id, address, linegeo) + VALUES (way_id, addr, geom); + ELSE + -- Update the interpolation table: + -- The first entry gets the original data, all other entries + -- are removed and will be recreated on indexing. + -- (An interpolation can be split up, if it has more than 2 address nodes) + UPDATE location_property_osmline + SET address = addr, + linegeo = geom, + startnumber = null, + indexed_status = 1 + WHERE place_id = existing[1]; + IF array_length(existing, 1) > 1 THEN + DELETE FROM location_property_osmline + WHERE place_id = any(existing[2:]); + END IF; + END IF; + + RETURN 1; END; $$ LANGUAGE plpgsql; diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql index 7ea08840..a472d26d 100644 --- a/lib-sql/functions/place_triggers.sql +++ b/lib-sql/functions/place_triggers.sql @@ -14,7 +14,7 @@ DECLARE existing RECORD; existingplacex RECORD; existingline BIGINT[]; - result BOOLEAN; + interpol RECORD; BEGIN {% if debug %} RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry); @@ -57,29 +57,7 @@ BEGIN IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN - -- Get the existing entry from the interpolation table. - SELECT array_agg(place_id) INTO existingline - FROM location_property_osmline WHERE osm_id = NEW.osm_id; - - IF existingline IS NULL or array_length(existingline, 1) = 0 THEN - INSERT INTO location_property_osmline (osm_id, address, linegeo) - VALUES (NEW.osm_id, NEW.address, NEW.geometry); - ELSE - -- Update the interpolation table: - -- The first entry gets the original data, all other entries - -- are removed and will be recreated on indexing. - -- (An interpolation can be split up, if it has more than 2 address nodes) - UPDATE location_property_osmline - SET address = NEW.address, - linegeo = NEW.geometry, - startnumber = null, - indexed_status = 1 - WHERE place_id = existingline[1]; - IF array_length(existingline, 1) > 1 THEN - DELETE FROM location_property_osmline - WHERE place_id = any(existingline[2:]); - END IF; - END IF; + PERFORM reinsert_interpolation(NEW.osm_id, NEW.address, NEW.geometry); -- Now invalidate all address nodes on the line. -- They get their parent from the interpolation. @@ -166,6 +144,28 @@ BEGIN RETURN null; END IF; + -- If an address node is part of a interpolation line and changes or is + -- newly inserted (happens when the node already existed but now gets address + -- information), then mark the interpolation line for reparenting. + -- (Already here, because interpolation lines are reindexed before nodes, + -- so in the second call it would be too late.) + IF NEW.osm_type='N' + and coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore) + THEN + FOR interpol IN + SELECT DISTINCT osm_id, address, geometry FROM place, planet_osm_ways w + WHERE NEW.geometry && place.geometry + and place.osm_type = 'W' + and exists (SELECT * FROM location_property_osmline + WHERE osm_id = place.osm_id + and indexed_status in (0, 2)) + and w.id = place.osm_id and NEW.osm_id = any (w.nodes) + LOOP + PERFORM reinsert_interpolation(interpol.osm_id, interpol.address, + interpol.geometry); + END LOOP; + END IF; + -- Get the existing placex entry. SELECT * INTO existingplacex FROM placex @@ -288,16 +288,6 @@ BEGIN geometry = NEW.geometry WHERE place_id = existingplacex.place_id; - -- If an address node which is part of a interpolation line changes - -- mark this line for reparenting. - -- (Already here, because interpolation lines are reindexed before nodes, - -- so in the second call it would be too late.) - IF NEW.osm_type='N' - and coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore) - THEN - result:= osmline_reinsert(NEW.osm_id, NEW.geometry); - END IF; - -- Invalidate linked places: they potentially get a new name and addresses. IF existingplacex.linked_place_id is not NULL THEN UPDATE placex x diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 1b96f87e..8ad8a336 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -626,10 +626,7 @@ BEGIN {% if not disable_diff_updates %} -- The following is not needed until doing diff updates, and slows the main index process down - IF NEW.osm_type = 'N' and NEW.rank_search > 28 THEN - -- might be part of an interpolation - result := osmline_reinsert(NEW.osm_id, NEW.geometry); - ELSEIF NEW.rank_address > 0 THEN + IF NEW.rank_address > 0 THEN IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN -- Performance: We just can't handle re-indexing for country level changes IF st_area(NEW.geometry) < 1 THEN diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql index bbcaf43d..fed34524 100644 --- a/lib-sql/indices.sql +++ b/lib-sql/indices.sql @@ -48,6 +48,10 @@ CREATE INDEX IF NOT EXISTS idx_postcode_postcode CREATE UNIQUE INDEX IF NOT EXISTS idx_place_osm_unique ON place USING btree(osm_id, osm_type, class, type) {{db.tablespace.address_index}}; + + CREATE INDEX IF NOT EXISTS idx_place_interpolations + ON place USING gist(geometry) {{db.tablespace.address_index}} + WHERE osm_type = 'W' and address ? 'interpolation'; {% endif %} -- Indices only needed for search. -- 2.39.5