lib-sql/functions/postcode_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) 2025 by the Nominatim developer community.
   6 -- For a full list of authors see the git log.
   7
   8 -- Trigger functions for location_postcodes table.
   9
  10
  11 -- Trigger for updates of location_postcode
  12 --
  13 -- Computes the parent object the postcode most likely refers to.
  14 -- This will be the place that determines the address displayed when
  15 -- searching for this postcode.
  16 CREATE OR REPLACE FUNCTION postcodes_update()
  17   RETURNS TRIGGER
  18   AS $$
  19 DECLARE
  20   partition SMALLINT;
  21   location RECORD;
  22 BEGIN
  23     IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
  24         RETURN NEW;
  25     END IF;
  26
  27     NEW.indexed_date = now();
  28
  29     partition := get_partition(NEW.country_code);
  30
  31     NEW.parent_place_id = 0;
  32     FOR location IN
  33       SELECT place_id
  34         FROM getNearFeatures(partition, NEW.centroid, NEW.centroid, NEW.rank_search)
  35         WHERE NOT isguess ORDER BY rank_address DESC, distance asc LIMIT 1
  36     LOOP
  37         NEW.parent_place_id = location.place_id;
  38     END LOOP;
  39
  40     RETURN NEW;
  41 END;
  42 $$
  43 LANGUAGE plpgsql;
  44
  45
  46 CREATE OR REPLACE FUNCTION postcodes_delete()
  47   RETURNS TRIGGER
  48   AS $$
  49 BEGIN
  50 {% if not disable_diff_updates %}
  51   UPDATE placex p SET indexed_status = 2
  52    WHERE p.postcode = OLD.postcode AND ST_Intersects(OLD.geometry, p.geometry)
  53          AND indexed_status = 0;
  54 {% endif %}
  55
  56   RETURN OLD;
  57 END;
  58 $$
  59 LANGUAGE plpgsql;
  60
  61
  62 CREATE OR REPLACE FUNCTION postcodes_insert()
  63   RETURNS TRIGGER
  64   AS $$
  65 DECLARE
  66   existing RECORD;
  67 BEGIN
  68   IF NEW.osm_id is not NULL THEN
  69     -- postcode area, remove existing from same OSM object
  70     SELECT * INTO existing FROM location_postcodes p
  71       WHERE p.osm_id = NEW.osm_id;
  72
  73     IF existing.place_id is not NULL THEN
  74       IF existing.postcode != NEW.postcode or existing.country_code != NEW.country_code THEN
  75         DELETE FROM location_postcodes p WHERE p.osm_id = NEW.osm_id;
  76         existing := NULL;
  77       END IF;
  78     END IF;
  79   END IF;
  80
  81   IF existing is NULL THEN
  82     SELECT * INTO existing FROM location_postcodes p
  83       WHERE p.country_code = NEW.country_code AND p.postcode = NEW.postcode;
  84
  85     IF existing.postcode is NULL THEN
  86 {% if not disable_diff_updates %}
  87       UPDATE placex p SET indexed_status = 2
  88        WHERE ST_Intersects(NEW.geometry, p.geometry)
  89              AND indexed_status = 0
  90              AND p.rank_address >= 22 AND not p.address ? 'postcode';
  91 {% endif %}
  92
  93       -- new entry, just insert
  94       NEW.indexed_status := 1;
  95       NEW.place_id := nextval('seq_place');
  96       RETURN NEW;
  97     END IF;
  98   END IF;
  99
 100   -- update: only when there are changes
 101   IF coalesce(NEW.osm_id, -1) != coalesce(existing.osm_id, -1)
 102      OR (NEW.osm_id is not null AND NEW.geometry::text != existing.geometry::text)
 103      OR (NEW.osm_id is null
 104          AND (abs(ST_X(existing.centroid) - ST_X(NEW.centroid)) > 0.0000001
 105               OR abs(ST_Y(existing.centroid) - ST_Y(NEW.centroid)) > 0.0000001))
 106   THEN
 107 {% if not disable_diff_updates %}
 108     UPDATE placex p SET indexed_status = 2
 109       WHERE ST_Intersects(ST_Difference(NEW.geometry, existing.geometry), p.geometry)
 110             AND indexed_status = 0
 111             AND p.rank_address >= 22 AND not p.address ? 'postcode';
 112
 113     UPDATE placex p SET indexed_status = 2
 114       WHERE ST_Intersects(ST_Difference(existing.geometry, NEW.geometry), p.geometry)
 115             AND indexed_status = 0
 116             AND p.postcode = OLD.postcode;
 117 {% endif %}
 118
 119     UPDATE location_postcodes p
 120       SET osm_id = NEW.osm_id,
 121           indexed_status = 2,
 122           centroid = NEW.centroid,
 123           geometry = NEW.geometry
 124       WHERE p.country_code = NEW.country_code AND p.postcode = NEW.postcode;
 125   END IF;
 126
 127   RETURN NULL;
 128 END;
 129 $$
 130 LANGUAGE plpgsql;