sql/functions.sql

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