From 4e792546e81844a8b4ba1026358b2115452f42ab Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sat, 1 Jul 2017 19:02:25 +0200
Subject: [PATCH] add postcode to location_area tables

---
 sql/functions.sql               | 23 +++++++++++++----------
 sql/partition-functions.src.sql |  8 ++++----
 sql/tables.sql                  |  1 +
 3 files changed, 18 insertions(+), 14 deletions(-)

diff --git a/sql/functions.sql b/sql/functions.sql
index fd71c6fd..3e80c2df 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -574,36 +574,39 @@ CREATE OR REPLACE FUNCTION add_location(
     keywords INTEGER[],
     rank_search INTEGER,
     rank_address INTEGER,
+    in_postcode TEXT,
     geometry GEOMETRY
   ) 
   RETURNS BOOLEAN
   AS $$
 DECLARE
   locationid INTEGER;
-  isarea BOOLEAN;
   centroid GEOMETRY;
   diameter FLOAT;
   x BOOLEAN;
   splitGeom RECORD;
   secgeo GEOMETRY;
+  postcode TEXT;
 BEGIN
 
   IF rank_search > 25 THEN
     RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
   END IF;
 
---  RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
-
   x := deleteLocationArea(partition, place_id, rank_search);
 
-  isarea := false;
-  IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
+  -- add postcode only if it contains a single entry, i.e. ignore postcode lists
+  IF in_postcode IS NULL OR in_postcode similar to '%(,|;)%' THEN
+      postcode := NULL;
+  ELSE
+      postcode := in_postcode;
+  END IF;
 
-    isArea := true;
+  IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
     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, centroid, secgeo);
+      x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
     END LOOP;
 
   ELSE
@@ -628,7 +631,7 @@ BEGIN
 --    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, ST_Centroid(geometry), secgeo);
+    x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
 
   END IF;
 
@@ -1529,7 +1532,7 @@ BEGIN
       -- Just be happy with inheriting from parent road only
 
       IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
-        result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
+        result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
         --DEBUG: RAISE WARNING 'Place added to location table';
       END IF;
 
@@ -1901,7 +1904,7 @@ BEGIN
   IF NEW.name IS NOT NULL THEN
 
     IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
-      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
+      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
       --DEBUG: RAISE WARNING 'added to location (full)';
     END IF;
 
diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql
index f4df9135..d7153ca3 100644
--- a/sql/partition-functions.src.sql
+++ b/sql/partition-functions.src.sql
@@ -55,8 +55,8 @@ $$
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaLarge(
-  in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[], 
-  in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
+  in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[],
+  in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT,
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE
 BEGIN
@@ -72,8 +72,8 @@ BEGIN
 
 -- start
   IF in_partition = -partition- THEN
-    INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, centroid, geometry)
-      values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
+    INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
+      values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
     RETURN TRUE;
   END IF;
 -- end
diff --git a/sql/tables.sql b/sql/tables.sql
index 8d8ba9a7..98bf0c02 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -62,6 +62,7 @@ CREATE TABLE location_area (
   rank_address SMALLINT NOT NULL,
   country_code VARCHAR(2),
   isguess BOOL,
+  postcode TEXT,
   centroid GEOMETRY(Point, 4326),
   geometry GEOMETRY(Geometry, 4326)
   );
-- 
2.39.5