Merge pull request #2105 from lonvia/fix-use-of-records

[nominatim.git] / sql / functions / ranking.sql

diff --git a/sql/functions/ranking.sql b/sql/functions/ranking.sql
index 0c8f4c497e613ee394fa1bda41f1191f34122202..1f2362114c322077a281f4d59f029181341e3913 100644 (file)
--- a/sql/functions/ranking.sql
+++ b/sql/functions/ranking.sql
@@ -55,6 +55,53 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
+-- Compute a base address rank from the extent of the given geometry.
+--
+-- This is all simple guess work. We don't need particularly good estimates
+-- here. This just avoids to have very high ranked address parts in features
+-- that span very large areas (or vice versa).
+CREATE OR REPLACE FUNCTION geometry_to_rank(search_rank SMALLINT, geometry GEOMETRY, country_code TEXT)
+  RETURNS SMALLINT
+  AS $$
+DECLARE
+  area FLOAT;
+BEGIN
+  IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
+      area := ST_Area(geometry);
+  ELSIF ST_GeometryType(geometry) in ('ST_LineString','ST_MultiLineString') THEN
+      area := (ST_Length(geometry)^2) * 0.1;
+  ELSE
+    RETURN search_rank;
+  END IF;
+
+  -- adjust for the fact that countries come in different sizes
+  IF country_code IN ('ca', 'au', 'ru') THEN
+    area := area / 5;
+  ELSIF country_code IN ('br', 'kz', 'cn', 'us', 'ne', 'gb', 'za', 'sa', 'id', 'eh', 'ml', 'tm') THEN
+    area := area / 3;
+  ELSIF country_code IN ('bo', 'ar', 'sd', 'mn', 'in', 'et', 'cd', 'mz', 'ly', 'cl', 'zm') THEN
+    area := area / 2;
+  END IF;
+
+  IF area > 1 THEN
+    RETURN 7;
+  ELSIF area > 0.1 THEN
+    RETURN 9;
+  ELSIF area > 0.01 THEN
+    RETURN 13;
+  ELSIF area > 0.001 THEN
+    RETURN 17;
+  ELSIF area > 0.0001 THEN
+    RETURN 19;
+  ELSIF area > 0.000005 THEN
+    RETURN 21;
+  END IF;
+
+   RETURN 23;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
 
 -- Guess a ranking for postcodes from country and postcode format.
 CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
@@ -147,10 +194,10 @@ BEGIN
       FROM get_postcode_rank(country, postcode);
   ELSEIF extended_type = 'N' AND place_class = 'highway' THEN
     search_rank = 30;
-    address_rank = 0;
+    address_rank = 30;
   ELSEIF place_class = 'landuse' AND extended_type != 'A' THEN
     search_rank = 30;
-    address_rank = 0;
+    address_rank = 30;
   ELSE
     IF place_class = 'boundary' and place_type = 'administrative' THEN
       classtype = place_type || admin_level::TEXT;
@@ -164,11 +211,8 @@ BEGIN
            AND l.class = place_class AND (l.type = classtype or l.type is NULL)
      ORDER BY l.country_code, l.class, l.type LIMIT 1;
 
-    IF search_rank is NULL THEN
+    IF search_rank is NULL OR address_rank is NULL THEN
       search_rank := 30;
-    END IF;
-
-    IF address_rank is NULL THEN
       address_rank := 30;
     END IF;
 
@@ -186,3 +230,50 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION get_addr_tag_rank(key TEXT, country TEXT,
+                                             OUT from_rank SMALLINT,
+                                             OUT to_rank SMALLINT,
+                                             OUT extent FLOAT)
+  AS $$
+DECLARE
+  ranks RECORD;
+BEGIN
+  from_rank := null;
+
+  FOR ranks IN
+    SELECT * FROM
+      (SELECT l.rank_search, l.rank_address FROM address_levels l
+        WHERE (l.country_code = country or l.country_code is NULL)
+               AND l.class = 'place' AND l.type = key
+        ORDER BY l.country_code LIMIT 1) r
+      WHERE rank_address > 0
+  LOOP
+    extent := reverse_place_diameter(ranks.rank_search);
+
+    IF ranks.rank_address <= 4 THEN
+        from_rank := 4;
+        to_rank := 4;
+    ELSEIF ranks.rank_address <= 9 THEN
+        from_rank := 5;
+        to_rank := 9;
+    ELSEIF ranks.rank_address <= 12 THEN
+        from_rank := 10;
+        to_rank := 12;
+    ELSEIF ranks.rank_address <= 16 THEN
+        from_rank := 13;
+        to_rank := 16;
+    ELSEIF ranks.rank_address <= 21 THEN
+        from_rank := 17;
+        to_rank := 21;
+    ELSEIF ranks.rank_address <= 24 THEN
+        from_rank := 22;
+        to_rank := 24;
+    ELSE
+        from_rank := 25;
+        to_rank := 25;
+    END IF;
+  END LOOP;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;