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
  42 CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
  43   RETURNS FLOAT
  44   AS $$
  45 BEGIN
  46   IF rank_search <= 4 THEN
  47     RETURN 5.0;
  48   ELSIF rank_search <= 8 THEN
  49     RETURN 1.8;
  50   ELSIF rank_search <= 12 THEN
  51     RETURN 0.6;
  52   ELSIF rank_search <= 17 THEN
  53     RETURN 0.16;
  54   ELSIF rank_search <= 18 THEN
  55     RETURN 0.08;
  56   ELSIF rank_search <= 19 THEN
  57     RETURN 0.04;
  58   END IF;
  59
  60   RETURN 0.02;
  61 END;
  62 $$
  63 LANGUAGE plpgsql IMMUTABLE;
  64
  65
  66 CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
  67                                              OUT rank_search SMALLINT,
  68                                              OUT rank_address SMALLINT)
  69 AS $$
  70 DECLARE
  71   part TEXT;
  72 BEGIN
  73     rank_search := 30;
  74     rank_address := 30;
  75     postcode := upper(postcode);
  76
  77     IF country_code = 'gb' THEN
  78         IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
  79             rank_search := 25;
  80             rank_address := 5;
  81         ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
  82             rank_search := 23;
  83             rank_address := 5;
  84         ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
  85             rank_search := 21;
  86             rank_address := 5;
  87         END IF;
  88
  89     ELSEIF country_code = 'sg' THEN
  90         IF postcode ~ '^([0-9]{6})$' THEN
  91             rank_search := 25;
  92             rank_address := 11;
  93         END IF;
  94
  95     ELSEIF country_code = 'de' THEN
  96         IF postcode ~ '^([0-9]{5})$' THEN
  97             rank_search := 21;
  98             rank_address := 11;
  99         END IF;
 100
 101     ELSE
 102         -- Guess at the postcode format and coverage (!)
 103         IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
 104             rank_search := 21;
 105             rank_address := 11;
 106         ELSE
 107             -- Does it look splitable into and area and local code?
 108             part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
 109
 110             IF part IS NOT NULL THEN
 111                 rank_search := 25;
 112                 rank_address := 11;
 113             ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
 114                 rank_search := 21;
 115                 rank_address := 11;
 116             END IF;
 117         END IF;
 118     END IF;
 119
 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   --
 194   FOR nearcountry IN
 195     SELECT country_code from country_osm_grid
 196     WHERE st_dwithin(geometry, place_centre, 0.5)
 197     ORDER BY st_distance(geometry, place_centre) asc, area asc limit 1
 198   LOOP
 199     RETURN nearcountry.country_code;
 200   END LOOP;
 201
 202   RETURN NULL;
 203 END;
 204 $$
 205 LANGUAGE plpgsql STABLE;
 206
 207
 208 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2))
 209   RETURNS TEXT
 210   AS $$
 211 DECLARE
 212   nearcountry RECORD;
 213 BEGIN
 214   FOR nearcountry IN
 215     SELECT distinct country_default_language_code from country_name
 216     WHERE country_code = search_country_code limit 1
 217   LOOP
 218     RETURN lower(nearcountry.country_default_language_code);
 219   END LOOP;
 220   RETURN NULL;
 221 END;
 222 $$
 223 LANGUAGE plpgsql STABLE;
 224
 225
 226 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10))
 227   RETURNS INTEGER
 228   AS $$
 229 DECLARE
 230   nearcountry RECORD;
 231 BEGIN
 232   FOR nearcountry IN
 233     SELECT partition from country_name where country_code = in_country_code
 234   LOOP
 235     RETURN nearcountry.partition;
 236   END LOOP;
 237   RETURN 0;
 238 END;
 239 $$
 240 LANGUAGE plpgsql STABLE;
 241
 242
 243 -- Find the parent of an address with addr:street/addr:place tag.
 244 --
 245 -- \param street     Value of addr:street or NULL if tag is missing.
 246 -- \param place      Value of addr:place or NULL if tag is missing.
 247 -- \param partition  Partition where to search the parent.
 248 -- \param centroid   Location of the address.
 249 --
 250 -- \return Place ID of the parent if one was found, NULL otherwise.
 251 --         The returned parent is always a street (rank 26/27 and a way).
 252 CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT,
 253                                                    partition SMALLINT,
 254                                                    centroid GEOMETRY)
 255   RETURNS BIGINT
 256   AS $$
 257 DECLARE
 258   parent_place_id BIGINT;
 259   word_ids INTEGER[];
 260 BEGIN
 261   IF street is not null THEN
 262     -- Check for addr:street attributes
 263     -- Note that addr:street links can only be indexed, once the street itself is indexed
 264     word_ids := word_ids_from_name(street);
 265     IF word_ids is not null THEN
 266       SELECT place_id
 267         FROM getNearestNamedRoadFeature(partition, centroid, word_ids)
 268         INTO parent_place_id;
 269       IF parent_place_id is not null THEN
 270         --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id;
 271         RETURN parent_place_id;
 272       END IF;
 273     END IF;
 274   END IF;
 275
 276   -- Check for addr:place attributes.
 277   IF place is not null THEN
 278     word_ids := word_ids_from_name(place);
 279     IF word_ids is not null THEN
 280       SELECT place_id
 281         FROM getNearestNamedPlaceFeature(partition, centroid, word_ids)
 282         INTO parent_place_id;
 283       IF parent_place_id is not null THEN
 284         --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id;
 285         RETURN parent_place_id;
 286       END IF;
 287     END IF;
 288   END IF;
 289
 290   RETURN NULL;
 291 END;
 292 $$
 293 LANGUAGE plpgsql STABLE;
 294
 295 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT)
 296   RETURNS BOOLEAN
 297   AS $$
 298 DECLARE
 299 BEGIN
 300   DELETE FROM location_area where place_id = OLD_place_id;
 301 -- TODO:location_area
 302   RETURN true;
 303 END;
 304 $$
 305 LANGUAGE plpgsql;
 306
 307
 308 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
 309                                         partition INTEGER, keywords INTEGER[],
 310                                         rank_search INTEGER, rank_address INTEGER,
 311                                         in_postcode TEXT, geometry GEOMETRY)
 312   RETURNS BOOLEAN
 313   AS $$
 314 DECLARE
 315   locationid INTEGER;
 316   centroid GEOMETRY;
 317   diameter FLOAT;
 318   x BOOLEAN;
 319   splitGeom RECORD;
 320   secgeo GEOMETRY;
 321   postcode TEXT;
 322 BEGIN
 323
 324   IF rank_search > 25 THEN
 325     RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
 326   END IF;
 327
 328   x := deleteLocationArea(partition, place_id, rank_search);
 329
 330   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
 331   postcode := NULL;
 332   IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
 333       postcode := upper(trim (in_postcode));
 334   END IF;
 335
 336   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
 337     centroid := ST_Centroid(geometry);
 338
 339     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
 340       x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
 341     END LOOP;
 342
 343   ELSE
 344
 345     diameter := 0.02;
 346     IF rank_address = 0 THEN
 347       diameter := 0.02;
 348     ELSEIF rank_search <= 14 THEN
 349       diameter := 1.2;
 350     ELSEIF rank_search <= 15 THEN
 351       diameter := 1;
 352     ELSEIF rank_search <= 16 THEN
 353       diameter := 0.5;
 354     ELSEIF rank_search <= 17 THEN
 355       diameter := 0.2;
 356     ELSEIF rank_search <= 21 THEN
 357       diameter := 0.05;
 358     ELSEIF rank_search = 25 THEN
 359       diameter := 0.005;
 360     END IF;
 361
 362 --    RAISE WARNING 'adding % diameter %', place_id, diameter;
 363
 364     secgeo := ST_Buffer(geometry, diameter);
 365     x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
 366
 367   END IF;
 368
 369   RETURN true;
 370 END;
 371 $$
 372 LANGUAGE plpgsql;
 373
 374
 375 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT,
 376                                                maxdepth INTEGER)
 377   RETURNS SETOF GEOMETRY
 378   AS $$
 379 DECLARE
 380   xmin FLOAT;
 381   ymin FLOAT;
 382   xmax FLOAT;
 383   ymax FLOAT;
 384   xmid FLOAT;
 385   ymid FLOAT;
 386   secgeo GEOMETRY;
 387   secbox GEOMETRY;
 388   seg INTEGER;
 389   geo RECORD;
 390   area FLOAT;
 391   remainingdepth INTEGER;
 392   added INTEGER;
 393 BEGIN
 394
 395 --  RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
 396
 397   IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
 398     RETURN NEXT geometry;
 399     RETURN;
 400   END IF;
 401
 402   remainingdepth := maxdepth - 1;
 403   area := ST_AREA(geometry);
 404   IF remainingdepth < 1 OR area < maxarea THEN
 405     RETURN NEXT geometry;
 406     RETURN;
 407   END IF;
 408
 409   xmin := st_xmin(geometry);
 410   xmax := st_xmax(geometry);
 411   ymin := st_ymin(geometry);
 412   ymax := st_ymax(geometry);
 413   secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
 414
 415   -- if the geometry completely covers the box don't bother to slice any more
 416   IF ST_AREA(secbox) = area THEN
 417     RETURN NEXT geometry;
 418     RETURN;
 419   END IF;
 420
 421   xmid := (xmin+xmax)/2;
 422   ymid := (ymin+ymax)/2;
 423
 424   added := 0;
 425   FOR seg IN 1..4 LOOP
 426
 427     IF seg = 1 THEN
 428       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
 429     END IF;
 430     IF seg = 2 THEN
 431       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
 432     END IF;
 433     IF seg = 3 THEN
 434       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
 435     END IF;
 436     IF seg = 4 THEN
 437       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
 438     END IF;
 439
 440     IF st_intersects(geometry, secbox) THEN
 441       secgeo := st_intersection(geometry, secbox);
 442       IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
 443         FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
 444           IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
 445             added := added + 1;
 446             RETURN NEXT geo.geom;
 447           END IF;
 448         END LOOP;
 449       END IF;
 450     END IF;
 451   END LOOP;
 452
 453   RETURN;
 454 END;
 455 $$
 456 LANGUAGE plpgsql IMMUTABLE;
 457
 458
 459 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
 460   RETURNS SETOF GEOMETRY
 461   AS $$
 462 DECLARE
 463   geo RECORD;
 464 BEGIN
 465   -- 10000000000 is ~~ 1x1 degree
 466   FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
 467     RETURN NEXT geo.geom;
 468   END LOOP;
 469   RETURN;
 470 END;
 471 $$
 472 LANGUAGE plpgsql IMMUTABLE;
 473
 474
 475 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
 476   RETURNS BOOLEAN
 477   AS $$
 478 DECLARE
 479     osmid BIGINT;
 480     osmtype character(1);
 481     pclass text;
 482     ptype text;
 483 BEGIN
 484   SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
 485   DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 486   DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 487   -- force delete from place/placex by making it a very small geometry
 488   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;
 489   DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 490
 491   RETURN TRUE;
 492 END;
 493 $$
 494 LANGUAGE plpgsql;
 495
 496
 497 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT)
 498   RETURNS BOOLEAN
 499   AS $$
 500 DECLARE
 501   placegeom GEOMETRY;
 502   geom GEOMETRY;
 503   diameter FLOAT;
 504   rank INTEGER;
 505 BEGIN
 506   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
 507   SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
 508   IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
 509     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
 510       FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
 511         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
 512         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'));
 513         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
 514         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'));
 515       END LOOP;
 516     ELSE
 517         diameter := 0;
 518         IF rank = 11 THEN
 519           diameter := 0.05;
 520         ELSEIF rank < 18 THEN
 521           diameter := 0.1;
 522         ELSEIF rank < 20 THEN
 523           diameter := 0.05;
 524         ELSEIF rank = 21 THEN
 525           diameter := 0.001;
 526         ELSEIF rank < 24 THEN
 527           diameter := 0.02;
 528         ELSEIF rank < 26 THEN
 529           diameter := 0.002; -- 100 to 200 meters
 530         ELSEIF rank < 28 THEN
 531           diameter := 0.001; -- 50 to 100 meters
 532         END IF;
 533         IF diameter > 0 THEN
 534           IF rank >= 26 THEN
 535             -- roads may cause reparenting for >27 rank places
 536             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
 537           ELSEIF rank >= 16 THEN
 538             -- up to rank 16, street-less addresses may need reparenting
 539             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');
 540           ELSE
 541             -- for all other places the search terms may change as well
 542             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);
 543           END IF;
 544         END IF;
 545     END IF;
 546     RETURN TRUE;
 547   END IF;
 548
 549   RETURN FALSE;
 550 END;
 551 $$
 552 LANGUAGE plpgsql;