From 0f5615b6189d4025d3d0f85ae10d001f495e3796 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 25 Oct 2020 22:19:43 +0100
Subject: [PATCH] guess a base address level for address rank 0 objects

The guess is based on the area and mainly avoids odd
addresses for very large or small objects.
---
 sql/functions/placex_triggers.sql | 21 +++++++++++------
 sql/functions/ranking.sql         | 38 +++++++++++++++++++++++++++++++
 2 files changed, 52 insertions(+), 7 deletions(-)

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 1e2aac4c..433ceb92 100644
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -509,6 +509,8 @@ DECLARE
   addr_street TEXT;
   addr_place TEXT;
 
+  max_rank SMALLINT;
+
   name_vector INTEGER[];
   nameaddress_vector INTEGER[];
   addr_nameaddress_vector INTEGER[];
@@ -906,14 +908,19 @@ BEGIN
     --DEBUG: RAISE WARNING 'Country names updated';
   END IF;
 
-  SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition,
-                                    CASE WHEN NEW.rank_address = 0 THEN NEW.rank_search
-                                         WHEN NEW.rank_address > 25 THEN 25::smallint
-                                         ELSE NEW.rank_address END,
+  IF NEW.rank_address = 0 THEN
+    max_rank := geometry_to_rank(NEW.rank_search, NEW.geometry);
+  ELSEIF NEW.rank_address > 25 THEN
+    max_rank := 25;
+  ELSE
+    max_rank = NEW.rank_address;
+  END IF;
+
+  SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition, max_rank,
                                     NEW.address,
-                                    CASE WHEN NEW.rank_search >= 26
-                                             AND NEW.rank_search < 30
-                                      THEN NEW.geometry ELSE NEW.centroid END)
+                                    CASE WHEN (NEW.rank_address = 0 or
+                                               NEW.rank_search between 26 and 29)
+                                         THEN NEW.geometry ELSE NEW.centroid END)
     INTO NEW.parent_place_id, NEW.postcode, nameaddress_vector;
 
   --DEBUG: RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;
diff --git a/sql/functions/ranking.sql b/sql/functions/ranking.sql
index 0c8f4c49..e918f924 100644
--- a/sql/functions/ranking.sql
+++ b/sql/functions/ranking.sql
@@ -55,6 +55,44 @@ 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)
+  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;
+
+  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,
-- 
2.45.2