From 784dad866fab9f5b2ccb72228d5eaacc7d5a070a Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Thu, 21 Apr 2022 21:56:59 +0200
Subject: [PATCH 1/1] change distance computation between place and address
 part

Instead of computing the distance to the centroid of the area
compute the distance of the area to the centroid of the feature.
This means we give preference to the area that covers the centroid.
It's still a heuristics but one that is a bit less random.
---
 lib-sql/functions/partition-functions.sql | 7 +++++--
 lib-sql/functions/placex_triggers.sql     | 6 ++++--
 lib-sql/functions/postcode_triggers.sql   | 2 +-
 3 files changed, 10 insertions(+), 5 deletions(-)

diff --git a/lib-sql/functions/partition-functions.sql b/lib-sql/functions/partition-functions.sql
index cf83f840..5eb1aef1 100644
--- a/lib-sql/functions/partition-functions.sql
+++ b/lib-sql/functions/partition-functions.sql
@@ -39,7 +39,10 @@ BEGIN
 END
 $$ LANGUAGE plpgsql IMMUTABLE;
 
-create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER) RETURNS setof nearfeaturecentr AS $$
+CREATE OR REPLACE function getNearFeatures(in_partition INTEGER, feature GEOMETRY,
+                                           feature_centroid GEOMETRY,
+                                           maxrank INTEGER)
+RETURNS setof nearfeaturecentr AS $$
 DECLARE
   r nearfeaturecentr%rowtype;
 BEGIN
@@ -48,7 +51,7 @@ BEGIN
   IF in_partition = {{ partition }} THEN
     FOR r IN
       SELECT place_id, keywords, rank_address, rank_search,
-             min(ST_Distance(feature, centroid)) as distance,
+             min(ST_Distance(feature_centroid, geometry)) as distance,
              isguess, postcode, centroid
       FROM location_area_large_{{ partition }}
       WHERE geometry && feature
diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 9463bb27..8a65c555 100644
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -449,6 +449,7 @@ CREATE OR REPLACE FUNCTION insert_addresslines(obj_place_id BIGINT,
                                                maxrank SMALLINT,
                                                token_info JSONB,
                                                geometry GEOMETRY,
+                                               centroid GEOMETRY,
                                                country TEXT,
                                                OUT parent_place_id BIGINT,
                                                OUT postcode TEXT,
@@ -511,7 +512,7 @@ BEGIN
   END LOOP;
 
   FOR location IN
-    SELECT * FROM getNearFeatures(partition, geometry, maxrank)
+    SELECT * FROM getNearFeatures(partition, geometry, centroid, maxrank)
     WHERE not addr_place_ids @> ARRAY[place_id]
     ORDER BY rank_address, isguess asc,
              distance *
@@ -1106,7 +1107,8 @@ BEGIN
   END IF;
 
   SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition, max_rank,
-                                    NEW.token_info, geom, NEW.country_code)
+                                    NEW.token_info, geom, NEW.centroid,
+                                    NEW.country_code)
     INTO NEW.parent_place_id, NEW.postcode, nameaddress_vector;
 
   {% if debug %}RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;{% endif %}
diff --git a/lib-sql/functions/postcode_triggers.sql b/lib-sql/functions/postcode_triggers.sql
index f6d81a88..3465e775 100644
--- a/lib-sql/functions/postcode_triggers.sql
+++ b/lib-sql/functions/postcode_triggers.sql
@@ -34,7 +34,7 @@ BEGIN
     NEW.parent_place_id = 0;
     FOR location IN
       SELECT place_id
-        FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search)
+        FROM getNearFeatures(partition, NEW.geometry, NEW.geometry, NEW.rank_search)
         WHERE NOT isguess ORDER BY rank_address DESC, distance asc LIMIT 1
     LOOP
         NEW.parent_place_id = location.place_id;
-- 
2.39.5