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