lib-sql/functions/ranking.sql

   1 -- Functions related to search and address ranks
   2
   3 -- Return an approximate search radius according to the search rank.
   4 CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
   5   RETURNS FLOAT
   6   AS $$
   7 BEGIN
   8   IF rank_search <= 4 THEN
   9     RETURN 5.0;
  10   ELSIF rank_search <= 8 THEN
  11     RETURN 1.8;
  12   ELSIF rank_search <= 12 THEN
  13     RETURN 0.6;
  14   ELSIF rank_search <= 17 THEN
  15     RETURN 0.16;
  16   ELSIF rank_search <= 18 THEN
  17     RETURN 0.08;
  18   ELSIF rank_search <= 19 THEN
  19     RETURN 0.04;
  20   END IF;
  21
  22   RETURN 0.02;
  23 END;
  24 $$
  25 LANGUAGE plpgsql IMMUTABLE;
  26
  27
  28 -- Return an approximate update radius according to the search rank.
  29 CREATE OR REPLACE FUNCTION update_place_diameter(rank_search SMALLINT)
  30   RETURNS FLOAT
  31   AS $$
  32 BEGIN
  33   -- postcodes
  34   IF rank_search = 11 or rank_search = 5 THEN
  35     RETURN 0.05;
  36   -- anything higher than city is effectively ignored (polygon required)
  37   ELSIF rank_search < 16 THEN
  38     RETURN 0;
  39   ELSIF rank_search < 18 THEN
  40     RETURN 0.1;
  41   ELSIF rank_search < 20 THEN
  42     RETURN 0.05;
  43   ELSIF rank_search = 21 THEN
  44     RETURN 0.001;
  45   ELSIF rank_search < 24 THEN
  46     RETURN 0.02;
  47   ELSIF rank_search < 26 THEN
  48     RETURN 0.002;
  49   ELSIF rank_search < 28 THEN
  50     RETURN 0.001;
  51   END IF;
  52
  53   RETURN 0;
  54 END;
  55 $$
  56 LANGUAGE plpgsql IMMUTABLE;
  57
  58 -- Compute a base address rank from the extent of the given geometry.
  59 --
  60 -- This is all simple guess work. We don't need particularly good estimates
  61 -- here. This just avoids to have very high ranked address parts in features
  62 -- that span very large areas (or vice versa).
  63 CREATE OR REPLACE FUNCTION geometry_to_rank(search_rank SMALLINT, geometry GEOMETRY, country_code TEXT)
  64   RETURNS SMALLINT
  65   AS $$
  66 DECLARE
  67   area FLOAT;
  68 BEGIN
  69   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
  70       area := ST_Area(geometry);
  71   ELSIF ST_GeometryType(geometry) in ('ST_LineString','ST_MultiLineString') THEN
  72       area := (ST_Length(geometry)^2) * 0.1;
  73   ELSE
  74     RETURN search_rank;
  75   END IF;
  76
  77   -- adjust for the fact that countries come in different sizes
  78   IF country_code IN ('ca', 'au', 'ru') THEN
  79     area := area / 5;
  80   ELSIF country_code IN ('br', 'kz', 'cn', 'us', 'ne', 'gb', 'za', 'sa', 'id', 'eh', 'ml', 'tm') THEN
  81     area := area / 3;
  82   ELSIF country_code IN ('bo', 'ar', 'sd', 'mn', 'in', 'et', 'cd', 'mz', 'ly', 'cl', 'zm') THEN
  83     area := area / 2;
  84   END IF;
  85
  86   IF area > 1 THEN
  87     RETURN 7;
  88   ELSIF area > 0.1 THEN
  89     RETURN 9;
  90   ELSIF area > 0.01 THEN
  91     RETURN 13;
  92   ELSIF area > 0.001 THEN
  93     RETURN 17;
  94   ELSIF area > 0.0001 THEN
  95     RETURN 19;
  96   ELSIF area > 0.000005 THEN
  97     RETURN 21;
  98   END IF;
  99
 100    RETURN 23;
 101 END;
 102 $$
 103 LANGUAGE plpgsql IMMUTABLE;
 104
 105
 106 -- Guess a ranking for postcodes from country and postcode format.
 107 CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
 108                                              OUT rank_search SMALLINT,
 109                                              OUT rank_address SMALLINT)
 110 AS $$
 111 DECLARE
 112   part TEXT;
 113 BEGIN
 114     rank_search := 30;
 115     rank_address := 30;
 116     postcode := upper(postcode);
 117
 118     IF country_code = 'gb' THEN
 119         IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
 120             rank_search := 25;
 121             rank_address := 5;
 122         ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
 123             rank_search := 23;
 124             rank_address := 5;
 125         ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
 126             rank_search := 21;
 127             rank_address := 5;
 128         END IF;
 129
 130     ELSEIF country_code = 'sg' THEN
 131         IF postcode ~ '^([0-9]{6})$' THEN
 132             rank_search := 25;
 133             rank_address := 11;
 134         END IF;
 135
 136     ELSEIF country_code = 'de' THEN
 137         IF postcode ~ '^([0-9]{5})$' THEN
 138             rank_search := 21;
 139             rank_address := 11;
 140         END IF;
 141
 142     ELSE
 143         -- Guess at the postcode format and coverage (!)
 144         IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
 145             rank_search := 21;
 146             rank_address := 11;
 147         ELSE
 148             -- Does it look splitable into and area and local code?
 149             part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
 150
 151             IF part IS NOT NULL THEN
 152                 rank_search := 25;
 153                 rank_address := 11;
 154             ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
 155                 rank_search := 21;
 156                 rank_address := 11;
 157             END IF;
 158         END IF;
 159     END IF;
 160
 161 END;
 162 $$
 163 LANGUAGE plpgsql IMMUTABLE;
 164
 165
 166 -- Get standard search and address rank for an object.
 167 --
 168 -- \param country        Two-letter country code where the object is in.
 169 -- \param extended_type  OSM type (N, W, R) or area type (A).
 170 -- \param place_class    Class (or tag key) of object.
 171 -- \param place_type     Type (or tag value) of object.
 172 -- \param admin_level    Value of admin_level tag.
 173 -- \param is_major       If true, boost search rank by one.
 174 -- \param postcode       Value of addr:postcode tag.
 175 -- \param[out] search_rank   Computed search rank.
 176 -- \param[out] address_rank  Computed address rank.
 177 --
 178 CREATE OR REPLACE FUNCTION compute_place_rank(country VARCHAR(2),
 179                                               extended_type VARCHAR(1),
 180                                               place_class TEXT, place_type TEXT,
 181                                               admin_level SMALLINT,
 182                                               is_major BOOLEAN,
 183                                               postcode TEXT,
 184                                               OUT search_rank SMALLINT,
 185                                               OUT address_rank SMALLINT)
 186 AS $$
 187 DECLARE
 188   classtype TEXT;
 189 BEGIN
 190   IF place_class in ('place','boundary')
 191      and place_type in ('postcode','postal_code')
 192   THEN
 193     SELECT * INTO search_rank, address_rank
 194       FROM get_postcode_rank(country, postcode);
 195   ELSEIF extended_type = 'N' AND place_class = 'highway' THEN
 196     search_rank = 30;
 197     address_rank = 30;
 198   ELSEIF place_class = 'landuse' AND extended_type != 'A' THEN
 199     search_rank = 30;
 200     address_rank = 30;
 201   ELSE
 202     IF place_class = 'boundary' and place_type = 'administrative' THEN
 203       classtype = place_type || admin_level::TEXT;
 204     ELSE
 205       classtype = place_type;
 206     END IF;
 207
 208     SELECT l.rank_search, l.rank_address INTO search_rank, address_rank
 209       FROM address_levels l
 210      WHERE (l.country_code = country or l.country_code is NULL)
 211            AND l.class = place_class AND (l.type = classtype or l.type is NULL)
 212      ORDER BY l.country_code, l.class, l.type LIMIT 1;
 213
 214     IF search_rank is NULL OR address_rank is NULL THEN
 215       search_rank := 30;
 216       address_rank := 30;
 217     END IF;
 218
 219     -- some postcorrections
 220     IF place_class = 'waterway' AND extended_type = 'R' THEN
 221         -- Slightly promote waterway relations so that they are processed
 222         -- before their members.
 223         search_rank := search_rank - 1;
 224     END IF;
 225
 226     IF is_major THEN
 227       search_rank := search_rank - 1;
 228     END IF;
 229   END IF;
 230 END;
 231 $$
 232 LANGUAGE plpgsql IMMUTABLE;
 233
 234 CREATE OR REPLACE FUNCTION get_addr_tag_rank(key TEXT, country TEXT,
 235                                              OUT from_rank SMALLINT,
 236                                              OUT to_rank SMALLINT,
 237                                              OUT extent FLOAT)
 238   AS $$
 239 DECLARE
 240   ranks RECORD;
 241 BEGIN
 242   from_rank := null;
 243
 244   FOR ranks IN
 245     SELECT * FROM
 246       (SELECT l.rank_search, l.rank_address FROM address_levels l
 247         WHERE (l.country_code = country or l.country_code is NULL)
 248                AND l.class = 'place' AND l.type = key
 249         ORDER BY l.country_code LIMIT 1) r
 250       WHERE rank_address > 0
 251   LOOP
 252     extent := reverse_place_diameter(ranks.rank_search);
 253
 254     IF ranks.rank_address <= 4 THEN
 255         from_rank := 4;
 256         to_rank := 4;
 257     ELSEIF ranks.rank_address <= 9 THEN
 258         from_rank := 5;
 259         to_rank := 9;
 260     ELSEIF ranks.rank_address <= 12 THEN
 261         from_rank := 10;
 262         to_rank := 12;
 263     ELSEIF ranks.rank_address <= 16 THEN
 264         from_rank := 13;
 265         to_rank := 16;
 266     ELSEIF ranks.rank_address <= 21 THEN
 267         from_rank := 17;
 268         to_rank := 21;
 269     ELSEIF ranks.rank_address <= 24 THEN
 270         from_rank := 22;
 271         to_rank := 24;
 272     ELSE
 273         from_rank := 25;
 274         to_rank := 25;
 275     END IF;
 276   END LOOP;
 277 END;
 278 $$
 279 LANGUAGE plpgsql IMMUTABLE;