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
  67 CREATE OR REPLACE FUNCTION get_address_place(in_partition SMALLINT, feature GEOMETRY,
  68                                              from_rank SMALLINT, to_rank SMALLINT,
  69                                              extent FLOAT, token_info JSONB, key TEXT)
  70   RETURNS nearfeaturecentr
  71   AS $$
  72 DECLARE
  73   r nearfeaturecentr%rowtype;
  74 BEGIN
  75 {% for partition in db.partitions %}
  76   IF in_partition = {{ partition }} THEN
  77       SELECT place_id, keywords, rank_address, rank_search,
  78              min(ST_Distance(feature, centroid)) as distance,
  79              isguess, postcode, centroid INTO r
  80         FROM location_area_large_{{ partition }}
  81         WHERE geometry && ST_Expand(feature, extent)
  82               AND rank_address between from_rank and to_rank
  83               AND token_matches_address(token_info, key, keywords)
  84         GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  85         ORDER BY bool_or(ST_Intersects(geometry, feature)), distance LIMIT 1;
  86       RETURN r;
  87   END IF;
  88 {% endfor %}
  89
  90   RAISE EXCEPTION 'Unknown partition %', in_partition;
  91 END;
  92 $$
  93 LANGUAGE plpgsql STABLE;
  94
  95
  96 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
  97 DECLARE
  98 BEGIN
  99
 100   IF in_rank_search <= 4 THEN
 101     DELETE from location_area_country WHERE place_id = in_place_id;
 102     RETURN TRUE;
 103   END IF;
 104
 105 {% for partition in db.partitions %}
 106   IF in_partition = {{ partition }} THEN
 107     DELETE from location_area_large_{{ partition }} WHERE place_id = in_place_id;
 108     RETURN TRUE;
 109   END IF;
 110 {% endfor %}
 111
 112   RAISE EXCEPTION 'Unknown partition %', in_partition;
 113
 114   RETURN FALSE;
 115 END
 116 $$
 117 LANGUAGE plpgsql;
 118
 119 create or replace function insertLocationAreaLarge(
 120   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
 121   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT,
 122   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 123 DECLARE
 124 BEGIN
 125   IF in_rank_address = 0 THEN
 126     RETURN TRUE;
 127   END IF;
 128
 129   IF in_rank_search <= 4 and not in_estimate THEN
 130     INSERT INTO location_area_country (place_id, country_code, geometry)
 131       values (in_place_id, in_country_code, in_geometry);
 132     RETURN TRUE;
 133   END IF;
 134
 135 {% for partition in db.partitions %}
 136   IF in_partition = {{ partition }} THEN
 137     INSERT INTO location_area_large_{{ partition }} (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
 138       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
 139     RETURN TRUE;
 140   END IF;
 141 {% endfor %}
 142
 143   RAISE EXCEPTION 'Unknown partition %', in_partition;
 144   RETURN FALSE;
 145 END
 146 $$
 147 LANGUAGE plpgsql;
 148
 149 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER,
 150                                                       point GEOMETRY,
 151                                                       token_info JSONB)
 152   RETURNS BIGINT
 153   AS $$
 154 DECLARE
 155   parent BIGINT;
 156 BEGIN
 157   IF not token_has_addr_street(token_info) THEN
 158     RETURN NULL;
 159   END IF;
 160
 161 {% for partition in db.partitions %}
 162   IF in_partition = {{ partition }} THEN
 163     SELECT place_id FROM search_name_{{ partition }}
 164       INTO parent
 165       WHERE token_matches_street(token_info, name_vector)
 166             AND centroid && ST_Expand(point, 0.015)
 167             AND address_rank between 26 and 27
 168       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 169     RETURN parent;
 170   END IF;
 171 {% endfor %}
 172
 173   RAISE EXCEPTION 'Unknown partition %', in_partition;
 174 END
 175 $$
 176 LANGUAGE plpgsql STABLE;
 177
 178 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
 179                                                        point GEOMETRY,
 180                                                        token_info JSONB)
 181   RETURNS BIGINT
 182   AS $$
 183 DECLARE
 184   parent BIGINT;
 185 BEGIN
 186   IF not token_has_addr_place(token_info) THEN
 187     RETURN NULL;
 188   END IF;
 189
 190 {% for partition in db.partitions %}
 191   IF in_partition = {{ partition }} THEN
 192     SELECT place_id
 193       INTO parent
 194       FROM search_name_{{ partition }}
 195       WHERE token_matches_place(token_info, name_vector)
 196             AND centroid && ST_Expand(point, 0.04)
 197             AND address_rank between 16 and 25
 198       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 199     RETURN parent;
 200   END IF;
 201 {% endfor %}
 202
 203   RAISE EXCEPTION 'Unknown partition %', in_partition;
 204 END
 205 $$
 206 LANGUAGE plpgsql STABLE;
 207
 208 create or replace function insertSearchName(
 209   in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[],
 210   in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY)
 211 RETURNS BOOLEAN AS $$
 212 DECLARE
 213 BEGIN
 214 {% for partition in db.partitions %}
 215   IF in_partition = {{ partition }} THEN
 216     DELETE FROM search_name_{{ partition }} values WHERE place_id = in_place_id;
 217     IF in_rank_address > 0 THEN
 218       INSERT INTO search_name_{{ partition }} (place_id, address_rank, name_vector, centroid)
 219         values (in_place_id, in_rank_address, in_name_vector, in_geometry);
 220     END IF;
 221     RETURN TRUE;
 222   END IF;
 223 {% endfor %}
 224
 225   RAISE EXCEPTION 'Unknown partition %', in_partition;
 226   RETURN FALSE;
 227 END
 228 $$
 229 LANGUAGE plpgsql;
 230
 231 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 232 DECLARE
 233 BEGIN
 234 {% for partition in db.partitions %}
 235   IF in_partition = {{ partition }} THEN
 236     DELETE from search_name_{{ partition }} WHERE place_id = in_place_id;
 237     RETURN TRUE;
 238   END IF;
 239 {% endfor %}
 240
 241   RAISE EXCEPTION 'Unknown partition %', in_partition;
 242
 243   RETURN FALSE;
 244 END
 245 $$
 246 LANGUAGE plpgsql;
 247
 248 create or replace function insertLocationRoad(
 249   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 250 DECLARE
 251 BEGIN
 252
 253 {% for partition in db.partitions %}
 254   IF in_partition = {{ partition }} THEN
 255     DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
 256     INSERT INTO location_road_{{ partition }} (partition, place_id, country_code, geometry)
 257       values (in_partition, in_place_id, in_country_code, in_geometry);
 258     RETURN TRUE;
 259   END IF;
 260 {% endfor %}
 261
 262   RAISE EXCEPTION 'Unknown partition %', in_partition;
 263   RETURN FALSE;
 264 END
 265 $$
 266 LANGUAGE plpgsql;
 267
 268 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 269 DECLARE
 270 BEGIN
 271
 272 {% for partition in db.partitions %}
 273   IF in_partition = {{ partition }} THEN
 274     DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
 275     RETURN TRUE;
 276   END IF;
 277 {% endfor %}
 278
 279   RAISE EXCEPTION 'Unknown partition %', in_partition;
 280
 281   RETURN FALSE;
 282 END
 283 $$
 284 LANGUAGE plpgsql;
 285
 286 CREATE OR REPLACE FUNCTION getNearestRoadPlaceId(in_partition INTEGER, point GEOMETRY)
 287   RETURNS BIGINT
 288   AS $$
 289 DECLARE
 290   r RECORD;
 291   search_diameter FLOAT;
 292 BEGIN
 293
 294 {% for partition in db.partitions %}
 295   IF in_partition = {{ partition }} THEN
 296     search_diameter := 0.00005;
 297     WHILE search_diameter < 0.1 LOOP
 298       FOR r IN
 299         SELECT place_id FROM location_road_{{ partition }}
 300           WHERE ST_DWithin(geometry, point, search_diameter)
 301           ORDER BY ST_Distance(geometry, point) ASC limit 1
 302       LOOP
 303         RETURN r.place_id;
 304       END LOOP;
 305       search_diameter := search_diameter * 2;
 306     END LOOP;
 307     RETURN NULL;
 308   END IF;
 309 {% endfor %}
 310
 311   RAISE EXCEPTION 'Unknown partition %', in_partition;
 312 END
 313 $$
 314 LANGUAGE plpgsql STABLE;
 315
 316 CREATE OR REPLACE FUNCTION getNearestParallelRoadFeature(in_partition INTEGER,
 317                                                          line GEOMETRY)
 318   RETURNS BIGINT
 319   AS $$
 320 DECLARE
 321   r RECORD;
 322   search_diameter FLOAT;
 323   p1 GEOMETRY;
 324   p2 GEOMETRY;
 325   p3 GEOMETRY;
 326 BEGIN
 327
 328   IF ST_GeometryType(line) not in ('ST_LineString') THEN
 329     RETURN NULL;
 330   END IF;
 331
 332   p1 := ST_LineInterpolatePoint(line,0);
 333   p2 := ST_LineInterpolatePoint(line,0.5);
 334   p3 := ST_LineInterpolatePoint(line,1);
 335
 336 {% for partition in db.partitions %}
 337   IF in_partition = {{ partition }} THEN
 338     search_diameter := 0.0005;
 339     WHILE search_diameter < 0.01 LOOP
 340       FOR r IN
 341         SELECT place_id FROM location_road_{{ partition }}
 342           WHERE ST_DWithin(line, geometry, search_diameter)
 343           ORDER BY (ST_distance(geometry, p1)+
 344                     ST_distance(geometry, p2)+
 345                     ST_distance(geometry, p3)) ASC limit 1
 346       LOOP
 347         RETURN r.place_id;
 348       END LOOP;
 349       search_diameter := search_diameter * 2;
 350     END LOOP;
 351     RETURN NULL;
 352   END IF;
 353 {% endfor %}
 354
 355   RAISE EXCEPTION 'Unknown partition %', in_partition;
 356 END
 357 $$
 358 LANGUAGE plpgsql STABLE;