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