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