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 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);
  32 CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops);
  33
  34 -- end
  35
  36 create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeature AS $$
  37 DECLARE
  38   r nearfeature%rowtype;
  39 BEGIN
  40
  41 -- start
  42   IF in_partition = -partition- THEN
  43     FOR r IN
  44       SELECT place_id, keywords, rank_address, rank_search, ST_Distance(point, centroid) as distance FROM (
  45         SELECT * FROM location_area_large_-partition- WHERE ST_Contains(geometry, point) and rank_search < maxrank
  46         UNION ALL
  47         SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) and rank_search < maxrank
  48       ) as location_area
  49       ORDER BY rank_search desc, isin_tokens && keywords desc, isguess asc, rank_address asc, ST_Distance(point, centroid) ASC
  50     LOOP
  51       RETURN NEXT r;
  52     END LOOP;
  53     RETURN;
  54   END IF;
  55 -- end
  56
  57   RAISE EXCEPTION 'Unknown partition %', in_partition;
  58 END
  59 $$
  60 LANGUAGE plpgsql;
  61
  62 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id integer) RETURNS BOOLEAN AS $$
  63 DECLARE
  64 BEGIN
  65
  66 -- start
  67   IF in_partition = -partition- THEN
  68     DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
  69     RETURN TRUE;
  70   END IF;
  71 -- end
  72
  73   RAISE EXCEPTION 'Unknown partition %', in_partition;
  74
  75   RETURN FALSE;
  76 END
  77 $$
  78 LANGUAGE plpgsql;
  79
  80 create or replace function insertLocationAreaLarge(
  81   in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[],
  82   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN,
  83   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
  84 DECLARE
  85 BEGIN
  86
  87   IF in_rank_search <= 4 THEN
  88     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);
  89     RETURN TRUE;
  90   END IF;
  91
  92 -- start
  93   IF in_partition = -partition- THEN
  94     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);
  95     RETURN TRUE;
  96   END IF;
  97 -- end
  98
  99   RAISE EXCEPTION 'Unknown partition %', in_partition;
 100   RETURN FALSE;
 101 END
 102 $$
 103 LANGUAGE plpgsql;
 104
 105 create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$
 106 DECLARE
 107   r nearfeature%rowtype;
 108 BEGIN
 109
 110 -- start
 111   IF in_partition = -partition- THEN
 112     FOR r IN
 113       SELECT place_id, name_vector, address_rank, search_rank,
 114           ST_Distance(centroid, point) as distance
 115           FROM search_name_-partition-
 116           WHERE name_vector @> ARRAY[isin_token]
 117           AND search_rank < maxrank
 118       UNION ALL
 119       SELECT place_id, name_vector, address_rank, search_rank,
 120           ST_Distance(centroid, point) as distance
 121           FROM search_name_country
 122           WHERE name_vector @> ARRAY[isin_token]
 123           AND search_rank < maxrank
 124       ORDER BY distance ASC limit 1
 125     LOOP
 126       RETURN NEXT r;
 127     END LOOP;
 128     RETURN;
 129   END IF;
 130 -- end
 131
 132   RAISE EXCEPTION 'Unknown partition %', in_partition;
 133 END
 134 $$
 135 LANGUAGE plpgsql;
 136
 137 create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) RETURNS setof nearfeature AS $$
 138 DECLARE
 139   r nearfeature%rowtype;
 140 BEGIN
 141
 142 -- start
 143   IF in_partition = -partition- THEN
 144     FOR r IN
 145       SELECT place_id, name_vector, address_rank, search_rank,
 146           ST_Distance(centroid, point) as distance
 147           FROM search_name_-partition-
 148           WHERE name_vector @> ARRAY[isin_token]
 149           AND ST_DWithin(centroid, point, 0.01)
 150           AND search_rank between 22 and 27
 151       ORDER BY distance ASC limit 1
 152     LOOP
 153       RETURN NEXT r;
 154     END LOOP;
 155     RETURN;
 156   END IF;
 157 -- end
 158
 159   RAISE EXCEPTION 'Unknown partition %', in_partition;
 160 END
 161 $$
 162 LANGUAGE plpgsql;
 163
 164 create or replace function insertSearchName(
 165   in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2),
 166   in_name_vector INTEGER[], in_nameaddress_vector INTEGER[],
 167   in_rank_search INTEGER, in_rank_address INTEGER,
 168   in_centroid GEOMETRY) RETURNS BOOLEAN AS $$
 169 DECLARE
 170 BEGIN
 171
 172   INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 173     in_name_vector, in_nameaddress_vector, in_centroid);
 174
 175   IF in_rank_search <= 4 THEN
 176     INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 177       in_name_vector, in_nameaddress_vector, in_centroid);
 178     RETURN TRUE;
 179   END IF;
 180
 181 -- start
 182   IF in_partition = -partition- THEN
 183     INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
 184       in_name_vector, in_nameaddress_vector, in_centroid);
 185     RETURN TRUE;
 186   END IF;
 187 -- end
 188
 189   RAISE EXCEPTION 'Unknown partition %', in_partition;
 190   RETURN FALSE;
 191 END
 192 $$
 193 LANGUAGE plpgsql;
 194
 195 create or replace function deleteSearchName(in_partition INTEGER, in_place_id integer) RETURNS BOOLEAN AS $$
 196 DECLARE
 197 BEGIN
 198
 199   DELETE from search_name WHERE place_id = in_place_id;
 200   DELETE from search_name_country WHERE place_id = in_place_id;
 201
 202 -- start
 203   IF in_partition = -partition- THEN
 204     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 205     RETURN TRUE;
 206   END IF;
 207 -- end
 208
 209   RAISE EXCEPTION 'Unknown partition %', in_partition;
 210
 211   RETURN FALSE;
 212 END
 213 $$
 214 LANGUAGE plpgsql;