lib-sql/functions/partition-functions.sql

   1 DROP TYPE IF EXISTS nearfeaturecentr CASCADE;
   2 CREATE TYPE nearfeaturecentr AS (
   3   place_id BIGINT,
   4   keywords int[],
   5   rank_address smallint,
   6   rank_search smallint,
   7   distance float,
   8   isguess boolean,
   9   postcode TEXT,
  10   centroid GEOMETRY
  11 );
  12
  13 -- feature intersects geoemtry
  14 -- for areas and linestrings they must touch at least along a line
  15 CREATE OR REPLACE FUNCTION is_relevant_geometry(de9im TEXT, geom_type TEXT)
  16 RETURNS BOOLEAN
  17 AS $$
  18 BEGIN
  19   IF substring(de9im from 1 for 2) != 'FF' THEN
  20     RETURN TRUE;
  21   END IF;
  22
  23   IF geom_type = 'ST_Point' THEN
  24     RETURN substring(de9im from 4 for 1) = '0';
  25   END IF;
  26
  27   IF geom_type in ('ST_LineString', 'ST_MultiLineString') THEN
  28     RETURN substring(de9im from 4 for 1) = '1';
  29   END IF;
  30
  31   RETURN substring(de9im from 4 for 1) = '2';
  32 END
  33 $$ LANGUAGE plpgsql IMMUTABLE;
  34
  35 create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER) RETURNS setof nearfeaturecentr AS $$
  36 DECLARE
  37   r nearfeaturecentr%rowtype;
  38 BEGIN
  39
  40 {% for partition in db.partitions %}
  41   IF in_partition = {{ partition }} THEN
  42     FOR r IN
  43       SELECT place_id, keywords, rank_address, rank_search,
  44              min(ST_Distance(feature, centroid)) as distance,
  45              isguess, postcode, centroid
  46       FROM location_area_large_{{ partition }}
  47       WHERE geometry && feature
  48         AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature))
  49         AND rank_address < maxrank
  50             -- Postcodes currently still use rank_search to define for which
  51             -- features they are relevant.
  52         AND not (rank_address in (5, 11) and rank_search > maxrank)
  53       GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  54     LOOP
  55       RETURN NEXT r;
  56     END LOOP;
  57     RETURN;
  58   END IF;
  59 {% endfor %}
  60
  61   RAISE EXCEPTION 'Unknown partition %', in_partition;
  62 END
  63 $$
  64 LANGUAGE plpgsql STABLE;
  65
  66 CREATE OR REPLACE FUNCTION get_places_for_addr_tags(in_partition SMALLINT,
  67                                                     feature GEOMETRY,
  68                                                     address HSTORE, country TEXT)
  69   RETURNS SETOF nearfeaturecentr
  70   AS $$
  71 DECLARE
  72   r nearfeaturecentr%rowtype;
  73   item RECORD;
  74 BEGIN
  75   FOR item IN
  76     SELECT (get_addr_tag_rank(key, country)).*, key, name FROM
  77       (SELECT skeys(address) as key, svals(address) as name) x
  78   LOOP
  79    IF item.from_rank is null THEN
  80      CONTINUE;
  81    END IF;
  82
  83 {% for partition in db.partitions %}
  84     IF in_partition = {{ partition }} THEN
  85         SELECT place_id, keywords, rank_address, rank_search,
  86                min(ST_Distance(feature, centroid)) as distance,
  87                isguess, postcode, centroid INTO r
  88         FROM location_area_large_{{ partition }}
  89         WHERE geometry && ST_Expand(feature, item.extent)
  90           AND rank_address between item.from_rank and item.to_rank
  91           AND word_ids_from_name(item.name) && keywords
  92         GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  93         ORDER BY bool_or(ST_Intersects(geometry, feature)), distance LIMIT 1;
  94       IF r.place_id is null THEN
  95         -- If we cannot find a place for the term, just return the
  96         -- search term for the given name. That ensures that the address
  97         -- element can still be searched for, even though it will not be
  98         -- displayed.
  99         RETURN NEXT ROW(null, addr_ids_from_name(item.name), null, null,
 100                         null, null, null, null)::nearfeaturecentr;
 101       ELSE
 102         RETURN NEXT r;
 103       END IF;
 104       CONTINUE;
 105     END IF;
 106 {% endfor %}
 107
 108     RAISE EXCEPTION 'Unknown partition %', in_partition;
 109   END LOOP;
 110 END;
 111 $$
 112 LANGUAGE plpgsql STABLE;
 113
 114 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
 115 DECLARE
 116 BEGIN
 117
 118   IF in_rank_search <= 4 THEN
 119     DELETE from location_area_country WHERE place_id = in_place_id;
 120     RETURN TRUE;
 121   END IF;
 122
 123 {% for partition in db.partitions %}
 124   IF in_partition = {{ partition }} THEN
 125     DELETE from location_area_large_{{ partition }} WHERE place_id = in_place_id;
 126     RETURN TRUE;
 127   END IF;
 128 {% endfor %}
 129
 130   RAISE EXCEPTION 'Unknown partition %', in_partition;
 131
 132   RETURN FALSE;
 133 END
 134 $$
 135 LANGUAGE plpgsql;
 136
 137 create or replace function insertLocationAreaLarge(
 138   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
 139   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT,
 140   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 141 DECLARE
 142 BEGIN
 143   IF in_rank_address = 0 THEN
 144     RETURN TRUE;
 145   END IF;
 146
 147   IF in_rank_search <= 4 and not in_estimate THEN
 148     INSERT INTO location_area_country (place_id, country_code, geometry)
 149       values (in_place_id, in_country_code, in_geometry);
 150     RETURN TRUE;
 151   END IF;
 152
 153 {% for partition in db.partitions %}
 154   IF in_partition = {{ partition }} THEN
 155     INSERT INTO location_area_large_{{ partition }} (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
 156       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
 157     RETURN TRUE;
 158   END IF;
 159 {% endfor %}
 160
 161   RAISE EXCEPTION 'Unknown partition %', in_partition;
 162   RETURN FALSE;
 163 END
 164 $$
 165 LANGUAGE plpgsql;
 166
 167 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER,
 168                                                       point GEOMETRY,
 169                                                       isin_token INTEGER[])
 170   RETURNS BIGINT
 171   AS $$
 172 DECLARE
 173   parent BIGINT;
 174 BEGIN
 175
 176 {% for partition in db.partitions %}
 177   IF in_partition = {{ partition }} THEN
 178     SELECT place_id FROM search_name_{{ partition }}
 179       INTO parent
 180       WHERE name_vector && isin_token
 181             AND centroid && ST_Expand(point, 0.015)
 182             AND address_rank between 26 and 27
 183       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 184     RETURN parent;
 185   END IF;
 186 {% endfor %}
 187
 188   RAISE EXCEPTION 'Unknown partition %', in_partition;
 189 END
 190 $$
 191 LANGUAGE plpgsql STABLE;
 192
 193 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
 194                                                        point GEOMETRY,
 195                                                        isin_token INTEGER[])
 196   RETURNS BIGINT
 197   AS $$
 198 DECLARE
 199   parent BIGINT;
 200 BEGIN
 201
 202 {% for partition in db.partitions %}
 203   IF in_partition = {{ partition }} THEN
 204     SELECT place_id
 205       INTO parent
 206       FROM search_name_{{ partition }}
 207       WHERE name_vector && isin_token
 208             AND centroid && ST_Expand(point, 0.04)
 209             AND address_rank between 16 and 25
 210       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 211     RETURN parent;
 212   END IF;
 213 {% endfor %}
 214
 215   RAISE EXCEPTION 'Unknown partition %', in_partition;
 216 END
 217 $$
 218 LANGUAGE plpgsql STABLE;
 219
 220 create or replace function insertSearchName(
 221   in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[],
 222   in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY)
 223 RETURNS BOOLEAN AS $$
 224 DECLARE
 225 BEGIN
 226 {% for partition in db.partitions %}
 227   IF in_partition = {{ partition }} THEN
 228     DELETE FROM search_name_{{ partition }} values WHERE place_id = in_place_id;
 229     IF in_rank_address > 0 THEN
 230       INSERT INTO search_name_{{ partition }} (place_id, address_rank, name_vector, centroid)
 231         values (in_place_id, in_rank_address, in_name_vector, in_geometry);
 232     END IF;
 233     RETURN TRUE;
 234   END IF;
 235 {% endfor %}
 236
 237   RAISE EXCEPTION 'Unknown partition %', in_partition;
 238   RETURN FALSE;
 239 END
 240 $$
 241 LANGUAGE plpgsql;
 242
 243 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 244 DECLARE
 245 BEGIN
 246 {% for partition in db.partitions %}
 247   IF in_partition = {{ partition }} THEN
 248     DELETE from search_name_{{ partition }} WHERE place_id = in_place_id;
 249     RETURN TRUE;
 250   END IF;
 251 {% endfor %}
 252
 253   RAISE EXCEPTION 'Unknown partition %', in_partition;
 254
 255   RETURN FALSE;
 256 END
 257 $$
 258 LANGUAGE plpgsql;
 259
 260 create or replace function insertLocationRoad(
 261   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 262 DECLARE
 263 BEGIN
 264
 265 {% for partition in db.partitions %}
 266   IF in_partition = {{ partition }} THEN
 267     DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
 268     INSERT INTO location_road_{{ partition }} (partition, place_id, country_code, geometry)
 269       values (in_partition, in_place_id, in_country_code, in_geometry);
 270     RETURN TRUE;
 271   END IF;
 272 {% endfor %}
 273
 274   RAISE EXCEPTION 'Unknown partition %', in_partition;
 275   RETURN FALSE;
 276 END
 277 $$
 278 LANGUAGE plpgsql;
 279
 280 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 281 DECLARE
 282 BEGIN
 283
 284 {% for partition in db.partitions %}
 285   IF in_partition = {{ partition }} THEN
 286     DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
 287     RETURN TRUE;
 288   END IF;
 289 {% endfor %}
 290
 291   RAISE EXCEPTION 'Unknown partition %', in_partition;
 292
 293   RETURN FALSE;
 294 END
 295 $$
 296 LANGUAGE plpgsql;
 297
 298 CREATE OR REPLACE FUNCTION getNearestRoadPlaceId(in_partition INTEGER, point GEOMETRY)
 299   RETURNS BIGINT
 300   AS $$
 301 DECLARE
 302   r RECORD;
 303   search_diameter FLOAT;
 304 BEGIN
 305
 306 {% for partition in db.partitions %}
 307   IF in_partition = {{ partition }} THEN
 308     search_diameter := 0.00005;
 309     WHILE search_diameter < 0.1 LOOP
 310       FOR r IN
 311         SELECT place_id FROM location_road_{{ partition }}
 312           WHERE ST_DWithin(geometry, point, search_diameter)
 313           ORDER BY ST_Distance(geometry, point) ASC limit 1
 314       LOOP
 315         RETURN r.place_id;
 316       END LOOP;
 317       search_diameter := search_diameter * 2;
 318     END LOOP;
 319     RETURN NULL;
 320   END IF;
 321 {% endfor %}
 322
 323   RAISE EXCEPTION 'Unknown partition %', in_partition;
 324 END
 325 $$
 326 LANGUAGE plpgsql STABLE;
 327
 328 CREATE OR REPLACE FUNCTION getNearestParallelRoadFeature(in_partition INTEGER,
 329                                                          line GEOMETRY)
 330   RETURNS BIGINT
 331   AS $$
 332 DECLARE
 333   r RECORD;
 334   search_diameter FLOAT;
 335   p1 GEOMETRY;
 336   p2 GEOMETRY;
 337   p3 GEOMETRY;
 338 BEGIN
 339
 340   IF ST_GeometryType(line) not in ('ST_LineString') THEN
 341     RETURN NULL;
 342   END IF;
 343
 344   p1 := ST_LineInterpolatePoint(line,0);
 345   p2 := ST_LineInterpolatePoint(line,0.5);
 346   p3 := ST_LineInterpolatePoint(line,1);
 347
 348 {% for partition in db.partitions %}
 349   IF in_partition = {{ partition }} THEN
 350     search_diameter := 0.0005;
 351     WHILE search_diameter < 0.01 LOOP
 352       FOR r IN
 353         SELECT place_id FROM location_road_{{ partition }}
 354           WHERE ST_DWithin(line, geometry, search_diameter)
 355           ORDER BY (ST_distance(geometry, p1)+
 356                     ST_distance(geometry, p2)+
 357                     ST_distance(geometry, p3)) ASC limit 1
 358       LOOP
 359         RETURN r.place_id;
 360       END LOOP;
 361       search_diameter := search_diameter * 2;
 362     END LOOP;
 363     RETURN NULL;
 364   END IF;
 365 {% endfor %}
 366
 367   RAISE EXCEPTION 'Unknown partition %', in_partition;
 368 END
 369 $$
 370 LANGUAGE plpgsql STABLE;