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);
  21 CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector gin__int_ops);
  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 location_area_roadnear_-partition- () INHERITS (location_area_roadnear);
  29 CREATE INDEX idx_location_area_roadnear_-partition-_place_id ON location_area_roadnear_-partition- USING BTREE (place_id);
  30 CREATE INDEX idx_location_area_roadnear_-partition-_geometry ON location_area_roadnear_-partition- USING GIST (geometry);
  31
  32 CREATE TABLE location_area_roadfar_-partition- () INHERITS (location_area_roadfar);
  33 CREATE INDEX idx_location_area_roadfar_-partition-_place_id ON location_area_roadfar_-partition- USING BTREE (place_id);
  34 CREATE INDEX idx_location_area_roadfar_-partition-_geometry ON location_area_roadfar_-partition- USING GIST (geometry);
  35
  36 CREATE TABLE search_name_-partition- () INHERITS (search_name_blank);
  37 CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id);
  38 CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid);
  39 CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector gin__int_ops);
  40 CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops);
  41
  42 -- end
  43
  44 create or replace function getNearRoads(in_partition INTEGER, point GEOMETRY) RETURNS setof nearplace AS $$
  45 DECLARE
  46   r nearplace%rowtype;
  47   a BOOLEAN;
  48 BEGIN
  49
  50 -- start
  51   IF in_partition = -partition- THEN
  52     a := FALSE;
  53     FOR r IN SELECT place_id FROM location_area_roadnear_-partition- WHERE ST_Contains(geometry, point) ORDER BY ST_Distance(point, centroid) ASC LIMIT 1 LOOP
  54       a := TRUE;
  55       RETURN NEXT r;
  56       RETURN;
  57     END LOOP;
  58     FOR r IN SELECT place_id FROM location_area_roadfar_-partition- WHERE ST_Contains(geometry, point) ORDER BY ST_Distance(point, centroid) ASC LOOP
  59       RETURN NEXT r;
  60       RETURN;
  61     END LOOP;
  62     RETURN;
  63   END IF;
  64 -- end
  65
  66   RAISE EXCEPTION 'Unknown partition %', in_partition;
  67 END
  68 $$
  69 LANGUAGE plpgsql;
  70
  71 create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeature AS $$
  72 DECLARE
  73   r nearfeature%rowtype;
  74 BEGIN
  75
  76 -- start
  77   IF in_partition = -partition- THEN
  78     FOR r IN
  79       SELECT place_id, keywords, rank_address, rank_search, ST_Distance(point, centroid) as distance FROM (
  80         SELECT * FROM location_area_large_-partition- WHERE ST_Contains(geometry, point) and rank_search < maxrank
  81         UNION ALL
  82         SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) and rank_search < maxrank
  83       ) as location_area
  84       ORDER BY rank_search desc, isin_tokens && keywords desc, isguess asc, rank_address asc, ST_Distance(point, centroid) ASC
  85     LOOP
  86       RETURN NEXT r;
  87     END LOOP;
  88     RETURN;
  89   END IF;
  90 -- end
  91
  92   RAISE EXCEPTION 'Unknown partition %', in_partition;
  93 END
  94 $$
  95 LANGUAGE plpgsql;
  96
  97 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id integer) RETURNS BOOLEAN AS $$
  98 DECLARE
  99 BEGIN
 100
 101 -- start
 102   IF in_partition = -partition- THEN
 103 --    DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
 104 --    DELETE from location_area_roadnear_-partition- WHERE place_id = in_place_id;
 105 --    DELETE from location_area_roadfar_-partition- WHERE place_id = in_place_id;
 106     RETURN TRUE;
 107   END IF;
 108 -- end
 109
 110   RAISE EXCEPTION 'Unknown partition %', in_partition;
 111
 112   RETURN FALSE;
 113 END
 114 $$
 115 LANGUAGE plpgsql;
 116
 117 create or replace function insertLocationAreaLarge(
 118   in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[],
 119   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN,
 120   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 121 DECLARE
 122 BEGIN
 123
 124   IF in_rank_search <= 4 THEN
 125     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);
 126     RETURN TRUE;
 127   END IF;
 128
 129 -- start
 130   IF in_partition = -partition- THEN
 131     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);
 132     RETURN TRUE;
 133   END IF;
 134 -- end
 135
 136   RAISE EXCEPTION 'Unknown partition %', in_partition;
 137   RETURN FALSE;
 138 END
 139 $$
 140 LANGUAGE plpgsql;
 141
 142 create or replace function insertLocationAreaRoadNear(
 143   in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[],
 144   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN,
 145   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 146 DECLARE
 147 BEGIN
 148
 149 -- start
 150   IF in_partition = -partition- THEN
 151     INSERT INTO location_area_roadnear_-partition- values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
 152     RETURN TRUE;
 153   END IF;
 154 -- end
 155
 156   RAISE EXCEPTION 'Unknown partition %', in_partition;
 157   RETURN FALSE;
 158 END
 159 $$
 160 LANGUAGE plpgsql;
 161
 162 create or replace function insertLocationAreaRoadFar(
 163   in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[],
 164   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN,
 165   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 166 DECLARE
 167 BEGIN
 168
 169 -- start
 170   IF in_partition = -partition- THEN
 171     INSERT INTO location_area_roadfar_-partition- values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
 172     RETURN TRUE;
 173   END IF;
 174 -- end
 175
 176   RAISE EXCEPTION 'Unknown partition %', in_partition;
 177   RETURN FALSE;
 178 END
 179 $$
 180 LANGUAGE plpgsql;
 181
 182 create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$
 183 DECLARE
 184   r nearfeature%rowtype;
 185 BEGIN
 186
 187 -- start
 188   IF in_partition = -partition- THEN
 189     FOR r IN
 190       SELECT place_id, name_vector, address_rank, search_rank,
 191           ST_Distance(centroid, point) as distance
 192           FROM search_name_-partition-
 193           WHERE name_vector @> ARRAY[isin_token]
 194           AND search_rank < maxrank
 195       UNION ALL
 196       SELECT place_id, name_vector, address_rank, search_rank,
 197           ST_Distance(centroid, point) as distance
 198           FROM search_name_country
 199           WHERE name_vector @> ARRAY[isin_token]
 200           AND search_rank < maxrank
 201       ORDER BY distance ASC limit 1
 202     LOOP
 203       RETURN NEXT r;
 204     END LOOP;
 205     RETURN;
 206   END IF;
 207 -- end
 208
 209   RAISE EXCEPTION 'Unknown partition %', in_partition;
 210 END
 211 $$
 212 LANGUAGE plpgsql;
 213
 214 create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) RETURNS setof nearfeature AS $$
 215 DECLARE
 216   r nearfeature%rowtype;
 217 BEGIN
 218
 219 -- start
 220   IF in_partition = -partition- THEN
 221     FOR r IN
 222       SELECT place_id, name_vector, address_rank, search_rank,
 223           ST_Distance(centroid, point) as distance
 224           FROM search_name_-partition-
 225           WHERE name_vector @> ARRAY[isin_token]
 226           AND ST_DWithin(centroid, point, 0.01)
 227           AND search_rank between 22 and 27
 228       ORDER BY distance ASC limit 1
 229     LOOP
 230       RETURN NEXT r;
 231     END LOOP;
 232     RETURN;
 233   END IF;
 234 -- end
 235
 236   RAISE EXCEPTION 'Unknown partition %', in_partition;
 237 END
 238 $$
 239 LANGUAGE plpgsql;
 240
 241 create or replace function insertSearchName(
 242   in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2),
 243   in_name_vector INTEGER[], in_nameaddress_vector INTEGER[],
 244   in_rank_search INTEGER, in_rank_address INTEGER,
 245   in_centroid GEOMETRY) RETURNS BOOLEAN AS $$
 246 DECLARE
 247 BEGIN
 248
 249   INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 250     in_name_vector, in_nameaddress_vector, in_centroid);
 251
 252   IF in_rank_search <= 4 THEN
 253     INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 254       in_name_vector, in_nameaddress_vector, in_centroid);
 255     RETURN TRUE;
 256   END IF;
 257
 258 -- start
 259   IF in_partition = -partition- THEN
 260     INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 261       in_name_vector, in_nameaddress_vector, in_centroid);
 262     RETURN TRUE;
 263   END IF;
 264 -- end
 265
 266   RAISE EXCEPTION 'Unknown partition %', in_partition;
 267   RETURN FALSE;
 268 END
 269 $$
 270 LANGUAGE plpgsql;
 271
 272 create or replace function deleteSearchName(in_partition INTEGER, in_place_id integer) RETURNS BOOLEAN AS $$
 273 DECLARE
 274 BEGIN
 275
 276   DELETE from search_name WHERE place_id = in_place_id;
 277   DELETE from search_name_country WHERE place_id = in_place_id;
 278
 279 -- start
 280   IF in_partition = -partition- THEN
 281     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 282     RETURN TRUE;
 283   END IF;
 284 -- end
 285
 286   RAISE EXCEPTION 'Unknown partition %', in_partition;
 287
 288   RETURN FALSE;
 289 END
 290 $$
 291 LANGUAGE plpgsql;