X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6757e1b865da3e01d1ae9ce5d1e71b039e4a7a75..5b4bbab9be984706313d9273d029df42f5762b54:/lib/Geocode.php diff --git a/lib/Geocode.php b/lib/Geocode.php index ae82498f..0546983f 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -2,6 +2,7 @@ namespace Nominatim; +require_once(CONST_BasePath.'/lib/NearPoint.php'); require_once(CONST_BasePath.'/lib/PlaceLookup.php'); require_once(CONST_BasePath.'/lib/ReverseGeocode.php'); @@ -32,7 +33,6 @@ class Geocode protected $bFallback = false; protected $aCountryCodes = false; - protected $aNearPoint = false; protected $bBoundedSearch = false; protected $aViewBox = false; @@ -67,19 +67,45 @@ class Geocode $this->aLangPrefOrder = $aLangPref; } - public function getIncludeAddressDetails() + public function getMoreUrlParams() { - return $this->bIncludeAddressDetails; - } + if ($this->aStructuredQuery) { + $aParams = $this->aStructuredQuery; + } else { + $aParams = array('q' => $this->sQuery); + } - public function getIncludeExtraTags() - { - return $this->bIncludeExtraTags; - } + if ($this->aExcludePlaceIDs) { + $aParams['exclude_place_ids'] = implode(',', $this->aExcludePlaceIDs); + } - public function getIncludeNameDetails() - { - return $this->bIncludeNameDetails; + if ($this->bIncludeAddressDetails) $aParams['addressdetails'] = '1'; + if ($this->bIncludeExtraTags) $aParams['extratags'] = '1'; + if ($this->bIncludeNameDetails) $aParams['namedetails'] = '1'; + + if ($this->bIncludePolygonAsPoints) $aParams['polygon'] = '1'; + if ($this->bIncludePolygonAsText) $aParams['polygon_text'] = '1'; + if ($this->bIncludePolygonAsGeoJSON) $aParams['polygon_geojson'] = '1'; + if ($this->bIncludePolygonAsKML) $aParams['polygon_kml'] = '1'; + if ($this->bIncludePolygonAsSVG) $aParams['polygon_svg'] = '1'; + + if ($this->fPolygonSimplificationThreshold > 0.0) { + $aParams['polygon_threshold'] = $this->fPolygonSimplificationThreshold; + } + + if ($this->bBoundedSearch) $aParams['bounded'] = '1'; + if (!$this->bDeDupe) $aParams['dedupe'] = '0'; + + if ($this->aCountryCodes) { + $aParams['countrycodes'] = implode(',', $this->aCountryCodes); + } + + if ($this->aViewBox) { + $aParams['viewbox'] = $this->aViewBox[0].','.$this->aViewBox[3] + .','.$this->aViewBox[2].','.$this->aViewBox[1]; + } + + return $aParams; } public function setIncludePolygonAsPoints($b = true) @@ -121,17 +147,6 @@ class Geocode $this->iLimit = $iLimit + min($iLimit, 10); } - public function getExcludedPlaceIDs() - { - return $this->aExcludePlaceIDs; - } - - public function getViewBoxString() - { - if (!$this->aViewBox) return null; - return $this->aViewBox[0].','.$this->aViewBox[3].','.$this->aViewBox[2].','.$this->aViewBox[1]; - } - public function setFeatureType($sFeatureType) { switch ($sFeatureType) { @@ -169,10 +184,10 @@ class Geocode } $this->sViewboxCentreSQL .= ")'::geometry,4326)"; - $this->sViewboxSmallSQL = 'st_buffer('.$this->sViewboxCentreSQL; + $this->sViewboxSmallSQL = 'ST_BUFFER('.$this->sViewboxCentreSQL; $this->sViewboxSmallSQL .= ','.($fRouteWidth/69).')'; - $this->sViewboxLargeSQL = 'st_buffer('.$this->sViewboxCentreSQL; + $this->sViewboxLargeSQL = 'ST_BUFFER('.$this->sViewboxCentreSQL; $this->sViewboxLargeSQL .= ','.($fRouteWidth/30).')'; } @@ -180,14 +195,15 @@ class Geocode { $this->aViewBox = array_map('floatval', $aViewbox); - if ($this->aViewBox[0] < -180 - || $this->aViewBox[2] > 180 - || $this->aViewBox[0] >= $this->aViewBox[2] - || $this->aViewBox[1] < -90 - || $this->aViewBox[3] > 90 - || $this->aViewBox[1] >= $this->aViewBox[3] + $this->aViewBox[0] = max(-180.0, min(180, $this->aViewBox[0])); + $this->aViewBox[1] = max(-90.0, min(90, $this->aViewBox[1])); + $this->aViewBox[2] = max(-180.0, min(180, $this->aViewBox[2])); + $this->aViewBox[3] = max(-90.0, min(90, $this->aViewBox[3])); + + if (abs($this->aViewBox[0] - $this->aViewBox[2]) < 0.000000001 + || abs($this->aViewBox[1] - $this->aViewBox[3]) < 0.000000001 ) { - userError("Bad parameter 'viewbox'. Out of range".$this->aViewBox[0]."|".$this->aViewBox[1]."|".$this->aViewBox[2]."|".$this->aViewBox[3]); + userError("Bad parameter 'viewbox'. Not a box."); } $fHeight = $this->aViewBox[0] - $this->aViewBox[2]; @@ -198,13 +214,20 @@ class Geocode $aBigViewBox[3] = $this->aViewBox[3] - $fWidth; $this->sViewboxCentreSQL = false; - $this->sViewboxSmallSQL = "ST_SetSRID(ST_MakeBox2D(ST_Point(".$this->aViewBox[0].",".$this->aViewBox[1]."),ST_Point(".$this->aViewBox[2].",".$this->aViewBox[3].")),4326)"; - $this->sViewboxLargeSQL = "ST_SetSRID(ST_MakeBox2D(ST_Point(".$aBigViewBox[0].",".$aBigViewBox[1]."),ST_Point(".$aBigViewBox[2].",".$aBigViewBox[3].")),4326)"; - } - - public function setNearPoint($aNearPoint, $fRadiusDeg = 0.1) - { - $this->aNearPoint = array((float)$aNearPoint[0], (float)$aNearPoint[1], (float)$fRadiusDeg); + $this->sViewboxSmallSQL = sprintf( + 'ST_SetSRID(ST_MakeBox2D(ST_Point(%F,%F),ST_Point(%F,%F)),4326)', + $this->aViewBox[0], + $this->aViewBox[1], + $this->aViewBox[2], + $this->aViewBox[3] + ); + $this->sViewboxLargeSQL = sprintf( + 'ST_SetSRID(ST_MakeBox2D(ST_Point(%F,%F),ST_Point(%F,%F)),4326)', + $aBigViewBox[0], + $aBigViewBox[1], + $aBigViewBox[2], + $aBigViewBox[3] + ); } public function setQuery($sQueryString) @@ -327,7 +350,7 @@ class Geocode return true; } - public function setStructuredQuery($sAmentiy = false, $sStreet = false, $sCity = false, $sCounty = false, $sState = false, $sCountry = false, $sPostalCode = false) + public function setStructuredQuery($sAmenity = false, $sStreet = false, $sCity = false, $sCounty = false, $sState = false, $sCountry = false, $sPostalCode = false) { $this->sQuery = false; @@ -339,7 +362,7 @@ class Geocode $this->aStructuredQuery = array(); $this->sAllowedTypesSQLList = ''; - $this->loadStructuredAddressElement($sAmentiy, 'amenity', 26, 30, false); + $this->loadStructuredAddressElement($sAmenity, 'amenity', 26, 30, false); $this->loadStructuredAddressElement($sStreet, 'street', 26, 30, false); $this->loadStructuredAddressElement($sCity, 'city', 14, 24, false); $this->loadStructuredAddressElement($sCounty, 'county', 9, 13, false); @@ -387,34 +410,100 @@ class Geocode $sPlaceIDs = join(',', array_keys($aPlaceIDs)); $sImportanceSQL = ''; - if ($this->sViewboxSmallSQL) $sImportanceSQL .= " case when ST_Contains($this->sViewboxSmallSQL, ST_Collect(centroid)) THEN 1 ELSE 0.75 END * "; - if ($this->sViewboxLargeSQL) $sImportanceSQL .= " case when ST_Contains($this->sViewboxLargeSQL, ST_Collect(centroid)) THEN 1 ELSE 0.75 END * "; - - $sSQL = "select osm_type,osm_id,class,type,admin_level,rank_search,rank_address,min(place_id) as place_id, min(parent_place_id) as parent_place_id, calculated_country_code as country_code,"; - $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress,"; - $sSQL .= "get_name_by_language(name, $sLanguagePrefArraySQL) as placename,"; - $sSQL .= "get_name_by_language(name, ARRAY['ref']) as ref,"; - if ($this->bIncludeExtraTags) $sSQL .= "hstore_to_json(extratags)::text as extra,"; - if ($this->bIncludeNameDetails) $sSQL .= "hstore_to_json(name)::text as names,"; - $sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, "; - $sSQL .= $sImportanceSQL."coalesce(importance,0.75-(rank_search::float/40)) as importance, "; - $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, "; - $sSQL .= "(extratags->'place') as extra_place "; - $sSQL .= "from placex where place_id in ($sPlaceIDs) "; - $sSQL .= "and (placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank "; - if (14 >= $this->iMinAddressRank && 14 <= $this->iMaxAddressRank) $sSQL .= " OR (extratags->'place') = 'city'"; - if ($this->aAddressRankList) $sSQL .= " OR placex.rank_address in (".join(',', $this->aAddressRankList).")"; - $sSQL .= ") "; - if ($this->sAllowedTypesSQLList) $sSQL .= "and placex.class in $this->sAllowedTypesSQLList "; - $sSQL .= "and linked_place_id is null "; - $sSQL .= "group by osm_type,osm_id,class,type,admin_level,rank_search,rank_address,calculated_country_code,importance"; - if (!$this->bDeDupe) $sSQL .= ",place_id"; - $sSQL .= ",langaddress "; - $sSQL .= ",placename "; - $sSQL .= ",ref "; - if ($this->bIncludeExtraTags) $sSQL .= ",extratags"; - if ($this->bIncludeNameDetails) $sSQL .= ",name"; - $sSQL .= ",extratags->'place' "; + if ($this->sViewboxSmallSQL) $sImportanceSQL .= " CASE WHEN ST_Contains($this->sViewboxSmallSQL, ST_Collect(centroid)) THEN 1 ELSE 0.75 END * "; + if ($this->sViewboxLargeSQL) $sImportanceSQL .= " CASE WHEN ST_Contains($this->sViewboxLargeSQL, ST_Collect(centroid)) THEN 1 ELSE 0.75 END * "; + + $sSQL = "SELECT "; + $sSQL .= " osm_type,"; + $sSQL .= " osm_id,"; + $sSQL .= " class,"; + $sSQL .= " type,"; + $sSQL .= " admin_level,"; + $sSQL .= " rank_search,"; + $sSQL .= " rank_address,"; + $sSQL .= " min(place_id) AS place_id, "; + $sSQL .= " min(parent_place_id) AS parent_place_id, "; + $sSQL .= " country_code, "; + $sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) AS langaddress,"; + $sSQL .= " get_name_by_language(name, $sLanguagePrefArraySQL) AS placename,"; + $sSQL .= " get_name_by_language(name, ARRAY['ref']) AS ref,"; + if ($this->bIncludeExtraTags) $sSQL .= "hstore_to_json(extratags)::text AS extra,"; + if ($this->bIncludeNameDetails) $sSQL .= "hstore_to_json(name)::text AS names,"; + $sSQL .= " avg(ST_X(centroid)) AS lon, "; + $sSQL .= " avg(ST_Y(centroid)) AS lat, "; + $sSQL .= " ".$sImportanceSQL."COALESCE(importance,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 = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END)"; + $sSQL .= " AND p.place_id = s.address_place_id "; + $sSQL .= " AND s.isaddress "; + $sSQL .= " AND p.importance is not null "; + $sSQL .= " ) AS addressimportance, "; + $sSQL .= " (extratags->'place') AS extra_place "; + $sSQL .= " FROM placex"; + $sSQL .= " WHERE place_id in ($sPlaceIDs) "; + $sSQL .= " AND ("; + $sSQL .= " placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank "; + if (14 >= $this->iMinAddressRank && 14 <= $this->iMaxAddressRank) { + $sSQL .= " OR (extratags->'place') = 'city'"; + } + if ($this->aAddressRankList) { + $sSQL .= " OR placex.rank_address in (".join(',', $this->aAddressRankList).")"; + } + $sSQL .= " ) "; + if ($this->sAllowedTypesSQLList) { + $sSQL .= "AND placex.class in $this->sAllowedTypesSQLList "; + } + $sSQL .= " AND linked_place_id is null "; + $sSQL .= " GROUP BY "; + $sSQL .= " osm_type, "; + $sSQL .= " osm_id, "; + $sSQL .= " class, "; + $sSQL .= " type, "; + $sSQL .= " admin_level, "; + $sSQL .= " rank_search, "; + $sSQL .= " rank_address, "; + $sSQL .= " country_code, "; + $sSQL .= " importance, "; + if (!$this->bDeDupe) $sSQL .= "place_id,"; + $sSQL .= " langaddress, "; + $sSQL .= " placename, "; + $sSQL .= " ref, "; + if ($this->bIncludeExtraTags) $sSQL .= "extratags, "; + 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 @@ -427,70 +516,156 @@ class Geocode $sHousenumbers .= "(".$placeID.", ".$housenumber.")"; if ($i<$length) $sHousenumbers .= ", "; } + if (CONST_Use_US_Tiger_Data) { // Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join) $sSQL .= " union"; - $sSQL .= " select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code"; - $sSQL .= ", get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress "; - $sSQL .= ", null as placename"; - $sSQL .= ", null as ref"; - if ($this->bIncludeExtraTags) $sSQL .= ", null as extra"; - if ($this->bIncludeNameDetails) $sSQL .= ", null as names"; - $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,"; - $sSQL .= $sImportanceSQL."-1.15 as importance "; - $sSQL .= ", (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance "; - $sSQL .= ", null as extra_place "; - $sSQL .= " from (select place_id"; - // interpolate the Tiger housenumbers here - $sSQL .= ", ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) as centroid, parent_place_id, housenumber_for_place"; - $sSQL .= " from (location_property_tiger "; - $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) "; - $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here - $sSQL .= " group by place_id, housenumber_for_place"; //is this group by really needed?, place_id + housenumber (in combination) are unique + $sSQL .= " SELECT "; + $sSQL .= " 'T' AS osm_type, "; + $sSQL .= " (SELECT osm_id from placex p WHERE p.place_id=min(blub.parent_place_id)) as osm_id, "; + $sSQL .= " 'place' AS class, "; + $sSQL .= " 'house' AS type, "; + $sSQL .= " null AS admin_level, "; + $sSQL .= " 30 AS rank_search, "; + $sSQL .= " 30 AS rank_address, "; + $sSQL .= " min(place_id) AS place_id, "; + $sSQL .= " min(parent_place_id) AS parent_place_id, "; + $sSQL .= " 'us' AS country_code, "; + $sSQL .= " get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) AS langaddress,"; + $sSQL .= " null AS placename, "; + $sSQL .= " null AS ref, "; + if ($this->bIncludeExtraTags) $sSQL .= "null AS extra,"; + if ($this->bIncludeNameDetails) $sSQL .= "null AS names,"; + $sSQL .= " avg(st_x(centroid)) AS lon, "; + $sSQL .= " avg(st_y(centroid)) AS lat,"; + $sSQL .= " ".$sImportanceSQL."-1.15 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 = min(blub.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 ("; + $sSQL .= " SELECT place_id, "; // interpolate the Tiger housenumbers here + $sSQL .= " ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) AS centroid, "; + $sSQL .= " parent_place_id, "; + $sSQL .= " housenumber_for_place"; + $sSQL .= " FROM ("; + $sSQL .= " location_property_tiger "; + $sSQL .= " JOIN (values ".$sHousenumbers.") AS housenumbers(place_id, housenumber_for_place) USING(place_id)) "; + $sSQL .= " WHERE "; + $sSQL .= " housenumber_for_place>=0"; + $sSQL .= " AND 30 between $this->iMinAddressRank AND $this->iMaxAddressRank"; + $sSQL .= " ) AS blub"; //postgres wants an alias here + $sSQL .= " GROUP BY"; + $sSQL .= " place_id, "; + $sSQL .= " housenumber_for_place"; //is this group by really needed?, place_id + housenumber (in combination) are unique if (!$this->bDeDupe) $sSQL .= ", place_id "; } // osmline // interpolation line search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join) - $sSQL .= " union "; - $sSQL .= "select 'W' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, calculated_country_code as country_code, "; - $sSQL .= "get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress, "; - $sSQL .= "null as placename, "; - $sSQL .= "null as ref, "; - if ($this->bIncludeExtraTags) $sSQL .= "null as extra, "; - if ($this->bIncludeNameDetails) $sSQL .= "null as names, "; - $sSQL .= " avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,"; - $sSQL .= $sImportanceSQL."-0.1 as importance, "; // slightly smaller than the importance for normal houses with rank 30, which is 0 - $sSQL .= " (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p"; - $sSQL .= " where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance,"; - $sSQL .= " null as extra_place "; - $sSQL .= " from (select place_id, calculated_country_code "; - // interpolate the housenumbers here - $sSQL .= ", CASE WHEN startnumber != endnumber THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) "; - $sSQL .= " ELSE ST_LineInterpolatePoint(linegeo, 0.5) END as centroid"; - $sSQL .= ", parent_place_id, housenumber_for_place "; - $sSQL .= " from (location_property_osmline "; - $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) "; - $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here - $sSQL .= " group by place_id, housenumber_for_place, calculated_country_code "; //is this group by really needed?, place_id + housenumber (in combination) are unique + $sSQL .= " UNION "; + $sSQL .= "SELECT "; + $sSQL .= " 'W' AS osm_type, "; + $sSQL .= " osm_id, "; + $sSQL .= " 'place' AS class, "; + $sSQL .= " 'house' AS type, "; + $sSQL .= " null AS admin_level, "; + $sSQL .= " 30 AS rank_search, "; + $sSQL .= " 30 AS rank_address, "; + $sSQL .= " min(place_id) as place_id, "; + $sSQL .= " min(parent_place_id) AS parent_place_id, "; + $sSQL .= " country_code, "; + $sSQL .= " get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) AS langaddress, "; + $sSQL .= " null AS placename, "; + $sSQL .= " null AS ref, "; + if ($this->bIncludeExtraTags) $sSQL .= "null AS extra, "; + if ($this->bIncludeNameDetails) $sSQL .= "null AS names, "; + $sSQL .= " AVG(st_x(centroid)) AS lon, "; + $sSQL .= " AVG(st_y(centroid)) AS lat, "; + $sSQL .= " ".$sImportanceSQL."-0.1 AS importance, "; // slightly smaller than the importance for normal houses with rank 30, which is 0 + $sSQL .= " ("; + $sSQL .= " SELECT "; + $sSQL .= " MAX(p.importance*(p.rank_address+2)) "; + $sSQL .= " FROM"; + $sSQL .= " place_addressline s, "; + $sSQL .= " placex p"; + $sSQL .= " WHERE s.place_id = min(blub.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 ("; + $sSQL .= " SELECT "; + $sSQL .= " osm_id, "; + $sSQL .= " place_id, "; + $sSQL .= " country_code, "; + $sSQL .= " CASE "; // interpolate the housenumbers here + $sSQL .= " WHEN startnumber != endnumber "; + $sSQL .= " THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) "; + $sSQL .= " ELSE ST_LineInterpolatePoint(linegeo, 0.5) "; + $sSQL .= " END as centroid, "; + $sSQL .= " parent_place_id, "; + $sSQL .= " housenumber_for_place "; + $sSQL .= " FROM ("; + $sSQL .= " location_property_osmline "; + $sSQL .= " JOIN (values ".$sHousenumbers.") AS housenumbers(place_id, housenumber_for_place) USING(place_id)"; + $sSQL .= " ) "; + $sSQL .= " WHERE housenumber_for_place>=0 "; + $sSQL .= " AND 30 between $this->iMinAddressRank AND $this->iMaxAddressRank"; + $sSQL .= " ) as blub"; //postgres wants an alias here + $sSQL .= " GROUP BY "; + $sSQL .= " osm_id, "; + $sSQL .= " place_id, "; + $sSQL .= " housenumber_for_place, "; + $sSQL .= " country_code "; //is this group by really needed?, place_id + housenumber (in combination) are unique if (!$this->bDeDupe) $sSQL .= ", place_id "; if (CONST_Use_Aux_Location_data) { - $sSQL .= " union "; - $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 0 as rank_search, 0 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code, "; - $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, "; - $sSQL .= "null as placename, "; - $sSQL .= "null as ref, "; - if ($this->bIncludeExtraTags) $sSQL .= "null as extra, "; - if ($this->bIncludeNameDetails) $sSQL .= "null as names, "; - $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, "; - $sSQL .= $sImportanceSQL."-1.10 as importance, "; - $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, "; - $sSQL .= "null as extra_place "; - $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) "; - $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank "; - $sSQL .= "group by place_id"; - if (!$this->bDeDupe) $sSQL .= ", place_id"; - $sSQL .= ", get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) "; + $sSQL .= " UNION "; + $sSQL .= " SELECT "; + $sSQL .= " 'L' AS osm_type, "; + $sSQL .= " place_id AS osm_id, "; + $sSQL .= " 'place' AS class,"; + $sSQL .= " 'house' AS type, "; + $sSQL .= " null AS admin_level, "; + $sSQL .= " 0 AS rank_search,"; + $sSQL .= " 0 AS rank_address, "; + $sSQL .= " min(place_id) AS place_id,"; + $sSQL .= " min(parent_place_id) AS parent_place_id, "; + $sSQL .= " 'us' AS country_code, "; + $sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) AS langaddress, "; + $sSQL .= " null AS placename, "; + $sSQL .= " null AS ref, "; + if ($this->bIncludeExtraTags) $sSQL .= "null AS extra, "; + if ($this->bIncludeNameDetails) $sSQL .= "null AS names, "; + $sSQL .= " avg(ST_X(centroid)) AS lon, "; + $sSQL .= " avg(ST_Y(centroid)) AS lat, "; + $sSQL .= " ".$sImportanceSQL."-1.10 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 = min(location_property_aux.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_property_aux "; + $sSQL .= " WHERE place_id in ($sPlaceIDs) "; + $sSQL .= " AND 30 between $this->iMinAddressRank and $this->iMaxAddressRank "; + $sSQL .= " GROUP BY "; + $sSQL .= " place_id, "; + if (!$this->bDeDupe) $sSQL .= "place_id, "; + $sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) "; } } @@ -507,7 +682,7 @@ class Geocode return $aSearchResults; } - public function getGroupedSearches($aSearches, $aPhraseTypes, $aPhrases, $aValidTokens, $aWordFrequencyScores, $bStructuredPhrases) + public function getGroupedSearches($aSearches, $aPhraseTypes, $aPhrases, $aValidTokens, $aWordFrequencyScores, $bStructuredPhrases, $sNormQuery) { /* Calculate all searches using aValidTokens i.e. @@ -520,12 +695,12 @@ class Geocode Score how good the search is so they can be ordered */ - foreach ($aPhrases as $iPhrase => $sPhrase) { + foreach ($aPhrases as $iPhrase => $aPhrase) { $aNewPhraseSearches = array(); if ($bStructuredPhrases) $sPhraseType = $aPhraseTypes[$iPhrase]; else $sPhraseType = ''; - foreach ($aPhrases[$iPhrase]['wordsets'] as $iWordSet => $aWordset) { + foreach ($aPhrase['wordsets'] as $iWordSet => $aWordset) { // Too many permutations - too expensive if ($iWordSet > 120) break; @@ -556,36 +731,33 @@ class Geocode if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aSearch; } } elseif (isset($aSearchTerm['lat']) && $aSearchTerm['lat'] !== '' && $aSearchTerm['lat'] !== null) { - if ($aSearch['fLat'] === '') { - $aSearch['fLat'] = $aSearchTerm['lat']; - $aSearch['fLon'] = $aSearchTerm['lon']; - $aSearch['fRadius'] = $aSearchTerm['radius']; + if ($aSearch['oNear'] === false) { + $aSearch['oNear'] = new NearPoint( + $aSearchTerm['lat'], + $aSearchTerm['lon'], + $aSearchTerm['radius'] + ); if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aSearch; } - } elseif ($sPhraseType == 'postalcode') { + } elseif ($sPhraseType == 'postalcode' || ($aSearchTerm['class'] == 'place' && $aSearchTerm['type'] == 'postcode')) { // We need to try the case where the postal code is the primary element (i.e. no way to tell if it is (postalcode, city) OR (city, postalcode) so try both if (isset($aSearchTerm['word_id']) && $aSearchTerm['word_id']) { - // If we already have a name try putting the postcode first - if (sizeof($aSearch['aName'])) { + // If we have structured search or this is the first term, + // make the postcode the primary search element. + if ($aSearchTerm['operator'] == '' && ($sPhraseType == 'postalcode' || sizeof($aSearch['aName']) == 0)) { $aNewSearch = $aSearch; + $aNewSearch['sOperator'] = 'postcode'; $aNewSearch['aAddress'] = array_merge($aNewSearch['aAddress'], $aNewSearch['aName']); - $aNewSearch['aName'] = array(); - $aNewSearch['aName'][$aSearchTerm['word_id']] = $aSearchTerm['word_id']; + $aNewSearch['aName'][$aSearchTerm['word_id']] = substr($aSearchTerm['word_token'], 1); if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aNewSearch; } - if (sizeof($aSearch['aName'])) { - if ((!$bStructuredPhrases || $iPhrase > 0) && $sPhraseType != 'country' && (!isset($aValidTokens[$sToken]) || strpos($sToken, ' ') !== false)) { - $aSearch['aAddress'][$aSearchTerm['word_id']] = $aSearchTerm['word_id']; - } else { - $aCurrentSearch['aFullNameAddress'][$aSearchTerm['word_id']] = $aSearchTerm['word_id']; - $aSearch['iSearchRank'] += 1000; // skip; - } - } else { - $aSearch['aName'][$aSearchTerm['word_id']] = $aSearchTerm['word_id']; - //$aSearch['iNamePhrase'] = $iPhrase; + // If we have a structured search or this is not the first term, + // add the postcode as an addendum. + if ($sPhraseType == 'postalcode' || sizeof($aSearch['aName'])) { + $aSearch['sPostcode'] = substr($aSearchTerm['word_token'], 1); + if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aSearch; } - if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aSearch; } } elseif (($sPhraseType == '' || $sPhraseType == 'street') && $aSearchTerm['class'] == 'place' && $aSearchTerm['type'] == 'house') { if ($aSearch['sHouseNumber'] === '') { @@ -604,13 +776,19 @@ class Geocode */ } } elseif ($sPhraseType == '' && $aSearchTerm['class'] !== '' && $aSearchTerm['class'] !== null) { - if ($aSearch['sClass'] === '') { - $aSearch['sOperator'] = $aSearchTerm['operator']; + // require a normalized exact match of the term + // if we have the normalizer version of the query + // available + if ($aSearch['sClass'] === '' + && ($sNormQuery === null || !($aSearchTerm['word'] && strpos($sNormQuery, $aSearchTerm['word']) === false))) { $aSearch['sClass'] = $aSearchTerm['class']; $aSearch['sType'] = $aSearchTerm['type']; - if (sizeof($aSearch['aName'])) $aSearch['sOperator'] = 'name'; - else $aSearch['sOperator'] = 'near'; // near = in for the moment - if (strlen($aSearchTerm['operator']) == 0) $aSearch['iSearchRank'] += 1; + if ($aSearchTerm['operator'] == '') { + $aSearch['sOperator'] = sizeof($aSearch['aName']) ? 'name' : 'near'; + $aSearch['iSearchRank'] += 2; + } else { + $aSearch['sOperator'] = 'near'; // near = in for the moment + } if ($aSearch['iSearchRank'] < $this->iMaxRank) $aNewWordsetSearches[] = $aSearch; } @@ -763,7 +941,14 @@ class Geocode public function lookup() { - if (!$this->sQuery && !$this->aStructuredQuery) return false; + if (!$this->sQuery && !$this->aStructuredQuery) return array(); + + $oNormalizer = \Transliterator::createFromRules(CONST_Term_Normalization_Rules); + if ($oNormalizer !== null) { + $sNormQuery = $oNormalizer->transliterate($this->sQuery); + } else { + $sNormQuery = null; + } $sLanguagePrefArraySQL = "ARRAY[".join(',', array_map("getDBQuoted", $this->aLangPrefOrder))."]"; $sCountryCodesSQL = false; @@ -772,6 +957,9 @@ class Geocode } $sQuery = $this->sQuery; + if (!preg_match('//u', $sQuery)) { + userError("Query string is not UTF-8 encoded."); + } // Conflicts between US state abreviations and various words for 'the' in different languages if (isset($this->aLangPrefOrder['name:en'])) { @@ -797,8 +985,9 @@ class Geocode } // Do we have anything that looks like a lat/lon pair? - if ($aLooksLike = looksLikeLatLonPair($sQuery)) { - $this->setNearPoint(array($aLooksLike['lat'], $aLooksLike['lon'])); + $oNearPoint = false; + if ($aLooksLike = NearPoint::extractFromQuery($sQuery)) { + $oNearPoint = $aLooksLike['pt']; $sQuery = $aLooksLike['query']; } @@ -820,21 +1009,11 @@ class Geocode 'sClass' => '', 'sType' => '', 'sHouseNumber' => '', - 'fLat' => '', - 'fLon' => '', - 'fRadius' => '' + 'sPostcode' => '', + 'oNear' => $oNearPoint ) ); - // Do we have a radius search? - $sNearPointSQL = false; - if ($this->aNearPoint) { - $sNearPointSQL = "ST_SetSRID(ST_Point(".(float)$this->aNearPoint[1].",".(float)$this->aNearPoint[0]."),4326)"; - $aSearches[0]['fLat'] = (float)$this->aNearPoint[0]; - $aSearches[0]['fLon'] = (float)$this->aNearPoint[1]; - $aSearches[0]['fRadius'] = (float)$this->aNearPoint[2]; - } - // Any 'special' terms in the search? $bSpecialTerms = false; preg_match_all('/\\[(.*)=(.*)\\]/', $sQuery, $aSpecialTermsRaw, PREG_SET_ORDER); @@ -853,9 +1032,15 @@ class Geocode foreach ($aSpecialTermsRaw as $aSpecialTerm) { $sQuery = str_replace($aSpecialTerm[0], ' ', $sQuery); - $sToken = chksql($this->oDB->getOne("select make_standard_name('".$aSpecialTerm[1]."') as string")); - $sSQL = 'select * from (select word_id,word_token, word, class, type, country_code, operator'; - $sSQL .= ' from word where word_token in (\' '.$sToken.'\')) as x where (class is not null and class not in (\'place\')) or country_code is not null'; + $sToken = chksql($this->oDB->getOne("SELECT make_standard_name('".$aSpecialTerm[1]."') AS string")); + $sSQL = 'SELECT * '; + $sSQL .= 'FROM ( '; + $sSQL .= ' SELECT word_id, word_token, word, class, type, country_code, operator'; + $sSQL .= ' FROM word '; + $sSQL .= ' WHERE word_token in (\' '.$sToken.'\')'; + $sSQL .= ') AS x '; + $sSQL .= ' WHERE (class is not null AND class not in (\'place\')) '; + $sSQL .= ' OR country_code is not null'; if (CONST_Debug) var_Dump($sSQL); $aSearchWords = chksql($this->oDB->getAll($sSQL)); $aNewSearches = array(); @@ -895,7 +1080,7 @@ class Geocode $aTokens = array(); foreach ($aPhrases as $iPhrase => $sPhrase) { $aPhrase = chksql( - $this->oDB->getRow("select make_standard_name('".pg_escape_string($sPhrase)."') as string"), + $this->oDB->getRow("SELECT make_standard_name('".pg_escape_string($sPhrase)."') as string"), "Cannot normalize query string (is it a UTF-8 string?)" ); if (trim($aPhrase['string'])) { @@ -914,8 +1099,9 @@ class Geocode if (sizeof($aTokens)) { // Check which tokens we have, get the ID numbers - $sSQL = 'select word_id,word_token, word, class, type, country_code, operator, search_name_count'; - $sSQL .= ' from word where word_token in ('.join(',', array_map("getDBQuoted", $aTokens)).')'; + $sSQL = 'SELECT word_id, word_token, word, class, type, country_code, operator, search_name_count'; + $sSQL .= ' FROM word '; + $sSQL .= ' WHERE word_token in ('.join(',', array_map("getDBQuoted", $aTokens)).')'; if (CONST_Debug) var_Dump($sSQL); @@ -948,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']) { @@ -991,7 +1165,7 @@ class Geocode // array with: placeid => -1 | tiger-housenumber $aResultPlaceIDs = array(); - $aGroupedSearches = $this->getGroupedSearches($aSearches, $aPhraseTypes, $aPhrases, $aValidTokens, $aWordFrequencyScores, $bStructuredPhrases); + $aGroupedSearches = $this->getGroupedSearches($aSearches, $aPhraseTypes, $aPhrases, $aValidTokens, $aWordFrequencyScores, $bStructuredPhrases, $sNormQuery); if ($this->bReverseInPlan) { // Reverse phrase array and also reverse the order of the wordsets in @@ -1003,7 +1177,7 @@ class Geocode $aFinalPhrase = end($aPhrases); $aPhrases[sizeof($aPhrases)-1]['wordsets'] = getInverseWordSets($aFinalPhrase['words'], 0); } - $aReverseGroupedSearches = $this->getGroupedSearches($aSearches, null, $aPhrases, $aValidTokens, $aWordFrequencyScores, false); + $aReverseGroupedSearches = $this->getGroupedSearches($aSearches, null, $aPhrases, $aValidTokens, $aWordFrequencyScores, false, $sNormQuery); foreach ($aGroupedSearches as $aSearches) { foreach ($aSearches as $aSearch) { @@ -1086,35 +1260,38 @@ class Geocode if (CONST_Debug) echo "
Search Loop, group $iGroupLoop, loop $iQueryLoop"; if (CONST_Debug) _debugDumpGroupedSearches(array($iGroupedRank => array($aSearch)), $aValidTokens); + if ($sCountryCodesSQL && $aSearch['sCountryCode'] && !in_array($aSearch['sCountryCode'], $this->aCountryCodes)) { + continue; + } + // No location term? - if (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']) && !$aSearch['fLon']) { + if (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']) && !$aSearch['oNear']) { if ($aSearch['sCountryCode'] && !$aSearch['sClass'] && !$aSearch['sHouseNumber']) { // Just looking for a country by code - look it up if (4 >= $this->iMinAddressRank && 4 <= $this->iMaxAddressRank) { - $sSQL = "select place_id from placex where calculated_country_code='".$aSearch['sCountryCode']."' and rank_search = 4"; - if ($sCountryCodesSQL) $sSQL .= " and calculated_country_code in ($sCountryCodesSQL)"; + $sSQL = "SELECT place_id FROM placex WHERE country_code='".$aSearch['sCountryCode']."' AND rank_search = 4"; if ($bBoundingBoxSearch) - $sSQL .= " and _st_intersects($this->sViewboxSmallSQL, geometry)"; - $sSQL .= " order by st_area(geometry) desc limit 1"; + $sSQL .= " AND _st_intersects($this->sViewboxSmallSQL, geometry)"; + $sSQL .= " ORDER BY st_area(geometry) DESC LIMIT 1"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); } else { $aPlaceIDs = array(); } } else { - if (!$bBoundingBoxSearch && !$aSearch['fLon']) continue; + if (!$bBoundingBoxSearch && !$aSearch['oNear']) continue; if (!$aSearch['sClass']) continue; - $sSQL = "select count(*) from pg_tables where tablename = 'place_classtype_".$aSearch['sClass']."_".$aSearch['sType']."'"; + $sSQL = "SELECT COUNT(*) FROM pg_tables WHERE tablename = 'place_classtype_".$aSearch['sClass']."_".$aSearch['sType']."'"; if (chksql($this->oDB->getOne($sSQL))) { - $sSQL = "select place_id from place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." ct"; - if ($sCountryCodesSQL) $sSQL .= " join placex using (place_id)"; - $sSQL .= " where st_contains($this->sViewboxSmallSQL, ct.centroid)"; - if ($sCountryCodesSQL) $sSQL .= " and calculated_country_code in ($sCountryCodesSQL)"; + $sSQL = "SELECT place_id FROM place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." ct"; + if ($sCountryCodesSQL) $sSQL .= " JOIN placex USING (place_id)"; + $sSQL .= " WHERE st_contains($this->sViewboxSmallSQL, ct.centroid)"; + if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)"; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")"; + $sSQL .= " AND place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } - if ($this->sViewboxCentreSQL) $sSQL .= " order by st_distance($this->sViewboxCentreSQL, ct.centroid) asc"; + if ($this->sViewboxCentreSQL) $sSQL .= " ORDER BY ST_Distance($this->sViewboxCentreSQL, ct.centroid) ASC"; $sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); @@ -1124,29 +1301,51 @@ class Geocode // expansion in that case. // Also don't expand if bounded results were requested. if (!sizeof($aPlaceIDs) && !sizeof($this->aExcludePlaceIDs) && !$this->bBoundedSearch) { - $sSQL = "select place_id from place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." ct"; + $sSQL = "SELECT place_id FROM place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." ct"; if ($sCountryCodesSQL) $sSQL .= " join placex using (place_id)"; - $sSQL .= " where st_contains($this->sViewboxLargeSQL, ct.centroid)"; - if ($sCountryCodesSQL) $sSQL .= " and calculated_country_code in ($sCountryCodesSQL)"; - if ($this->sViewboxCentreSQL) $sSQL .= " order by st_distance($this->sViewboxCentreSQL, ct.centroid) asc"; - $sSQL .= " limit $this->iLimit"; + $sSQL .= " WHERE ST_Contains($this->sViewboxLargeSQL, ct.centroid)"; + if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)"; + if ($this->sViewboxCentreSQL) $sSQL .= " ORDER BY ST_Distance($this->sViewboxCentreSQL, ct.centroid) ASC"; + $sSQL .= " LIMIT $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); } } else { - $sSQL = "select place_id from placex where class='".$aSearch['sClass']."' and type='".$aSearch['sType']."'"; - $sSQL .= " and st_contains($this->sViewboxSmallSQL, geometry) and linked_place_id is null"; - if ($sCountryCodesSQL) $sSQL .= " and calculated_country_code in ($sCountryCodesSQL)"; - if ($this->sViewboxCentreSQL) $sSQL .= " order by st_distance($this->sViewboxCentreSQL, centroid) asc"; - $sSQL .= " limit $this->iLimit"; + $sSQL = "SELECT place_id "; + $sSQL .= "FROM placex "; + $sSQL .= "WHERE class='".$aSearch['sClass']."' "; + $sSQL .= " AND type='".$aSearch['sType']."'"; + $sSQL .= " AND ST_Contains($this->sViewboxSmallSQL, geometry) "; + $sSQL .= " AND linked_place_id is null"; + if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)"; + if ($this->sViewboxCentreSQL) $sSQL .= " ORDER BY ST_Distance($this->sViewboxCentreSQL, centroid) ASC"; + $sSQL .= " LIMIT $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); } } - } elseif ($aSearch['fLon'] && !sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']) && !$aSearch['sClass']) { + } elseif ($aSearch['oNear'] && !sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']) && !$aSearch['sClass']) { // 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 p.place_id FROM location_postcode p "; + if (sizeof($aSearch['aAddress'])) { + $sSQL .= ", search_name s "; + $sSQL .= "WHERE s.place_id = p.parent_place_id "; + $sSQL .= "AND array_cat(s.nameaddress_vector, s.name_vector) @> ARRAY[".join($aSearch['aAddress'], ",")."] AND "; + } else { + $sSQL .= " WHERE "; + } + $sSQL .= "p.postcode = '".pg_escape_string(reset($aSearch['aName']))."'"; + if ($aSearch['sCountryCode']) { + $sSQL .= " AND p.country_code = '".$aSearch['sCountryCode']."'"; + } elseif ($sCountryCodesSQL) { + $sSQL .= " AND p.country_code in ($sCountryCodesSQL)"; + } + $sSQL .= " LIMIT $this->iLimit"; + if (CONST_Debug) var_dump($sSQL); + $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); } else { $aPlaceIDs = array(); @@ -1157,12 +1356,26 @@ class Geocode if ($aSearch['sHouseNumber'] && sizeof($aSearch['aAddress'])) { $sHouseNumberRegex = '\\\\m'.$aSearch['sHouseNumber'].'\\\\M'; $aOrder[] = ""; - $aOrder[0] = " (exists(select place_id from placex where parent_place_id = search_name.place_id"; - $aOrder[0] .= " and transliteration(housenumber) ~* E'".$sHouseNumberRegex."' limit 1) "; + $aOrder[0] = " ("; + $aOrder[0] .= " EXISTS("; + $aOrder[0] .= " SELECT place_id "; + $aOrder[0] .= " FROM placex "; + $aOrder[0] .= " WHERE parent_place_id = search_name.place_id"; + $aOrder[0] .= " AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."' "; + $aOrder[0] .= " LIMIT 1"; + $aOrder[0] .= " ) "; // also housenumbers from interpolation lines table are needed - $aOrder[0] .= " or exists(select place_id from location_property_osmline where parent_place_id = search_name.place_id"; - $aOrder[0] .= " and ".intval($aSearch['sHouseNumber']).">=startnumber and ".intval($aSearch['sHouseNumber'])."<=endnumber limit 1))"; - $aOrder[0] .= " desc"; + $aOrder[0] .= " OR EXISTS("; + $aOrder[0] .= " SELECT place_id "; + $aOrder[0] .= " FROM location_property_osmline "; + $aOrder[0] .= " WHERE parent_place_id = search_name.place_id"; + $aOrder[0] .= " AND startnumber is not NULL"; + $aOrder[0] .= " AND ".intval($aSearch['sHouseNumber']).">=startnumber "; + $aOrder[0] .= " AND ".intval($aSearch['sHouseNumber'])."<=endnumber "; + $aOrder[0] .= " LIMIT 1"; + $aOrder[0] .= " )"; + $aOrder[0] .= " )"; + $aOrder[0] .= " DESC"; } // TODO: filter out the pointless search terms (2 letter name tokens and less) @@ -1194,9 +1407,12 @@ class Geocode $aTerms[] = "address_rank <= ".$this->iMaxAddressRank; } } - if ($aSearch['fLon'] && $aSearch['fLat']) { - $aTerms[] = "ST_DWithin(centroid, ST_SetSRID(ST_Point(".$aSearch['fLon'].",".$aSearch['fLat']."),4326), ".$aSearch['fRadius'].")"; - $aOrder[] = "ST_Distance(centroid, ST_SetSRID(ST_Point(".$aSearch['fLon'].",".$aSearch['fLat']."),4326)) ASC"; + if ($aSearch['oNear']) { + $aTerms[] = $aSearch['oNear']->withinSQL('centroid'); + + $aOrder[] = $aSearch['oNear']->distanceSQL('centroid'); + } elseif ($aSearch['sPostcode']) { + $aOrder[] = "(SELECT min(ST_Distance(search_name.centroid, p.geometry)) FROM location_postcode p WHERE p.postcode = '".$aSearch['sPostcode']."')"; } if (sizeof($this->aExcludePlaceIDs)) { $aTerms[] = "place_id not in (".join(',', $this->aExcludePlaceIDs).")"; @@ -1206,36 +1422,44 @@ class Geocode } if ($bBoundingBoxSearch) $aTerms[] = "centroid && $this->sViewboxSmallSQL"; - if ($sNearPointSQL) $aOrder[] = "ST_Distance($sNearPointSQL, centroid) asc"; + if ($oNearPoint) { + $aOrder[] = $oNearPoint->distanceSQL('centroid'); + } if ($aSearch['sHouseNumber']) { $sImportanceSQL = '- abs(26 - address_rank) + 3'; } else { - $sImportanceSQL = '(case when importance = 0 OR importance IS NULL then 0.75-(search_rank::float/40) else importance end)'; + $sImportanceSQL = '(CASE WHEN importance = 0 OR importance IS NULL THEN 0.75-(search_rank::float/40) ELSE importance END)'; } - if ($this->sViewboxSmallSQL) $sImportanceSQL .= " * case when ST_Contains($this->sViewboxSmallSQL, centroid) THEN 1 ELSE 0.5 END"; - if ($this->sViewboxLargeSQL) $sImportanceSQL .= " * case when ST_Contains($this->sViewboxLargeSQL, centroid) THEN 1 ELSE 0.5 END"; + if ($this->sViewboxSmallSQL) $sImportanceSQL .= " * CASE WHEN ST_Contains($this->sViewboxSmallSQL, centroid) THEN 1 ELSE 0.5 END"; + if ($this->sViewboxLargeSQL) $sImportanceSQL .= " * CASE WHEN ST_Contains($this->sViewboxLargeSQL, centroid) THEN 1 ELSE 0.5 END"; $aOrder[] = "$sImportanceSQL DESC"; if (sizeof($aSearch['aFullNameAddress'])) { - $sExactMatchSQL = '(select count(*) from (select unnest(ARRAY['.join($aSearch['aFullNameAddress'], ",").']) INTERSECT select unnest(nameaddress_vector))s) as exactmatch'; + $sExactMatchSQL = ' ( '; + $sExactMatchSQL .= ' SELECT count(*) FROM ( '; + $sExactMatchSQL .= ' SELECT unnest(ARRAY['.join($aSearch['aFullNameAddress'], ",").']) '; + $sExactMatchSQL .= ' INTERSECT '; + $sExactMatchSQL .= ' SELECT unnest(nameaddress_vector)'; + $sExactMatchSQL .= ' ) s'; + $sExactMatchSQL .= ') as exactmatch'; $aOrder[] = 'exactmatch DESC'; } else { $sExactMatchSQL = '0::int as exactmatch'; } if (sizeof($aTerms)) { - $sSQL = "select place_id, "; + $sSQL = "SELECT place_id, "; $sSQL .= $sExactMatchSQL; - $sSQL .= " from search_name"; - $sSQL .= " where ".join(' and ', $aTerms); - $sSQL .= " order by ".join(', ', $aOrder); + $sSQL .= " FROM search_name"; + $sSQL .= " WHERE ".join(' and ', $aTerms); + $sSQL .= " ORDER BY ".join(', ', $aOrder); if ($aSearch['sHouseNumber'] || $aSearch['sClass']) { - $sSQL .= " limit 20"; + $sSQL .= " LIMIT 20"; } elseif (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']) && $aSearch['sClass']) { - $sSQL .= " limit 1"; + $sSQL .= " LIMIT 1"; } else { - $sSQL .= " limit ".$this->iLimit; + $sSQL .= " LIMIT ".$this->iLimit; } if (CONST_Debug) var_dump($sSQL); @@ -1267,40 +1491,48 @@ class Geocode // Now they are indexed, look for a house attached to a street we found $sHouseNumberRegex = '\\\\m'.$aSearch['sHouseNumber'].'\\\\M'; - $sSQL = "select place_id from placex where parent_place_id in (".$sPlaceIDs.") and transliteration(housenumber) ~* E'".$sHouseNumberRegex."'"; + $sSQL = "SELECT place_id FROM placex "; + $sSQL .= "WHERE parent_place_id in (".$sPlaceIDs.") and transliteration(housenumber) ~* E'".$sHouseNumberRegex."'"; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")"; + $sSQL .= " AND place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } - $sSQL .= " limit $this->iLimit"; + $sSQL .= " LIMIT $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); - + // if nothing found, search in the interpolation line table if (!sizeof($aPlaceIDs)) { // do we need to use transliteration and the regex for housenumbers??? //new query for lines, not housenumbers anymore + $sSQL = "SELECT distinct place_id FROM location_property_osmline"; + $sSQL .= " WHERE startnumber is not NULL and parent_place_id in (".$sPlaceIDs.") and ("; if ($searchedHousenumber%2 == 0) { //if housenumber is even, look for housenumber in streets with interpolationtype even or all - $sSQL = "select distinct place_id from location_property_osmline where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='even' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber"; + $sSQL .= "interpolationtype='even'"; } else { //look for housenumber in streets with interpolationtype odd or all - $sSQL = "select distinct place_id from location_property_osmline where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='odd' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber"; + $sSQL .= "interpolationtype='odd'"; } + $sSQL .= " or interpolationtype='all') and "; + $sSQL .= $searchedHousenumber.">=startnumber and "; + $sSQL .= $searchedHousenumber."<=endnumber"; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")"; + $sSQL .= " AND place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } //$sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); //get place IDs $aPlaceIDs = chksql($this->oDB->getCol($sSQL, 0)); } - + // If nothing found try the aux fallback table if (CONST_Use_Aux_Location_data && !sizeof($aPlaceIDs)) { - $sSQL = "select place_id from location_property_aux where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'"; + $sSQL = "SELECT place_id FROM location_property_aux "; + $sSQL .= " WHERE parent_place_id in (".$sPlaceIDs.") "; + $sSQL .= " AND housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'"; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and parent_place_id not in (".join(',', $this->aExcludePlaceIDs).")"; + $sSQL .= " AND parent_place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } //$sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); @@ -1309,17 +1541,19 @@ class Geocode //if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger) if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs)) { - //new query for lines, not housenumbers anymore + $sSQL = "SELECT distinct place_id FROM location_property_tiger"; + $sSQL .= " WHERE parent_place_id in (".$sPlaceIDs.") and ("; if ($searchedHousenumber%2 == 0) { - //if housenumber is even, look for housenumber in streets with interpolationtype even or all - $sSQL = "select distinct place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='even' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber"; + $sSQL .= "interpolationtype='even'"; } else { - //look for housenumber in streets with interpolationtype odd or all - $sSQL = "select distinct place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='odd' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber"; + $sSQL .= "interpolationtype='odd'"; } + $sSQL .= " or interpolationtype='all') and "; + $sSQL .= $searchedHousenumber.">=startnumber and "; + $sSQL .= $searchedHousenumber."<=endnumber"; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")"; + $sSQL .= " AND place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } //$sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); @@ -1343,19 +1577,26 @@ class Geocode if (!$aSearch['sOperator'] || $aSearch['sOperator'] == 'name') { // If they were searching for a named class (i.e. 'Kings Head pub') then we might have an extra match - $sSQL = "select place_id from placex where place_id in ($sPlaceIDs) and class='".$aSearch['sClass']."' and type='".$aSearch['sType']."'"; - $sSQL .= " and linked_place_id is null"; - if ($sCountryCodesSQL) $sSQL .= " and calculated_country_code in ($sCountryCodesSQL)"; - $sSQL .= " order by rank_search asc limit $this->iLimit"; + $sSQL = "SELECT place_id "; + $sSQL .= " FROM placex "; + $sSQL .= " WHERE place_id in ($sPlaceIDs) "; + $sSQL .= " AND class='".$aSearch['sClass']."' "; + $sSQL .= " AND type='".$aSearch['sType']."'"; + $sSQL .= " AND linked_place_id is null"; + if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)"; + $sSQL .= " ORDER BY rank_search ASC "; + $sSQL .= " LIMIT $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aClassPlaceIDs = chksql($this->oDB->getCol($sSQL)); } if (!$aSearch['sOperator'] || $aSearch['sOperator'] == 'near') { // & in - $sSQL = "select count(*) from pg_tables where tablename = 'place_classtype_".$aSearch['sClass']."_".$aSearch['sType']."'"; + $sClassTable = 'place_classtype_'.$aSearch['sClass'].'_'.$aSearch['sType']; + $sSQL = "SELECT count(*) FROM pg_tables "; + $sSQL .= "WHERE tablename = '$sClassTable'"; $bCacheTable = chksql($this->oDB->getOne($sSQL)); - $sSQL = "select min(rank_search) from placex where place_id in ($sPlaceIDs)"; + $sSQL = "SELECT min(rank_search) FROM placex WHERE place_id in ($sPlaceIDs)"; if (CONST_Debug) var_dump($sSQL); $this->iMaxRank = ((int)chksql($this->oDB->getOne($sSQL))); @@ -1364,7 +1605,13 @@ class Geocode $sPlaceGeom = false; if ($this->iMaxRank < 9 && $bCacheTable) { // Try and get a polygon to search in instead - $sSQL = "select geometry from placex where place_id in ($sPlaceIDs) and rank_search < $this->iMaxRank + 5 and st_geometrytype(geometry) in ('ST_Polygon','ST_MultiPolygon') order by rank_search asc limit 1"; + $sSQL = "SELECT geometry "; + $sSQL .= " FROM placex"; + $sSQL .= " WHERE place_id in ($sPlaceIDs)"; + $sSQL .= " AND rank_search < $this->iMaxRank + 5"; + $sSQL .= " AND ST_Geometrytype(geometry) in ('ST_Polygon','ST_MultiPolygon')"; + $sSQL .= " ORDER BY rank_search ASC "; + $sSQL .= " LIMIT 1"; if (CONST_Debug) var_dump($sSQL); $sPlaceGeom = chksql($this->oDB->getOne($sSQL)); } @@ -1373,7 +1620,7 @@ class Geocode $sPlaceIDs = false; } else { $this->iMaxRank += 5; - $sSQL = "select place_id from placex where place_id in ($sPlaceIDs) and rank_search < $this->iMaxRank"; + $sSQL = "SELECT place_id FROM placex WHERE place_id in ($sPlaceIDs) and rank_search < $this->iMaxRank"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = chksql($this->oDB->getCol($sSQL)); $sPlaceIDs = join(',', $aPlaceIDs); @@ -1386,11 +1633,16 @@ class Geocode $fRange = 0.05; $sOrderBySQL = ''; - if ($sNearPointSQL) $sOrderBySQL = "ST_Distance($sNearPointSQL, l.centroid)"; - elseif ($sPlaceIDs) $sOrderBySQL = "ST_Distance(l.centroid, f.geometry)"; - elseif ($sPlaceGeom) $sOrderBysSQL = "ST_Distance(st_centroid('".$sPlaceGeom."'), l.centroid)"; + if ($oNearPoint) { + $sOrderBySQL = $oNearPoint->distanceSQL('l.centroid'); + } elseif ($sPlaceIDs) { + $sOrderBySQL = "ST_Distance(l.centroid, f.geometry)"; + } elseif ($sPlaceGeom) { + $sOrderBysSQL = "ST_Distance(st_centroid('".$sPlaceGeom."'), l.centroid)"; + } - $sSQL = "select distinct l.place_id".($sOrderBySQL?','.$sOrderBySQL:'')." from place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." as l"; + $sSQL = "select distinct i.place_id".($sOrderBySQL?', i.order_term':'')." from ("; + $sSQL .= "select l.place_id".($sOrderBySQL?','.$sOrderBySQL.' as order_term':'')." from ".$sClassTable." as l"; if ($sCountryCodesSQL) $sSQL .= " join placex as lp using (place_id)"; if ($sPlaceIDs) { $sSQL .= ",placex as f where "; @@ -1403,28 +1655,37 @@ class Geocode if (sizeof($this->aExcludePlaceIDs)) { $sSQL .= " and l.place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } - if ($sCountryCodesSQL) $sSQL .= " and lp.calculated_country_code in ($sCountryCodesSQL)"; - if ($sOrderBySQL) $sSQL .= "order by ".$sOrderBySQL." asc"; + if ($sCountryCodesSQL) $sSQL .= " and lp.country_code in ($sCountryCodesSQL)"; + $sSQL .= 'limit 300) i '; + if ($sOrderBySQL) $sSQL .= "order by order_term asc"; if ($this->iOffset) $sSQL .= " offset $this->iOffset"; $sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($this->oDB->getCol($sSQL))); } else { - if (isset($aSearch['fRadius']) && $aSearch['fRadius']) $fRange = $aSearch['fRadius']; + if ($aSearch['oNear']) { + $fRange = $aSearch['oNear']->radius(); + } $sOrderBySQL = ''; - if ($sNearPointSQL) $sOrderBySQL = "ST_Distance($sNearPointSQL, l.geometry)"; - else $sOrderBySQL = "ST_Distance(l.geometry, f.geometry)"; + if ($oNearPoint) { + $sOrderBySQL = $oNearPoint->distanceSQL('l.geometry'); + } else { + $sOrderBySQL = "ST_Distance(l.geometry, f.geometry)"; + } - $sSQL = "select distinct l.place_id".($sOrderBysSQL?','.$sOrderBysSQL:'')." from placex as l,placex as f where "; - $sSQL .= "f.place_id in ( $sPlaceIDs) and ST_DWithin(l.geometry, f.centroid, $fRange) "; - $sSQL .= "and l.class='".$aSearch['sClass']."' and l.type='".$aSearch['sType']."' "; + $sSQL = "SELECT distinct l.place_id".($sOrderBysSQL?','.$sOrderBysSQL:''); + $sSQL .= " FROM placex as l, placex as f "; + $sSQL .= " WHERE f.place_id in ($sPlaceIDs) "; + $sSQL .= " AND ST_DWithin(l.geometry, f.centroid, $fRange) "; + $sSQL .= " AND l.class='".$aSearch['sClass']."' "; + $sSQL .= " AND l.type='".$aSearch['sType']."' "; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and l.place_id not in (".join(',', $this->aExcludePlaceIDs).")"; + $sSQL .= " AND l.place_id not in (".join(',', $this->aExcludePlaceIDs).")"; } - if ($sCountryCodesSQL) $sSQL .= " and l.calculated_country_code in ($sCountryCodesSQL)"; - if ($sOrderBy) $sSQL .= "order by ".$OrderBysSQL." asc"; - if ($this->iOffset) $sSQL .= " offset $this->iOffset"; + if ($sCountryCodesSQL) $sSQL .= " AND l.country_code in ($sCountryCodesSQL)"; + if ($sOrderBy) $sSQL .= "ORDER BY ".$OrderBysSQL." ASC"; + if ($this->iOffset) $sSQL .= " OFFSET $this->iOffset"; $sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($this->oDB->getCol($sSQL))); @@ -1440,6 +1701,21 @@ class Geocode var_Dump($aPlaceIDs); } + if ($aSearch['sPostcode']) { + $sSQL = 'SELECT place_id FROM placex'; + $sSQL .= ' WHERE place_id in ('.join(',', $aPlaceIDs).')'; + $sSQL .= " AND postcode = '".pg_escape_string($aSearch['sPostcode'])."'"; + if (CONST_Debug) var_dump($sSQL); + $aFilteredPlaceIDs = chksql($this->oDB->getCol($sSQL)); + if ($aFilteredPlaceIDs) { + $aPlaceIDs = $aFilteredPlaceIDs; + if (CONST_Debug) { + echo "
Place IDs after postcode filtering: "; + var_Dump($aPlaceIDs); + } + } + } + foreach ($aPlaceIDs as $iPlaceID) { // array for placeID => -1 | Tiger housenumber $aResultPlaceIDs[$iPlaceID] = $searchedHousenumber; @@ -1451,17 +1727,31 @@ class Geocode // Need to verify passes rank limits before dropping out of the loop (yuk!) // reduces the number of place ids, like a filter // rank_address is 30 for interpolated housenumbers - $sSQL = "select place_id from placex where place_id in (".join(',', array_keys($aResultPlaceIDs)).") "; - $sSQL .= "and (placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank "; - if (14 >= $this->iMinAddressRank && 14 <= $this->iMaxAddressRank) $sSQL .= " OR (extratags->'place') = 'city'"; - if ($this->aAddressRankList) $sSQL .= " OR placex.rank_address in (".join(',', $this->aAddressRankList).")"; + $sSQL = "SELECT place_id "; + $sSQL .= "FROM placex "; + $sSQL .= "WHERE place_id in (".join(',', array_keys($aResultPlaceIDs)).") "; + $sSQL .= " AND ("; + $sSQL .= " placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank "; + if (14 >= $this->iMinAddressRank && 14 <= $this->iMaxAddressRank) { + $sSQL .= " OR (extratags->'place') = 'city'"; + } + if ($this->aAddressRankList) { + $sSQL .= " OR placex.rank_address in (".join(',', $this->aAddressRankList).")"; + } if (CONST_Use_US_Tiger_Data) { - $sSQL .= ") UNION select place_id from location_property_tiger where place_id in (".join(',', array_keys($aResultPlaceIDs)).") "; - $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank "; + $sSQL .= " ) "; + $sSQL .= "UNION "; + $sSQL .= " SELECT place_id "; + $sSQL .= " FROM location_property_tiger "; + $sSQL .= " WHERE place_id in (".join(',', array_keys($aResultPlaceIDs)).") "; + $sSQL .= " AND (30 between $this->iMinAddressRank and $this->iMaxAddressRank "; if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',', $this->aAddressRankList).")"; } - $sSQL .= ") UNION select place_id from location_property_osmline where place_id in (".join(',', array_keys($aResultPlaceIDs)).")"; - $sSQL .= " and (30 between $this->iMinAddressRank and $this->iMaxAddressRank)"; + $sSQL .= ") UNION "; + $sSQL .= " SELECT place_id "; + $sSQL .= " FROM location_property_osmline "; + $sSQL .= " WHERE place_id in (".join(',', array_keys($aResultPlaceIDs)).")"; + $sSQL .= " AND startnumber is not NULL AND (30 between $this->iMinAddressRank and $this->iMaxAddressRank)"; if (CONST_Debug) var_dump($sSQL); $aFilteredPlaceIDs = chksql($this->oDB->getCol($sSQL)); $tempIDs = array(); @@ -1487,8 +1777,8 @@ class Geocode $oReverse->setZoom(18); $aLookup = $oReverse->lookup( - (float)$this->aNearPoint[0], - (float)$this->aNearPoint[1], + $oNearPoint->lat(), + $oNearPoint->lon(), false ); @@ -1516,7 +1806,7 @@ class Geocode $aClassType = getClassTypesWithImportance(); $aRecheckWords = preg_split('/\b[\s,\\-]*/u', $sQuery); foreach ($aRecheckWords as $i => $sWord) { - if (!preg_match('/\pL/', $sWord)) unset($aRecheckWords[$i]); + if (!preg_match('/[\pL\pN]/', $sWord)) unset($aRecheckWords[$i]); } if (CONST_Debug) {