X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/b03be15442502aa2e52bacff70fb983cf4397d70..badd2e46aa59c7eef187eada4ef1bbd32873b6f5:/lib/Geocode.php diff --git a/lib/Geocode.php b/lib/Geocode.php index de178431..0c3c5f73 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -431,24 +431,23 @@ 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 + $sHousenumbers = ""; + $i = 0; + $length = count($aPlaceIDs); + foreach($aPlaceIDs as $placeID => $housenumber) + { + $i++; + $sHousenumbers .= "(".$placeID.", ".$housenumber.")"; + if($i<$length) + $sHousenumbers .= ", "; + } if (CONST_Use_US_Tiger_Data) { - //query also location_property_tiger and location_property_aux //Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join) - //only Tiger housenumbers need to be interpolated, because they are saved as lines with start- and endnumber, the common osm housenumbers are usually saved as points - $sHousenumbers = ""; - $i = 0; - $length = count($aPlaceIDs); - foreach($aPlaceIDs as $placeID => $housenumber) - { - $i++; - $sHousenumbers .= "(".$placeID.", ".$housenumber.")"; - if($i<$length) - $sHousenumbers .= ", "; - } - - $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 .= " 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"; @@ -460,16 +459,17 @@ $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 .= ", 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 if (!$this->bDeDupe) $sSQL .= ", place_id "; } - // osmline, osm_type is 'I' for Interpolation Line + // 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 'I' 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 .= "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, "; @@ -477,7 +477,8 @@ 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 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 .= " (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 @@ -1330,9 +1331,11 @@ { $sHouseNumberRegex = '\\\\m'.$aSearch['sHouseNumber'].'\\\\M'; $aOrder[] = ""; - $aOrder[0] = "exists(select place_id from placex where parent_place_id = search_name.place_id and transliteration(housenumber) ~* E'".$sHouseNumberRegex."' limit 1) "; + $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) "; // 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 and ".$aSearch['sHouseNumber'].">=startnumber and ".$aSearch['sHouseNumber']."<=endnumber limit 1)"; + $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"; } @@ -1452,7 +1455,7 @@ $aRoadPlaceIDs = $aPlaceIDs; $sPlaceIDs = join(',',$aPlaceIDs); - // Now they are indexed look for a house attached to a street we found + // 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."'"; if (sizeof($this->aExcludePlaceIDs)) @@ -1478,7 +1481,7 @@ if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and parent_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); @@ -1500,7 +1503,6 @@ } //if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger) - $searchedHousenumber = intval($aSearch['sHouseNumber']); if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs)) { //new query for lines, not housenumbers anymore @@ -1529,7 +1531,7 @@ //set to -1, if no housenumbers were found $searchedHousenumber = -1; } - //else: housenumber was found, remains saved in searchedHousenumber + //else: housenumber was found, remains saved in searchedHousenumber } @@ -1680,12 +1682,13 @@ $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank "; if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',',$this->aAddressRankList).")"; } - $sSQL .= ")"; + $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)"; if (CONST_Debug) var_dump($sSQL); $aFilteredPlaceIDs = $this->oDB->getCol($sSQL); $tempIDs = array(); foreach($aFilteredPlaceIDs as $placeID) - { + { $tempIDs[$placeID] = $aResultPlaceIDs[$placeID]; //assign housenumber to placeID } $aResultPlaceIDs = $tempIDs;