lib-sql/functions/place_triggers.sql

   1 -- SPDX-License-Identifier: GPL-2.0-only
   2 --
   3 -- This file is part of Nominatim. (https://nominatim.org)
   4 --
   5 -- Copyright (C) 2026 by the Nominatim developer community.
   6 -- For a full list of authors see the git log.
   7
   8 CREATE OR REPLACE FUNCTION place_insert()
   9   RETURNS TRIGGER
  10   AS $$
  11 DECLARE
  12   existing RECORD;
  13   existingplacex RECORD;
  14   newplacex RECORD;
  15   is_area BOOLEAN;
  16   existing_is_area BOOLEAN;
  17   area_size FLOAT;
  18   address_rank SMALLINT;
  19   search_rank SMALLINT;
  20 BEGIN
  21   {% if debug %}
  22     RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
  23   {% endif %}
  24
  25   -- Filter tuples with bad geometries.
  26   IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) THEN
  27     INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name,
  28                                       country_code, updated, errormessage,
  29                                       prevgeometry, newgeometry)
  30       VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
  31               NEW.address->'country', now(), ST_IsValidReason(NEW.geometry),
  32               null, NEW.geometry);
  33     {% if debug %}
  34       RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
  35     {% endif %}
  36     RETURN null;
  37   END IF;
  38
  39   -- Remove the place from the list of places to be deleted
  40   DELETE FROM place_to_be_deleted pdel
  41     WHERE pdel.osm_type = NEW.osm_type and pdel.osm_id = NEW.osm_id
  42           and pdel.class = NEW.class and pdel.type = NEW.type;
  43
  44   -- Have we already done this place?
  45   SELECT * INTO existing
  46     FROM place
  47     WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
  48           and class = NEW.class and type = NEW.type;
  49
  50   {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
  51
  52   IF existing.osm_type IS NULL THEN
  53     DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
  54   END IF;
  55
  56   -- Remove any old logged data.
  57   DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
  58   DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
  59
  60   is_area := ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon');
  61   IF is_area THEN
  62     area_size = ST_Area(NEW.geometry);
  63   END IF;
  64
  65   -- When an area is changed from large to small: log and discard change
  66   IF existing.geometry is not null AND ST_IsValid(existing.geometry)
  67     AND ST_Area(existing.geometry) > 0.02
  68     AND is_area AND area_size < ST_Area(existing.geometry) * 0.5
  69   THEN
  70     INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name,
  71                                       country_code, updated, errormessage,
  72                                       prevgeometry, newgeometry)
  73       VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
  74               NEW.address->'country', now(),
  75               'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry),
  76               existing.geometry, NEW.geometry);
  77
  78     RETURN null;
  79   END IF;
  80
  81   -- Get the existing placex entry.
  82   SELECT * INTO existingplacex
  83     FROM placex
  84     WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
  85           and class = NEW.class and type = NEW.type;
  86
  87   {% if debug %}RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;{% endif %}
  88
  89   IF existingplacex.osm_type IS NULL THEN
  90     -- Inserting a new placex.
  91     FOR newplacex IN
  92       INSERT INTO placex (osm_type, osm_id, class, type, name,
  93                         admin_level, address, extratags, geometry)
  94       VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
  95               NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry)
  96       RETURNING rank_address
  97     LOOP
  98       PERFORM update_invalidate_for_new_place(newplacex.rank_address,
  99                                               is_area, area_size,
 100                                               NEW.osm_Type, NEW.geometry);
 101     END LOOP;
 102   ELSE
 103     -- Modify an existing placex.
 104     IF is_rankable_place(NEW.osm_type, NEW.class, NEW.admin_level,
 105                          NEW.name, NEW.extratags, is_area)
 106     THEN
 107       -- Recompute the ranks to look out for changes.
 108       -- We use the old country assignment here which is good enough for the
 109       -- purpose.
 110       SELECT * INTO search_rank, address_rank
 111         FROM compute_place_rank(existingplacex.country_code,
 112                                 CASE WHEN is_area THEN 'A' ELSE NEW.osm_type END,
 113                                 NEW.class, NEW.type, NEW.admin_level,
 114                                 (NEW.extratags->'capital') = 'yes',
 115                                 NEW.address->'postcode');
 116
 117       existing_is_area := ST_GeometryType(existingplacex.geometry) in ('ST_Polygon','ST_MultiPolygon');
 118
 119       IF (address_rank BETWEEN 4 and 27
 120           AND (existingplacex.rank_address <= 0 OR existingplacex.rank_address > 27))
 121          OR (not existing_is_area and is_area)
 122       THEN
 123         -- object newly relevant for addressing, invalidate new version
 124         PERFORM update_invalidate_for_new_place(address_rank,
 125                                                 is_area, area_size,
 126                                                 NEW.osm_type, NEW.geometry);
 127       ELSEIF (existingplacex.rank_address BETWEEN 4 and 27
 128               AND (address_rank <= 0 OR address_rank > 27))
 129              OR (existing_is_area and not is_area)
 130       THEN
 131         -- object no longer relevant for addressing, invalidate old version
 132         PERFORM update_invalidate_for_new_place(existingplacex.rank_address,
 133                                                 existing_is_area, null,
 134                                                 existingplacex.osm_type,
 135                                                 existingplacex.geometry);
 136       ELSEIF address_rank BETWEEN 4 and 27 THEN
 137         IF coalesce(existing.name, ''::hstore) != coalesce(NEW.name, ''::hstore)
 138            OR existing.admin_level != NEW.admin_level
 139         THEN
 140           -- Name changes may have an effect on searchable objects and parenting
 141           -- for new and old areas.
 142           PERFORM update_invalidate_for_new_place(address_rank, is_area, null,
 143                                                   NEW.osm_type,
 144                                                   CASE WHEN is_area and existing_is_area
 145                                                     THEN ST_Union(NEW.geometry, existingplacex.geometry)
 146                                                     ELSE ST_Collect(NEW.geometry, existingplacex.geometry)
 147                                                   END);
 148         ELSEIF existingplacex.geometry::text != NEW.geometry::text THEN
 149             -- Geometry change, just invalidate the changed areas.
 150             -- Changes of other geometry types are currently ignored.
 151           IF is_area and existing_is_area THEN
 152             PERFORM update_invalidate_for_new_place(address_rank, true, null,
 153                                                     NEW.osm_type,
 154                                                     ST_SymDifference(existingplacex.geometry,
 155                                                                      NEW.geometry));
 156           END IF;
 157         END IF;
 158       END IF;
 159
 160       -- Invalidate linked places: they potentially get a new name and addresses.
 161       IF existingplacex.linked_place_id is not NULL THEN
 162         UPDATE placex x
 163           SET name = p.name,
 164               extratags = p.extratags,
 165               indexed_status = 2
 166           FROM place p
 167           WHERE x.place_id = existingplacex.linked_place_id
 168                 and x.indexed_status = 0
 169                 and x.osm_type = p.osm_type
 170                 and x.osm_id = p.osm_id
 171                 and x.class = p.class;
 172       END IF;
 173
 174       -- update placex in place
 175       UPDATE placex
 176         SET name = NEW.name,
 177             address = NEW.address,
 178             parent_place_id = null,
 179             extratags = NEW.extratags,
 180             admin_level = NEW.admin_level,
 181             rank_address = address_rank,
 182             rank_search = search_rank,
 183             indexed_status = 2,
 184             geometry = CASE WHEN address_rank = 0
 185                             THEN simplify_large_polygons(NEW.geometry)
 186                             ELSE NEW.geometry END
 187         WHERE place_id = existingplacex.place_id;
 188     ELSE
 189       -- New place is not really valid, remove the placex entry
 190       UPDATE placex SET indexed_status = 100 WHERE place_id = existingplacex.place_id;
 191     END IF;
 192
 193     -- When an existing way is updated, recalculate entrances
 194     IF existingplacex.osm_type = 'W' and (existingplacex.rank_search > 27 or existingplacex.class IN ('landuse', 'leisure')) THEN
 195       PERFORM place_update_entrances(existingplacex.place_id, existingplacex.osm_id);
 196     END IF;
 197   END IF;
 198
 199   -- Finally update place itself.
 200   IF existing.osm_type is not NULL THEN
 201     -- If there is already an entry in place, just update that, if necessary.
 202     IF coalesce(existing.name, ''::hstore) != coalesce(NEW.name, ''::hstore)
 203        or coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
 204        or coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore)
 205        or coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
 206        or existing.geometry::text != NEW.geometry::text
 207     THEN
 208       UPDATE place
 209         SET name = NEW.name,
 210             address = NEW.address,
 211             extratags = NEW.extratags,
 212             admin_level = NEW.admin_level,
 213             geometry = NEW.geometry
 214         WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id
 215               and class = NEW.class and type = NEW.type;
 216     END IF;
 217
 218     RETURN NULL;
 219   END IF;
 220
 221   RETURN NEW;
 222 END;
 223 $$ LANGUAGE plpgsql;
 224
 225 CREATE OR REPLACE FUNCTION place_delete()
 226   RETURNS TRIGGER
 227   AS $$
 228 DECLARE
 229   deferred BOOLEAN;
 230 BEGIN
 231   {% if debug %}RAISE WARNING 'Delete for % % %/%', OLD.osm_type, OLD.osm_id, OLD.class, OLD.type;{% endif %}
 232
 233   deferred := ST_IsValid(OLD.geometry) and ST_Area(OLD.geometry) > 2;
 234   IF deferred THEN
 235     SELECT bool_or(not (rank_address = 0 or rank_address > 25)) INTO deferred
 236       FROM placex
 237       WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id
 238             and class = OLD.class and type = OLD.type;
 239   END IF;
 240
 241   INSERT INTO place_to_be_deleted (osm_type, osm_id, class, type, deferred)
 242     VALUES(OLD.osm_type, OLD.osm_id, OLD.class, OLD.type, COALESCE(deferred, FALSE));
 243
 244   RETURN NULL;
 245 END;
 246 $$ LANGUAGE plpgsql;