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