<?php
+/**
+ * SPDX-License-Identifier: GPL-2.0-only
+ *
+ * This file is part of Nominatim. (https://nominatim.org)
+ *
+ * Copyright (C) 2022 by the Nominatim developer community.
+ * For a full list of authors see the git log.
+ */
namespace Nominatim;
$iLimit
);
- // Now search for housenumber, if housenumber provided. Can be zero.
- if (($this->sHouseNumber || $this->sHouseNumber === '0') && !empty($aResults)) {
- $aHnResults = $this->queryHouseNumber($oDB, $aResults);
-
- // Downgrade the rank of the street results, they are missing
- // the housenumber. Also drop POI places (rank 30) here, they
- // cannot be a parent place and therefore must not be shown
- // as a result for a search with a missing housenumber.
- foreach ($aResults as $oRes) {
- if ($oRes->iAddressRank < 28) {
- if ($oRes->iAddressRank >= 26) {
- $oRes->iResultRank++;
- } else {
- $oRes->iResultRank += 2;
- }
- $aHnResults[$oRes->iId] = $oRes;
- }
- }
-
- $aResults = $aHnResults;
- }
-
// finally get POIs if requested
if ($this->sClass && !empty($aResults)) {
$aResults = $this->queryPoiByOperator($oDB, $aResults, $iLimit);
$aTerms = array();
$aOrder = array();
- // Sort by existence of the requested house number but only if not
- // too many results are expected for the street, i.e. if the result
- // will be narrowed down by an address. Remember that with ordering
- // every single result has to be checked.
- if ($this->sHouseNumber && ($this->bRareName || !empty($this->aAddress) || $this->sPostcode)) {
- $sHouseNumberRegex = $oDB->getDBQuoted('\\\\m'.$this->sHouseNumber.'\\\\M');
-
- // Housenumbers on streets and places.
- $sChildHnr = 'SELECT * FROM placex WHERE parent_place_id = search_name.place_id';
- $sChildHnr .= ' AND housenumber ~* E'.$sHouseNumberRegex;
- // Interpolations on streets and places.
- if (preg_match('/^[0-9]+$/', $this->sHouseNumber)) {
- $sIpolHnr = 'SELECT * FROM location_property_osmline ';
- $sIpolHnr .= 'WHERE parent_place_id = search_name.place_id ';
- $sIpolHnr .= ' AND startnumber is not NULL';
- $sIpolHnr .= ' AND '.$this->sHouseNumber.'>=startnumber ';
- $sIpolHnr .= ' AND '.$this->sHouseNumber.'<=endnumber ';
- } else {
- $sIpolHnr = false;
- }
- // Housenumbers on the object iteself for unlisted places.
- $sSelfHnr = 'SELECT * FROM placex WHERE place_id = search_name.place_id';
- $sSelfHnr .= ' AND housenumber ~* E'.$sHouseNumberRegex;
-
- $sSql = '(CASE WHEN address_rank = 30 THEN EXISTS('.$sSelfHnr.') ';
- $sSql .= ' ELSE EXISTS('.$sChildHnr.') ';
- if ($sIpolHnr) {
- $sSql .= 'OR EXISTS('.$sIpolHnr.') ';
- }
- $sSql .= 'END) DESC';
-
-
- $aOrder[] = $sSql;
- }
-
if (!empty($this->aName)) {
$aTerms[] = 'name_vector @> '.$oDB->getArraySQL($this->aName);
}
$aTerms[] = 'centroid && '.$this->oContext->sqlViewboxSmall;
}
- if ($this->oContext->hasNearPoint()) {
- $aOrder[] = $this->oContext->distanceSQL('centroid');
- }
-
if ($this->sHouseNumber) {
$sImportanceSQL = '- abs(26 - address_rank) + 3';
} else {
$sExactMatchSQL = '0::int as exactmatch';
}
- if ($this->sHouseNumber || $this->sClass) {
- $iLimit = 40;
+ if (empty($aTerms)) {
+ return array();
}
- $aResults = array();
-
- if (!empty($aTerms)) {
- $sSQL = 'SELECT place_id, address_rank,'.$sExactMatchSQL;
- $sSQL .= ' FROM search_name';
- $sSQL .= ' WHERE '.join(' and ', $aTerms);
- $sSQL .= ' ORDER BY '.join(', ', $aOrder);
- $sSQL .= ' LIMIT '.$iLimit;
-
- Debug::printSQL($sSQL);
+ if ($this->hasHousenumber()) {
+ $sHouseNumberRegex = $oDB->getDBQuoted('\\\\m'.$this->sHouseNumber.'\\\\M');
- $aDBResults = $oDB->getAll($sSQL, null, 'Could not get places for search terms.');
+ // Housenumbers on streets and places.
+ $sPlacexSql = 'SELECT array_agg(place_id) FROM placex';
+ $sPlacexSql .= ' WHERE parent_place_id = sin.place_id AND sin.address_rank < 30';
+ $sPlacexSql .= ' and housenumber ~* E'.$sHouseNumberRegex;
- foreach ($aDBResults as $aResult) {
- $oResult = new Result($aResult['place_id']);
- $oResult->iExactMatches = $aResult['exactmatch'];
- $oResult->iAddressRank = $aResult['address_rank'];
- $aResults[$aResult['place_id']] = $oResult;
+ // Interpolations on streets and places.
+ $sInterpolSql = 'null';
+ $sTigerSql = 'null';
+ if (preg_match('/^[0-9]+$/', $this->sHouseNumber)) {
+ $sIpolHnr = 'WHERE parent_place_id = sin.place_id ';
+ $sIpolHnr .= ' AND startnumber is not NULL AND sin.address_rank < 30';
+ $sIpolHnr .= ' AND '.$this->sHouseNumber.' between startnumber and endnumber';
+ $sIpolHnr .= ' AND ('.$this->sHouseNumber.' - startnumber) % step = 0';
+
+ $sInterpolSql = 'SELECT array_agg(place_id) FROM location_property_osmline '.$sIpolHnr;
+ if (CONST_Use_US_Tiger_Data) {
+ $sTigerSql = 'SELECT array_agg(place_id) FROM location_property_tiger '.$sIpolHnr;
+ $sTigerSql .= " and sin.country_code = 'us'";
+ }
}
- }
- return $aResults;
- }
+ if ($this->sClass) {
+ $iLimit = 40;
+ }
- private function queryHouseNumber(&$oDB, $aRoadPlaceIDs)
- {
- $aResults = array();
- $sRoadPlaceIDs = Result::joinIdsByTableMaxRank(
- $aRoadPlaceIDs,
- Result::TABLE_PLACEX,
- 27
- );
- $sPOIPlaceIDs = Result::joinIdsByTableMinRank(
- $aRoadPlaceIDs,
- Result::TABLE_PLACEX,
- 30
- );
+ $sSelfHnr = 'SELECT * FROM placex WHERE place_id = search_name.place_id';
+ $sSelfHnr .= ' AND housenumber ~* E'.$sHouseNumberRegex;
- $aIDCondition = array();
- if ($sRoadPlaceIDs) {
- $aIDCondition[] = 'parent_place_id in ('.$sRoadPlaceIDs.')';
- }
- if ($sPOIPlaceIDs) {
- $aIDCondition[] = 'place_id in ('.$sPOIPlaceIDs.')';
- }
+ $aTerms[] = '(address_rank < 30 or exists('.$sSelfHnr.'))';
+
+
+ $sSQL = 'SELECT sin.*, ';
+ $sSQL .= '('.$sPlacexSql.') as placex_hnr, ';
+ $sSQL .= '('.$sInterpolSql.') as interpol_hnr, ';
+ $sSQL .= '('.$sTigerSql.') as tiger_hnr ';
+ $sSQL .= ' FROM (';
+ $sSQL .= ' SELECT place_id, address_rank, country_code,'.$sExactMatchSQL.',';
+ $sSQL .= ' CASE WHEN importance = 0 OR importance IS NULL';
+ $sSQL .= ' THEN 0.75001-(search_rank::float/40) ELSE importance END as importance';
+ $sSQL .= ' FROM search_name';
+ $sSQL .= ' WHERE '.join(' and ', $aTerms);
+ $sSQL .= ' ORDER BY '.join(', ', $aOrder);
+ $sSQL .= ' LIMIT 40000';
+ $sSQL .= ') as sin';
+ $sSQL .= ' ORDER BY address_rank = 30 desc, placex_hnr, interpol_hnr, tiger_hnr,';
+ $sSQL .= ' importance';
+ $sSQL .= ' LIMIT '.$iLimit;
+ } else {
+ if ($this->sClass) {
+ $iLimit = 40;
+ }
- if (empty($aIDCondition)) {
- return $aResults;
+ $sSQL = 'SELECT place_id, address_rank, '.$sExactMatchSQL;
+ $sSQL .= ' FROM search_name';
+ $sSQL .= ' WHERE '.join(' and ', $aTerms);
+ $sSQL .= ' ORDER BY '.join(', ', $aOrder);
+ $sSQL .= ' LIMIT '.$iLimit;
}
- $sHouseNumberRegex = $oDB->getDBQuoted('\\\\m'.$this->sHouseNumber.'\\\\M');
- $sSQL = 'SELECT place_id FROM placex WHERE';
- $sSQL .= ' housenumber ~* E'.$sHouseNumberRegex;
- $sSQL .= ' AND ('.join(' OR ', $aIDCondition).')';
- $sSQL .= $this->oContext->excludeSQL(' AND place_id');
-
Debug::printSQL($sSQL);
- // XXX should inherit the exactMatches from its parent
- foreach ($oDB->getCol($sSQL) as $iPlaceId) {
- $aResults[$iPlaceId] = new Result($iPlaceId);
- }
-
- $bIsIntHouseNumber= (bool) preg_match('/[0-9]+/', $this->sHouseNumber);
- $iHousenumber = intval($this->sHouseNumber);
- if ($bIsIntHouseNumber && $sRoadPlaceIDs && empty($aResults)) {
- // if nothing found, search in the interpolation line table
- $sSQL = 'SELECT distinct place_id FROM location_property_osmline';
- $sSQL .= ' WHERE startnumber is not NULL';
- $sSQL .= ' AND parent_place_id in ('.$sRoadPlaceIDs.') AND (';
- if ($iHousenumber % 2 == 0) {
- // If housenumber is even, look for housenumber in streets
- // with interpolationtype even or all.
- $sSQL .= "interpolationtype='even'";
- } else {
- // Else look for housenumber with interpolationtype odd or all.
- $sSQL .= "interpolationtype='odd'";
- }
- $sSQL .= " or interpolationtype='all') and ";
- $sSQL .= $iHousenumber.'>=startnumber and ';
- $sSQL .= $iHousenumber.'<=endnumber';
- $sSQL .= $this->oContext->excludeSQL(' AND place_id');
+ $aDBResults = $oDB->getAll($sSQL, null, 'Could not get places for search terms.');
- Debug::printSQL($sSQL);
+ $aResults = array();
- foreach ($oDB->getCol($sSQL) as $iPlaceId) {
- $oResult = new Result($iPlaceId, Result::TABLE_OSMLINE);
- $oResult->iHouseNumber = $iHousenumber;
- $aResults[$iPlaceId] = $oResult;
- }
- }
+ foreach ($aDBResults as $aResult) {
+ $oResult = new Result($aResult['place_id']);
+ $oResult->iExactMatches = $aResult['exactmatch'];
+ $oResult->iAddressRank = $aResult['address_rank'];
+
+ $bNeedResult = true;
+ if ($this->hasHousenumber() && $aResult['address_rank'] < 30) {
+ if ($aResult['placex_hnr']) {
+ foreach (explode(',', substr($aResult['placex_hnr'], 1, -1)) as $sPlaceID) {
+ $iPlaceID = intval($sPlaceID);
+ $oHnrResult = new Result($iPlaceID);
+ $oHnrResult->iExactMatches = $aResult['exactmatch'];
+ $oHnrResult->iAddressRank = 30;
+ $aResults[$iPlaceID] = $oHnrResult;
+ $bNeedResult = false;
+ }
+ }
+ if ($aResult['interpol_hnr']) {
+ foreach (explode(',', substr($aResult['interpol_hnr'], 1, -1)) as $sPlaceID) {
+ $iPlaceID = intval($sPlaceID);
+ $oHnrResult = new Result($iPlaceID, Result::TABLE_OSMLINE);
+ $oHnrResult->iExactMatches = $aResult['exactmatch'];
+ $oHnrResult->iAddressRank = 30;
+ $oHnrResult->iHouseNumber = intval($this->sHouseNumber);
+ $aResults[$iPlaceID] = $oHnrResult;
+ $bNeedResult = false;
+ }
+ }
+ if ($aResult['tiger_hnr']) {
+ foreach (explode(',', substr($aResult['tiger_hnr'], 1, -1)) as $sPlaceID) {
+ $iPlaceID = intval($sPlaceID);
+ $oHnrResult = new Result($iPlaceID, Result::TABLE_TIGER);
+ $oHnrResult->iExactMatches = $aResult['exactmatch'];
+ $oHnrResult->iAddressRank = 30;
+ $oHnrResult->iHouseNumber = intval($this->sHouseNumber);
+ $aResults[$iPlaceID] = $oHnrResult;
+ $bNeedResult = false;
+ }
+ }
- // If nothing found then search in Tiger data (location_property_tiger)
- if (CONST_Use_US_Tiger_Data && $sRoadPlaceIDs && $bIsIntHouseNumber && empty($aResults)) {
- $sSQL = 'SELECT place_id FROM location_property_tiger';
- $sSQL .= ' WHERE parent_place_id in ('.$sRoadPlaceIDs.') and (';
- if ($iHousenumber % 2 == 0) {
- $sSQL .= "interpolationtype='even'";
- } else {
- $sSQL .= "interpolationtype='odd'";
+ if ($aResult['address_rank'] < 26) {
+ $oResult->iResultRank += 2;
+ } else {
+ $oResult->iResultRank++;
+ }
}
- $sSQL .= " or interpolationtype='all') and ";
- $sSQL .= $iHousenumber.'>=startnumber and ';
- $sSQL .= $iHousenumber.'<=endnumber';
- $sSQL .= $this->oContext->excludeSQL(' AND place_id');
-
- Debug::printSQL($sSQL);
- foreach ($oDB->getCol($sSQL) as $iPlaceId) {
- $oResult = new Result($iPlaceId, Result::TABLE_TIGER);
- $oResult->iHouseNumber = $iHousenumber;
- $aResults[$iPlaceId] = $oResult;
+ if ($bNeedResult) {
+ $aResults[$aResult['place_id']] = $oResult;
}
}
projects / nominatim.git / blobdiff