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