sql/partition-functions.src.sql

   1 create or replace function getNearFeatures(in_partition INTEGER, point 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(point, centroid)) as distance, isguess, centroid FROM (
  10         SELECT * FROM location_area_large_-partition- WHERE ST_Contains(geometry, point) and rank_search < maxrank
  11         UNION ALL
  12         SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) 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(point, 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
  64   IF in_rank_search <= 4 THEN
  65     INSERT INTO location_area_country values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
  66     RETURN TRUE;
  67   END IF;
  68
  69 -- start
  70   IF in_partition = -partition- THEN
  71     INSERT INTO location_area_large_-partition- values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
  72     RETURN TRUE;
  73   END IF;
  74 -- end
  75
  76   RAISE EXCEPTION 'Unknown partition %', in_partition;
  77   RETURN FALSE;
  78 END
  79 $$
  80 LANGUAGE plpgsql;
  81
  82 create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$
  83 DECLARE
  84   r nearfeature%rowtype;
  85 BEGIN
  86
  87 -- start
  88   IF in_partition = -partition- THEN
  89     FOR r IN
  90       SELECT place_id, name_vector, address_rank, search_rank,
  91           ST_Distance(centroid, point) as distance, null as isguess
  92           FROM search_name_-partition-
  93           WHERE name_vector @> ARRAY[isin_token]
  94           AND search_rank < maxrank
  95       UNION ALL
  96       SELECT place_id, name_vector, address_rank, search_rank,
  97           ST_Distance(centroid, point) as distance, null as isguess
  98           FROM search_name_country
  99           WHERE name_vector @> ARRAY[isin_token]
 100           AND search_rank < maxrank
 101       ORDER BY distance ASC limit 1
 102     LOOP
 103       RETURN NEXT r;
 104     END LOOP;
 105     RETURN;
 106   END IF;
 107 -- end
 108
 109   RAISE EXCEPTION 'Unknown partition %', in_partition;
 110 END
 111 $$
 112 LANGUAGE plpgsql;
 113
 114 create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER)
 115   RETURNS setof nearfeature AS $$
 116 DECLARE
 117   r nearfeature%rowtype;
 118 BEGIN
 119
 120 -- start
 121   IF in_partition = -partition- THEN
 122     FOR r IN
 123       SELECT place_id, name_vector, address_rank, search_rank,
 124           ST_Distance(centroid, point) as distance, null as isguess
 125           FROM search_name_-partition-
 126           WHERE name_vector @> ARRAY[isin_token]
 127           AND ST_DWithin(centroid, point, 0.01)
 128           AND search_rank between 26 and 27
 129       ORDER BY distance ASC limit 1
 130     LOOP
 131       RETURN NEXT r;
 132     END LOOP;
 133     RETURN;
 134   END IF;
 135 -- end
 136
 137   RAISE EXCEPTION 'Unknown partition %', in_partition;
 138 END
 139 $$
 140 LANGUAGE plpgsql;
 141
 142 create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER)
 143   RETURNS setof nearfeature AS $$
 144 DECLARE
 145   r nearfeature%rowtype;
 146 BEGIN
 147
 148 -- start
 149   IF in_partition = -partition- THEN
 150     FOR r IN
 151       SELECT place_id, name_vector, address_rank, search_rank,
 152           ST_Distance(centroid, point) as distance, null as isguess
 153           FROM search_name_-partition-
 154           WHERE name_vector @> ARRAY[isin_token]
 155           AND ST_DWithin(centroid, point, 0.03)
 156           AND search_rank between 16 and 22
 157       ORDER BY distance ASC limit 1
 158     LOOP
 159       RETURN NEXT r;
 160     END LOOP;
 161     RETURN;
 162   END IF;
 163 -- end
 164
 165   RAISE EXCEPTION 'Unknown partition %', in_partition;
 166 END
 167 $$
 168 LANGUAGE plpgsql;
 169
 170
 171 create or replace function getNearestPostcode(in_partition INTEGER, point GEOMETRY)
 172   RETURNS TEXT AS $$
 173 DECLARE
 174   out_postcode TEXT;
 175 BEGIN
 176
 177 -- start
 178   IF in_partition = -partition- THEN
 179     SELECT postcode
 180         FROM location_area_large_-partition- join placex using (place_id)
 181         WHERE st_contains(location_area_large_-partition-.geometry, point)
 182         AND class = 'place' and type = 'postcode'
 183       ORDER BY st_distance(location_area_large_-partition-.centroid, point) ASC limit 1
 184       INTO out_postcode;
 185     RETURN out_postcode;
 186   END IF;
 187 -- end
 188
 189   RAISE EXCEPTION 'Unknown partition %', in_partition;
 190 END
 191 $$
 192 LANGUAGE plpgsql;
 193
 194 create or replace function insertSearchName(
 195   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2),
 196   in_name_vector INTEGER[], in_nameaddress_vector INTEGER[],
 197   in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT,
 198   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 199 DECLARE
 200 BEGIN
 201
 202   DELETE FROM search_name WHERE place_id = in_place_id;
 203   INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code,
 204     in_name_vector, in_nameaddress_vector, in_centroid);
 205
 206   IF in_rank_search <= 4 THEN
 207     DELETE FROM search_name_country WHERE place_id = in_place_id;
 208     INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address,
 209       in_name_vector, in_geometry);
 210     RETURN TRUE;
 211   END IF;
 212
 213 -- start
 214   IF in_partition = -partition- THEN
 215     DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
 216     INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address,
 217       in_name_vector, in_geometry);
 218     RETURN TRUE;
 219   END IF;
 220 -- end
 221
 222   RAISE EXCEPTION 'Unknown partition %', in_partition;
 223   RETURN FALSE;
 224 END
 225 $$
 226 LANGUAGE plpgsql;
 227
 228 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 229 DECLARE
 230 BEGIN
 231
 232   DELETE from search_name WHERE place_id = in_place_id;
 233   DELETE from search_name_country WHERE place_id = in_place_id;
 234
 235 -- start
 236   IF in_partition = -partition- THEN
 237     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 238     RETURN TRUE;
 239   END IF;
 240 -- end
 241
 242   RAISE EXCEPTION 'Unknown partition %', in_partition;
 243
 244   RETURN FALSE;
 245 END
 246 $$
 247 LANGUAGE plpgsql;
 248
 249 create or replace function insertLocationRoad(
 250   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 251 DECLARE
 252 BEGIN
 253
 254 -- start
 255   IF in_partition = -partition- THEN
 256     DELETE FROM location_road_-partition- where place_id = in_place_id;
 257     INSERT INTO location_road_-partition- values (in_partition, in_place_id, in_country_code, in_geometry);
 258     RETURN TRUE;
 259   END IF;
 260 -- end
 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 -- start
 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 -- end
 278
 279   RAISE EXCEPTION 'Unknown partition %', in_partition;
 280
 281   RETURN FALSE;
 282 END
 283 $$
 284 LANGUAGE plpgsql;
 285
 286 create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$
 287 DECLARE
 288   r nearfeature%rowtype;
 289   search_diameter FLOAT;
 290 BEGIN
 291
 292 -- start
 293   IF in_partition = -partition- THEN
 294     search_diameter := 0.00005;
 295     WHILE search_diameter < 0.1 LOOP
 296       FOR r IN
 297         SELECT place_id, null, null, null,
 298             ST_Distance(geometry, point) as distance, null as isguess
 299             FROM location_road_-partition-
 300             WHERE ST_DWithin(geometry, point, search_diameter)
 301         ORDER BY distance ASC limit 1
 302       LOOP
 303         RETURN NEXT r;
 304         RETURN;
 305       END LOOP;
 306       search_diameter := search_diameter * 2;
 307     END LOOP;
 308     RETURN;
 309   END IF;
 310 -- end
 311
 312   RAISE EXCEPTION 'Unknown partition %', in_partition;
 313 END
 314 $$
 315 LANGUAGE plpgsql;
 316
 317 create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$
 318 DECLARE
 319   r nearfeature%rowtype;
 320   search_diameter FLOAT;
 321   p1 GEOMETRY;
 322   p2 GEOMETRY;
 323   p3 GEOMETRY;
 324 BEGIN
 325
 326   IF st_geometrytype(line) not in ('ST_LineString') THEN
 327     RETURN;
 328   END IF;
 329
 330   p1 := ST_Line_Interpolate_Point(line,0);
 331   p2 := ST_Line_Interpolate_Point(line,0.5);
 332   p3 := ST_Line_Interpolate_Point(line,1);
 333
 334 -- start
 335   IF in_partition = -partition- THEN
 336     search_diameter := 0.0005;
 337     WHILE search_diameter < 0.01 LOOP
 338       FOR r IN
 339         SELECT place_id, null, null, null,
 340             ST_Distance(geometry, line) as distance, null as isguess
 341             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 NEXT r;
 348         RETURN;
 349       END LOOP;
 350       search_diameter := search_diameter * 2;
 351     END LOOP;
 352     RETURN;
 353   END IF;
 354 -- end
 355
 356   RAISE EXCEPTION 'Unknown partition %', in_partition;
 357 END
 358 $$
 359 LANGUAGE plpgsql;