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) 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,
  44              min(ST_Distance(feature, centroid)) as distance,
  45              isguess, postcode, centroid
  46       FROM location_area_large_-partition-
  47       WHERE geometry && feature
  48         AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature))
  49         AND rank_address < maxrank
  50       GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  51     LOOP
  52       RETURN NEXT r;
  53     END LOOP;
  54     RETURN;
  55   END IF;
  56 -- end
  57
  58   RAISE EXCEPTION 'Unknown partition %', in_partition;
  59 END
  60 $$
  61 LANGUAGE plpgsql STABLE;
  62
  63 CREATE OR REPLACE FUNCTION get_places_for_addr_tags(in_partition SMALLINT,
  64                                                     feature GEOMETRY,
  65                                                     address HSTORE, country TEXT)
  66   RETURNS SETOF nearfeaturecentr
  67   AS $$
  68 DECLARE
  69   r nearfeaturecentr%rowtype;
  70   item RECORD;
  71 BEGIN
  72   FOR item IN
  73     SELECT (get_addr_tag_rank(key, country)).*, key, name FROM
  74       (SELECT skeys(address) as key, svals(address) as name) x
  75   LOOP
  76    IF item.from_rank is null THEN
  77      CONTINUE;
  78    END IF;
  79
  80 -- start
  81     IF in_partition = -partition- THEN
  82         SELECT place_id, keywords, rank_address, rank_search,
  83                min(ST_Distance(feature, centroid)) as distance,
  84                isguess, postcode, centroid INTO r
  85         FROM location_area_large_-partition-
  86         WHERE geometry && ST_Expand(feature, item.extent)
  87           AND rank_address between item.from_rank and item.to_rank
  88           AND word_ids_from_name(item.name) && keywords
  89         GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  90         ORDER BY bool_or(ST_Intersects(geometry, feature)), distance LIMIT 1;
  91       IF r.place_id is null THEN
  92         -- If we cannot find a place for the term, just return the
  93         -- search term for the given name. That ensures that the address
  94         -- element can still be searched for, even though it will not be
  95         -- displayed.
  96         RETURN NEXT ROW(null, addr_ids_from_name(item.name), null, null,
  97                         null, null, null, null)::nearfeaturecentr;
  98       ELSE
  99         RETURN NEXT r;
 100       END IF;
 101       CONTINUE;
 102     END IF;
 103 -- end
 104
 105     RAISE EXCEPTION 'Unknown partition %', in_partition;
 106   END LOOP;
 107 END;
 108 $$
 109 LANGUAGE plpgsql STABLE;
 110
 111 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
 112 DECLARE
 113 BEGIN
 114
 115   IF in_rank_search <= 4 THEN
 116     DELETE from location_area_country WHERE place_id = in_place_id;
 117     RETURN TRUE;
 118   END IF;
 119
 120 -- start
 121   IF in_partition = -partition- THEN
 122     DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
 123     RETURN TRUE;
 124   END IF;
 125 -- end
 126
 127   RAISE EXCEPTION 'Unknown partition %', in_partition;
 128
 129   RETURN FALSE;
 130 END
 131 $$
 132 LANGUAGE plpgsql;
 133
 134 create or replace function insertLocationAreaLarge(
 135   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
 136   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT,
 137   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 138 DECLARE
 139 BEGIN
 140   IF in_rank_address = 0 THEN
 141     RETURN TRUE;
 142   END IF;
 143
 144   IF in_rank_search <= 4 and not in_estimate THEN
 145     INSERT INTO location_area_country (place_id, country_code, geometry)
 146       values (in_place_id, in_country_code, in_geometry);
 147     RETURN TRUE;
 148   END IF;
 149
 150 -- start
 151   IF in_partition = -partition- THEN
 152     INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
 153       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
 154     RETURN TRUE;
 155   END IF;
 156 -- end
 157
 158   RAISE EXCEPTION 'Unknown partition %', in_partition;
 159   RETURN FALSE;
 160 END
 161 $$
 162 LANGUAGE plpgsql;
 163
 164 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER,
 165                                                       point GEOMETRY,
 166                                                       isin_token INTEGER[])
 167   RETURNS BIGINT
 168   AS $$
 169 DECLARE
 170   parent BIGINT;
 171 BEGIN
 172
 173 -- start
 174   IF in_partition = -partition- THEN
 175     SELECT place_id FROM search_name_-partition-
 176       INTO parent
 177       WHERE name_vector && isin_token
 178             AND centroid && ST_Expand(point, 0.015)
 179             AND address_rank between 26 and 27
 180       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 181     RETURN parent;
 182   END IF;
 183 -- end
 184
 185   RAISE EXCEPTION 'Unknown partition %', in_partition;
 186 END
 187 $$
 188 LANGUAGE plpgsql STABLE;
 189
 190 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
 191                                                        point GEOMETRY,
 192                                                        isin_token INTEGER[])
 193   RETURNS BIGINT
 194   AS $$
 195 DECLARE
 196   parent BIGINT;
 197 BEGIN
 198
 199 -- start
 200   IF in_partition = -partition- THEN
 201     SELECT place_id
 202       INTO parent
 203       FROM search_name_-partition-
 204       WHERE name_vector && isin_token
 205             AND centroid && ST_Expand(point, 0.04)
 206             AND address_rank between 16 and 25
 207       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 208     RETURN parent;
 209   END IF;
 210 -- end
 211
 212   RAISE EXCEPTION 'Unknown partition %', in_partition;
 213 END
 214 $$
 215 LANGUAGE plpgsql STABLE;
 216
 217 create or replace function insertSearchName(
 218   in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[],
 219   in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY)
 220 RETURNS BOOLEAN AS $$
 221 DECLARE
 222 BEGIN
 223 -- start
 224   IF in_partition = -partition- THEN
 225     DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
 226     IF in_rank_address > 0 THEN
 227       INSERT INTO search_name_-partition- (place_id, address_rank, name_vector, centroid)
 228         values (in_place_id, in_rank_address, in_name_vector, in_geometry);
 229     END IF;
 230     RETURN TRUE;
 231   END IF;
 232 -- end
 233
 234   RAISE EXCEPTION 'Unknown partition %', in_partition;
 235   RETURN FALSE;
 236 END
 237 $$
 238 LANGUAGE plpgsql;
 239
 240 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 241 DECLARE
 242 BEGIN
 243 -- start
 244   IF in_partition = -partition- THEN
 245     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 246     RETURN TRUE;
 247   END IF;
 248 -- end
 249
 250   RAISE EXCEPTION 'Unknown partition %', in_partition;
 251
 252   RETURN FALSE;
 253 END
 254 $$
 255 LANGUAGE plpgsql;
 256
 257 create or replace function insertLocationRoad(
 258   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 259 DECLARE
 260 BEGIN
 261
 262 -- start
 263   IF in_partition = -partition- THEN
 264     DELETE FROM location_road_-partition- where place_id = in_place_id;
 265     INSERT INTO location_road_-partition- (partition, place_id, country_code, geometry)
 266       values (in_partition, in_place_id, in_country_code, in_geometry);
 267     RETURN TRUE;
 268   END IF;
 269 -- end
 270
 271   RAISE EXCEPTION 'Unknown partition %', in_partition;
 272   RETURN FALSE;
 273 END
 274 $$
 275 LANGUAGE plpgsql;
 276
 277 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 278 DECLARE
 279 BEGIN
 280
 281 -- start
 282   IF in_partition = -partition- THEN
 283     DELETE FROM location_road_-partition- where place_id = in_place_id;
 284     RETURN TRUE;
 285   END IF;
 286 -- end
 287
 288   RAISE EXCEPTION 'Unknown partition %', in_partition;
 289
 290   RETURN FALSE;
 291 END
 292 $$
 293 LANGUAGE plpgsql;
 294
 295 CREATE OR REPLACE FUNCTION getNearestRoadPlaceId(in_partition INTEGER, point GEOMETRY)
 296   RETURNS BIGINT
 297   AS $$
 298 DECLARE
 299   r RECORD;
 300   search_diameter FLOAT;
 301 BEGIN
 302
 303 -- start
 304   IF in_partition = -partition- THEN
 305     search_diameter := 0.00005;
 306     WHILE search_diameter < 0.1 LOOP
 307       FOR r IN
 308         SELECT place_id FROM location_road_-partition-
 309           WHERE ST_DWithin(geometry, point, search_diameter)
 310           ORDER BY ST_Distance(geometry, point) ASC limit 1
 311       LOOP
 312         RETURN r.place_id;
 313       END LOOP;
 314       search_diameter := search_diameter * 2;
 315     END LOOP;
 316     RETURN NULL;
 317   END IF;
 318 -- end
 319
 320   RAISE EXCEPTION 'Unknown partition %', in_partition;
 321 END
 322 $$
 323 LANGUAGE plpgsql STABLE;
 324
 325 CREATE OR REPLACE FUNCTION getNearestParallelRoadFeature(in_partition INTEGER,
 326                                                          line GEOMETRY)
 327   RETURNS BIGINT
 328   AS $$
 329 DECLARE
 330   r RECORD;
 331   search_diameter FLOAT;
 332   p1 GEOMETRY;
 333   p2 GEOMETRY;
 334   p3 GEOMETRY;
 335 BEGIN
 336
 337   IF ST_GeometryType(line) not in ('ST_LineString') THEN
 338     RETURN NULL;
 339   END IF;
 340
 341   p1 := ST_LineInterpolatePoint(line,0);
 342   p2 := ST_LineInterpolatePoint(line,0.5);
 343   p3 := ST_LineInterpolatePoint(line,1);
 344
 345 -- start
 346   IF in_partition = -partition- THEN
 347     search_diameter := 0.0005;
 348     WHILE search_diameter < 0.01 LOOP
 349       FOR r IN
 350         SELECT place_id FROM location_road_-partition-
 351           WHERE ST_DWithin(line, geometry, search_diameter)
 352           ORDER BY (ST_distance(geometry, p1)+
 353                     ST_distance(geometry, p2)+
 354                     ST_distance(geometry, p3)) ASC limit 1
 355       LOOP
 356         RETURN r.place_id;
 357       END LOOP;
 358       search_diameter := search_diameter * 2;
 359     END LOOP;
 360     RETURN NULL;
 361   END IF;
 362 -- end
 363
 364   RAISE EXCEPTION 'Unknown partition %', in_partition;
 365 END
 366 $$
 367 LANGUAGE plpgsql STABLE;