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, tokens INT[])
  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 tokens && 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                                                       isin_token INTEGER[])
 152   RETURNS BIGINT
 153   AS $$
 154 DECLARE
 155   parent BIGINT;
 156 BEGIN
 157
 158 {% for partition in db.partitions %}
 159   IF in_partition = {{ partition }} THEN
 160     SELECT place_id FROM search_name_{{ partition }}
 161       INTO parent
 162       WHERE name_vector && isin_token
 163             AND centroid && ST_Expand(point, 0.015)
 164             AND address_rank between 26 and 27
 165       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 166     RETURN parent;
 167   END IF;
 168 {% endfor %}
 169
 170   RAISE EXCEPTION 'Unknown partition %', in_partition;
 171 END
 172 $$
 173 LANGUAGE plpgsql STABLE;
 174
 175 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
 176                                                        point GEOMETRY,
 177                                                        isin_token INTEGER[])
 178   RETURNS BIGINT
 179   AS $$
 180 DECLARE
 181   parent BIGINT;
 182 BEGIN
 183
 184 {% for partition in db.partitions %}
 185   IF in_partition = {{ partition }} THEN
 186     SELECT place_id
 187       INTO parent
 188       FROM search_name_{{ partition }}
 189       WHERE name_vector && isin_token
 190             AND centroid && ST_Expand(point, 0.04)
 191             AND address_rank between 16 and 25
 192       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 193     RETURN parent;
 194   END IF;
 195 {% endfor %}
 196
 197   RAISE EXCEPTION 'Unknown partition %', in_partition;
 198 END
 199 $$
 200 LANGUAGE plpgsql STABLE;
 201
 202 create or replace function insertSearchName(
 203   in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[],
 204   in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY)
 205 RETURNS BOOLEAN AS $$
 206 DECLARE
 207 BEGIN
 208 {% for partition in db.partitions %}
 209   IF in_partition = {{ partition }} THEN
 210     DELETE FROM search_name_{{ partition }} values WHERE place_id = in_place_id;
 211     IF in_rank_address > 0 THEN
 212       INSERT INTO search_name_{{ partition }} (place_id, address_rank, name_vector, centroid)
 213         values (in_place_id, in_rank_address, in_name_vector, in_geometry);
 214     END IF;
 215     RETURN TRUE;
 216   END IF;
 217 {% endfor %}
 218
 219   RAISE EXCEPTION 'Unknown partition %', in_partition;
 220   RETURN FALSE;
 221 END
 222 $$
 223 LANGUAGE plpgsql;
 224
 225 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 226 DECLARE
 227 BEGIN
 228 {% for partition in db.partitions %}
 229   IF in_partition = {{ partition }} THEN
 230     DELETE from search_name_{{ partition }} WHERE place_id = in_place_id;
 231     RETURN TRUE;
 232   END IF;
 233 {% endfor %}
 234
 235   RAISE EXCEPTION 'Unknown partition %', in_partition;
 236
 237   RETURN FALSE;
 238 END
 239 $$
 240 LANGUAGE plpgsql;
 241
 242 create or replace function insertLocationRoad(
 243   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 244 DECLARE
 245 BEGIN
 246
 247 {% for partition in db.partitions %}
 248   IF in_partition = {{ partition }} THEN
 249     DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
 250     INSERT INTO location_road_{{ partition }} (partition, place_id, country_code, geometry)
 251       values (in_partition, in_place_id, in_country_code, in_geometry);
 252     RETURN TRUE;
 253   END IF;
 254 {% endfor %}
 255
 256   RAISE EXCEPTION 'Unknown partition %', in_partition;
 257   RETURN FALSE;
 258 END
 259 $$
 260 LANGUAGE plpgsql;
 261
 262 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 263 DECLARE
 264 BEGIN
 265
 266 {% for partition in db.partitions %}
 267   IF in_partition = {{ partition }} THEN
 268     DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
 269     RETURN TRUE;
 270   END IF;
 271 {% endfor %}
 272
 273   RAISE EXCEPTION 'Unknown partition %', in_partition;
 274
 275   RETURN FALSE;
 276 END
 277 $$
 278 LANGUAGE plpgsql;
 279
 280 CREATE OR REPLACE FUNCTION getNearestRoadPlaceId(in_partition INTEGER, point GEOMETRY)
 281   RETURNS BIGINT
 282   AS $$
 283 DECLARE
 284   r RECORD;
 285   search_diameter FLOAT;
 286 BEGIN
 287
 288 {% for partition in db.partitions %}
 289   IF in_partition = {{ partition }} THEN
 290     search_diameter := 0.00005;
 291     WHILE search_diameter < 0.1 LOOP
 292       FOR r IN
 293         SELECT place_id FROM location_road_{{ partition }}
 294           WHERE ST_DWithin(geometry, point, search_diameter)
 295           ORDER BY ST_Distance(geometry, point) ASC limit 1
 296       LOOP
 297         RETURN r.place_id;
 298       END LOOP;
 299       search_diameter := search_diameter * 2;
 300     END LOOP;
 301     RETURN NULL;
 302   END IF;
 303 {% endfor %}
 304
 305   RAISE EXCEPTION 'Unknown partition %', in_partition;
 306 END
 307 $$
 308 LANGUAGE plpgsql STABLE;
 309
 310 CREATE OR REPLACE FUNCTION getNearestParallelRoadFeature(in_partition INTEGER,
 311                                                          line GEOMETRY)
 312   RETURNS BIGINT
 313   AS $$
 314 DECLARE
 315   r RECORD;
 316   search_diameter FLOAT;
 317   p1 GEOMETRY;
 318   p2 GEOMETRY;
 319   p3 GEOMETRY;
 320 BEGIN
 321
 322   IF ST_GeometryType(line) not in ('ST_LineString') THEN
 323     RETURN NULL;
 324   END IF;
 325
 326   p1 := ST_LineInterpolatePoint(line,0);
 327   p2 := ST_LineInterpolatePoint(line,0.5);
 328   p3 := ST_LineInterpolatePoint(line,1);
 329
 330 {% for partition in db.partitions %}
 331   IF in_partition = {{ partition }} THEN
 332     search_diameter := 0.0005;
 333     WHILE search_diameter < 0.01 LOOP
 334       FOR r IN
 335         SELECT place_id FROM location_road_{{ partition }}
 336           WHERE ST_DWithin(line, geometry, search_diameter)
 337           ORDER BY (ST_distance(geometry, p1)+
 338                     ST_distance(geometry, p2)+
 339                     ST_distance(geometry, p3)) ASC limit 1
 340       LOOP
 341         RETURN r.place_id;
 342       END LOOP;
 343       search_diameter := search_diameter * 2;
 344     END LOOP;
 345     RETURN NULL;
 346   END IF;
 347 {% endfor %}
 348
 349   RAISE EXCEPTION 'Unknown partition %', in_partition;
 350 END
 351 $$
 352 LANGUAGE plpgsql STABLE;