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