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