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
  10       FROM location_area_large_-partition-
  11       WHERE ST_Intersects(geometry, feature) and rank_search < maxrank
  12       GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  13       ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
  14         ST_Distance(feature, centroid) *
  15           CASE
  16                WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city
  17                WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city
  18                WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town
  19                ELSE 1 END ASC -- everything else
  20     LOOP
  21       RETURN NEXT r;
  22     END LOOP;
  23     RETURN;
  24   END IF;
  25 -- end
  26
  27   RAISE EXCEPTION 'Unknown partition %', in_partition;
  28 END
  29 $$
  30 LANGUAGE plpgsql;
  31
  32 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
  33 DECLARE
  34 BEGIN
  35
  36   IF in_rank_search <= 4 THEN
  37     DELETE from location_area_country WHERE place_id = in_place_id;
  38     RETURN TRUE;
  39   END IF;
  40
  41 -- start
  42   IF in_partition = -partition- THEN
  43     DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
  44     RETURN TRUE;
  45   END IF;
  46 -- end
  47
  48   RAISE EXCEPTION 'Unknown partition %', in_partition;
  49
  50   RETURN FALSE;
  51 END
  52 $$
  53 LANGUAGE plpgsql;
  54
  55 create or replace function insertLocationAreaLarge(
  56   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
  57   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT,
  58   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
  59 DECLARE
  60 BEGIN
  61   IF in_rank_address = 0 THEN
  62     RETURN TRUE;
  63   END IF;
  64
  65   IF in_rank_search <= 4 and not in_estimate THEN
  66     INSERT INTO location_area_country (place_id, country_code, geometry)
  67       values (in_place_id, in_country_code, in_geometry);
  68     RETURN TRUE;
  69   END IF;
  70
  71 -- start
  72   IF in_partition = -partition- THEN
  73     INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
  74       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, 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 getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[])
  86   RETURNS setof nearfeature AS $$
  87 DECLARE
  88   r nearfeature%rowtype;
  89 BEGIN
  90
  91 -- start
  92   IF in_partition = -partition- THEN
  93     FOR r IN
  94       SELECT place_id, name_vector, address_rank, search_rank,
  95           ST_Distance(centroid, point) as distance, null as isguess
  96           FROM search_name_-partition-
  97           WHERE name_vector && isin_token
  98           AND ST_DWithin(centroid, point, 0.015)
  99           AND search_rank between 26 and 27
 100       ORDER BY distance ASC limit 1
 101     LOOP
 102       RETURN NEXT r;
 103     END LOOP;
 104     RETURN;
 105   END IF;
 106 -- end
 107
 108   RAISE EXCEPTION 'Unknown partition %', in_partition;
 109 END
 110 $$
 111 LANGUAGE plpgsql;
 112
 113 create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[])
 114   RETURNS setof nearfeature AS $$
 115 DECLARE
 116   r nearfeature%rowtype;
 117 BEGIN
 118
 119 -- start
 120   IF in_partition = -partition- THEN
 121     FOR r IN
 122       SELECT place_id, name_vector, address_rank, search_rank,
 123           ST_Distance(centroid, point) as distance, null as isguess
 124           FROM search_name_-partition-
 125           WHERE name_vector && isin_token
 126           AND ST_DWithin(centroid, point, 0.04)
 127           AND search_rank between 16 and 22
 128       ORDER BY distance ASC limit 1
 129     LOOP
 130       RETURN NEXT r;
 131     END LOOP;
 132     RETURN;
 133   END IF;
 134 -- end
 135
 136   RAISE EXCEPTION 'Unknown partition %', in_partition;
 137 END
 138 $$
 139 LANGUAGE plpgsql;
 140
 141
 142 create or replace function insertSearchName(
 143   in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[],
 144   in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY)
 145 RETURNS BOOLEAN AS $$
 146 DECLARE
 147 BEGIN
 148 -- start
 149   IF in_partition = -partition- THEN
 150     DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
 151     IF in_rank_address > 0 THEN
 152       INSERT INTO search_name_-partition- (place_id, search_rank, address_rank, name_vector, centroid)
 153         values (in_place_id, in_rank_search, in_rank_address, in_name_vector, in_geometry);
 154     END IF;
 155     RETURN TRUE;
 156   END IF;
 157 -- end
 158
 159   RAISE EXCEPTION 'Unknown partition %', in_partition;
 160   RETURN FALSE;
 161 END
 162 $$
 163 LANGUAGE plpgsql;
 164
 165 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 166 DECLARE
 167 BEGIN
 168 -- start
 169   IF in_partition = -partition- THEN
 170     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 171     RETURN TRUE;
 172   END IF;
 173 -- end
 174
 175   RAISE EXCEPTION 'Unknown partition %', in_partition;
 176
 177   RETURN FALSE;
 178 END
 179 $$
 180 LANGUAGE plpgsql;
 181
 182 create or replace function insertLocationRoad(
 183   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 184 DECLARE
 185 BEGIN
 186
 187 -- start
 188   IF in_partition = -partition- THEN
 189     DELETE FROM location_road_-partition- where place_id = in_place_id;
 190     INSERT INTO location_road_-partition- (partition, place_id, country_code, geometry)
 191       values (in_partition, in_place_id, in_country_code, in_geometry);
 192     RETURN TRUE;
 193   END IF;
 194 -- end
 195
 196   RAISE EXCEPTION 'Unknown partition %', in_partition;
 197   RETURN FALSE;
 198 END
 199 $$
 200 LANGUAGE plpgsql;
 201
 202 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 203 DECLARE
 204 BEGIN
 205
 206 -- start
 207   IF in_partition = -partition- THEN
 208     DELETE FROM location_road_-partition- where place_id = in_place_id;
 209     RETURN TRUE;
 210   END IF;
 211 -- end
 212
 213   RAISE EXCEPTION 'Unknown partition %', in_partition;
 214
 215   RETURN FALSE;
 216 END
 217 $$
 218 LANGUAGE plpgsql;
 219
 220 create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$
 221 DECLARE
 222   r nearfeature%rowtype;
 223   search_diameter FLOAT;
 224 BEGIN
 225
 226 -- start
 227   IF in_partition = -partition- THEN
 228     search_diameter := 0.00005;
 229     WHILE search_diameter < 0.1 LOOP
 230       FOR r IN
 231         SELECT place_id, null, null, null,
 232             ST_Distance(geometry, point) as distance, null as isguess
 233             FROM location_road_-partition-
 234             WHERE ST_DWithin(geometry, point, search_diameter)
 235         ORDER BY distance ASC limit 1
 236       LOOP
 237         RETURN NEXT r;
 238         RETURN;
 239       END LOOP;
 240       search_diameter := search_diameter * 2;
 241     END LOOP;
 242     RETURN;
 243   END IF;
 244 -- end
 245
 246   RAISE EXCEPTION 'Unknown partition %', in_partition;
 247 END
 248 $$
 249 LANGUAGE plpgsql;
 250
 251 create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$
 252 DECLARE
 253   r nearfeature%rowtype;
 254   search_diameter FLOAT;
 255   p1 GEOMETRY;
 256   p2 GEOMETRY;
 257   p3 GEOMETRY;
 258 BEGIN
 259
 260   IF st_geometrytype(line) not in ('ST_LineString') THEN
 261     RETURN;
 262   END IF;
 263
 264   p1 := ST_LineInterpolatePoint(line,0);
 265   p2 := ST_LineInterpolatePoint(line,0.5);
 266   p3 := ST_LineInterpolatePoint(line,1);
 267
 268 -- start
 269   IF in_partition = -partition- THEN
 270     search_diameter := 0.0005;
 271     WHILE search_diameter < 0.01 LOOP
 272       FOR r IN
 273         SELECT place_id, null, null, null,
 274             ST_Distance(geometry, line) as distance, null as isguess
 275             FROM location_road_-partition-
 276             WHERE ST_DWithin(line, geometry, search_diameter)
 277             ORDER BY (ST_distance(geometry, p1)+
 278                       ST_distance(geometry, p2)+
 279                       ST_distance(geometry, p3)) ASC limit 1
 280       LOOP
 281         RETURN NEXT r;
 282         RETURN;
 283       END LOOP;
 284       search_diameter := search_diameter * 2;
 285     END LOOP;
 286     RETURN;
 287   END IF;
 288 -- end
 289
 290   RAISE EXCEPTION 'Unknown partition %', in_partition;
 291 END
 292 $$
 293 LANGUAGE plpgsql;