lib-sql/functions/utils.sql

   1 -- SPDX-License-Identifier: GPL-2.0-only
   2 --
   3 -- This file is part of Nominatim. (https://nominatim.org)
   4 --
   5 -- Copyright (C) 2022 by the Nominatim developer community.
   6 -- For a full list of authors see the git log.
   7
   8 -- Assorted helper functions for the triggers.
   9
  10 CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry)
  11   RETURNS INTEGER
  12   AS $$
  13 DECLARE
  14   NEWgeometry geometry;
  15 BEGIN
  16 --  RAISE WARNING '%',place;
  17   NEWgeometry := ST_PointOnSurface(place);
  18   RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer);
  19 END;
  20 $$
  21 LANGUAGE plpgsql IMMUTABLE;
  22
  23
  24 CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
  25   RETURNS INTEGER[]
  26   AS $$
  27 DECLARE
  28   i INTEGER;
  29   r INTEGER[];
  30 BEGIN
  31   IF array_upper(a, 1) IS NULL THEN
  32     RETURN b;
  33   END IF;
  34   IF array_upper(b, 1) IS NULL THEN
  35     RETURN a;
  36   END IF;
  37   r := a;
  38   FOR i IN 1..array_upper(b, 1) LOOP
  39     IF NOT (ARRAY[b[i]] <@ r) THEN
  40       r := r || b[i];
  41     END IF;
  42   END LOOP;
  43   RETURN r;
  44 END;
  45 $$
  46 LANGUAGE plpgsql IMMUTABLE;
  47
  48 -- Return the node members with a given label from a relation member list
  49 -- as a set.
  50 --
  51 -- \param members      Member list in osm2pgsql middle format.
  52 -- \param memberLabels Array of labels to accept.
  53 --
  54 -- \returns Set of OSM ids of nodes that are found.
  55 --
  56 CREATE OR REPLACE FUNCTION get_rel_node_members(members TEXT[],
  57                                                 memberLabels TEXT[])
  58   RETURNS SETOF BIGINT
  59   AS $$
  60 DECLARE
  61   i INTEGER;
  62 BEGIN
  63   FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
  64     IF members[i+1] = ANY(memberLabels)
  65        AND upper(substring(members[i], 1, 1))::char(1) = 'N'
  66     THEN
  67       RETURN NEXT substring(members[i], 2)::bigint;
  68     END IF;
  69   END LOOP;
  70
  71   RETURN;
  72 END;
  73 $$
  74 LANGUAGE plpgsql IMMUTABLE;
  75
  76 -- Copy 'name' to or from the default language.
  77 --
  78 -- \param country_code     Country code of the object being named.
  79 -- \param[inout] name      List of names of the object.
  80 --
  81 -- If the country named by country_code has a single default language,
  82 -- then a `name` tag is copied to `name:<country_code>` if this tag does
  83 -- not yet exist and vice versa.
  84 CREATE OR REPLACE FUNCTION add_default_place_name(country_code VARCHAR(2),
  85                                                   INOUT name HSTORE)
  86   AS $$
  87 DECLARE
  88   default_language VARCHAR(10);
  89 BEGIN
  90   IF name is not null AND array_upper(akeys(name),1) > 1 THEN
  91     default_language := get_country_language_code(country_code);
  92     IF default_language IS NOT NULL THEN
  93       IF name ? 'name' AND NOT name ? ('name:'||default_language) THEN
  94         name := name || hstore(('name:'||default_language), (name -> 'name'));
  95       ELSEIF name ? ('name:'||default_language) AND NOT name ? 'name' THEN
  96         name := name || hstore('name', (name -> ('name:'||default_language)));
  97       END IF;
  98     END IF;
  99   END IF;
 100 END;
 101 $$
 102 LANGUAGE plpgsql IMMUTABLE;
 103
 104
 105 -- Find the nearest artificial postcode for the given geometry.
 106 -- TODO For areas there should not be more than two inside the geometry.
 107 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY)
 108   RETURNS TEXT
 109   AS $$
 110 DECLARE
 111   outcode TEXT;
 112   cnt INTEGER;
 113 BEGIN
 114     -- If the geometry is an area then only one postcode must be within
 115     -- that area, otherwise consider the area as not having a postcode.
 116     IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
 117         SELECT min(postcode), count(*) FROM
 118               (SELECT postcode FROM location_postcode
 119                 WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
 120           INTO outcode, cnt;
 121
 122         IF cnt = 1 THEN
 123             RETURN outcode;
 124         ELSE
 125             RETURN null;
 126         END IF;
 127     END IF;
 128
 129     SELECT postcode FROM location_postcode
 130      WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
 131           AND location_postcode.country_code = country
 132      ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
 133     INTO outcode;
 134
 135     RETURN outcode;
 136 END;
 137 $$
 138 LANGUAGE plpgsql STABLE;
 139
 140
 141 CREATE OR REPLACE FUNCTION get_country_code(place geometry)
 142   RETURNS TEXT
 143   AS $$
 144 DECLARE
 145   place_centre GEOMETRY;
 146   nearcountry RECORD;
 147 BEGIN
 148   place_centre := ST_PointOnSurface(place);
 149
 150 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
 151
 152   -- Try for a OSM polygon
 153   FOR nearcountry IN
 154     SELECT country_code from location_area_country
 155     WHERE country_code is not null and st_covers(geometry, place_centre) limit 1
 156   LOOP
 157     RETURN nearcountry.country_code;
 158   END LOOP;
 159
 160 -- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
 161
 162   -- Try for OSM fallback data
 163   -- The order is to deal with places like HongKong that are 'states' within another polygon
 164   FOR nearcountry IN
 165     SELECT country_code from country_osm_grid
 166     WHERE st_covers(geometry, place_centre) order by area asc limit 1
 167   LOOP
 168     RETURN nearcountry.country_code;
 169   END LOOP;
 170
 171 -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
 172
 173   RETURN NULL;
 174 END;
 175 $$
 176 LANGUAGE plpgsql STABLE;
 177
 178
 179 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2))
 180   RETURNS TEXT
 181   AS $$
 182 DECLARE
 183   nearcountry RECORD;
 184 BEGIN
 185   FOR nearcountry IN
 186     SELECT distinct country_default_language_code from country_name
 187     WHERE country_code = search_country_code limit 1
 188   LOOP
 189     RETURN lower(nearcountry.country_default_language_code);
 190   END LOOP;
 191   RETURN NULL;
 192 END;
 193 $$
 194 LANGUAGE plpgsql STABLE;
 195
 196
 197 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10))
 198   RETURNS INTEGER
 199   AS $$
 200 DECLARE
 201   nearcountry RECORD;
 202 BEGIN
 203   FOR nearcountry IN
 204     SELECT partition from country_name where country_code = in_country_code
 205   LOOP
 206     RETURN nearcountry.partition;
 207   END LOOP;
 208   RETURN 0;
 209 END;
 210 $$
 211 LANGUAGE plpgsql STABLE;
 212
 213
 214 -- Find the parent of an address with addr:street/addr:place tag.
 215 --
 216 -- \param token_info Naming info with the address information.
 217 -- \param partition  Partition where to search the parent.
 218 -- \param centroid   Location of the address.
 219 --
 220 -- \return Place ID of the parent if one was found, NULL otherwise.
 221 CREATE OR REPLACE FUNCTION find_parent_for_address(token_info JSONB,
 222                                                    partition SMALLINT,
 223                                                    centroid GEOMETRY)
 224   RETURNS BIGINT
 225   AS $$
 226 DECLARE
 227   parent_place_id BIGINT;
 228 BEGIN
 229   -- Check for addr:street attributes
 230   parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, token_info);
 231   IF parent_place_id is not null THEN
 232     {% if debug %}RAISE WARNING 'Get parent from addr:street: %', parent_place_id;{% endif %}
 233     RETURN parent_place_id;
 234   END IF;
 235
 236   -- Check for addr:place attributes.
 237   parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, token_info);
 238   {% if debug %}RAISE WARNING 'Get parent from addr:place: %', parent_place_id;{% endif %}
 239   RETURN parent_place_id;
 240 END;
 241 $$
 242 LANGUAGE plpgsql STABLE;
 243
 244
 245 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT)
 246   RETURNS BOOLEAN
 247   AS $$
 248 DECLARE
 249 BEGIN
 250   DELETE FROM location_area where place_id = OLD_place_id;
 251 -- TODO:location_area
 252   RETURN true;
 253 END;
 254 $$
 255 LANGUAGE plpgsql;
 256
 257 -- Create a bounding box with an extent computed from the radius (in meters)
 258 -- which in turn is derived from the given search rank.
 259 CREATE OR REPLACE FUNCTION place_node_fuzzy_area(geom GEOMETRY, rank_search INTEGER)
 260   RETURNS GEOMETRY
 261   AS $$
 262 DECLARE
 263   radius FLOAT := 500;
 264 BEGIN
 265   IF rank_search <= 16 THEN -- city
 266     radius := 15000;
 267   ELSIF rank_search <= 18 THEN -- town
 268     radius := 4000;
 269   ELSIF rank_search <= 19 THEN -- village
 270     radius := 2000;
 271   ELSIF rank_search  <= 20 THEN -- hamlet
 272     radius := 1000;
 273   END IF;
 274
 275   RETURN ST_Envelope(ST_Collect(
 276                      ST_Project(geom::geography, radius, 0.785398)::geometry,
 277                      ST_Project(geom::geography, radius, 3.9269908)::geometry));
 278 END;
 279 $$
 280 LANGUAGE plpgsql IMMUTABLE;
 281
 282
 283 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
 284                                         partition INTEGER, keywords INTEGER[],
 285                                         rank_search INTEGER, rank_address INTEGER,
 286                                         in_postcode TEXT, geometry GEOMETRY,
 287                                         centroid GEOMETRY)
 288   RETURNS BOOLEAN
 289   AS $$
 290 DECLARE
 291   locationid INTEGER;
 292   secgeo GEOMETRY;
 293   postcode TEXT;
 294 BEGIN
 295   PERFORM deleteLocationArea(partition, place_id, rank_search);
 296
 297   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
 298   postcode := NULL;
 299   IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
 300       postcode := upper(trim (in_postcode));
 301   END IF;
 302
 303   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
 304     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
 305       PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
 306     END LOOP;
 307
 308   ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
 309     secgeo := place_node_fuzzy_area(geometry, rank_search);
 310     PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, centroid, secgeo);
 311
 312   END IF;
 313
 314   RETURN true;
 315 END;
 316 $$
 317 LANGUAGE plpgsql;
 318
 319
 320 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT,
 321                                                maxdepth INTEGER)
 322   RETURNS SETOF GEOMETRY
 323   AS $$
 324 DECLARE
 325   xmin FLOAT;
 326   ymin FLOAT;
 327   xmax FLOAT;
 328   ymax FLOAT;
 329   xmid FLOAT;
 330   ymid FLOAT;
 331   secgeo GEOMETRY;
 332   secbox GEOMETRY;
 333   seg INTEGER;
 334   geo RECORD;
 335   area FLOAT;
 336   remainingdepth INTEGER;
 337   added INTEGER;
 338 BEGIN
 339
 340 --  RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
 341
 342   IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
 343     RETURN NEXT geometry;
 344     RETURN;
 345   END IF;
 346
 347   remainingdepth := maxdepth - 1;
 348   area := ST_AREA(geometry);
 349   IF remainingdepth < 1 OR area < maxarea THEN
 350     RETURN NEXT geometry;
 351     RETURN;
 352   END IF;
 353
 354   xmin := st_xmin(geometry);
 355   xmax := st_xmax(geometry);
 356   ymin := st_ymin(geometry);
 357   ymax := st_ymax(geometry);
 358   secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
 359
 360   -- if the geometry completely covers the box don't bother to slice any more
 361   IF ST_AREA(secbox) = area THEN
 362     RETURN NEXT geometry;
 363     RETURN;
 364   END IF;
 365
 366   xmid := (xmin+xmax)/2;
 367   ymid := (ymin+ymax)/2;
 368
 369   added := 0;
 370   FOR seg IN 1..4 LOOP
 371
 372     IF seg = 1 THEN
 373       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
 374     END IF;
 375     IF seg = 2 THEN
 376       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
 377     END IF;
 378     IF seg = 3 THEN
 379       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
 380     END IF;
 381     IF seg = 4 THEN
 382       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
 383     END IF;
 384
 385     IF st_intersects(geometry, secbox) THEN
 386       secgeo := st_intersection(geometry, secbox);
 387       IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
 388         FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
 389           IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
 390             added := added + 1;
 391             RETURN NEXT geo.geom;
 392           END IF;
 393         END LOOP;
 394       END IF;
 395     END IF;
 396   END LOOP;
 397
 398   RETURN;
 399 END;
 400 $$
 401 LANGUAGE plpgsql IMMUTABLE;
 402
 403
 404 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
 405   RETURNS SETOF GEOMETRY
 406   AS $$
 407 DECLARE
 408   geo RECORD;
 409 BEGIN
 410   -- 10000000000 is ~~ 1x1 degree
 411   FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
 412     RETURN NEXT geo.geom;
 413   END LOOP;
 414   RETURN;
 415 END;
 416 $$
 417 LANGUAGE plpgsql IMMUTABLE;
 418
 419
 420 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
 421   RETURNS BOOLEAN
 422   AS $$
 423 DECLARE
 424     osmid BIGINT;
 425     osmtype character(1);
 426     pclass text;
 427     ptype text;
 428 BEGIN
 429   SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
 430   DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 431   DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 432   -- force delete by directly entering it into the to-be-deleted table
 433   INSERT INTO place_to_be_deleted (osm_type, osm_id, class, type, deferred)
 434          VALUES(osmtype, osmid, pclass, ptype, false);
 435   PERFORM flush_deleted_places();
 436
 437   RETURN TRUE;
 438 END;
 439 $$
 440 LANGUAGE plpgsql;
 441
 442
 443 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT)
 444   RETURNS BOOLEAN
 445   AS $$
 446 DECLARE
 447   placegeom GEOMETRY;
 448   geom GEOMETRY;
 449   diameter FLOAT;
 450   rank SMALLINT;
 451 BEGIN
 452   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
 453
 454   SELECT geometry, rank_address INTO placegeom, rank
 455     FROM placex WHERE place_id = placeid;
 456
 457   IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
 458     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon')
 459        AND rank > 0
 460     THEN
 461       FOR geom IN SELECT split_geometry(placegeom) LOOP
 462         UPDATE placex SET indexed_status = 2
 463          WHERE ST_Intersects(geom, placex.geometry)
 464                and indexed_status = 0
 465                and ((rank_address = 0 and rank_search > rank) or rank_address > rank)
 466                and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
 467       END LOOP;
 468     ELSE
 469         diameter := update_place_diameter(rank);
 470         IF diameter > 0 THEN
 471           IF rank >= 26 THEN
 472             -- roads may cause reparenting for >27 rank places
 473             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
 474           ELSEIF rank >= 16 THEN
 475             -- up to rank 16, street-less addresses may need reparenting
 476             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null or address ? 'place');
 477           ELSE
 478             -- for all other places the search terms may change as well
 479             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
 480           END IF;
 481         END IF;
 482     END IF;
 483     RETURN TRUE;
 484   END IF;
 485
 486   RETURN FALSE;
 487 END;
 488 $$
 489 LANGUAGE plpgsql;
 490
 491 CREATE OR REPLACE FUNCTION flush_deleted_places()
 492   RETURNS INTEGER
 493   AS $$
 494 BEGIN
 495   -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
 496   INSERT INTO import_polygon_delete (osm_type, osm_id, class, type)
 497     SELECT osm_type, osm_id, class, type FROM place_to_be_deleted WHERE deferred;
 498
 499   -- delete from place table
 500   ALTER TABLE place DISABLE TRIGGER place_before_delete;
 501   DELETE FROM place USING place_to_be_deleted
 502     WHERE place.osm_type = place_to_be_deleted.osm_type
 503           and place.osm_id = place_to_be_deleted.osm_id
 504           and place.class = place_to_be_deleted.class
 505           and place.type = place_to_be_deleted.type
 506           and not deferred;
 507   ALTER TABLE place ENABLE TRIGGER place_before_delete;
 508
 509   -- Mark for delete in the placex table
 510   UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
 511     WHERE placex.osm_type = 'N' and place_to_be_deleted.osm_type = 'N'
 512           and placex.osm_id = place_to_be_deleted.osm_id
 513           and placex.class = place_to_be_deleted.class
 514           and placex.type = place_to_be_deleted.type
 515           and not deferred;
 516   UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
 517     WHERE placex.osm_type = 'W' and place_to_be_deleted.osm_type = 'W'
 518           and placex.osm_id = place_to_be_deleted.osm_id
 519           and placex.class = place_to_be_deleted.class
 520           and placex.type = place_to_be_deleted.type
 521           and not deferred;
 522   UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
 523     WHERE placex.osm_type = 'R' and place_to_be_deleted.osm_type = 'R'
 524           and placex.osm_id = place_to_be_deleted.osm_id
 525           and placex.class = place_to_be_deleted.class
 526           and placex.type = place_to_be_deleted.type
 527           and not deferred;
 528
 529    -- Mark for delete in interpolations
 530    UPDATE location_property_osmline SET indexed_status = 100 FROM place_to_be_deleted
 531     WHERE place_to_be_deleted.osm_type = 'W'
 532           and place_to_be_deleted.class = 'place'
 533           and place_to_be_deleted.type = 'houses'
 534           and location_property_osmline.osm_id = place_to_be_deleted.osm_id
 535           and not deferred;
 536
 537    -- Clear todo list.
 538    TRUNCATE TABLE place_to_be_deleted;
 539
 540    RETURN NULL;
 541 END;
 542 $$ LANGUAGE plpgsql;