sql/partition-functions.src.sql

   1 create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$
   2 DECLARE
   3   r nearfeaturecentr%rowtype;
   4 BEGIN
   5
   6 -- start
   7   IF in_partition = -partition- THEN
   8     FOR r IN
   9       SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, centroid FROM (
  10         SELECT * FROM location_area_large_-partition- WHERE ST_Intersects(geometry, feature) and rank_search < maxrank
  11         UNION ALL
  12         SELECT * FROM location_area_country WHERE ST_Intersects(geometry, feature) and rank_search < maxrank
  13       ) as location_area
  14       GROUP BY place_id, keywords, rank_address, rank_search, isguess, centroid
  15       ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
  16         ST_Distance(feature, centroid) *
  17           CASE
  18                WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city
  19                WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city
  20                WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town
  21                ELSE 1 END ASC -- everything else
  22     LOOP
  23       RETURN NEXT r;
  24     END LOOP;
  25     RETURN;
  26   END IF;
  27 -- end
  28
  29   RAISE EXCEPTION 'Unknown partition %', in_partition;
  30 END
  31 $$
  32 LANGUAGE plpgsql;
  33
  34 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
  35 DECLARE
  36 BEGIN
  37
  38   IF in_rank_search <= 4 THEN
  39     DELETE from location_area_country WHERE place_id = in_place_id;
  40     RETURN TRUE;
  41   END IF;
  42
  43 -- start
  44   IF in_partition = -partition- THEN
  45     DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
  46     RETURN TRUE;
  47   END IF;
  48 -- end
  49
  50   RAISE EXCEPTION 'Unknown partition %', in_partition;
  51
  52   RETURN FALSE;
  53 END
  54 $$
  55 LANGUAGE plpgsql;
  56
  57 create or replace function insertLocationAreaLarge(
  58   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
  59   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN,
  60   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
  61 DECLARE
  62 BEGIN
  63   IF in_rank_address = 0 THEN
  64     RETURN TRUE;
  65   END IF;
  66
  67   IF in_rank_search <= 4 THEN
  68     INSERT INTO location_area_country (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, centroid, geometry)
  69       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
  70     RETURN TRUE;
  71   END IF;
  72
  73 -- start
  74   IF in_partition = -partition- THEN
  75     INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, centroid, geometry)
  76       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
  77     RETURN TRUE;
  78   END IF;
  79 -- end
  80
  81   RAISE EXCEPTION 'Unknown partition %', in_partition;
  82   RETURN FALSE;
  83 END
  84 $$
  85 LANGUAGE plpgsql;
  86
  87 create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$
  88 DECLARE
  89   r nearfeature%rowtype;
  90 BEGIN
  91
  92 -- start
  93   IF in_partition = -partition- THEN
  94     FOR r IN
  95       SELECT place_id, name_vector, address_rank, search_rank,
  96           ST_Distance(centroid, point) as distance, null as isguess
  97           FROM search_name_-partition-
  98           WHERE name_vector @> ARRAY[isin_token]
  99           AND search_rank < maxrank
 100       UNION ALL
 101       SELECT place_id, name_vector, address_rank, search_rank,
 102           ST_Distance(centroid, point) as distance, null as isguess
 103           FROM search_name_country
 104           WHERE name_vector @> ARRAY[isin_token]
 105           AND search_rank < maxrank
 106       ORDER BY distance ASC limit 1
 107     LOOP
 108       RETURN NEXT r;
 109     END LOOP;
 110     RETURN;
 111   END IF;
 112 -- end
 113
 114   RAISE EXCEPTION 'Unknown partition %', in_partition;
 115 END
 116 $$
 117 LANGUAGE plpgsql;
 118
 119 create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[])
 120   RETURNS setof nearfeature AS $$
 121 DECLARE
 122   r nearfeature%rowtype;
 123 BEGIN
 124
 125 -- start
 126   IF in_partition = -partition- THEN
 127     FOR r IN
 128       SELECT place_id, name_vector, address_rank, search_rank,
 129           ST_Distance(centroid, point) as distance, null as isguess
 130           FROM search_name_-partition-
 131           WHERE name_vector @> isin_token
 132           AND ST_DWithin(centroid, point, 0.01)
 133           AND search_rank between 26 and 27
 134       ORDER BY distance ASC limit 1
 135     LOOP
 136       RETURN NEXT r;
 137     END LOOP;
 138     RETURN;
 139   END IF;
 140 -- end
 141
 142   RAISE EXCEPTION 'Unknown partition %', in_partition;
 143 END
 144 $$
 145 LANGUAGE plpgsql;
 146
 147 create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[])
 148   RETURNS setof nearfeature AS $$
 149 DECLARE
 150   r nearfeature%rowtype;
 151 BEGIN
 152
 153 -- start
 154   IF in_partition = -partition- THEN
 155     FOR r IN
 156       SELECT place_id, name_vector, address_rank, search_rank,
 157           ST_Distance(centroid, point) as distance, null as isguess
 158           FROM search_name_-partition-
 159           WHERE name_vector @> isin_token
 160           AND ST_DWithin(centroid, point, 0.03)
 161           AND search_rank between 16 and 22
 162       ORDER BY distance ASC limit 1
 163     LOOP
 164       RETURN NEXT r;
 165     END LOOP;
 166     RETURN;
 167   END IF;
 168 -- end
 169
 170   RAISE EXCEPTION 'Unknown partition %', in_partition;
 171 END
 172 $$
 173 LANGUAGE plpgsql;
 174
 175
 176 create or replace function getNearestPostcode(in_partition INTEGER, point GEOMETRY)
 177   RETURNS TEXT AS $$
 178 DECLARE
 179   out_postcode TEXT;
 180 BEGIN
 181
 182 -- start
 183   IF in_partition = -partition- THEN
 184     SELECT postcode
 185         FROM location_area_large_-partition- join placex using (place_id)
 186         WHERE st_contains(location_area_large_-partition-.geometry, point)
 187         AND class = 'place' and type = 'postcode'
 188       ORDER BY st_distance(location_area_large_-partition-.centroid, point) ASC limit 1
 189       INTO out_postcode;
 190     RETURN out_postcode;
 191   END IF;
 192 -- end
 193
 194   RAISE EXCEPTION 'Unknown partition %', in_partition;
 195 END
 196 $$
 197 LANGUAGE plpgsql;
 198
 199 create or replace function insertSearchName(
 200   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2),
 201   in_name_vector INTEGER[], in_nameaddress_vector INTEGER[],
 202   in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT,
 203   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 204 DECLARE
 205 BEGIN
 206
 207   DELETE FROM search_name WHERE place_id = in_place_id;
 208   INSERT INTO search_name (place_id, search_rank, address_rank, importance, country_code, name_vector, nameaddress_vector, centroid)
 209     values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, in_name_vector, in_nameaddress_vector, in_centroid);
 210
 211   IF in_rank_search <= 4 THEN
 212     DELETE FROM search_name_country WHERE place_id = in_place_id;
 213     IF in_rank_address > 0 THEN
 214       INSERT INTO search_name_country (place_id, search_rank, address_rank, name_vector, centroid)
 215         values (in_place_id, in_rank_search, in_rank_address, in_name_vector, in_geometry);
 216     END IF;
 217     RETURN TRUE;
 218   END IF;
 219
 220 -- start
 221   IF in_partition = -partition- THEN
 222     DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
 223     IF in_rank_address > 0 THEN
 224       INSERT INTO search_name_-partition- (place_id, search_rank, address_rank, name_vector, centroid)
 225         values (in_place_id, in_rank_search, in_rank_address, in_name_vector, in_geometry);
 226     END IF;
 227     RETURN TRUE;
 228   END IF;
 229 -- end
 230
 231   RAISE EXCEPTION 'Unknown partition %', in_partition;
 232   RETURN FALSE;
 233 END
 234 $$
 235 LANGUAGE plpgsql;
 236
 237 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 238 DECLARE
 239 BEGIN
 240
 241   DELETE from search_name WHERE place_id = in_place_id;
 242   DELETE from search_name_country WHERE place_id = in_place_id;
 243
 244 -- start
 245   IF in_partition = -partition- THEN
 246     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 247     RETURN TRUE;
 248   END IF;
 249 -- end
 250
 251   RAISE EXCEPTION 'Unknown partition %', in_partition;
 252
 253   RETURN FALSE;
 254 END
 255 $$
 256 LANGUAGE plpgsql;
 257
 258 create or replace function insertLocationRoad(
 259   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 260 DECLARE
 261 BEGIN
 262
 263 -- start
 264   IF in_partition = -partition- THEN
 265     DELETE FROM location_road_-partition- where place_id = in_place_id;
 266     INSERT INTO location_road_-partition- (partition, place_id, country_code, geometry)
 267       values (in_partition, in_place_id, in_country_code, in_geometry);
 268     RETURN TRUE;
 269   END IF;
 270 -- end
 271
 272   RAISE EXCEPTION 'Unknown partition %', in_partition;
 273   RETURN FALSE;
 274 END
 275 $$
 276 LANGUAGE plpgsql;
 277
 278 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 279 DECLARE
 280 BEGIN
 281
 282 -- start
 283   IF in_partition = -partition- THEN
 284     DELETE FROM location_road_-partition- where place_id = in_place_id;
 285     RETURN TRUE;
 286   END IF;
 287 -- end
 288
 289   RAISE EXCEPTION 'Unknown partition %', in_partition;
 290
 291   RETURN FALSE;
 292 END
 293 $$
 294 LANGUAGE plpgsql;
 295
 296 create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$
 297 DECLARE
 298   r nearfeature%rowtype;
 299   search_diameter FLOAT;
 300 BEGIN
 301
 302 -- start
 303   IF in_partition = -partition- THEN
 304     search_diameter := 0.00005;
 305     WHILE search_diameter < 0.1 LOOP
 306       FOR r IN
 307         SELECT place_id, null, null, null,
 308             ST_Distance(geometry, point) as distance, null as isguess
 309             FROM location_road_-partition-
 310             WHERE ST_DWithin(geometry, point, search_diameter)
 311         ORDER BY distance ASC limit 1
 312       LOOP
 313         RETURN NEXT r;
 314         RETURN;
 315       END LOOP;
 316       search_diameter := search_diameter * 2;
 317     END LOOP;
 318     RETURN;
 319   END IF;
 320 -- end
 321
 322   RAISE EXCEPTION 'Unknown partition %', in_partition;
 323 END
 324 $$
 325 LANGUAGE plpgsql;
 326
 327 create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$
 328 DECLARE
 329   r nearfeature%rowtype;
 330   search_diameter FLOAT;
 331   p1 GEOMETRY;
 332   p2 GEOMETRY;
 333   p3 GEOMETRY;
 334 BEGIN
 335
 336   IF st_geometrytype(line) not in ('ST_LineString') THEN
 337     RETURN;
 338   END IF;
 339
 340   p1 := ST_LineInterpolatePoint(line,0);
 341   p2 := ST_LineInterpolatePoint(line,0.5);
 342   p3 := ST_LineInterpolatePoint(line,1);
 343
 344 -- start
 345   IF in_partition = -partition- THEN
 346     search_diameter := 0.0005;
 347     WHILE search_diameter < 0.01 LOOP
 348       FOR r IN
 349         SELECT place_id, null, null, null,
 350             ST_Distance(geometry, line) as distance, null as isguess
 351             FROM location_road_-partition-
 352             WHERE ST_DWithin(line, geometry, search_diameter)
 353             ORDER BY (ST_distance(geometry, p1)+
 354                       ST_distance(geometry, p2)+
 355                       ST_distance(geometry, p3)) ASC limit 1
 356       LOOP
 357         RETURN NEXT r;
 358         RETURN;
 359       END LOOP;
 360       search_diameter := search_diameter * 2;
 361     END LOOP;
 362     RETURN;
 363   END IF;
 364 -- end
 365
 366   RAISE EXCEPTION 'Unknown partition %', in_partition;
 367 END
 368 $$
 369 LANGUAGE plpgsql;