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 -- Find the nearest artificial postcode for the given geometry.
  99 -- TODO For areas there should not be more than two inside the geometry.
 100 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY)
 101   RETURNS TEXT
 102   AS $$
 103 DECLARE
 104   outcode TEXT;
 105   cnt INTEGER;
 106 BEGIN
 107     -- If the geometry is an area then only one postcode must be within
 108     -- that area, otherwise consider the area as not having a postcode.
 109     IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
 110         SELECT min(postcode), count(*) FROM
 111               (SELECT postcode FROM location_postcode
 112                 WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
 113           INTO outcode, cnt;
 114
 115         IF cnt = 1 THEN
 116             RETURN outcode;
 117         ELSE
 118             RETURN null;
 119         END IF;
 120     END IF;
 121
 122     SELECT postcode FROM location_postcode
 123      WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
 124           AND location_postcode.country_code = country
 125      ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
 126     INTO outcode;
 127
 128     RETURN outcode;
 129 END;
 130 $$
 131 LANGUAGE plpgsql STABLE;
 132
 133
 134 CREATE OR REPLACE FUNCTION get_country_code(place geometry)
 135   RETURNS TEXT
 136   AS $$
 137 DECLARE
 138   place_centre GEOMETRY;
 139   nearcountry RECORD;
 140 BEGIN
 141   place_centre := ST_PointOnSurface(place);
 142
 143 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
 144
 145   -- Try for a OSM polygon
 146   FOR nearcountry IN
 147     SELECT country_code from location_area_country
 148     WHERE country_code is not null and st_covers(geometry, place_centre) limit 1
 149   LOOP
 150     RETURN nearcountry.country_code;
 151   END LOOP;
 152
 153 -- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
 154
 155   -- Try for OSM fallback data
 156   -- The order is to deal with places like HongKong that are 'states' within another polygon
 157   FOR nearcountry IN
 158     SELECT country_code from country_osm_grid
 159     WHERE st_covers(geometry, place_centre) order by area asc limit 1
 160   LOOP
 161     RETURN nearcountry.country_code;
 162   END LOOP;
 163
 164 -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
 165
 166   --
 167   FOR nearcountry IN
 168     SELECT country_code from country_osm_grid
 169     WHERE st_dwithin(geometry, place_centre, 0.5)
 170     ORDER BY st_distance(geometry, place_centre) asc, area asc limit 1
 171   LOOP
 172     RETURN nearcountry.country_code;
 173   END LOOP;
 174
 175   RETURN NULL;
 176 END;
 177 $$
 178 LANGUAGE plpgsql STABLE;
 179
 180
 181 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2))
 182   RETURNS TEXT
 183   AS $$
 184 DECLARE
 185   nearcountry RECORD;
 186 BEGIN
 187   FOR nearcountry IN
 188     SELECT distinct country_default_language_code from country_name
 189     WHERE country_code = search_country_code limit 1
 190   LOOP
 191     RETURN lower(nearcountry.country_default_language_code);
 192   END LOOP;
 193   RETURN NULL;
 194 END;
 195 $$
 196 LANGUAGE plpgsql STABLE;
 197
 198
 199 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10))
 200   RETURNS INTEGER
 201   AS $$
 202 DECLARE
 203   nearcountry RECORD;
 204 BEGIN
 205   FOR nearcountry IN
 206     SELECT partition from country_name where country_code = in_country_code
 207   LOOP
 208     RETURN nearcountry.partition;
 209   END LOOP;
 210   RETURN 0;
 211 END;
 212 $$
 213 LANGUAGE plpgsql STABLE;
 214
 215
 216 -- Find the parent of an address with addr:street/addr:place tag.
 217 --
 218 -- \param street     Value of addr:street or NULL if tag is missing.
 219 -- \param place      Value of addr:place or NULL if tag is missing.
 220 -- \param partition  Partition where to search the parent.
 221 -- \param centroid   Location of the address.
 222 --
 223 -- \return Place ID of the parent if one was found, NULL otherwise.
 224 CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT,
 225                                                    partition SMALLINT,
 226                                                    centroid GEOMETRY)
 227   RETURNS BIGINT
 228   AS $$
 229 DECLARE
 230   parent_place_id BIGINT;
 231   word_ids INTEGER[];
 232 BEGIN
 233   IF street is not null THEN
 234     -- Check for addr:street attributes
 235     -- Note that addr:street links can only be indexed, once the street itself is indexed
 236     word_ids := word_ids_from_name(street);
 237     IF word_ids is not null THEN
 238       parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids);
 239       IF parent_place_id is not null THEN
 240         --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id;
 241         RETURN parent_place_id;
 242       END IF;
 243     END IF;
 244   END IF;
 245
 246   -- Check for addr:place attributes.
 247   IF place is not null THEN
 248     word_ids := word_ids_from_name(place);
 249     IF word_ids is not null THEN
 250       parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids);
 251       IF parent_place_id is not null THEN
 252         --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id;
 253         RETURN parent_place_id;
 254       END IF;
 255     END IF;
 256   END IF;
 257
 258   RETURN NULL;
 259 END;
 260 $$
 261 LANGUAGE plpgsql STABLE;
 262
 263 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT)
 264   RETURNS BOOLEAN
 265   AS $$
 266 DECLARE
 267 BEGIN
 268   DELETE FROM location_area where place_id = OLD_place_id;
 269 -- TODO:location_area
 270   RETURN true;
 271 END;
 272 $$
 273 LANGUAGE plpgsql;
 274
 275
 276 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
 277                                         partition INTEGER, keywords INTEGER[],
 278                                         rank_search INTEGER, rank_address INTEGER,
 279                                         in_postcode TEXT, geometry GEOMETRY)
 280   RETURNS BOOLEAN
 281   AS $$
 282 DECLARE
 283   locationid INTEGER;
 284   centroid GEOMETRY;
 285   diameter FLOAT;
 286   x BOOLEAN;
 287   splitGeom RECORD;
 288   secgeo GEOMETRY;
 289   postcode TEXT;
 290 BEGIN
 291
 292   IF rank_search > 25 THEN
 293     RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
 294   END IF;
 295
 296   x := deleteLocationArea(partition, place_id, rank_search);
 297
 298   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
 299   postcode := NULL;
 300   IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
 301       postcode := upper(trim (in_postcode));
 302   END IF;
 303
 304   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
 305     centroid := ST_Centroid(geometry);
 306
 307     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
 308       x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
 309     END LOOP;
 310
 311   ELSE
 312
 313     diameter := 0.02;
 314     IF rank_address = 0 THEN
 315       diameter := 0.02;
 316     ELSEIF rank_search <= 14 THEN
 317       diameter := 1.2;
 318     ELSEIF rank_search <= 15 THEN
 319       diameter := 1;
 320     ELSEIF rank_search <= 16 THEN
 321       diameter := 0.5;
 322     ELSEIF rank_search <= 17 THEN
 323       diameter := 0.2;
 324     ELSEIF rank_search <= 21 THEN
 325       diameter := 0.05;
 326     ELSEIF rank_search = 25 THEN
 327       diameter := 0.005;
 328     END IF;
 329
 330 --    RAISE WARNING 'adding % diameter %', place_id, diameter;
 331
 332     secgeo := ST_Buffer(geometry, diameter);
 333     x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
 334
 335   END IF;
 336
 337   RETURN true;
 338 END;
 339 $$
 340 LANGUAGE plpgsql;
 341
 342
 343 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT,
 344                                                maxdepth INTEGER)
 345   RETURNS SETOF GEOMETRY
 346   AS $$
 347 DECLARE
 348   xmin FLOAT;
 349   ymin FLOAT;
 350   xmax FLOAT;
 351   ymax FLOAT;
 352   xmid FLOAT;
 353   ymid FLOAT;
 354   secgeo GEOMETRY;
 355   secbox GEOMETRY;
 356   seg INTEGER;
 357   geo RECORD;
 358   area FLOAT;
 359   remainingdepth INTEGER;
 360   added INTEGER;
 361 BEGIN
 362
 363 --  RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
 364
 365   IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
 366     RETURN NEXT geometry;
 367     RETURN;
 368   END IF;
 369
 370   remainingdepth := maxdepth - 1;
 371   area := ST_AREA(geometry);
 372   IF remainingdepth < 1 OR area < maxarea THEN
 373     RETURN NEXT geometry;
 374     RETURN;
 375   END IF;
 376
 377   xmin := st_xmin(geometry);
 378   xmax := st_xmax(geometry);
 379   ymin := st_ymin(geometry);
 380   ymax := st_ymax(geometry);
 381   secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
 382
 383   -- if the geometry completely covers the box don't bother to slice any more
 384   IF ST_AREA(secbox) = area THEN
 385     RETURN NEXT geometry;
 386     RETURN;
 387   END IF;
 388
 389   xmid := (xmin+xmax)/2;
 390   ymid := (ymin+ymax)/2;
 391
 392   added := 0;
 393   FOR seg IN 1..4 LOOP
 394
 395     IF seg = 1 THEN
 396       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
 397     END IF;
 398     IF seg = 2 THEN
 399       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
 400     END IF;
 401     IF seg = 3 THEN
 402       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
 403     END IF;
 404     IF seg = 4 THEN
 405       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
 406     END IF;
 407
 408     IF st_intersects(geometry, secbox) THEN
 409       secgeo := st_intersection(geometry, secbox);
 410       IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
 411         FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
 412           IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
 413             added := added + 1;
 414             RETURN NEXT geo.geom;
 415           END IF;
 416         END LOOP;
 417       END IF;
 418     END IF;
 419   END LOOP;
 420
 421   RETURN;
 422 END;
 423 $$
 424 LANGUAGE plpgsql IMMUTABLE;
 425
 426
 427 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
 428   RETURNS SETOF GEOMETRY
 429   AS $$
 430 DECLARE
 431   geo RECORD;
 432 BEGIN
 433   -- 10000000000 is ~~ 1x1 degree
 434   FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
 435     RETURN NEXT geo.geom;
 436   END LOOP;
 437   RETURN;
 438 END;
 439 $$
 440 LANGUAGE plpgsql IMMUTABLE;
 441
 442
 443 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
 444   RETURNS BOOLEAN
 445   AS $$
 446 DECLARE
 447     osmid BIGINT;
 448     osmtype character(1);
 449     pclass text;
 450     ptype text;
 451 BEGIN
 452   SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
 453   DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 454   DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 455   -- force delete from place/placex by making it a very small geometry
 456   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;
 457   DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
 458
 459   RETURN TRUE;
 460 END;
 461 $$
 462 LANGUAGE plpgsql;
 463
 464
 465 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT)
 466   RETURNS BOOLEAN
 467   AS $$
 468 DECLARE
 469   placegeom GEOMETRY;
 470   geom GEOMETRY;
 471   diameter FLOAT;
 472   rank INTEGER;
 473 BEGIN
 474   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
 475   SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
 476   IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
 477     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
 478       FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
 479         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
 480         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'));
 481         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
 482         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'));
 483       END LOOP;
 484     ELSE
 485         diameter := update_place_diameter(rank);
 486         IF diameter > 0 THEN
 487           IF rank >= 26 THEN
 488             -- roads may cause reparenting for >27 rank places
 489             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
 490           ELSEIF rank >= 16 THEN
 491             -- up to rank 16, street-less addresses may need reparenting
 492             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');
 493           ELSE
 494             -- for all other places the search terms may change as well
 495             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);
 496           END IF;
 497         END IF;
 498     END IF;
 499     RETURN TRUE;
 500   END IF;
 501
 502   RETURN FALSE;
 503 END;
 504 $$
 505 LANGUAGE plpgsql;