sql/partition-functions.src.sql

   1 create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$
   2 DECLARE
   3   r nearfeaturecentr%rowtype;
   4 BEGIN
   5
   6 -- start
   7   IF in_partition = -partition- THEN
   8     FOR r IN
   9       SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, postcode, centroid
  10       FROM location_area_large_-partition-
  11       WHERE ST_Intersects(geometry, feature) and rank_search < maxrank
  12       GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
  13       ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
  14         ST_Distance(feature, centroid) *
  15           CASE
  16                WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city
  17                WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city
  18                WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town
  19                ELSE 1 END ASC -- everything else
  20     LOOP
  21       RETURN NEXT r;
  22     END LOOP;
  23     RETURN;
  24   END IF;
  25 -- end
  26
  27   RAISE EXCEPTION 'Unknown partition %', in_partition;
  28 END
  29 $$
  30 LANGUAGE plpgsql;
  31
  32 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
  33 DECLARE
  34 BEGIN
  35
  36   IF in_rank_search <= 4 THEN
  37     DELETE from location_area_country WHERE place_id = in_place_id;
  38     RETURN TRUE;
  39   END IF;
  40
  41 -- start
  42   IF in_partition = -partition- THEN
  43     DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
  44     RETURN TRUE;
  45   END IF;
  46 -- end
  47
  48   RAISE EXCEPTION 'Unknown partition %', in_partition;
  49
  50   RETURN FALSE;
  51 END
  52 $$
  53 LANGUAGE plpgsql;
  54
  55 create or replace function insertLocationAreaLarge(
  56   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
  57   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT,
  58   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
  59 DECLARE
  60 BEGIN
  61   IF in_rank_address = 0 THEN
  62     RETURN TRUE;
  63   END IF;
  64
  65   IF in_rank_search <= 4 and not in_estimate THEN
  66     INSERT INTO location_area_country (place_id, country_code, geometry)
  67       values (in_place_id, in_country_code, in_geometry);
  68     RETURN TRUE;
  69   END IF;
  70
  71 -- start
  72   IF in_partition = -partition- THEN
  73     INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
  74       values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
  75     RETURN TRUE;
  76   END IF;
  77 -- end
  78
  79   RAISE EXCEPTION 'Unknown partition %', in_partition;
  80   RETURN FALSE;
  81 END
  82 $$
  83 LANGUAGE plpgsql;
  84
  85 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER,
  86                                                       point GEOMETRY,
  87                                                       isin_token INTEGER[])
  88   RETURNS BIGINT
  89   AS $$
  90 DECLARE
  91   parent BIGINT;
  92 BEGIN
  93
  94 -- start
  95   IF in_partition = -partition- THEN
  96     SELECT place_id FROM search_name_-partition-
  97       INTO parent
  98       WHERE name_vector && isin_token
  99             AND centroid && ST_Expand(point, 0.015)
 100             AND search_rank between 26 and 27
 101       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 102     RETURN parent;
 103   END IF;
 104 -- end
 105
 106   RAISE EXCEPTION 'Unknown partition %', in_partition;
 107 END
 108 $$
 109 LANGUAGE plpgsql STABLE;
 110
 111 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
 112                                                        point GEOMETRY,
 113                                                        isin_token INTEGER[])
 114   RETURNS BIGINT
 115   AS $$
 116 DECLARE
 117   parent BIGINT;
 118 BEGIN
 119
 120 -- start
 121   IF in_partition = -partition- THEN
 122     SELECT place_id
 123       INTO parent
 124       FROM search_name_-partition-
 125       WHERE name_vector && isin_token
 126             AND centroid && ST_Expand(point, 0.04)
 127             AND search_rank between 16 and 22
 128       ORDER BY ST_Distance(centroid, point) ASC limit 1;
 129     RETURN parent;
 130   END IF;
 131 -- end
 132
 133   RAISE EXCEPTION 'Unknown partition %', in_partition;
 134 END
 135 $$
 136 LANGUAGE plpgsql;
 137
 138
 139 create or replace function insertSearchName(
 140   in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[],
 141   in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY)
 142 RETURNS BOOLEAN AS $$
 143 DECLARE
 144 BEGIN
 145 -- start
 146   IF in_partition = -partition- THEN
 147     DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
 148     IF in_rank_address > 0 THEN
 149       INSERT INTO search_name_-partition- (place_id, search_rank, address_rank, name_vector, centroid)
 150         values (in_place_id, in_rank_search, in_rank_address, in_name_vector, in_geometry);
 151     END IF;
 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 deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 163 DECLARE
 164 BEGIN
 165 -- start
 166   IF in_partition = -partition- THEN
 167     DELETE from search_name_-partition- WHERE place_id = in_place_id;
 168     RETURN TRUE;
 169   END IF;
 170 -- end
 171
 172   RAISE EXCEPTION 'Unknown partition %', in_partition;
 173
 174   RETURN FALSE;
 175 END
 176 $$
 177 LANGUAGE plpgsql;
 178
 179 create or replace function insertLocationRoad(
 180   in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 181 DECLARE
 182 BEGIN
 183
 184 -- start
 185   IF in_partition = -partition- THEN
 186     DELETE FROM location_road_-partition- where place_id = in_place_id;
 187     INSERT INTO location_road_-partition- (partition, place_id, country_code, geometry)
 188       values (in_partition, in_place_id, in_country_code, in_geometry);
 189     RETURN TRUE;
 190   END IF;
 191 -- end
 192
 193   RAISE EXCEPTION 'Unknown partition %', in_partition;
 194   RETURN FALSE;
 195 END
 196 $$
 197 LANGUAGE plpgsql;
 198
 199 create or replace function deleteRoad(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
 200 DECLARE
 201 BEGIN
 202
 203 -- start
 204   IF in_partition = -partition- THEN
 205     DELETE FROM location_road_-partition- where place_id = in_place_id;
 206     RETURN TRUE;
 207   END IF;
 208 -- end
 209
 210   RAISE EXCEPTION 'Unknown partition %', in_partition;
 211
 212   RETURN FALSE;
 213 END
 214 $$
 215 LANGUAGE plpgsql;
 216
 217 create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$
 218 DECLARE
 219   r nearfeature%rowtype;
 220   search_diameter FLOAT;
 221 BEGIN
 222
 223 -- start
 224   IF in_partition = -partition- THEN
 225     search_diameter := 0.00005;
 226     WHILE search_diameter < 0.1 LOOP
 227       FOR r IN
 228         SELECT place_id, null, null, null,
 229             ST_Distance(geometry, point) as distance, null as isguess
 230             FROM location_road_-partition-
 231             WHERE ST_DWithin(geometry, point, search_diameter)
 232         ORDER BY distance ASC limit 1
 233       LOOP
 234         RETURN NEXT r;
 235         RETURN;
 236       END LOOP;
 237       search_diameter := search_diameter * 2;
 238     END LOOP;
 239     RETURN;
 240   END IF;
 241 -- end
 242
 243   RAISE EXCEPTION 'Unknown partition %', in_partition;
 244 END
 245 $$
 246 LANGUAGE plpgsql;
 247
 248 create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$
 249 DECLARE
 250   r nearfeature%rowtype;
 251   search_diameter FLOAT;
 252   p1 GEOMETRY;
 253   p2 GEOMETRY;
 254   p3 GEOMETRY;
 255 BEGIN
 256
 257   IF st_geometrytype(line) not in ('ST_LineString') THEN
 258     RETURN;
 259   END IF;
 260
 261   p1 := ST_LineInterpolatePoint(line,0);
 262   p2 := ST_LineInterpolatePoint(line,0.5);
 263   p3 := ST_LineInterpolatePoint(line,1);
 264
 265 -- start
 266   IF in_partition = -partition- THEN
 267     search_diameter := 0.0005;
 268     WHILE search_diameter < 0.01 LOOP
 269       FOR r IN
 270         SELECT place_id, null, null, null,
 271             ST_Distance(geometry, line) as distance, null as isguess
 272             FROM location_road_-partition-
 273             WHERE ST_DWithin(line, geometry, search_diameter)
 274             ORDER BY (ST_distance(geometry, p1)+
 275                       ST_distance(geometry, p2)+
 276                       ST_distance(geometry, p3)) ASC limit 1
 277       LOOP
 278         RETURN NEXT r;
 279         RETURN;
 280       END LOOP;
 281       search_diameter := search_diameter * 2;
 282     END LOOP;
 283     RETURN;
 284   END IF;
 285 -- end
 286
 287   RAISE EXCEPTION 'Unknown partition %', in_partition;
 288 END
 289 $$
 290 LANGUAGE plpgsql;