AS $$
DECLARE
i INTEGER;
+ country RECORD;
existing RECORD;
existingplacex RECORD;
- existingline RECORD;
- existinggeometry GEOMETRY;
- existingplace_id BIGINT;
- result BOOLEAN;
- partition INTEGER;
+ existingline BIGINT[];
+ interpol RECORD;
BEGIN
-
{% if debug %}
- RAISE WARNING '-----------------------------------------------------------------------------------';
RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
{% endif %}
- -- filter wrong tupels
- IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN
- INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
- VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
--- RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
+
+ -- Filter tuples with bad geometries.
+ IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) THEN
+ INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name,
+ country_code, updated, errormessage,
+ prevgeometry, newgeometry)
+ VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
+ NEW.address->'country', now(), ST_IsValidReason(NEW.geometry),
+ null, NEW.geometry);
+ {% if debug %}
+ RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
+ {% endif %}
RETURN null;
END IF;
- -- decide, whether it is an osm interpolation line => insert intoosmline, or else just placex
- IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
- -- Have we already done this place?
- select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
+ -- 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 and pdel.type = NEW.type;
- -- Get the existing place_id
- select * from location_property_osmline where osm_id = NEW.osm_id INTO existingline;
+ -- Have we already done this place?
+ SELECT * INTO existing
+ FROM place
+ WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
+ and class = NEW.class and type = NEW.type;
- -- Handle a place changing type by removing the old data (this trigger is executed BEFORE INSERT of the NEW tupel)
- 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;
+ {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
- 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;
-
- -- 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);
+ 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;
+ -- Remove any old logged data.
+ 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;
- INSERT INTO location_property_osmline (osm_id, address, linegeo)
- VALUES (NEW.osm_id, NEW.address, NEW.geometry);
+ -- ---- Interpolation Lines
+ IF NEW.class='place' and NEW.type='houses'
+ and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString'
+ THEN
+ PERFORM reinsert_interpolation(NEW.osm_id, NEW.address, NEW.geometry);
- IF existing.osm_type IS NULL THEN
- return NEW;
- END IF;
+ -- Now invalidate all address nodes on the line.
+ -- They get their parent from the interpolation.
+ 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);
- IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
- OR (coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore))
- 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;
+ -- If there is already an entry in place, just update that, if necessary.
+ IF existing.osm_type is not null THEN
+ IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+ 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 NULL;
-
- ELSE -- insert to placex
+ RETURN NEW;
+ END IF;
- -- Patch in additional country names
- IF NEW.admin_level = 2 AND NEW.type = 'administrative'
- AND NEW.address is not NULL AND NEW.address ? 'country' THEN
- SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country') INTO existing;
- IF existing.name IS NOT NULL THEN
- NEW.name = existing.name || NEW.name;
- END IF;
- END IF;
-
- -- Have we already done this place?
- select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
-
- -- Get the existing place_id
- select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
-
- -- Handle a place changing type by removing the old data
- -- My generated 'place' types are causing havok because they overlap with real keys
- -- TODO: move them to their own special purpose key/class to avoid collisions
- 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;
+ -- ---- Postcode points.
+ IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
-- Pure postcodes are never queried from placex so we don't add them.
-- location_postcodes is filled from the place table directly.
- IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
- -- Remove old placex entry.
- DELETE FROM placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
- IF existing.osm_type IS NOT NULL THEN
- IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
- OR existing.geometry::text != NEW.geometry::text
- THEN
+ -- Remove any old placex entry.
+ DELETE FROM placex WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id;
- update place set address = NEW.address, 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;
+ IF existing.osm_type IS NOT NULL THEN
+ IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+ 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 NEW;
+ RETURN NULL;
END IF;
- {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
- {% if debug %}RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;{% endif %}
-
- -- Log and discard
- IF existing.geometry is not null AND st_isvalid(existing.geometry)
- AND st_area(existing.geometry) > 0.02
- AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
- AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
- THEN
- INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
- VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(),
- 'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
- RETURN null;
- END IF;
+ RETURN NEW;
+ END IF;
- 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;
+ -- ---- All other place types.
+
+ -- 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
+ AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
+ AND ST_Area(NEW.geometry) < ST_Area(existing.geometry) * 0.5
+ THEN
+ INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name,
+ country_code, updated, errormessage,
+ prevgeometry, newgeometry)
+ VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
+ NEW.address->'country', now(),
+ 'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry),
+ existing.geometry, NEW.geometry);
- -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
- IF existingplacex.osm_type IS NULL OR
- (existingplacex.class = 'boundary' AND
- ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR
- (existingplacex.type != NEW.type)))
- THEN
+ RETURN null;
+ END IF;
- {% if config.get_bool('LIMIT_REINDEXING') %}
- IF existingplacex.osm_type IS NOT NULL THEN
- -- sanity check: ignore admin_level changes on places with too many active children
- -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
- SELECT count(*) INTO i FROM
- (SELECT 'a' FROM placex, place_addressline
- WHERE address_place_id = existingplacex.place_id
- and placex.place_id = place_addressline.place_id
- and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub;
- IF i > 100000 THEN
- RETURN null;
- END IF;
- END IF;
- {% endif %}
+ -- 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;
- 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;
+ -- Get the existing placex entry.
+ SELECT * INTO existingplacex
+ FROM placex
+ WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
+ and class = NEW.class and type = NEW.type;
+
+ {% if debug %}RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;{% endif %}
+
+ -- To paraphrase: if there isn't an existing item, OR if the admin level has changed
+ IF existingplacex.osm_type IS NULL
+ or (existingplacex.class = 'boundary'
+ and ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15)
+ and existingplacex.type = 'administrative')
+ or existingplacex.type != NEW.type))
+ THEN
+ {% if config.get_bool('LIMIT_REINDEXING') %}
+ -- sanity check: ignore admin_level changes on places with too many active children
+ -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
+ IF existingplacex.osm_type IS NOT NULL THEN
+ SELECT count(*) INTO i FROM
+ (SELECT 'a' FROM placex, place_addressline
+ WHERE address_place_id = existingplacex.place_id
+ and placex.place_id = place_addressline.place_id
+ and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub;
+ IF i > 100000 THEN
+ RETURN null;
END IF;
-
- -- No - process it as a new insertion (hopefully of low rank or it will be slow)
- insert into placex (osm_type, osm_id, class, type, name,
- admin_level, address, extratags, geometry)
- values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
- NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry);
-
- {% if debug %}RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;{% endif %}
-
- RETURN NEW;
END IF;
+ {% endif %}
- -- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
- IF existing.geometry::text != NEW.geometry::text
- AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
- AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
- THEN
-
- -- Get the version of the geometry actually used (in placex table)
- select geometry from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existinggeometry;
-
- -- Performance limit
- IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN
+ 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;
- -- re-index points that have moved in / out of the polygon, could be done as a single query but postgres gets the index usage wrong
- update placex set indexed_status = 2 where indexed_status = 0
- AND ST_Intersects(NEW.geometry, placex.geometry)
- AND NOT ST_Intersects(existinggeometry, placex.geometry)
- AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+ -- Process it as a new insertion
+ INSERT INTO placex (osm_type, osm_id, class, type, name,
+ admin_level, address, extratags, geometry)
+ VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
+ NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry);
+
+ {% 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;
- update placex set indexed_status = 2 where indexed_status = 0
- AND ST_Intersects(existinggeometry, placex.geometry)
- AND NOT ST_Intersects(NEW.geometry, placex.geometry)
- AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+ RETURN NULL;
+ END IF;
- END IF;
+ RETURN NEW;
+ END IF;
+ -- Special case for polygon shape changes because they tend to be large
+ -- and we can be a bit clever about how we handle them
+ IF existing.geometry::text != NEW.geometry::text
+ AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
+ AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
+ THEN
+ -- Performance limit
+ IF ST_Area(NEW.geometry) < 0.000000001 AND ST_Area(existingplacex.geometry) < 1
+ THEN
+ -- re-index points that have moved in / out of the polygon.
+ -- Could be done as a single query but postgres gets the index usage wrong.
+ update placex set indexed_status = 2 where indexed_status = 0
+ AND ST_Intersects(NEW.geometry, placex.geometry)
+ AND NOT ST_Intersects(existingplacex.geometry, placex.geometry)
+ AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+
+ update placex set indexed_status = 2 where indexed_status = 0
+ AND ST_Intersects(existingplacex.geometry, placex.geometry)
+ AND NOT ST_Intersects(NEW.geometry, placex.geometry)
+ AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
END IF;
+ END IF;
- IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
- OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
- OR coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::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;
-
-
- IF NEW.class = 'boundary' AND NEW.type = 'postal_code' THEN
- IF NEW.address is NULL OR NOT NEW.address ? 'postcode' THEN
- -- postcode was deleted, no longer retain in placex
- DELETE FROM placex where place_id = existingplacex.place_id;
- RETURN NULL;
- END IF;
-
- NEW.name := hstore('ref', NEW.address->'postcode');
+ -- Has something relevant changed?
+ IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
+ OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
+ OR coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::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;
+
+ -- Postcode areas are only kept, when there is an actual postcode assigned.
+ IF NEW.class = 'boundary' AND NEW.type = 'postal_code' THEN
+ IF NEW.address is NULL OR NOT NEW.address ? 'postcode' THEN
+ -- postcode was deleted, no longer retain in placex
+ DELETE FROM placex where place_id = existingplacex.place_id;
+ RETURN NULL;
END IF;
- IF NEW.class in ('boundary')
- AND ST_GeometryType(NEW.geometry) not in ('ST_Polygon','ST_MultiPolygon') THEN
- DELETE FROM placex where place_id = existingplacex.place_id;
- RETURN NULL;
- END IF;
+ NEW.name := hstore('ref', NEW.address->'postcode');
+ END IF;
- update placex set
- name = NEW.name,
- address = NEW.address,
- parent_place_id = null,
- extratags = NEW.extratags,
- admin_level = NEW.admin_level,
- indexed_status = 2,
- geometry = NEW.geometry
- where place_id = existingplacex.place_id;
-
- -- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => 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)
- or existing.geometry::text != NEW.geometry::text)
- THEN
- result:= osmline_reinsert(NEW.osm_id, NEW.geometry);
- END IF;
+ -- Boundaries must be areas.
+ IF NEW.class in ('boundary')
+ AND ST_GeometryType(NEW.geometry) not in ('ST_Polygon','ST_MultiPolygon')
+ THEN
+ DELETE FROM placex where place_id = existingplacex.place_id;
+ RETURN NULL;
+ END IF;
- -- linked places should get potential new naming and addresses
- IF existingplacex.linked_place_id is not NULL THEN
- update placex x set
- name = p.name,
- extratags = p.extratags,
- indexed_status = 2
- from place p
- where x.place_id = existingplacex.linked_place_id
+ -- Update the placex entry in-place.
+ UPDATE placex
+ SET name = NEW.name,
+ address = NEW.address,
+ parent_place_id = null,
+ extratags = NEW.extratags,
+ admin_level = NEW.admin_level,
+ indexed_status = 2,
+ geometry = NEW.geometry
+ WHERE place_id = existingplacex.place_id;
+
+ -- Invalidate linked places: they potentially get a new name and addresses.
+ IF existingplacex.linked_place_id is not NULL THEN
+ UPDATE placex x
+ SET name = p.name,
+ extratags = p.extratags,
+ indexed_status = 2
+ FROM place p
+ WHERE x.place_id = existingplacex.linked_place_id
and x.indexed_status = 0
and x.osm_type = p.osm_type
and x.osm_id = p.osm_id
and x.class = p.class;
- END IF;
+ END IF;
- IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
- THEN
- IF existingplacex.rank_address between 26 and 27 THEN
- -- When streets change their name, this may have an effect on POI objects
- -- with addr:street tags.
- UPDATE placex SET indexed_status = 2
+ -- Invalidate dependent objects effected by name changes
+ IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
+ THEN
+ IF existingplacex.rank_address between 26 and 27 THEN
+ -- When streets change their name, this may have an effect on POI objects
+ -- with addr:street tags.
+ UPDATE placex SET indexed_status = 2
WHERE indexed_status = 0 and address ? 'street'
and parent_place_id = existingplacex.place_id;
- UPDATE placex SET indexed_status = 2
+ UPDATE placex SET indexed_status = 2
WHERE indexed_status = 0 and rank_search = 30 and address ? 'street'
and ST_DWithin(NEW.geometry, geometry, 0.002);
- ELSEIF existingplacex.rank_address between 16 and 25 THEN
- -- When places change their name, this may have an effect on POI objects
- -- with addr:place tags.
- UPDATE placex SET indexed_status = 2
+ ELSEIF existingplacex.rank_address between 16 and 25 THEN
+ -- When places change their name, this may have an effect on POI objects
+ -- with addr:place tags.
+ UPDATE placex SET indexed_status = 2
WHERE indexed_status = 0 and address ? 'place' and rank_search = 30
and parent_place_id = existingplacex.place_id;
- -- No update of surrounding objects, potentially too expensive.
- END IF;
+ -- No update of surrounding objects, potentially too expensive.
END IF;
END IF;
-
- -- Abort the add (we modified the existing place instead)
- RETURN NULL;
END IF;
+ -- Abort the insertion (we modified the existing place instead)
+ RETURN NULL;
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;
projects / nominatim.git / blobdiff