From 1424e8e29bd54d3f713a38a834ff0c8ff5f463fb Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Fri, 20 Oct 2017 15:41:26 +0200 Subject: [PATCH] use Result class in reverse geocoding Also simplifies the reverse algorithm slightly by no longer having an additional distance lookup. --- lib/Geocode.php | 6 +- lib/PlaceLookup.php | 63 +++++++++-------- lib/ReverseGeocode.php | 155 ++++++++++++++++++----------------------- lib/lib.php | 20 ++++++ website/reverse.php | 10 +-- 5 files changed, 129 insertions(+), 125 deletions(-) diff --git a/lib/Geocode.php b/lib/Geocode.php index 7c43aec0..09c1ccb4 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -1197,12 +1197,12 @@ class Geocode $oReverse = new ReverseGeocode($this->oDB); $oReverse->setZoom(18); - $aLookup = $oReverse->lookupPoint($oCtx->sqlNear, false); + $oLookup = $oReverse->lookupPoint($oCtx->sqlNear, false); if (CONST_Debug) var_dump("Reverse search", $aLookup); - if ($aLookup['place_id']) { - $aResults = array($aLookup['place_id'] => new Result($aLookup['place_id'])); + if ($oLookup) { + $aResults = array($oLookup->iId => $oLookup); $aSearchResults = $this->getDetails($aResults, $oCtx); } else { $aSearchResults = array(); diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php index 4920a131..933ab00c 100644 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@ -2,6 +2,8 @@ namespace Nominatim; +require_once(CONST_BasePath.'/lib/Result.php'); + class PlaceLookup { protected $oDB; @@ -75,54 +77,58 @@ class PlaceLookup $this->fPolygonSimplificationThreshold = $f; } + private function languagePrefSql() + { + return 'ARRAY['.join(',', array_map('getDBQuoted', $this->aLangPrefOrder)).']'; + } + public function lookupOSMID($sType, $iID) { - $sSQL = "select place_id from placex where osm_type = '".pg_escape_string($sType)."' and osm_id = ".(int)$iID." order by type = 'postcode' asc"; + $sSQL = "select place_id from placex where osm_type = '".$sType."' and osm_id = ".$iID; $iPlaceID = chksql($this->oDB->getOne($sSQL)); - return $this->lookup((int)$iPlaceID); + if (!$iPlaceID) { + return null; + } + + return $this->lookup(new Result($iPlaceID)); } - public function lookup($iPlaceID, $sType = '', $fInterpolFraction = 0.0) + public function lookup($oResult) { - if (!$iPlaceID) return null; + if ($oResult === null) { + return null; + } - $sLanguagePrefArraySQL = "ARRAY[".join(',', array_map("getDBQuoted", $this->aLangPrefOrder))."]"; - $bIsTiger = CONST_Use_US_Tiger_Data && $sType == 'tiger'; - $bIsInterpolation = $sType == 'interpolation'; + $sLanguagePrefArraySQL = $this->languagePrefSql(); + $iPlaceID = $oResult->iId; - if ($bIsTiger) { - $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, housenumber, postcode,"; + if ($oResult->iTable == Result::TABLE_TIGER) { + $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level,"; + $sSQL .= $oResult->iHouseNumber.' as housenumber, postcode,'; $sSQL .= " 'us' as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,"; $sSQL .= " coalesce(null,0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, 'us' as country_code, "; - $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,"; + $sSQL .= " get_address_by_language(place_id,".$oResult->iHouseNumber.", $sLanguagePrefArraySQL) as langaddress,"; $sSQL .= " null as placename,"; $sSQL .= " null as ref,"; if ($this->bExtraTags) $sSQL .= " null as extra,"; if ($this->bNameDetails) $sSQL .= " null as names,"; - $sSQL .= " ST_X(point) as lon, ST_Y(point) as lat from (select *, ST_LineInterpolatePoint(linegeo, (housenumber-startnumber::float)/(endnumber-startnumber)::float) as point from "; - $sSQL .= " (select *, "; - $sSQL .= " CASE WHEN interpolationtype='odd' THEN floor((".$fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2+1"; - $sSQL .= " WHEN interpolationtype='even' THEN ((".$fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2"; - $sSQL .= " WHEN interpolationtype='all' THEN (".$fInterpolFraction."*(endnumber-startnumber)+startnumber)::int"; - $sSQL .= " END as housenumber"; - $sSQL .= " from location_property_tiger where place_id = ".$iPlaceID.") as blub1) as blub2"; - } elseif ($bIsInterpolation) { - $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level, housenumber, postcode,"; + $sSQL .= " ST_X(point) as lon, ST_Y(point) as lat"; + $sSQL .= " FROM (select *, ST_LineInterpolatePoint(linegeo, (".$oResult->iHouseNumber."-startnumber::float)/(endnumber-startnumber)::float) as point "; + $sSQL .= " FROM location_property_tiger where place_id = ".$iPlaceID.") as blub"; + } elseif ($oResult->iTable == Result::TABLE_OSMLINE) { + $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level,"; + $sSQL .= $oResult->iHouseNumber.' as housenumber, postcode,'; $sSQL .= " country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,"; $sSQL .= " (0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, country_code, "; - $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,"; + $sSQL .= " get_address_by_language(place_id,".$oResult->iHouseNumber.", $sLanguagePrefArraySQL) as langaddress,"; $sSQL .= " null as placename,"; $sSQL .= " null as ref,"; if ($this->bExtraTags) $sSQL .= " null as extra,"; if ($this->bNameDetails) $sSQL .= " null as names,"; - $sSQL .= " ST_X(point) as lon, ST_Y(point) as lat from (select *, ST_LineInterpolatePoint(linegeo, (housenumber-startnumber::float)/(endnumber-startnumber)::float) as point from "; - $sSQL .= " (select *, "; - $sSQL .= " CASE WHEN interpolationtype='odd' THEN floor((".$fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2+1"; - $sSQL .= " WHEN interpolationtype='even' THEN ((".$fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2"; - $sSQL .= " WHEN interpolationtype='all' THEN (".$fInterpolFraction."*(endnumber-startnumber)+startnumber)::int"; - $sSQL .= " END as housenumber"; - $sSQL .= " from location_property_osmline where place_id = ".$iPlaceID.") as blub1) as blub2"; + $sSQL .= " ST_X(point) as lon, ST_Y(point) as lat "; + $sSQL .= " FROM (select *, ST_LineInterpolatePoint(linegeo, (".$oResult->iHouseNumber."-startnumber::float)/(endnumber-startnumber)::float) as point "; + $sSQL .= " from location_property_osmline where place_id = ".$iPlaceID.") as blub"; // testcase: interpolationtype=odd, startnumber=1000, endnumber=1006, fInterpolFraction=1 => housenumber=1007 => error in st_lineinterpolatepoint // but this will never happen, because if the searched point is that close to the endnumber, the endnumber house will be directly taken from placex (in ReverseGeocode.php line 220) // and not interpolated @@ -147,8 +153,7 @@ class PlaceLookup if ($this->bAddressDetails) { // to get addressdetails for tiger data, the housenumber is needed - $iHousenumber = ($bIsTiger || $bIsInterpolation) ? $aPlace['housenumber'] : -1; - $aPlace['aAddress'] = $this->getAddressNames($aPlace['place_id'], $iHousenumber); + $aPlace['aAddress'] = $this->getAddressNames($aPlace['place_id'], $oResult->iHouseNumber); } if ($this->bExtraTags) { diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index 9b43a3e3..31ebe717 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -2,6 +2,8 @@ namespace Nominatim; +require_once(CONST_BasePath.'/lib/Result.php'); + class ReverseGeocode { protected $oDB; @@ -53,12 +55,13 @@ class ReverseGeocode protected function lookupInterpolation($sPointSQL, $fSearchDiam) { $sSQL = 'SELECT place_id, parent_place_id, 30 as rank_search,'; - $sSQL .= ' ST_LineLocatePoint(linegeo,'.$sPointSQL.') as fraction'; - $sSQL .= ' , ST_Distance(linegeo,'.$sPointSQL.') as distance'; + $sSQL .= ' ST_LineLocatePoint(linegeo,'.$sPointSQL.') as fraction,'; + $sSQL .= ' startnumber, endnumber, interpolationtype,'; + $sSQL .= ' ST_Distance(linegeo,'.$sPointSQL.') as distance'; $sSQL .= ' FROM location_property_osmline'; $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.')'; $sSQL .= ' and indexed_status = 0 and startnumber is not NULL '; - $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', linegeo) ASC limit 1'; + $sSQL .= ' ORDER BY distance ASC limit 1'; return chksql( $this->oDB->getRow($sSQL), @@ -74,24 +77,17 @@ class ReverseGeocode ); } - /* lookup() - * returns { place_id =>, type => '(osm|tiger)' } - * fails if no place was found - */ - - public function lookupPoint($sPointSQL, $bDoInterpolation = true) { $iMaxRank = $this->iMaxRank; // Find the nearest point $fSearchDiam = 0.0004; - $iPlaceID = null; + $oResult = null; + $aPlace = null; $fMaxAreaDistance = 1; - $bIsInUnitedStates = false; - $bPlaceIsTiger = false; - $bPlaceIsLine = false; - while (!$iPlaceID && $fSearchDiam < $fMaxAreaDistance) { + $bIsTigerStreet = false; + while ($oResult === null && $fSearchDiam < $fMaxAreaDistance) { $fSearchDiam = $fSearchDiam * 2; // If we have to expand the search area by a large amount then we need a larger feature @@ -106,16 +102,14 @@ class ReverseGeocode if ($fSearchDiam > 0.001 && $iMaxRank > 26) { // try with interpolations before continuing if ($bDoInterpolation) { - // no house found, try with interpolations - $aPlaceLine = $this->lookupInterpolation($sPointSQL, $fSearchDiam/2); - - if ($aPlaceLine) { - // interpolation is closer to point than placex house - $bPlaceIsLine = true; - $aPlace = $aPlaceLine; - $iPlaceID = $aPlaceLine['place_id']; - $iParentPlaceID = $aPlaceLine['parent_place_id']; // the street - $fFraction = $aPlaceLine['fraction']; + $aHouse = $this->lookupInterpolation($sPointSQL, $fSearchDiam/2); + + if ($aHouse) { + $oResult = new Result($aHouse['place_id'], Result::TABLE_OSMLINE); + $oResult->iHouseNumber = closestHouseNumber($aHouse); + + $aPlace = $aHouse; + $iParentPlaceID = $aHouse['parent_place_id']; // the street $iMaxRank = 30; break; @@ -125,7 +119,8 @@ class ReverseGeocode $iMaxRank = 26; } - $sSQL = 'select place_id,parent_place_id,rank_search,country_code'; + $sSQL = 'select place_id,parent_place_id,rank_search,country_code,'; + $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance'; $sSQL .= ' FROM placex'; $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')'; $sSQL .= ' and rank_search != 28 and rank_search >= '.$iMaxRank; @@ -134,60 +129,52 @@ class ReverseGeocode $sSQL .= ' and indexed_status = 0 '; $sSQL .= ' and (ST_GeometryType(geometry) not in (\'ST_Polygon\',\'ST_MultiPolygon\') '; $sSQL .= ' OR ST_DWithin('.$sPointSQL.', centroid, '.$fSearchDiam.'))'; - $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', geometry) ASC limit 1'; + $sSQL .= ' ORDER BY distance ASC limit 1'; if (CONST_Debug) var_dump($sSQL); $aPlace = chksql( $this->oDB->getRow($sSQL), "Could not determine closest place." ); - $iPlaceID = $aPlace['place_id']; - $iParentPlaceID = $aPlace['parent_place_id']; - $bIsInUnitedStates = ($aPlace['country_code'] == 'us'); - } - - // If a house was found make sure there isn't an interpolation line - // that is closer - if ($bDoInterpolation && !$bPlaceIsLine && $aPlace && $aPlace['rank_search'] == 30) { - // get the distance of the house to the search point - $sSQL = 'SELECT ST_distance('.$sPointSQL.', house.geometry)'; - $sSQL .= ' FROM placex as house WHERE house.place_id='.$iPlaceID; - - $fDistancePlacex = chksql( - $this->oDB->getOne($sSQL), - "Could not determine distance between searched point and placex house." - ); - - // look for an interpolation that is closer - $aPlaceLine = $this->lookupInterpolation($sPointSQL, $fDistancePlacex); - - if ($aPlaceLine && (float) $aPlaceLine['distance'] < (float) $fDistancePlacex) { - // interpolation is closer to point than placex house - $bPlaceIsLine = true; - $aPlace = $aPlaceLine; - $iPlaceID = $aPlaceLine['place_id']; - $iParentPlaceID = $aPlaceLine['parent_place_id']; // the street - $fFraction = $aPlaceLine['fraction']; + if ($aPlace) { + $oResult = new Result($aPlace['place_id']); + $iParentPlaceID = $aPlace['parent_place_id']; + if ($bDoInterpolation) { + if ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27) { + $bIsTigerStreet = ($aPlace['country_code'] == 'us'); + } elseif ($aPlace['rank_search'] == 30) { + // If a house was found, make sure there isn't an + // interpolation line that is closer. + $aHouse = $this->lookupInterpolation( + $sPointSQL, + $aPlace['distance'] + ); + if ($aHouse && $aPlace['distance'] < $aHouse['distance']) { + $oResult = new Result( + $aHouse['place_id'], + Result::TABLE_OSMLINE + ); + $oResult->iHouseNumber = closestHouseNumber($aHouse); + + $aPlace = $aHouse; + $iParentPlaceID = $aHouse['parent_place_id']; + } + } + } } } - // Only street found? If it's in the US we can check TIGER data for nearest housenumber - if (CONST_Use_US_Tiger_Data && $bDoInterpolation && $bIsInUnitedStates && $this->iMaxRank >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 )) { - $fSearchDiam = 0.001; - $sSQL = 'SELECT place_id,parent_place_id,30 as rank_search, ST_LineLocatePoint(linegeo,'.$sPointSQL.') as fraction'; - //if (CONST_Debug) { $sSQL .= ', housenumber, ST_distance('.$sPointSQL.', centroid) as distance, st_y(centroid) as lat, st_x(centroid) as lon'; } - $sSQL .= ' FROM location_property_tiger WHERE parent_place_id = '.$iPlaceID; - $sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.')'; //no centroid anymore in Tiger data, now we have lines - $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', linegeo) ASC limit 1'; - - if (CONST_Debug) { - $sSQL = preg_replace('/limit 1/', 'limit 100', $sSQL); - var_dump($sSQL); - - $aAllHouses = chksql($this->oDB->getAll($sSQL)); - foreach ($aAllHouses as $i) { - echo $i['housenumber'] . ' | ' . $i['distance'] * 1000 . ' | ' . $i['lat'] . ' | ' . $i['lon']. ' | '. "
\n"; - } - } + // Only street found? In the US we can check TIGER data for nearest housenumber + if (CONST_Use_US_Tiger_Data && $bIsTigerStreet && $this->iMaxRank >= 28) { + $fSearchDiam = $aPlace['rank_search'] > 28 ? $aPlace['distance'] : 0.001; + $sSQL = 'SELECT place_id,parent_place_id,30 as rank_search,'; + $sSQL .= 'ST_LineLocatePoint(linegeo,'.$sPointSQL.') as fraction,'; + $sSQL .= 'ST_distance('.$sPointSQL.', linegeo) as distance,'; + $sSQL .= 'startnumber,endnumber,interpolationtype'; + $sSQL .= ' FROM location_property_tiger WHERE parent_place_id = '.$oResult->iId; + $sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.')'; + $sSQL .= ' ORDER BY distance ASC limit 1'; + + if (CONST_Debug) var_dump($sSQL); $aPlaceTiger = chksql( $this->oDB->getRow($sSQL), @@ -195,21 +182,20 @@ class ReverseGeocode ); if ($aPlaceTiger) { if (CONST_Debug) var_dump('found Tiger housenumber', $aPlaceTiger); - $bPlaceIsTiger = true; $aPlace = $aPlaceTiger; - $iPlaceID = $aPlaceTiger['place_id']; - $iParentPlaceID = $aPlaceTiger['parent_place_id']; // the street - $fFraction = $aPlaceTiger['fraction']; + $oResult = new Result($aPlace['place_id'], Result::TABLE_TIGER); + $oResult->iHouseNumber = closestHouseNumber($aPlaceTiger); + $iParentPlaceID = $aPlace['parent_place_id']; $iMaxRank = 30; } } // The point we found might be too small - use the address to find what it is a child of - if ($iPlaceID && $iMaxRank < 28) { - if (($aPlace['rank_search'] > 28 || $bPlaceIsTiger || $bPlaceIsLine) && $iParentPlaceID) { + if ($oResult !== null && $iMaxRank < 28) { + if ($aPlace['rank_search'] > 28 && $iParentPlaceID) { $iPlaceID = $iParentPlaceID; - $bPlaceIsLine = false; - $bPlaceIsTiger = false; + } else { + $iPlaceID = $oResult->iId; } $sSQL = 'select address_place_id'; $sSQL .= ' FROM place_addressline'; @@ -217,14 +203,11 @@ class ReverseGeocode $sSQL .= " ORDER BY abs(cached_rank_address - $iMaxRank) asc,cached_rank_address desc,isaddress desc,distance desc"; $sSQL .= ' LIMIT 1'; $iPlaceID = chksql($this->oDB->getOne($sSQL), "Could not get parent for place."); - if (!$iPlaceID) { - $iPlaceID = $aPlace['place_id']; + if ($iPlaceID) { + $oResult = new Result($iPlaceID); } } - return array( - 'place_id' => $iPlaceID, - 'type' => $bPlaceIsTiger ? 'tiger' : ($bPlaceIsLine ? 'interpolation' : 'osm'), - 'fraction' => ($bPlaceIsTiger || $bPlaceIsLine) ? $fFraction : -1 - ); + + return $oResult; } } diff --git a/lib/lib.php b/lib/lib.php index 76775d6c..afcc3c7f 100644 --- a/lib/lib.php +++ b/lib/lib.php @@ -615,3 +615,23 @@ function createPointsAroundCenter($fLon, $fLat, $fRadius) } return $aPolyPoints; } + +function closestHouseNumber($aRow) +{ + $fHouse = $aRow['startnumber'] + + ($aRow['endnumber'] - $aRow['startnumber']) * $aRow['fraction']; + + switch ($aRow['interpolationtype']) { + case 'odd': + $iHn = (int)($fHouse/2) * 2 + 1; + break; + case 'even': + $iHn = (int)(round($fHouse/2)) * 2; + break; + default: + $iHn = (int)(round($fHouse)); + break; + } + + return max(min($aRow['endnumber'], $iHn), $aRow['startnumber']); +} diff --git a/website/reverse.php b/website/reverse.php index 026fa85b..19f1ae31 100755 --- a/website/reverse.php +++ b/website/reverse.php @@ -56,14 +56,10 @@ if ($sOsmType && $iOsmId > 0) { $oReverseGeocode = new Nominatim\ReverseGeocode($oDB); $oReverseGeocode->setZoom($iZoom !== false ? $iZoom : 18); - $aLookup = $oReverseGeocode->lookup($fLat, $fLon); - if (CONST_Debug) var_dump($aLookup); + $oLookup = $oReverseGeocode->lookup($fLat, $fLon); + if (CONST_Debug) var_dump($oLookup); - $aPlace = $oPlaceLookup->lookup( - (int)$aLookup['place_id'], - $aLookup['type'], - $aLookup['fraction'] - ); + $aPlace = $oPlaceLookup->lookup($oLookup); } elseif ($sOutputFormat != 'html') { userError("Need coordinates or OSM object to lookup."); } -- 2.45.1