From a5697c5279512dfafdf693ffe2a20d5db08cda5f Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Wed, 6 May 2020 22:22:24 +0200
Subject: [PATCH 1/1] change place node expansion for large area table

So far we've used a buffer around a place node to define its
potential address reach. This had two problems: the buffer was
so large that addresses often contain false positives and the
buffer is really distorted when getting closer to the poles.

Change the buffer here to draw a bounndig box at a certain
distance in meter. This means that we always use the same
box everywhere on the planet and can make the extent much
smaller. Using a box has the advantage that it is much faster
to figure out if a point is within the box.
---
 settings/address-levels.json | 22 ++++++-------
 sql/functions/utils.sql      | 62 +++++++++++++++++-------------------
 2 files changed, 41 insertions(+), 43 deletions(-)

diff --git a/settings/address-levels.json b/settings/address-levels.json
index 8b25fb1a..b7fbe2f2 100644
--- a/settings/address-levels.json
+++ b/settings/address-levels.json
@@ -2,6 +2,8 @@
 { "tags" : {
       "place" : {
           "sea" : [2, 0],
+          "island" : [17, 0],
+          "islet" : [20, 0],
           "continent" : [2, 0],
           "country" : [4, 0],
           "state" : [8, 0],
@@ -10,25 +12,23 @@
           "county" : 12,
           "municipality" : [17, 14],
           "city" : 16,
-          "island" : [17, 0],
           "town" : [18, 16],
+          "borough" : 18,
           "village" : [19, 16],
           "district" : [19, 16],
-          "borough" : [19, 18],
+          "suburb" : [19, 20],
           "hamlet" : 20,
-          "suburb" : 20,
           "croft" : 20,
           "subdivision" : 20,
-          "isolated_dwelling" : 20,
           "allotments" : 20,
-          "farm" : [20, 0],
-          "locality" : [20, 0],
-          "islet" : [20, 0],
-          "mountain_pass" : [20, 0],
-          "neighbourhood" : 22,
-          "quarter" : 22,
+          "neighbourhood" : [20, 22],
+          "quarter" : [20, 22],
+          "isolated_dwelling" : [22, 20],
           "city_block" : 22,
-          "houses" : [28, 0]
+          "mountain_pass" : [20, 0],
+          "houses" : [28, 0],
+          "farm" : [20, 0],
+          "locality" : [20, 0]
       },
       "boundary" : {
           "administrative2" : 4,
diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql
index 61033fb4..2b3db04a 100644
--- a/sql/functions/utils.sql
+++ b/sql/functions/utils.sql
@@ -272,6 +272,25 @@ END;
 $$
 LANGUAGE plpgsql;
 
+CREATE OR REPLACE FUNCTION near_feature_rank_distance(rank_search INTEGER)
+  RETURNS FLOAT
+  AS $$
+BEGIN
+  IF rank_search <= 16 THEN -- city
+    RETURN 7500;
+  ELSIF rank_search <= 18 THEN -- town
+    RETURN 4000;
+  ELSIF rank_search <= 19 THEN -- village
+    RETURN 2000;
+  ELSIF rank_search  <= 20 THEN -- hamlet
+    RETURN 1000;
+  END IF;
+
+  RETURN 500;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
 
 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
                                         partition INTEGER, keywords INTEGER[],
@@ -282,18 +301,11 @@ CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2)
 DECLARE
   locationid INTEGER;
   centroid GEOMETRY;
-  diameter FLOAT;
-  x BOOLEAN;
-  splitGeom RECORD;
+  radius FLOAT;
   secgeo GEOMETRY;
   postcode TEXT;
 BEGIN
-
-  IF rank_search > 25 THEN
-    RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
-  END IF;
-
-  x := deleteLocationArea(partition, place_id, rank_search);
+  PERFORM deleteLocationArea(partition, place_id, rank_search);
 
   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
   postcode := NULL;
@@ -305,32 +317,18 @@ BEGIN
     centroid := ST_Centroid(geometry);
 
     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
-      x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
+      PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
     END LOOP;
 
-  ELSE
-
-    diameter := 0.02;
-    IF rank_address = 0 THEN
-      diameter := 0.02;
-    ELSEIF rank_search <= 14 THEN
-      diameter := 1.2;
-    ELSEIF rank_search <= 15 THEN
-      diameter := 1;
-    ELSEIF rank_search <= 16 THEN
-      diameter := 0.5;
-    ELSEIF rank_search <= 17 THEN
-      diameter := 0.2;
-    ELSEIF rank_search <= 21 THEN
-      diameter := 0.05;
-    ELSEIF rank_search = 25 THEN
-      diameter := 0.005;
-    END IF;
-
---    RAISE WARNING 'adding % diameter %', place_id, diameter;
+  ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
+    radius := near_feature_rank_distance(rank_search);
+    --DEBUG: RAISE WARNING 'adding % diameter %', place_id, diameter;
 
-    secgeo := ST_Buffer(geometry, diameter);
-    x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
+    -- Create a bounding box with an extent computed from the radius (in meters).
+    secgeo := ST_Envelope(ST_Collect(
+                            ST_Project(geometry, radius, 0.785398)::geometry,
+                            ST_Project(geometry, radius, 3.9269908)::geometry));
+    PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, geometry, secgeo);
 
   END IF;
 
-- 
2.39.5