X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/b44493e7f240525224de85b4df53dccc81f0a01f..f59a072aa6805bfce9ca4cc097c550af0f64879e:/lib-sql/functions/place_triggers.sql diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql index 4e316990..6a52021b 100644 --- a/lib-sql/functions/place_triggers.sql +++ b/lib-sql/functions/place_triggers.sql @@ -13,8 +13,8 @@ DECLARE country RECORD; existing RECORD; existingplacex RECORD; - existingline RECORD; - result BOOLEAN; + existingline BIGINT[]; + interpol RECORD; BEGIN {% if debug %} RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry); @@ -34,6 +34,11 @@ BEGIN RETURN null; END IF; + -- Remove the place from the list of places to be deleted + DELETE FROM place_to_be_deleted pdel + WHERE pdel.osm_type = NEW.osm_type and pdel.osm_id = NEW.osm_id + and pdel.class = NEW.class; + -- Have we already done this place? SELECT * INTO existing FROM place @@ -42,8 +47,6 @@ BEGIN {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %} - -- Handle a place changing type by removing the old data. - -- (This trigger is executed BEFORE INSERT of the NEW tuple.) IF existing.osm_type IS NULL THEN DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class; END IF; @@ -57,19 +60,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 * INTO existingline - FROM location_property_osmline WHERE osm_id = NEW.osm_id; - - -- Update the interpolation table: - -- delete all old interpolation lines with same osm_id - -- 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; - - INSERT INTO location_property_osmline (osm_id, address, linegeo) - VALUES (NEW.osm_id, NEW.address, NEW.geometry); + 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. @@ -129,16 +120,6 @@ BEGIN -- ---- All other place types. - -- Patch in additional country names - IF NEW.admin_level = 2 and NEW.type = 'administrative' and NEW.address ? 'country' - THEN - FOR country IN - SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country') - LOOP - NEW.name = country.name || NEW.name; - END LOOP; - END IF; - -- When an area is changed from large to small: log and discard change IF existing.geometry is not null AND ST_IsValid(existing.geometry) AND ST_Area(existing.geometry) > 0.02 @@ -156,6 +137,29 @@ 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 place.address ? 'interpolation' + 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 @@ -186,15 +190,11 @@ BEGIN END IF; {% endif %} - IF existing.osm_type IS NOT NULL THEN - -- Pathological case caused by the triggerless copy into place during initial import - -- force delete even for large areas, it will be reinserted later - UPDATE place SET geometry = ST_SetSRID(ST_Point(0,0), 4326) - WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id - and class = NEW.class and type = NEW.type; - DELETE FROM place - WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id - and class = NEW.class and type = NEW.type; + IF existingplacex.osm_type is not NULL THEN + -- Mark any existing place for delete in the placex table + UPDATE placex SET indexed_status = 100 + WHERE placex.osm_type = NEW.osm_type and placex.osm_id = NEW.osm_id + and placex.class = NEW.class and placex.type = NEW.type; END IF; -- Process it as a new insertion @@ -205,6 +205,27 @@ BEGIN {% if debug %}RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;{% endif %} + IF existing.osm_type is not NULL THEN + -- If there is already an entry in place, just update that, if necessary. + IF coalesce(existing.name, ''::hstore) != coalesce(NEW.name, ''::hstore) + or coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore) + or coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore) + or coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15) + or existing.geometry::text != NEW.geometry::text + THEN + UPDATE place + SET name = NEW.name, + address = NEW.address, + extratags = NEW.extratags, + admin_level = NEW.admin_level, + geometry = NEW.geometry + WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id + and class = NEW.class and type = NEW.type; + END IF; + + RETURN NULL; + END IF; + RETURN NEW; END IF; @@ -278,16 +299,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 @@ -330,35 +341,79 @@ BEGIN END; $$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER AS $$ DECLARE - has_rank BOOLEAN; + deferred BOOLEAN; BEGIN - - {% if debug %}RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;{% endif %} - - -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through - IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN - SELECT bool_or(not (rank_address = 0 or rank_address > 25)) as ranked FROM placex WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type INTO has_rank; - IF has_rank THEN - insert into import_polygon_delete (osm_type, osm_id, class, type) values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type); - RETURN NULL; - END IF; + {% if debug %}RAISE WARNING 'Delete for % % %/%', OLD.osm_type, OLD.osm_id, OLD.class, OLD.type;{% endif %} + + deferred := ST_IsValid(OLD.geometry) and ST_Area(OLD.geometry) > 2; + IF deferred THEN + SELECT bool_or(not (rank_address = 0 or rank_address > 25)) INTO deferred + FROM placex + WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id + and class = OLD.class and type = OLD.type; END IF; - -- 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; + INSERT INTO place_to_be_deleted (osm_type, osm_id, class, type, deferred) + VALUES(OLD.osm_type, OLD.osm_id, OLD.class, OLD.type, deferred); - -- interpolations are special - IF OLD.osm_type='W' and OLD.class = 'place' and OLD.type = 'houses' THEN - UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id) - END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; - RETURN OLD; +CREATE OR REPLACE FUNCTION flush_deleted_places() + RETURNS INTEGER + AS $$ +BEGIN + -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through + INSERT INTO import_polygon_delete (osm_type, osm_id, class, type) + SELECT osm_type, osm_id, class, type FROM place_to_be_deleted WHERE deferred; + + -- delete from place table + ALTER TABLE place DISABLE TRIGGER place_before_delete; + DELETE FROM place USING place_to_be_deleted + WHERE place.osm_type = place_to_be_deleted.osm_type + and place.osm_id = place_to_be_deleted.osm_id + and place.class = place_to_be_deleted.class + and place.type = place_to_be_deleted.type + and not deferred; + ALTER TABLE place ENABLE TRIGGER place_before_delete; + + -- Mark for delete in the placex table + UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted + WHERE placex.osm_type = 'N' and place_to_be_deleted.osm_type = 'N' + and placex.osm_id = place_to_be_deleted.osm_id + and placex.class = place_to_be_deleted.class + and placex.type = place_to_be_deleted.type + and not deferred; + UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted + WHERE placex.osm_type = 'W' and place_to_be_deleted.osm_type = 'W' + and placex.osm_id = place_to_be_deleted.osm_id + and placex.class = place_to_be_deleted.class + and placex.type = place_to_be_deleted.type + and not deferred; + UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted + WHERE placex.osm_type = 'R' and place_to_be_deleted.osm_type = 'R' + and placex.osm_id = place_to_be_deleted.osm_id + and placex.class = place_to_be_deleted.class + and placex.type = place_to_be_deleted.type + and not deferred; + + -- Mark for delete in interpolations + UPDATE location_property_osmline SET indexed_status = 100 FROM place_to_be_deleted + WHERE place_to_be_deleted.osm_type = 'W' + and place_to_be_deleted.class = 'place' + and place_to_be_deleted.type = 'houses' + and location_property_osmline.osm_id = place_to_be_deleted.osm_id + and not deferred; + + -- Clear todo list. + TRUNCATE TABLE place_to_be_deleted; + + RETURN NULL; END; -$$ -LANGUAGE plpgsql; +$$ LANGUAGE plpgsql;