add search for postcode

Implements the 'postcode' operator.

diff --git a/lib/Geocode.php b/lib/Geocode.php
index f07c51045fb120563799539c295235cbc4f06c91..82c9b0b68db735285716dd7431f03637ba078133 100644 (file)
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -476,6 +476,35 @@ class Geocode
         if ($this->bIncludeNameDetails) $sSQL .= "name, ";
         $sSQL .= "     extratags->'place' ";
 
+        // postcode table
+        $sSQL .= "UNION ";
+        $sSQL .= "SELECT";
+        $sSQL .= "  'P' as osm_type,";
+        $sSQL .= "  (SELECT osm_id from placex p WHERE p.place_id = parent_place_id) as osm_id,";
+        $sSQL .= "  'place' as class, 'postcode' as type,";
+        $sSQL .= "  null as admin_level, rank_search, rank_address,";
+        $sSQL .= "  place_id, parent_place_id, country_code,";
+        $sSQL .= "  get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) AS langaddress,";
+        $sSQL .= "  postcode as placename,";
+        $sSQL .= "  postcode as ref,";
+        if ($this->bIncludeExtraTags) $sSQL .= "null AS extra,";
+        if ($this->bIncludeNameDetails) $sSQL .= "null AS names,";
+        $sSQL .= "  ST_x(st_centroid(geometry)) AS lon, ST_y(st_centroid(geometry)) AS lat,";
+        $sSQL .=    $sImportanceSQL."(0.75-(rank_search::float/40)) AS importance, ";
+        $sSQL .= "  (";
+        $sSQL .= "     SELECT max(p.importance*(p.rank_address+2))";
+        $sSQL .= "     FROM ";
+        $sSQL .= "       place_addressline s, ";
+        $sSQL .= "       placex p";
+        $sSQL .= "     WHERE s.place_id = parent_place_id";
+        $sSQL .= "       AND p.place_id = s.address_place_id ";
+        $sSQL .= "       AND s.isaddress";
+        $sSQL .= "       AND p.importance is not null";
+        $sSQL .= "  ) AS addressimportance, ";
+        $sSQL .= "  null AS extra_place ";
+        $sSQL .= "FROM location_postcode";
+        $sSQL .= " WHERE place_id in ($sPlaceIDs) ";
+
         if (30 >= $this->iMinAddressRank && 30 <= $this->iMaxAddressRank) {
             // only Tiger housenumbers and interpolation lines need to be interpolated, because they are saved as lines
             // with start- and endnumber, the common osm housenumbers are usually saved as points
@@ -719,7 +748,7 @@ class Geocode
                                             $aNewSearch = $aSearch;
                                             $aNewSearch['sOperator'] = 'postcode';
                                             $aNewSearch['aAddress'] = array_merge($aNewSearch['aAddress'], $aNewSearch['aName']);
-                                            $aNewSearch['aName'][$aSearchTerm['word_id']] = $aSearchTerm['word_token'];
+                                            $aNewSearch['aName'][$aSearchTerm['word_id']] = substr($aSearchTerm['word_token'], 1);
                                             if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aNewSearch;
                                         }
 
@@ -1308,6 +1337,18 @@ class Geocode
                         // If a coordinate is given, the search must either
                         // be for a name or a special search. Ignore everythin else.
                         $aPlaceIDs = array();
+                    } elseif ($aSearch['sOperator'] == 'postcode') {
+                        $sSQL  = "SELECT place_id FROM location_postcode ";
+                        $sSQL .= "WHERE postcode = '".pg_escape_string(reset($aSearch['aName']))."'";
+                        if ($aSearch['sCountryCode']) {
+                            $sSQL .= " AND country_code = '".$aSearch['sCountryCode']."'";
+                        }
+                        if ($sCountryCodesSQL) {
+                            $sSQL .= " AND country_code in ($sCountryCodesSQL)";
+                        }
+                        $sSQL .= " LIMIT $this->iLimit";
+                        if (CONST_Debug) var_dump($sSQL);
+                        $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
                     } else {
                         $aPlaceIDs = array();
 

diff --git a/lib/lib.php b/lib/lib.php
index cb599ae8b1c46ed758b9f0d52fe3e3a01b571c42..98b7d09289ba4f0e8c62448394d3fc25f969ef82 100644 (file)
--- a/lib/lib.php
+++ b/lib/lib.php
@@ -516,7 +516,7 @@ function _debugDumpGroupedSearches($aData, $aTokens)
     echo "<table border=\"1\">";
     echo "<tr><th>rank</th><th>Name Tokens</th><th>Name Not</th>";
     echo "<th>Address Tokens</th><th>Address Not</th><th>country</th>";
-    echo "<th>operator</th><th>class</th><th>type</th><th>house#</th>";
+    echo "<th>operator</th><th>class</th><th>type</th><th>postcode</th><th>house#</th>";
     echo "<th>Lat</th><th>Lon</th><th>Radius</th></tr>";
     foreach ($aData as $iRank => $aRankedSet) {
         foreach ($aRankedSet as $aRow) {
@@ -561,6 +561,7 @@ function _debugDumpGroupedSearches($aData, $aTokens)
             echo "<td>".$aRow['sClass']."</td>";
             echo "<td>".$aRow['sType']."</td>";
 
+            echo "<td>".$aRow['sPostcode']."</td>";
             echo "<td>".$aRow['sHouseNumber']."</td>";
 
             echo "<td>".$aRow['fLat']."</td>";

diff --git a/sql/functions.sql b/sql/functions.sql
index b5ac15a3c30771913a1cc60ebc2afe55449e171b..bceb9ec4eee603ee63cd25f403d2d1ed3d9a5f86 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -2431,11 +2431,19 @@ BEGIN
   -- %NOAUXDATA% IF 0 THEN
   IF for_place_id IS NULL THEN
     select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
-      WHERE place_id = in_place_id 
+      WHERE place_id = in_place_id
       INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
   END IF;
   -- %NOAUXDATA% END IF;
 
+  -- postcode table
+  IF for_place_id IS NULL THEN
+    select parent_place_id, country_code, rank_address, postcode, 'place', 'postcode'
+      FROM location_postcode
+      WHERE place_id = in_place_id
+      INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, searchclass, searchtype;
+  END IF;
+
   IF for_place_id IS NULL THEN
     select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex 
       WHERE place_id = in_place_id and  rank_search > 27

diff --git a/sql/tables.sql b/sql/tables.sql
index caaa62dfdccd5addfbedca702d8298e5b0a7ae55..4a22a814feab17140a2af2b0f994fa9bfb0b1935 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -212,6 +212,7 @@ CREATE TABLE location_postcode (
   geometry GEOMETRY(Geometry, 4326)
   );
 CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index};
+GRANT SELECT ON location_postcode TO "{www-user}" ;
 
 CREATE TRIGGER location_postcode_before_update BEFORE UPDATE ON location_postcode
     FOR EACH ROW EXECUTE PROCEDURE postcode_update();