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