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