sql/functions/utils.sql

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