From 5673c4cf919d36ea440cfc23c2e5ebb41d815b43 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 9 Jul 2017 17:29:48 +0200
Subject: [PATCH] special handling for estimated postcode in areas

Don't add a postcode at all if multiple estimated
postcodes fall into the area.
---
 sql/functions.sql | 32 +++++++++++++++++++++++++-------
 1 file changed, 25 insertions(+), 7 deletions(-)

diff --git a/sql/functions.sql b/sql/functions.sql
index c779e826..8d477a64 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -321,6 +321,22 @@ CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETR
 DECLARE
   item RECORD;
 BEGIN
+    -- If the geometry is an area then only one postcode must be within
+    -- that area, otherwise consider the area as not having a postcode.
+    IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
+        FOR item IN
+            SELECT min(postcode) as postcode, count(*) as cnt FROM
+              (SELECT postcode FROM location_postcode
+                WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
+        LOOP
+            IF item.cnt > 1 THEN
+                RETURN null;
+            ELSEIF item.cnt = 1 THEN
+                RETURN item.postcode;
+            END IF;
+        END LOOP;
+    END IF;
+
     FOR item IN
         SELECT postcode FROM location_postcode
         WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
@@ -1545,13 +1561,15 @@ BEGIN
       --DEBUG: RAISE WARNING 'Got parent details from search name';
 
       -- determine postcode
-      IF NEW.address is not null AND NEW.address ? 'postcode' THEN
-          NEW.postcode = NEW.address->'postcode';
-      ELSE
-         SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode;
-      END IF;
-      IF NEW.postcode is null THEN
-        NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
+      IF NEW.rank_search > 4 THEN
+          IF NEW.address is not null AND NEW.address ? 'postcode' THEN
+              NEW.postcode = NEW.address->'postcode';
+          ELSE
+             SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode;
+          END IF;
+          IF NEW.postcode is null THEN
+            NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
+          END IF;
       END IF;
 
       -- If there is no name it isn't searchable, don't bother to create a search record
-- 
2.39.5