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