lib-sql/functions/place_triggers.sql

   1 CREATE OR REPLACE FUNCTION place_insert()
   2   RETURNS TRIGGER
   3   AS $$
   4 DECLARE
   5   i INTEGER;
   6   existing RECORD;
   7   existingplacex RECORD;
   8   existingline RECORD;
   9   existinggeometry GEOMETRY;
  10   existingplace_id BIGINT;
  11   result BOOLEAN;
  12   partition INTEGER;
  13 BEGIN
  14
  15   {% if debug %}
  16     RAISE WARNING '-----------------------------------------------------------------------------------';
  17     RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
  18   {% endif %}
  19   -- filter wrong tupels
  20   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
  21     INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
  22       VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
  23 --    RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
  24     RETURN null;
  25   END IF;
  26
  27   -- decide, whether it is an osm interpolation line => insert intoosmline, or else just placex
  28   IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
  29     -- Have we already done this place?
  30     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;
  31
  32     -- Get the existing place_id
  33     select * from location_property_osmline where osm_id = NEW.osm_id INTO existingline;
  34
  35     -- Handle a place changing type by removing the old data (this trigger is executed BEFORE INSERT of the NEW tupel)
  36     IF existing.osm_type IS NULL THEN
  37       DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
  38     END IF;
  39
  40     DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
  41     DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
  42
  43     -- 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)
  44     IF existingline.osm_id IS NOT NULL THEN
  45       delete from location_property_osmline where osm_id = NEW.osm_id;
  46     END IF;
  47
  48     -- for interpolations invalidate all nodes on the line
  49     update placex p set indexed_status = 2
  50       from planet_osm_ways w
  51       where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
  52
  53
  54     INSERT INTO location_property_osmline (osm_id, address, linegeo)
  55       VALUES (NEW.osm_id, NEW.address, NEW.geometry);
  56
  57
  58     IF existing.osm_type IS NULL THEN
  59       return NEW;
  60     END IF;
  61
  62     IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
  63        OR (coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore))
  64        OR existing.geometry::text != NEW.geometry::text
  65        THEN
  66
  67       update place set
  68         name = NEW.name,
  69         address = NEW.address,
  70         extratags = NEW.extratags,
  71         admin_level = NEW.admin_level,
  72         geometry = NEW.geometry
  73         where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
  74     END IF;
  75
  76     RETURN NULL;
  77
  78   ELSE -- insert to placex
  79
  80     -- Patch in additional country names
  81     IF NEW.admin_level = 2 AND NEW.type = 'administrative'
  82           AND NEW.address is not NULL AND NEW.address ? 'country' THEN
  83         SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country') INTO existing;
  84         IF existing.name IS NOT NULL THEN
  85             NEW.name = existing.name || NEW.name;
  86         END IF;
  87     END IF;
  88
  89     -- Have we already done this place?
  90     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;
  91
  92     -- Get the existing place_id
  93     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;
  94
  95     -- Handle a place changing type by removing the old data
  96     -- My generated 'place' types are causing havok because they overlap with real keys
  97     -- TODO: move them to their own special purpose key/class to avoid collisions
  98     IF existing.osm_type IS NULL THEN
  99       DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
 100     END IF;
 101
 102     -- Pure postcodes are never queried from placex so we don't add them.
 103     -- location_postcodes is filled from the place table directly.
 104     IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
 105       -- Remove old placex entry.
 106       DELETE FROM placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
 107
 108       IF existing.osm_type IS NOT NULL THEN
 109         IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
 110            OR existing.geometry::text != NEW.geometry::text
 111         THEN
 112
 113           update place set address = NEW.address, geometry = NEW.geometry
 114             where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 115         END IF;
 116
 117         RETURN NULL;
 118       END IF;
 119
 120       RETURN NEW;
 121     END IF;
 122
 123     {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
 124     {% if debug %}RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;{% endif %}
 125
 126     -- Log and discard
 127     IF existing.geometry is not null AND st_isvalid(existing.geometry)
 128       AND st_area(existing.geometry) > 0.02
 129       AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
 130       AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
 131       THEN
 132       INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
 133         VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(),
 134         'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
 135       RETURN null;
 136     END IF;
 137
 138     DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
 139     DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
 140
 141     -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
 142     IF existingplacex.osm_type IS NULL OR
 143         (existingplacex.class = 'boundary' AND
 144           ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR
 145           (existingplacex.type != NEW.type)))
 146     THEN
 147
 148       {% if config.get_bool('LIMIT_REINDEXING') %}
 149       IF existingplacex.osm_type IS NOT NULL THEN
 150         -- sanity check: ignore admin_level changes on places with too many active children
 151         -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
 152         SELECT count(*) INTO i FROM
 153           (SELECT 'a' FROM placex, place_addressline
 154             WHERE address_place_id = existingplacex.place_id
 155                   and placex.place_id = place_addressline.place_id
 156                   and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub;
 157         IF i > 100000 THEN
 158           RETURN null;
 159         END IF;
 160       END IF;
 161       {% endif %}
 162
 163       IF existing.osm_type IS NOT NULL THEN
 164         -- pathological case caused by the triggerless copy into place during initial import
 165         -- force delete even for large areas, it will be reinserted later
 166         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;
 167         DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 168       END IF;
 169
 170       -- No - process it as a new insertion (hopefully of low rank or it will be slow)
 171       insert into placex (osm_type, osm_id, class, type, name,
 172                           admin_level, address, extratags, geometry)
 173         values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
 174                 NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry);
 175
 176       {% if debug %}RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;{% endif %}
 177
 178       RETURN NEW;
 179     END IF;
 180
 181     -- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
 182     IF existing.geometry::text != NEW.geometry::text
 183        AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
 184        AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
 185        THEN
 186
 187       -- Get the version of the geometry actually used (in placex table)
 188       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;
 189
 190       -- Performance limit
 191       IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN
 192
 193         -- 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
 194         update placex set indexed_status = 2 where indexed_status = 0
 195             AND ST_Intersects(NEW.geometry, placex.geometry)
 196             AND NOT ST_Intersects(existinggeometry, placex.geometry)
 197             AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
 198
 199         update placex set indexed_status = 2 where indexed_status = 0
 200             AND ST_Intersects(existinggeometry, placex.geometry)
 201             AND NOT ST_Intersects(NEW.geometry, placex.geometry)
 202             AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
 203
 204       END IF;
 205
 206     END IF;
 207
 208
 209     IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
 210        OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
 211        OR coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
 212        OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
 213        OR existing.geometry::text != NEW.geometry::text
 214        THEN
 215
 216       update place set
 217         name = NEW.name,
 218         address = NEW.address,
 219         extratags = NEW.extratags,
 220         admin_level = NEW.admin_level,
 221         geometry = NEW.geometry
 222         where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 223
 224
 225       IF NEW.class = 'boundary' AND NEW.type = 'postal_code' THEN
 226           IF NEW.address is NULL OR NOT NEW.address ? 'postcode' THEN
 227               -- postcode was deleted, no longer retain in placex
 228               DELETE FROM placex where place_id = existingplacex.place_id;
 229               RETURN NULL;
 230           END IF;
 231
 232           NEW.name := hstore('ref', NEW.address->'postcode');
 233       END IF;
 234
 235       IF NEW.class in ('boundary')
 236          AND ST_GeometryType(NEW.geometry) not in ('ST_Polygon','ST_MultiPolygon') THEN
 237           DELETE FROM placex where place_id = existingplacex.place_id;
 238           RETURN NULL;
 239       END IF;
 240
 241       update placex set
 242         name = NEW.name,
 243         address = NEW.address,
 244         parent_place_id = null,
 245         extratags = NEW.extratags,
 246         admin_level = NEW.admin_level,
 247         indexed_status = 2,
 248         geometry = NEW.geometry
 249         where place_id = existingplacex.place_id;
 250
 251       -- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => mark this line for reparenting
 252       -- (already here, because interpolation lines are reindexed before nodes, so in the second call it would be too late)
 253       IF NEW.osm_type='N'
 254          and (coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
 255              or existing.geometry::text != NEW.geometry::text)
 256       THEN
 257           result:= osmline_reinsert(NEW.osm_id, NEW.geometry);
 258       END IF;
 259
 260       -- linked places should get potential new naming and addresses
 261       IF existingplacex.linked_place_id is not NULL THEN
 262         update placex x set
 263           name = p.name,
 264           extratags = p.extratags,
 265           indexed_status = 2
 266         from place p
 267         where x.place_id = existingplacex.linked_place_id
 268               and x.indexed_status = 0
 269               and x.osm_type = p.osm_type
 270               and x.osm_id = p.osm_id
 271               and x.class = p.class;
 272       END IF;
 273
 274       IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
 275       THEN
 276         IF existingplacex.rank_address between 26 and 27 THEN
 277           -- When streets change their name, this may have an effect on POI objects
 278           -- with addr:street tags.
 279           UPDATE placex SET indexed_status = 2
 280           WHERE indexed_status = 0 and address ? 'street'
 281                 and parent_place_id = existingplacex.place_id;
 282           UPDATE placex SET indexed_status = 2
 283           WHERE indexed_status = 0 and rank_search = 30 and address ? 'street'
 284                 and ST_DWithin(NEW.geometry, geometry, 0.002);
 285         ELSEIF existingplacex.rank_address between 16 and 25 THEN
 286           -- When places change their name, this may have an effect on POI objects
 287           -- with addr:place tags.
 288           UPDATE placex SET indexed_status = 2
 289           WHERE indexed_status = 0 and address ? 'place' and rank_search = 30
 290                 and parent_place_id = existingplacex.place_id;
 291           -- No update of surrounding objects, potentially too expensive.
 292         END IF;
 293       END IF;
 294     END IF;
 295
 296     -- Abort the add (we modified the existing place instead)
 297     RETURN NULL;
 298   END IF;
 299
 300 END;
 301 $$ LANGUAGE plpgsql;
 302
 303
 304 CREATE OR REPLACE FUNCTION place_delete()
 305   RETURNS TRIGGER
 306   AS $$
 307 DECLARE
 308   has_rank BOOLEAN;
 309 BEGIN
 310
 311   {% if debug %}RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;{% endif %}
 312
 313   -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
 314   IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
 315     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;
 316     IF has_rank THEN
 317       insert into import_polygon_delete (osm_type, osm_id, class, type) values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
 318       RETURN NULL;
 319     END IF;
 320   END IF;
 321
 322   -- mark for delete
 323   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;
 324
 325   -- interpolations are special
 326   IF OLD.osm_type='W' and OLD.class = 'place' and OLD.type = 'houses' THEN
 327     UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id)
 328   END IF;
 329
 330   RETURN OLD;
 331 END;
 332 $$
 333 LANGUAGE plpgsql;
 334