sql/partitions.src.sql

   1 drop type nearplace cascade;
   2 create type nearplace as (
   3   place_id BIGINT
   4 );
   5
   6 drop type nearfeature cascade;
   7 create type nearfeature as (
   8   place_id BIGINT,
   9   keywords int[],
  10   rank_address integer,
  11   rank_search integer,
  12   distance float,
  13   isguess boolean
  14 );
  15
  16 CREATE TABLE location_area_country () INHERITS (location_area_large);
  17 CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id);
  18 CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry);
  19
  20 CREATE TABLE search_name_country () INHERITS (search_name_blank);
  21 CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id);
  22 CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid);
  23 CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off);
  24 CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector) WITH (fastupdate = off);
  25
  26 -- start
  27 CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large);
  28 CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id);
  29 CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry);
  30
  31 CREATE TABLE search_name_-partition- () INHERITS (search_name_blank);
  32 CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id);
  33 CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid);
  34 CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off);
  35 CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector) WITH (fastupdate = off);
  36
  37 CREATE TABLE location_property_-partition- () INHERITS (location_property);
  38 CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id);
  39 CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id);
  40 CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber);
  41 --CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid);
  42
  43 CREATE TABLE location_road_-partition- (
  44   partition integer,
  45   place_id BIGINT,
  46   country_code VARCHAR(2)
  47   );
  48 SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2);
  49 CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry);
  50 CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id);
  51
  52 -- end
  53
  54 create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeature AS $$
  55 DECLARE
  56   r nearfeature%rowtype;
  57 BEGIN
  58
  59 -- start
  60   IF in_partition = -partition- THEN
  61     FOR r IN
  62       SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(point, centroid)) as distance, isguess FROM (
  63         SELECT * FROM location_area_large_-partition- WHERE ST_Contains(geometry, point) and rank_search < maxrank
  64         UNION ALL
  65         SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) and rank_search < maxrank
  66       ) as location_area
  67       GROUP BY place_id, keywords, rank_address, rank_search, isguess, centroid
  68       ORDER BY rank_address desc, isin_tokens && keywords desc, isguess asc,
  69         ST_Distance(point, centroid) *
  70           CASE
  71                WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city
  72                WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city
  73                WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town
  74                ELSE 1 END ASC -- everything else
  75     LOOP
  76       RETURN NEXT r;
  77     END LOOP;
  78     RETURN;
  79   END IF;
  80 -- end
  81
  82   RAISE EXCEPTION 'Unknown partition %', in_partition;
  83 END
  84 $$
  85 LANGUAGE plpgsql;
  86
  87 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
  88 DECLARE
  89 BEGIN
  90
  91 -- start
  92   IF in_partition = -partition- THEN
  93     DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
  94     RETURN TRUE;
  95   END IF;
  96 -- end
  97
  98   RAISE EXCEPTION 'Unknown partition %', in_partition;
  99
 100   RETURN FALSE;
 101 END
 102 $$
 103 LANGUAGE plpgsql;
 104
 105 create or replace function insertLocationAreaLarge(
 106   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
 107   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN,
 108   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 109 DECLARE
 110 BEGIN
 111
 112   IF in_rank_search <= 4 THEN
 113     INSERT INTO location_area_country values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
 114     RETURN TRUE;
 115   END IF;
 116
 117 -- start
 118   IF in_partition = -partition- THEN
 119     INSERT INTO location_area_large_-partition- values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
 120     RETURN TRUE;
 121   END IF;
 122 -- end
 123
 124   RAISE EXCEPTION 'Unknown partition %', in_partition;
 125   RETURN FALSE;
 126 END
 127 $$
 128 LANGUAGE plpgsql;
 129
 130 create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$
 131 DECLARE
 132   r nearfeature%rowtype;
 133 BEGIN
 134
 135 -- start
 136   IF in_partition = -partition- THEN
 137     FOR r IN
 138       SELECT place_id, name_vector, address_rank, search_rank,
 139           ST_Distance(centroid, point) as distance, null as isguess
 140           FROM search_name_-partition-
 141           WHERE name_vector @> ARRAY[isin_token]
 142           AND search_rank < maxrank
 143       UNION ALL
 144       SELECT place_id, name_vector, address_rank, search_rank,
 145           ST_Distance(centroid, point) as distance, null as isguess
 146           FROM search_name_country
 147           WHERE name_vector @> ARRAY[isin_token]
 148           AND search_rank < maxrank
 149       ORDER BY distance ASC limit 1
 150     LOOP
 151       RETURN NEXT r;
 152     END LOOP;
 153     RETURN;
 154   END IF;
 155 -- end
 156
 157   RAISE EXCEPTION 'Unknown partition %', in_partition;
 158 END
 159 $$
 160 LANGUAGE plpgsql;
 161
 162 create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER)
 163   RETURNS setof nearfeature AS $$
 164 DECLARE
 165   r nearfeature%rowtype;
 166 BEGIN
 167
 168 -- start
 169   IF in_partition = -partition- THEN
 170     FOR r IN
 171       SELECT place_id, name_vector, address_rank, search_rank,
 172           ST_Distance(centroid, point) as distance, null as isguess
 173           FROM search_name_-partition-
 174           WHERE name_vector @> ARRAY[isin_token]
 175           AND ST_DWithin(centroid, point, 0.01)
 176           AND search_rank between 22 and 27
 177       ORDER BY distance ASC limit 1
 178     LOOP
 179       RETURN NEXT r;
 180     END LOOP;
 181     RETURN;
 182   END IF;
 183 -- end
 184
 185   RAISE EXCEPTION 'Unknown partition %', in_partition;
 186 END
 187 $$
 188 LANGUAGE plpgsql;
 189
 190 create or replace function getNearestPostcode(in_partition INTEGER, point GEOMETRY)
 191   RETURNS TEXT AS $$
 192 DECLARE
 193   out_postcode TEXT;
 194 BEGIN
 195
 196 -- start
 197   IF in_partition = -partition- THEN
 198     SELECT postcode
 199         FROM location_area_large_-partition- join placex using (place_id)
 200         WHERE st_contains(location_area_large_-partition-.geometry, point)
 201         AND class = 'place' and type = 'postcode'
 202       ORDER BY st_distance(location_area_large_-partition-.centroid, point) ASC limit 1
 203       INTO out_postcode;
 204     RETURN out_postcode;
 205   END IF;
 206 -- end
 207
 208   RAISE EXCEPTION 'Unknown partition %', in_partition;
 209 END
 210 $$
 211 LANGUAGE plpgsql;
 212
 213 create or replace function insertSearchName(
 214   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2),
 215   in_name_vector INTEGER[], in_nameaddress_vector INTEGER[],
 216   in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT,
 217   in_centroid GEOMETRY) RETURNS BOOLEAN AS $$
 218 DECLARE
 219 BEGIN
 220
 221   DELETE FROM search_name WHERE place_id = in_place_id;
 222   INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code,
 223     in_name_vector, in_nameaddress_vector, in_centroid);
 224
 225   IF in_rank_search <= 4 THEN
 226     DELETE FROM search_name_country WHERE place_id = in_place_id;
 227     INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code,
 228       in_name_vector, in_nameaddress_vector, in_centroid);
 229     RETURN TRUE;
 230   END IF;
 231
 232 -- start
 233   IF in_partition = -partition- THEN
 234     DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
 235     INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 236       in_name_vector, in_nameaddress_vector, in_centroid);
 237     RETURN TRUE;
 238   END IF;
 239 -- end
 240
 241   RAISE EXCEPTION 'Unknown partition %', in_partition;
 242   RETURN FALSE;
 243 END
 244 $$
 245 LANGUAGE plpgsql;
 246
 247 create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 248 DECLARE
 249 BEGIN
 250
 251   DELETE from search_name WHERE place_id = in_place_id;
 252   DELETE from search_name_country WHERE place_id = in_place_id;
 253
 254 -- start
 255   IF in_partition = -partition- THEN
 256     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 257     RETURN TRUE;
 258   END IF;
 259 -- end
 260
 261   RAISE EXCEPTION 'Unknown partition %', in_partition;
 262
 263   RETURN FALSE;
 264 END
 265 $$
 266 LANGUAGE plpgsql;
 267
 268 create or replace function insertLocationRoad(
 269   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 270 DECLARE
 271 BEGIN
 272
 273 -- start
 274   IF in_partition = -partition- THEN
 275     DELETE FROM location_road_-partition- where place_id = in_place_id;
 276     INSERT INTO location_road_-partition- values (in_partition, in_place_id, in_country_code, in_geometry);
 277     RETURN TRUE;
 278   END IF;
 279 -- end
 280
 281   RAISE EXCEPTION 'Unknown partition %', in_partition;
 282   RETURN FALSE;
 283 END
 284 $$
 285 LANGUAGE plpgsql;
 286
 287 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 288 DECLARE
 289 BEGIN
 290
 291 -- start
 292   IF in_partition = -partition- THEN
 293     DELETE FROM location_road_-partition- where place_id = in_place_id;
 294     RETURN TRUE;
 295   END IF;
 296 -- end
 297
 298   RAISE EXCEPTION 'Unknown partition %', in_partition;
 299
 300   RETURN FALSE;
 301 END
 302 $$
 303 LANGUAGE plpgsql;
 304
 305 create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$
 306 DECLARE
 307   r nearfeature%rowtype;
 308   search_diameter FLOAT;
 309 BEGIN
 310
 311 -- start
 312   IF in_partition = -partition- THEN
 313     search_diameter := 0.00005;
 314     WHILE search_diameter < 0.1 LOOP
 315       FOR r IN
 316         SELECT place_id, null, null, null,
 317             ST_Distance(geometry, point) as distance, null as isguess
 318             FROM location_road_-partition-
 319             WHERE ST_DWithin(geometry, point, search_diameter)
 320         ORDER BY distance ASC limit 1
 321       LOOP
 322         RETURN NEXT r;
 323         RETURN;
 324       END LOOP;
 325       search_diameter := search_diameter * 2;
 326     END LOOP;
 327     RETURN;
 328   END IF;
 329 -- end
 330
 331   RAISE EXCEPTION 'Unknown partition %', in_partition;
 332 END
 333 $$
 334 LANGUAGE plpgsql;
 335
 336 create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$
 337 DECLARE
 338   r nearfeature%rowtype;
 339   search_diameter FLOAT;
 340   p1 GEOMETRY;
 341   p2 GEOMETRY;
 342   p3 GEOMETRY;
 343 BEGIN
 344
 345   IF st_geometrytype(line) not in ('ST_LineString') THEN
 346     RETURN;
 347   END IF;
 348
 349   p1 := ST_Line_Interpolate_Point(line,0);
 350   p2 := ST_Line_Interpolate_Point(line,0.5);
 351   p3 := ST_Line_Interpolate_Point(line,1);
 352
 353 -- start
 354   IF in_partition = -partition- THEN
 355     search_diameter := 0.0005;
 356     WHILE search_diameter < 0.01 LOOP
 357       FOR r IN
 358         SELECT place_id, null, null, null,
 359             ST_Distance(geometry, line) as distance, null as isguess
 360             FROM location_road_-partition-
 361             WHERE ST_DWithin(line, geometry, search_diameter)
 362             ORDER BY (ST_distance(geometry, p1)+
 363                       ST_distance(geometry, p2)+
 364                       ST_distance(geometry, p3)) ASC limit 1
 365       LOOP
 366         RETURN NEXT r;
 367         RETURN;
 368       END LOOP;
 369       search_diameter := search_diameter * 2;
 370     END LOOP;
 371     RETURN;
 372   END IF;
 373 -- end
 374
 375   RAISE EXCEPTION 'Unknown partition %', in_partition;
 376 END
 377 $$
 378 LANGUAGE plpgsql;