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