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