sql/partitions.src.sql

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