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