From 57dc0304b50e9e4519cbecabdd5e5c43c1130121 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 4 Jul 2017 23:25:48 +0200 Subject: [PATCH] add search for postcode Implements the 'postcode' operator. --- lib/Geocode.php | 43 ++++++++++++++++++++++++++++++++++++++++++- lib/lib.php | 3 ++- sql/functions.sql | 10 +++++++++- sql/tables.sql | 1 + 4 files changed, 54 insertions(+), 3 deletions(-) diff --git a/lib/Geocode.php b/lib/Geocode.php index f07c5104..82c9b0b6 100644 --- 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 cb599ae8..98b7d092 100644 --- a/lib/lib.php +++ b/lib/lib.php @@ -516,7 +516,7 @@ function _debugDumpGroupedSearches($aData, $aTokens) echo ""; echo ""; echo ""; - echo ""; + echo ""; echo ""; foreach ($aData as $iRank => $aRankedSet) { foreach ($aRankedSet as $aRow) { @@ -561,6 +561,7 @@ function _debugDumpGroupedSearches($aData, $aTokens) echo ""; echo ""; + echo ""; echo ""; echo ""; diff --git a/sql/functions.sql b/sql/functions.sql index b5ac15a3..bceb9ec4 100644 --- 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 caaa62df..4a22a814 100644 --- 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(); -- 2.45.1
rankName TokensName NotAddress TokensAddress Notcountryoperatorclasstypehouse#operatorclasstypepostcodehouse#LatLonRadius
".$aRow['sClass']."".$aRow['sType']."".$aRow['sPostcode']."".$aRow['sHouseNumber']."".$aRow['fLat']."