sql/partition-functions.src.sql

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