From 5b4bbab9be984706313d9273d029df42f5762b54 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Thu, 6 Jul 2017 22:48:09 +0200
Subject: [PATCH] include GB CodePoint data into location_postcode table

---
 lib/Geocode.php     | 16 ++--------------
 lib/lib.php         | 19 -------------------
 sql/indices.src.sql |  5 +----
 utils/setup.php     | 16 ++++++++++++++++
 4 files changed, 19 insertions(+), 37 deletions(-)

diff --git a/lib/Geocode.php b/lib/Geocode.php
index d77d9752..0546983f 100644
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -1134,21 +1134,9 @@ class Geocode
                 }
                 if (CONST_Debug) var_Dump($aPhrases, $aValidTokens);
 
-                // Try and calculate GB postcodes we might be missing
+                // US ZIP+4 codes - if there is no token, merge in the 5-digit ZIP code
                 foreach ($aTokens as $sToken) {
-                    // Source of gb postcodes is now definitive - always use
-                    if (preg_match('/^([A-Z][A-Z]?[0-9][0-9A-Z]? ?[0-9])([A-Z][A-Z])$/', strtoupper(trim($sToken)), $aData)) {
-                        if (substr($aData[1], -2, 1) != ' ') {
-                            $aData[0] = substr($aData[0], 0, strlen($aData[1])-1).' '.substr($aData[0], strlen($aData[1])-1);
-                            $aData[1] = substr($aData[1], 0, -1).' '.substr($aData[1], -1, 1);
-                        }
-                        $aGBPostcodeLocation = gbPostcodeCalculate($aData[0], $aData[1], $aData[2], $this->oDB);
-                        if ($aGBPostcodeLocation) {
-                            $aValidTokens[$sToken] = $aGBPostcodeLocation;
-                        }
-                    } elseif (!isset($aValidTokens[$sToken]) && preg_match('/^([0-9]{5}) [0-9]{4}$/', $sToken, $aData)) {
-                        // US ZIP+4 codes - if there is no token,
-                        // merge in the 5-digit ZIP code
+                    if (!isset($aValidTokens[$sToken]) && preg_match('/^([0-9]{5}) [0-9]{4}$/', $sToken, $aData)) {
                         if (isset($aValidTokens[$aData[1]])) {
                             foreach ($aValidTokens[$aData[1]] as $aToken) {
                                 if (!$aToken['class']) {
diff --git a/lib/lib.php b/lib/lib.php
index 98b7d092..8f353a4d 100644
--- a/lib/lib.php
+++ b/lib/lib.php
@@ -116,25 +116,6 @@ function getTokensFromSets($aSets)
 }
 
 
-function gbPostcodeCalculate($sPostcode, $sPostcodeSector, $sPostcodeEnd, &$oDB)
-{
-    // Try an exact match on the gb_postcode table
-    $sSQL = 'select \'AA\', ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from gb_postcode where postcode = \''.$sPostcode.'\'';
-    $aNearPostcodes = chksql($oDB->getAll($sSQL));
-
-    if (sizeof($aNearPostcodes)) {
-        $aPostcodes = array();
-        foreach ($aNearPostcodes as $aPostcode) {
-            $aPostcodes[] = array('lat' => $aPostcode['lat'], 'lon' => $aPostcode['lon'], 'radius' => 0.005);
-        }
-
-        return $aPostcodes;
-    }
-
-    return false;
-}
-
-
 function getClassTypes()
 {
     return array(
diff --git a/sql/indices.src.sql b/sql/indices.src.sql
index 9de2c97f..cf5c4bc1 100644
--- a/sql/indices.src.sql
+++ b/sql/indices.src.sql
@@ -25,8 +25,5 @@ DROP INDEX IF EXISTS place_id_idx;
 CREATE UNIQUE INDEX idx_place_osm_unique on place using btree(osm_id,osm_type,class,type) {ts:address-index};
 
 
-CREATE INDEX idx_gb_postcode_postcode ON gb_postcode USING BTREE (postcode) {ts:search-index};
-
 CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) {ts:search-index};
-CREATE INDEX idx_postcode_parent_id ON location_postcode USING BTREE (parent_place_id) {ts:search-index} where parent_place_id IS NOT NULL;
-CREATE INDEX idx_postcode_postcode ON location_postcode USING BTREE (postcode, country_code) {ts:search-index};
+CREATE INDEX idx_postcode_postcode ON location_postcode USING BTREE (postcode) {ts:search-index};
diff --git a/utils/setup.php b/utils/setup.php
index 94087323..23861201 100755
--- a/utils/setup.php
+++ b/utils/setup.php
@@ -522,7 +522,22 @@ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all']) {
         $sSQL .= "          WHERE country_code = 'us')";
 
         if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+
+        $sSQL = "SELECT count(getorcreate_postcode_id(postcode))  FROM us_postcode";
+        if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
     }
+
+    // add missing postcodes for GB (if available)
+    $sSQL  = "INSERT INTO location_postcode";
+    $sSQL .= " (place_id, indexed_status, country_code, postcode, geometry) ";
+    $sSQL .= "SELECT nextval('seq_place'), 1, 'gb', postcode, geometry";
+    $sSQL .= "  FROM gb_postcode WHERE postcode NOT IN";
+    $sSQL .= "           (SELECT postcode FROM location_postcode";
+    $sSQL .= "             WHERE country_code = 'gb')";
+    if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+
+        $sSQL = "SELECT count(getorcreate_postcode_id(postcode))  FROM gb_postcode";
+        if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 }
 
 if ($aCMDResult['osmosis-init']) {
@@ -541,6 +556,7 @@ if ($aCMDResult['index'] || $aCMDResult['all']) {
     passthruCheckReturn($sBaseCmd.' -r 26');
 
     echo "Indexing postcodes....\n";
+    $oDB =& getDB();
     $sSQL = 'UPDATE location_postcode SET indexed_status = 0';
     if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 }
-- 
2.39.5