<?php
require_once(CONST_BasePath.'/lib/PlaceLookup.php');
+ require_once(CONST_BasePath.'/lib/ReverseGeocode.php');
class Geocode
{
protected $aExcludePlaceIDs = array();
protected $bDeDupe = true;
- protected $bReverseInPlan = false;
+ protected $bReverseInPlan = true;
protected $iLimit = 20;
protected $iFinalLimit = 10;
$this->aLangPrefOrder = $aLangPref;
}
- function setIncludeAddressDetails($bAddressDetails = true)
- {
- $this->bIncludeAddressDetails = (bool)$bAddressDetails;
- }
-
function getIncludeAddressDetails()
{
return $this->bIncludeAddressDetails;
$this->bIncludePolygonAsPoints = $b;
}
- function getIncludePolygonAsPoints()
- {
- return $this->bIncludePolygonAsPoints;
- }
-
function setIncludePolygonAsText($b = true)
{
$this->bIncludePolygonAsText = $b;
}
- function getIncludePolygonAsText()
- {
- return $this->bIncludePolygonAsText;
- }
-
function setIncludePolygonAsGeoJSON($b = true)
{
$this->bIncludePolygonAsGeoJSON = $b;
$this->fPolygonSimplificationThreshold = $f;
}
- function setDeDupe($bDeDupe = true)
- {
- $this->bDeDupe = (bool)$bDeDupe;
- }
-
function setLimit($iLimit = 10)
{
if ($iLimit > 50) $iLimit = 50;
$this->iLimit = $this->iFinalLimit + min($this->iFinalLimit, 10);
}
- function setOffset($iOffset = 0)
- {
- $this->iOffset = $iOffset;
- }
-
- function setFallback($bFallback = true)
- {
- $this->bFallback = (bool)$bFallback;
- }
-
- function setExcludedPlaceIDs($a)
- {
- // TODO: force to int
- $this->aExcludePlaceIDs = $a;
- }
-
function getExcludedPlaceIDs()
{
return $this->aExcludePlaceIDs;
}
- function setBounded($bBoundedSearch = true)
- {
- $this->bBoundedSearch = (bool)$bBoundedSearch;
- }
-
function setViewBox($fLeft, $fBottom, $fRight, $fTop)
{
$this->aViewBox = array($fLeft, $fBottom, $fRight, $fTop);
return $this->aViewBox[0].','.$this->aViewBox[3].','.$this->aViewBox[2].','.$this->aViewBox[1];
}
- function setRoute($aRoutePoints)
- {
- $this->aRoutePoints = $aRoutePoints;
- }
-
function setFeatureType($sFeatureType)
{
switch($sFeatureType)
function setRankRange($iMin, $iMax)
{
- $this->iMinAddressRank = (int)$iMin;
- $this->iMaxAddressRank = (int)$iMax;
+ $this->iMinAddressRank = $iMin;
+ $this->iMaxAddressRank = $iMax;
}
function setNearPoint($aNearPoint, $fRadiusDeg = 0.1)
$this->aNearPoint = array((float)$aNearPoint[0], (float)$aNearPoint[1], (float)$fRadiusDeg);
}
- function setCountryCodesList($aCountryCodes)
- {
- $this->aCountryCodes = $aCountryCodes;
- }
-
function setQuery($sQueryString)
{
$this->sQuery = $sQueryString;
function loadParamArray($aParams)
{
if (isset($aParams['addressdetails'])) $this->bIncludeAddressDetails = (bool)$aParams['addressdetails'];
- if ((float) CONST_Postgresql_Version > 9.2)
- {
- if (isset($aParams['extratags'])) $this->bIncludeExtraTags = (bool)$aParams['extratags'];
- if (isset($aParams['namedetails'])) $this->bIncludeNameDetails = (bool)$aParams['namedetails'];
- }
+ if (isset($aParams['extratags'])) $this->bIncludeExtraTags = (bool)$aParams['extratags'];
+ if (isset($aParams['namedetails'])) $this->bIncludeNameDetails = (bool)$aParams['namedetails'];
+
if (isset($aParams['bounded'])) $this->bBoundedSearch = (bool)$aParams['bounded'];
if (isset($aParams['dedupe'])) $this->bDeDupe = (bool)$aParams['dedupe'];
$this->loadStructuredAddressElement($sPostalCode, 'postalcode' , 5, 11, array(5, 11));
$this->loadStructuredAddressElement($sCountry, 'country', 4, 4, false);
- if (sizeof($this->aStructuredQuery) > 0)
+ if (sizeof($this->aStructuredQuery) > 0)
{
$this->sQuery = join(', ', $this->aStructuredQuery);
if ($this->iMaxAddressRank < 30)
function getDetails($aPlaceIDs)
{
+ //$aPlaceIDs is an array with key: placeID and value: tiger-housenumber, if found, else -1
if (sizeof($aPlaceIDs) == 0) return array();
$sLanguagePrefArraySQL = "ARRAY[".join(',',array_map("getDBQuoted",$this->aLangPrefOrder))."]";
// Get the details for display (is this a redundant extra step?)
- $sPlaceIDs = join(',',$aPlaceIDs);
+ $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, $sLanguagePrefArraySQL) as langaddress,";
+ $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 (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)
+ {
+ //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
+ 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 '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, $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(location_property_tiger.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_tiger 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 .= " union ";
- $sSQL .= "select 'L' 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, $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, $sLanguagePrefArraySQL) ";
- */
+ $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
+ 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 .= " order by importance desc";
if (CONST_Debug) { echo "<hr>"; var_dump($sSQL); }
- $aSearchResults = $this->oDB->getAll($sSQL);
-
- if (PEAR::IsError($aSearchResults))
- {
- failInternalError("Could not get details for place.", $sSQL, $aSearchResults);
- }
+ $aSearchResults = chksql($this->oDB->getAll($sSQL),
+ "Could not get details for place.");
return $aSearchResults;
}
addressimportance: cumulated importance of address elements
extra_place: type of place (for admin boundaries, if there is a place tag)
aBoundingBox: bounding Box
- label: short description of the object class/type (English only)
+ label: short description of the object class/type (English only)
name: full name (currently the same as langaddress)
foundorder: secondary ordering for places with same importance
*/
$sViewboxCentreSQL .= ")'::geometry,4326)";
$sSQL = "select st_buffer(".$sViewboxCentreSQL.",".(float)($_GET['routewidth']/69).")";
- $this->sViewboxSmallSQL = $this->oDB->getOne($sSQL);
- if (PEAR::isError($this->sViewboxSmallSQL))
- {
- failInternalError("Could not get small viewbox.", $sSQL, $this->sViewboxSmallSQL);
- }
+ $this->sViewboxSmallSQL = chksql($this->oDB->getOne($sSQL),
+ "Could not get small viewbox.");
$this->sViewboxSmallSQL = "'".$this->sViewboxSmallSQL."'::geometry";
$sSQL = "select st_buffer(".$sViewboxCentreSQL.",".(float)($_GET['routewidth']/30).")";
- $this->sViewboxLargeSQL = $this->oDB->getOne($sSQL);
- if (PEAR::isError($this->sViewboxLargeSQL))
- {
- failInternalError("Could not get large viewbox.", $sSQL, $this->sViewboxLargeSQL);
- }
+ $this->sViewboxLargeSQL = chksql($this->oDB->getOne($sSQL),
+ "Could not get large viewbox.");
$this->sViewboxLargeSQL = "'".$this->sViewboxLargeSQL."'::geometry";
$bBoundingBoxSearch = $this->bBoundedSearch;
}
// Do we have anything that looks like a lat/lon pair?
- if ( $aLooksLike = looksLikeLatLonPair($sQuery) ){
+ if ( $aLooksLike = looksLikeLatLonPair($sQuery) )
+ {
$this->setNearPoint(array($aLooksLike['lat'], $aLooksLike['lon']));
- $sQuery = $aLooksLike['query'];
+ $sQuery = $aLooksLike['query'];
}
$aSearchResults = array();
{
// Start with a blank search
$aSearches = array(
- array('iSearchRank' => 0,
- 'iNamePhrase' => -1,
- 'sCountryCode' => false,
- 'aName' => array(),
- 'aAddress' => array(),
+ array('iSearchRank' => 0,
+ 'iNamePhrase' => -1,
+ 'sCountryCode' => false,
+ 'aName' => array(),
+ 'aAddress' => array(),
'aFullNameAddress' => array(),
- 'aNameNonSearch' => array(),
+ 'aNameNonSearch' => array(),
'aAddressNonSearch' => array(),
- 'sOperator' => '',
- 'aFeatureName' => array(),
- 'sClass' => '',
- 'sType' => '',
- 'sHouseNumber' => '',
- 'fLat' => '',
- 'fLon' => '',
+ 'sOperator' => '',
+ 'aFeatureName' => array(),
+ 'sClass' => '',
+ 'sType' => '',
+ 'sHouseNumber' => '',
+ 'fLat' => '',
+ 'fLon' => '',
'fRadius' => ''
)
);
foreach($aSpecialTermsRaw as $aSpecialTerm)
{
$sQuery = str_replace($aSpecialTerm[0], ' ', $sQuery);
- $sToken = $this->oDB->getOne("select make_standard_name('".$aSpecialTerm[1]."') as string");
+ $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';
if (CONST_Debug) var_Dump($sSQL);
- $aSearchWords = $this->oDB->getAll($sSQL);
+ $aSearchWords = chksql($this->oDB->getAll($sSQL));
$aNewSearches = array();
foreach($aSearches as $aSearch)
{
$aTokens = array();
foreach($aPhrases as $iPhrase => $sPhrase)
{
- $aPhrase = $this->oDB->getRow("select make_standard_name('".pg_escape_string($sPhrase)."') as string");
- if (PEAR::isError($aPhrase))
- {
- userError("Illegal query string (not an UTF-8 string): ".$sPhrase);
- if (CONST_Debug) var_dump($aPhrase);
- exit;
- }
+ $aPhrase = chksql($this->oDB->getRow("select make_standard_name('".pg_escape_string($sPhrase)."') as string"),
+ "Cannot nomralize query string (is it an UTF-8 string?)");
if (trim($aPhrase['string']))
{
$aPhrases[$iPhrase] = $aPhrase;
if (CONST_Debug) var_Dump($sSQL);
$aValidTokens = array();
- if (sizeof($aTokens)) $aDatabaseWords = $this->oDB->getAll($sSQL);
- else $aDatabaseWords = array();
- if (PEAR::IsError($aDatabaseWords))
+ if (sizeof($aTokens))
+ {
+ $aDatabaseWords = chksql($this->oDB->getAll($sSQL),
+ "Could not get word tokens.");
+ }
+ else
{
- failInternalError("Could not get word tokens.", $sSQL, $aDatabaseWords);
+ $aDatabaseWords = array();
}
$aPossibleMainWordIDs = array();
$aWordFrequencyScores = array();
// TODO: suggestions
// Start the search process
+ // array with: placeid => -1 | tiger-housenumber
$aResultPlaceIDs = array();
$aGroupedSearches = $this->getGroupedSearches($aSearches, $aPhraseTypes, $aPhrases, $aValidTokens, $aWordFrequencyScores, $bStructuredPhrases);
foreach($aSearches as $aSearch)
{
$iQueryLoop++;
+ $searchedHousenumber = -1;
if (CONST_Debug) { echo "<hr><b>Search Loop, group $iGroupLoop, loop $iQueryLoop</b>"; }
if (CONST_Debug) _debugDumpGroupedSearches(array($iGroupedRank => array($aSearch)), $aValidTokens);
$sSQL .= " and _st_intersects($this->sViewboxSmallSQL, geometry)";
$sSQL .= " order by st_area(geometry) desc limit 1";
if (CONST_Debug) var_dump($sSQL);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
}
else
{
if (!$bBoundingBoxSearch && !$aSearch['fLon']) continue;
if (!$aSearch['sClass']) continue;
$sSQL = "select count(*) from pg_tables where tablename = 'place_classtype_".$aSearch['sClass']."_".$aSearch['sType']."'";
- if ($this->oDB->getOne($sSQL))
+ 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)";
if ($sViewboxCentreSQL) $sSQL .= " order by st_distance($sViewboxCentreSQL, ct.centroid) asc";
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
// If excluded place IDs are given, it is fair to assume that
// there have been results in the small box, so no further
if ($sViewboxCentreSQL) $sSQL .= " order by st_distance($sViewboxCentreSQL, ct.centroid) asc";
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
}
}
else
if ($sViewboxCentreSQL) $sSQL .= " order by st_distance($sViewboxCentreSQL, centroid) asc";
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
}
}
}
if ($aSearch['sHouseNumber'] && sizeof($aSearch['aAddress']))
{
$sHouseNumberRegex = '\\\\m'.$aSearch['sHouseNumber'].'\\\\M';
- $aOrder[] = "exists(select place_id from placex where parent_place_id = search_name.place_id and transliteration(housenumber) ~* E'".$sHouseNumberRegex."' limit 1) desc";
+ $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) ";
+ // 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";
}
// TODO: filter out the pointless search terms (2 letter name tokens and less)
// they might be right - but they are just too darned expensive to run
if (sizeof($aSearch['aName'])) $aTerms[] = "name_vector @> ARRAY[".join($aSearch['aName'],",")."]";
- if (sizeof($aSearch['aNameNonSearch'])) $aTerms[] = "array_cat(name_vector,ARRAY[]::integer[]) @> ARRAY[".join($aSearch['aNameNonSearch'],",")."]";
+ //if (sizeof($aSearch['aNameNonSearch'])) $aTerms[] = "array_cat(name_vector,ARRAY[]::integer[]) @> ARRAY[".join($aSearch['aNameNonSearch'],",")."]";
if (sizeof($aSearch['aAddress']) && $aSearch['aName'] != $aSearch['aAddress'])
{
// For infrequent name terms disable index usage for address
sizeof($aSearch['aName']) == 1 &&
$aWordFrequencyScores[$aSearch['aName'][reset($aSearch['aName'])]] < CONST_Search_NameOnlySearchFrequencyThreshold)
{
- $aTerms[] = "array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[".join(array_merge($aSearch['aAddress'],$aSearch['aAddressNonSearch']),",")."]";
+ //$aTerms[] = "array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[".join(array_merge($aSearch['aAddress'],$aSearch['aAddressNonSearch']),",")."]";
+ $aTerms[] = "array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[".join($aSearch['aAddress'],",")."]";
}
else
{
$aTerms[] = "nameaddress_vector @> ARRAY[".join($aSearch['aAddress'],",")."]";
- if (sizeof($aSearch['aAddressNonSearch'])) $aTerms[] = "array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[".join($aSearch['aAddressNonSearch'],",")."]";
+ //if (sizeof($aSearch['aAddressNonSearch'])) $aTerms[] = "array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[".join($aSearch['aAddressNonSearch'],",")."]";
}
}
if ($aSearch['sCountryCode']) $aTerms[] = "country_code = '".pg_escape_string($aSearch['sCountryCode'])."'";
$sSQL .= " limit ".$this->iLimit;
if (CONST_Debug) { var_dump($sSQL); }
- $aViewBoxPlaceIDs = $this->oDB->getAll($sSQL);
- if (PEAR::IsError($aViewBoxPlaceIDs))
- {
- failInternalError("Could not get places for search terms.", $sSQL, $aViewBoxPlaceIDs);
- }
+ $aViewBoxPlaceIDs = chksql($this->oDB->getAll($sSQL),
+ "Could not get places for search terms.");
//var_dump($aViewBoxPlaceIDs);
// Did we have an viewbox matches?
$aPlaceIDs = array();
//var_Dump($aPlaceIDs);
//exit;
+ //now search for housenumber, if housenumber provided
if ($aSearch['sHouseNumber'] && sizeof($aPlaceIDs))
{
+ $searchedHousenumber = intval($aSearch['sHouseNumber']);
$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))
}
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aPlaceIDs = $this->oDB->getCol($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
+ 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";
+ }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";
+ }
- // If not try the aux fallback table
- /*
- if (!sizeof($aPlaceIDs))
+ if (sizeof($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'])."'";
if (sizeof($this->aExcludePlaceIDs))
{
- $sSQL .= " and 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);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
}
+ */
- if (!sizeof($aPlaceIDs))
+ //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))
{
- $sSQL = "select place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'";
+ //new query for lines, not housenumbers anymore
+ 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";
+ }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";
+ }
+
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);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ //get place IDs
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL, 0));
}
- // Fallback to the road
+ // Fallback to the road (if no housenumber was found)
if (!sizeof($aPlaceIDs) && preg_match('/[0-9]+/', $aSearch['sHouseNumber']))
{
$aPlaceIDs = $aRoadPlaceIDs;
+ //set to -1, if no housenumbers were found
+ $searchedHousenumber = -1;
}
-
+ //else: housenumber was found, remains saved in searchedHousenumber
}
+
if ($aSearch['sClass'] && sizeof($aPlaceIDs))
{
- $sPlaceIDs = join(',',$aPlaceIDs);
+ $sPlaceIDs = join(',', $aPlaceIDs);
$aClassPlaceIDs = array();
if (!$aSearch['sOperator'] || $aSearch['sOperator'] == 'name')
if ($sCountryCodesSQL) $sSQL .= " and calculated_country_code in ($sCountryCodesSQL)";
$sSQL .= " order by rank_search asc limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aClassPlaceIDs = $this->oDB->getCol($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']."'";
- $bCacheTable = $this->oDB->getOne($sSQL);
+ $bCacheTable = chksql($this->oDB->getOne($sSQL));
$sSQL = "select min(rank_search) from placex where place_id in ($sPlaceIDs)";
if (CONST_Debug) var_dump($sSQL);
- $this->iMaxRank = ((int)$this->oDB->getOne($sSQL));
+ $this->iMaxRank = ((int)chksql($this->oDB->getOne($sSQL)));
// For state / country level searches the normal radius search doesn't work very well
$sPlaceGeom = false;
// 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";
if (CONST_Debug) var_dump($sSQL);
- $sPlaceGeom = $this->oDB->getOne($sSQL);
+ $sPlaceGeom = chksql($this->oDB->getOne($sSQL));
}
if ($sPlaceGeom)
$this->iMaxRank += 5;
$sSQL = "select place_id from placex where place_id in ($sPlaceIDs) and rank_search < $this->iMaxRank";
if (CONST_Debug) var_dump($sSQL);
- $aPlaceIDs = $this->oDB->getCol($sSQL);
+ $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
$sPlaceIDs = join(',',$aPlaceIDs);
}
if ($this->iOffset) $sSQL .= " offset $this->iOffset";
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aClassPlaceIDs = array_merge($aClassPlaceIDs, $this->oDB->getCol($sSQL));
+ $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($this->oDB->getCol($sSQL)));
}
else
{
if ($this->iOffset) $sSQL .= " offset $this->iOffset";
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
- $aClassPlaceIDs = array_merge($aClassPlaceIDs, $this->oDB->getCol($sSQL));
+ $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($this->oDB->getCol($sSQL)));
}
}
}
}
- if (PEAR::IsError($aPlaceIDs))
- {
- failInternalError("Could not get place IDs from tokens." ,$sSQL, $aPlaceIDs);
- }
-
if (CONST_Debug) { echo "<br><b>Place IDs:</b> "; var_Dump($aPlaceIDs); }
foreach($aPlaceIDs as $iPlaceID)
{
- $aResultPlaceIDs[$iPlaceID] = $iPlaceID;
+ // array for placeID => -1 | Tiger housenumber
+ $aResultPlaceIDs[$iPlaceID] = $searchedHousenumber;
}
if ($iQueryLoop > 20) break;
}
if (isset($aResultPlaceIDs) && sizeof($aResultPlaceIDs) && ($this->iMinAddressRank != 0 || $this->iMaxAddressRank != 30))
{
// Need to verify passes rank limits before dropping out of the loop (yuk!)
- $sSQL = "select place_id from placex where place_id in (".join(',',$aResultPlaceIDs).") ";
+ // 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 .= ") UNION select place_id from location_property_tiger where place_id in (".join(',',$aResultPlaceIDs).") ";
- $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank ";
- if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',',$this->aAddressRankList).")";
- $sSQL .= ")";
+ 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 ";
+ 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)";
if (CONST_Debug) var_dump($sSQL);
- $aResultPlaceIDs = $this->oDB->getCol($sSQL);
+ $aFilteredPlaceIDs = chksql($this->oDB->getCol($sSQL));
+ $tempIDs = array();
+ foreach($aFilteredPlaceIDs as $placeID)
+ {
+ $tempIDs[$placeID] = $aResultPlaceIDs[$placeID]; //assign housenumber to placeID
+ }
+ $aResultPlaceIDs = $tempIDs;
}
//exit;
else
{
// Just interpret as a reverse geocode
- $iPlaceID = geocodeReverse((float)$this->aNearPoint[0], (float)$this->aNearPoint[1]);
- if ($iPlaceID)
- $aSearchResults = $this->getDetails(array($iPlaceID));
+ $oReverse = new ReverseGeocode($this->oDB);
+ $oReverse->setZoom(18);
+
+ $aLookup = $oReverse->lookup((float)$this->aNearPoint[0],
+ (float)$this->aNearPoint[1],
+ false);
+
+ if (CONST_Debug) var_dump("Reverse search", $aLookup);
+
+ if ($aLookup['place_id'])
+ $aSearchResults = $this->getDetails(array($aLookup['place_id'] => -1));
else
$aSearchResults = array();
}
if (CONST_Debug) { echo '<i>Recheck words:<\i>'; var_dump($aRecheckWords); }
+ $oPlaceLookup = new PlaceLookup($this->oDB);
+ $oPlaceLookup->setIncludePolygonAsPoints($this->bIncludePolygonAsPoints);
+ $oPlaceLookup->setIncludePolygonAsText($this->bIncludePolygonAsText);
+ $oPlaceLookup->setIncludePolygonAsGeoJSON($this->bIncludePolygonAsGeoJSON);
+ $oPlaceLookup->setIncludePolygonAsKML($this->bIncludePolygonAsKML);
+ $oPlaceLookup->setIncludePolygonAsSVG($this->bIncludePolygonAsSVG);
+ $oPlaceLookup->setPolygonSimplificationThreshold($this->fPolygonSimplificationThreshold);
+
foreach($aSearchResults as $iResNum => $aResult)
{
// Default
$fDiameter = getResultDiameter($aResult);
- $oPlaceLookup = new PlaceLookup($this->oDB);
- $oPlaceLookup->setIncludePolygonAsPoints($this->bIncludePolygonAsPoints);
- $oPlaceLookup->setIncludePolygonAsText($this->bIncludePolygonAsText);
- $oPlaceLookup->setIncludePolygonAsGeoJSON($this->bIncludePolygonAsGeoJSON);
- $oPlaceLookup->setIncludePolygonAsKML($this->bIncludePolygonAsKML);
- $oPlaceLookup->setIncludePolygonAsSVG($this->bIncludePolygonAsSVG);
- $oPlaceLookup->setPolygonSimplificationThreshold($this->fPolygonSimplificationThreshold);
-
$aOutlineResult = $oPlaceLookup->getOutlines($aResult['place_id'], $aResult['lon'], $aResult['lat'], $fDiameter/2);
if ($aOutlineResult)
{
{
$aResult['label'] = $aClassType[$aResult['class'].':'.$aResult['type']]['label'];
}
-
+ // if tag '&addressdetails=1' is set in query
if ($this->bIncludeAddressDetails)
{
- $aResult['address'] = getAddressDetails($this->oDB, $sLanguagePrefArraySQL, $aResult['place_id'], $aResult['country_code']);
+ // getAddressDetails() is defined in lib.php and uses the SQL function get_addressdata in functions.sql
+ $aResult['address'] = getAddressDetails($this->oDB, $sLanguagePrefArraySQL, $aResult['place_id'], $aResult['country_code'], $aResultPlaceIDs[$aResult['place_id']]);
if ($aResult['extra_place'] == 'city' && !isset($aResult['address']['city']))
{
$aResult['address'] = array_merge(array('city' => array_shift(array_values($aResult['address']))), $aResult['address']);
}
}
-
if ($this->bIncludeExtraTags)
{
if ($aResult['extra'])
<?php
require_once('init.php');
+ require_once('website.php');
if (CONST_NoAccessControl)
{
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: OPTIONS,GET");
+ header("Access-Control-Max-Age: 8640000");
if (!empty($_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS']))
{
header("Access-Control-Allow-Headers: ".$_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS']);
if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') exit;
header('Content-type: text/html; charset=utf-8');
-
<?php
- function failInternalError($sError, $sSQL = false, $vDumpVar = false)
- {
- header('HTTP/1.0 500 Internal Server Error');
- header('Content-type: text/html; charset=utf-8');
- echo "<html><body><h1>Internal Server Error</h1>";
- echo '<p>Nominatim has encountered an internal error while processing your request. This is most likely because of a bug in the software.</p>';
- echo "<p><b>Details:</b> ".$sError,"</p>";
- echo '<p>Feel free to report the bug in the <a href="http://trac.openstreetmap.org">OSM bug database</a>. Please include the error message above and the URL you used.</p>';
- if (CONST_Debug)
- {
- echo "<hr><h2>Debugging Information</h2><br>";
- if ($sSQL)
- {
- echo "<h3>SQL query</h3><code>".$sSQL."</code>";
- }
- if ($vDumpVar)
- {
- echo "<h3>Result</h3> <code>";
- var_dump($vDumpVar);
- echo "</code>";
- }
- }
- echo "\n</body></html>\n";
- exit;
- }
-
-
- function userError($sError)
- {
- header('HTTP/1.0 400 Bad Request');
- header('Content-type: text/html; charset=utf-8');
- echo "<html><body><h1>Bad Request</h1>";
- echo '<p>Nominatim has encountered an error with your request.</p>';
- echo "<p><b>Details:</b> ".$sError,"</p>";
- echo '<p>If you feel this error is incorrect feel free to report the bug in the <a href="http://trac.openstreetmap.org">OSM bug database</a>. Please include the error message above and the URL you used.</p>';
- echo "\n</body></html>\n";
- exit;
- }
-
- function getParamBool($name, $default=false)
- {
- if (!isset($_GET[$name])) return $default;
-
- return (bool) $_GET[$name];
- }
-
function fail($sError, $sUserError = false)
{
if (!$sUserError) $sUserError = $sError;
}
- function getBlockingProcesses()
- {
- $sStats = file_get_contents('/proc/stat');
- if (preg_match('/procs_blocked ([0-9]+)/i', $sStats, $aMatches))
- {
- return (int)$aMatches[1];
- }
- return 0;
- }
-
-
- function getLoadAverage()
- {
- $sLoadAverage = file_get_contents('/proc/loadavg');
- $aLoadAverage = explode(' ',$sLoadAverage);
- return (float)$aLoadAverage[0];
- }
-
-
function getProcessorCount()
{
$sCPU = file_get_contents('/proc/cpuinfo');
- preg_match_all('#processor : [0-9]+#', $sCPU, $aMatches);
+ preg_match_all('#processor\s+: [0-9]+#', $sCPU, $aMatches);
return sizeof($aMatches[0]);
}
{
if ($a['importance'] != $b['importance'])
return ($a['importance'] > $b['importance']?-1:1);
- /*
- if ($a['aPointPolygon']['numfeatures'] != $b['aPointPolygon']['numfeatures'])
- return ($a['aPointPolygon']['numfeatures'] > $b['aPointPolygon']['numfeatures']?-1:1);
- if ($a['aPointPolygon']['area'] != $b['aPointPolygon']['area'])
- return ($a['aPointPolygon']['area'] > $b['aPointPolygon']['area']?-1:1);
- // if ($a['levenshtein'] != $b['levenshtein'])
- // return ($a['levenshtein'] < $b['levenshtein']?-1:1);
- if ($a['rank_search'] != $b['rank_search'])
- return ($a['rank_search'] < $b['rank_search']?-1:1);
- */
+
return ($a['foundorder'] < $b['foundorder']?-1:1);
}
{
$aResult = array(array(join(' ',$aWords)));
$sFirstToken = '';
- if ($iDepth < 8) {
+ if ($iDepth < 7) {
while(sizeof($aWords) > 1)
{
$sWord = array_shift($aWords);
{
$aTokens[' '.$sWord] = ' '.$sWord;
$aTokens[$sWord] = $sWord;
- //if (!strpos($sWord,' ')) $aTokens[$sWord] = $sWord;
}
}
return $aTokens;
GB Postcode functions
*/
- function gbPostcodeAlphaDifference($s1, $s2)
- {
- $aValues = array(
- 'A'=>0,
- 'B'=>1,
- 'D'=>2,
- 'E'=>3,
- 'F'=>4,
- 'G'=>5,
- 'H'=>6,
- 'J'=>7,
- 'L'=>8,
- 'N'=>9,
- 'O'=>10,
- 'P'=>11,
- 'Q'=>12,
- 'R'=>13,
- 'S'=>14,
- 'T'=>15,
- 'U'=>16,
- 'W'=>17,
- 'X'=>18,
- 'Y'=>19,
- 'Z'=>20);
- return abs(($aValues[$s1[0]]*21+$aValues[$s1[1]]) - ($aValues[$s2[0]]*21+$aValues[$s2[1]]));
- }
-
-
function gbPostcodeCalculate($sPostcode, $sPostcodeSector, $sPostcodeEnd, &$oDB)
{
// Try an exact match on the gb_postcode table
$sSQL = 'select \'AA\', ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from gb_postcode where postcode = \''.$sPostcode.'\'';
- $aNearPostcodes = $oDB->getAll($sSQL);
- if (PEAR::IsError($aNearPostcodes))
- {
- var_dump($sSQL, $aNearPostcodes);
- exit;
- }
+ $aNearPostcodes = chksql($oDB->getAll($sSQL));
if (sizeof($aNearPostcodes))
{
}
- function usPostcodeCalculate($sPostcode, &$oDB)
- {
- $iZipcode = (int)$sPostcode;
-
- // Try an exact match on the us_zippostcode table
- $sSQL = 'select zipcode, ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from us_zipcode where zipcode = '.$iZipcode;
- $aNearPostcodes = $oDB->getAll($sSQL);
- if (PEAR::IsError($aNearPostcodes))
- {
- var_dump($sSQL, $aNearPostcodes);
- exit;
- }
-
- if (!sizeof($aNearPostcodes))
- {
- $sSQL = 'select zipcode,ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from us_zipcode where zipcode between '.($iZipcode-100).' and '.($iZipcode+100).' order by abs(zipcode - '.$iZipcode.') asc limit 5';
- $aNearPostcodes = $oDB->getAll($sSQL);
- if (PEAR::IsError($aNearPostcodes))
- {
- var_dump($sSQL, $aNearPostcodes);
- exit;
- }
- }
-
- if (!sizeof($aNearPostcodes))
- {
- return false;
- }
-
- $fTotalLat = 0;
- $fTotalLon = 0;
- $fTotalFac = 0;
- foreach($aNearPostcodes as $aPostcode)
- {
- $iDiff = abs($aPostcode['zipcode'] - $iZipcode) + 1;
- if ($iDiff == 0)
- $fFac = 1;
- else
- $fFac = 1/($iDiff*$iDiff);
-
- $fTotalFac += $fFac;
- $fTotalLat += $aPostcode['lat'] * $fFac;
- $fTotalLon += $aPostcode['lon'] * $fFac;
- }
- if ($fTotalFac)
- {
- $fLat = $fTotalLat / $fTotalFac;
- $fLon = $fTotalLon / $fTotalFac;
- return array(array('lat' => $fLat, 'lon' => $fLon, 'radius' => 0.2));
- }
- return false;
-
- /*
- $fTotalFac is a surprisingly good indicator of accuracy
- $iZoom = 18 + round(log($fTotalFac,32));
- $iZoom = max(13,min(18,$iZoom));
- */
- }
-
-
function getClassTypes()
{
return array(
function javascript_renderData($xVal, $iOptions = 0)
{
- header("Access-Control-Allow-Origin: *");
if (defined('PHP_VERSION_ID') && PHP_VERSION_ID > 50400)
$iOptions |= JSON_UNESCAPED_UNICODE;
$jsonout = json_encode($xVal, $iOptions);
}
- function getAddressDetails(&$oDB, $sLanguagePrefArraySQL, $iPlaceID, $sCountryCode = false, $bRaw = false)
+ function getAddressDetails(&$oDB, $sLanguagePrefArraySQL, $iPlaceID, $sCountryCode = false, $housenumber = -1, $bRaw = false)
{
- $sSQL = "select *,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata($iPlaceID)";
+ $sSQL = "select *,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata($iPlaceID, $housenumber)";
if (!$bRaw) $sSQL .= " WHERE isaddress OR type = 'country_code'";
$sSQL .= " order by rank_address desc,isaddress desc";
- $aAddressLines = $oDB->getAll($sSQL);
- if (PEAR::IsError($aAddressLines))
- {
- var_dump($aAddressLines);
- exit;
- }
+ $aAddressLines = chksql($oDB->getAll($sSQL));
if ($bRaw) return $aAddressLines;
//echo "<pre>";
//var_dump($aAddressLines);
}
- function geocodeReverse($fLat, $fLon, $iZoom=18)
- {
- $oDB =& getDB();
-
- $sPointSQL = "ST_SetSRID(ST_Point($fLon,$fLat),4326)";
-
- // Zoom to rank, this could probably be calculated but a lookup gives fine control
- $aZoomRank = array(
- 0 => 2, // Continent / Sea
- 1 => 2,
- 2 => 2,
- 3 => 4, // Country
- 4 => 4,
- 5 => 8, // State
- 6 => 10, // Region
- 7 => 10,
- 8 => 12, // County
- 9 => 12,
- 10 => 17, // City
- 11 => 17,
- 12 => 18, // Town / Village
- 13 => 18,
- 14 => 22, // Suburb
- 15 => 22,
- 16 => 26, // Street, TODO: major street?
- 17 => 26,
- 18 => 30, // or >, Building
- 19 => 30, // or >, Building
- );
- $iMaxRank = isset($aZoomRank[$iZoom])?$aZoomRank[$iZoom]:28;
-
- // Find the nearest point
- $fSearchDiam = 0.0001;
- $iPlaceID = null;
- $aArea = false;
- $fMaxAreaDistance = 1;
- while(!$iPlaceID && $fSearchDiam < $fMaxAreaDistance)
- {
- $fSearchDiam = $fSearchDiam * 2;
-
- // If we have to expand the search area by a large amount then we need a larger feature
- // then there is a limit to how small the feature should be
- if ($fSearchDiam > 2 && $iMaxRank > 4) $iMaxRank = 4;
- if ($fSearchDiam > 1 && $iMaxRank > 9) $iMaxRank = 8;
- if ($fSearchDiam > 0.8 && $iMaxRank > 10) $iMaxRank = 10;
- if ($fSearchDiam > 0.6 && $iMaxRank > 12) $iMaxRank = 12;
- if ($fSearchDiam > 0.2 && $iMaxRank > 17) $iMaxRank = 17;
- if ($fSearchDiam > 0.1 && $iMaxRank > 18) $iMaxRank = 18;
- if ($fSearchDiam > 0.008 && $iMaxRank > 22) $iMaxRank = 22;
- if ($fSearchDiam > 0.001 && $iMaxRank > 26) $iMaxRank = 26;
-
- $sSQL = 'select place_id,parent_place_id from placex';
- $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')';
- $sSQL .= ' and rank_search != 28 and rank_search >= '.$iMaxRank;
- $sSQL .= ' and (name is not null or housenumber is not null)';
- $sSQL .= ' and class not in (\'waterway\')';
- $sSQL .= ' and (ST_GeometryType(geometry) not in (\'ST_Polygon\',\'ST_MultiPolygon\') ';
- $sSQL .= ' OR ST_DWithin('.$sPointSQL.', ST_Centroid(geometry), '.$fSearchDiam.'))';
- $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', geometry) ASC limit 1';
- //var_dump($sSQL);
- $aPlace = $oDB->getRow($sSQL);
- if (PEAR::IsError($aPlace))
- {
- var_Dump($sSQL, $aPlace);
- exit;
- }
- $iPlaceID = $aPlace['place_id'];
- }
-
- // The point we found might be too small - use the address to find what it is a child of
- if ($iPlaceID)
- {
- $sSQL = "select address_place_id from place_addressline where cached_rank_address <= $iMaxRank and place_id = $iPlaceID order by cached_rank_address desc,isaddress desc,distance desc limit 1";
- $iPlaceID = $oDB->getOne($sSQL);
- if (PEAR::IsError($iPlaceID))
- {
- var_Dump($sSQL, $iPlaceID);
- exit;
- }
-
- if ($iPlaceID && $aPlace['place_id'] && $iMaxRank < 28)
- {
- $sSQL = "select address_place_id from place_addressline where cached_rank_address <= $iMaxRank and place_id = ".$aPlace['place_id']." order by cached_rank_address desc,isaddress desc,distance desc";
- $iPlaceID = $oDB->getOne($sSQL);
- if (PEAR::IsError($iPlaceID))
- {
- var_Dump($sSQL, $iPlaceID);
- exit;
- }
- }
- if (!$iPlaceID)
- {
- $iPlaceID = $aPlace['place_id'];
- }
- }
-
- return $iPlaceID;
- }
-
function addQuotes($s)
{
return "'".$s."'";
{
preg_match_all('/(-?[0-9.]+) (-?[0-9.]+)/', $aMatch[1], $aPolyPoints, PREG_SET_ORDER);
}
- elseif (preg_match('#MULTIPOLYGON\\(\\(\\(([- 0-9.,]+)#', $geometry_as_text, $aMatch))
+/* elseif (preg_match('#MULTIPOLYGON\\(\\(\\(([- 0-9.,]+)#', $geometry_as_text, $aMatch))
{
preg_match_all('/(-?[0-9.]+) (-?[0-9.]+)/', $aMatch[1], $aPolyPoints, PREG_SET_ORDER);
- }
+ }*/
elseif (preg_match('#POINT\\((-?[0-9.]+) (-?[0-9.]+)\\)#', $geometry_as_text, $aMatch))
{
$aPolyPoints = createPointsAroundCenter($aMatch[1], $aMatch[2], $fRadius);
+ -- Splits the line at the given point and returns the two parts
+ -- in a multilinestring.
+ CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
+ RETURNS GEOMETRY
+ AS $$
+ BEGIN
+ RETURN ST_Split(ST_Snap(line, point, 0.0005), point);
+ END;
+ $$
+ LANGUAGE plpgsql;
+
+
CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER
AS $$
DECLARE
LANGUAGE plpgsql;
-
- -- find the parant road of an interpolation
+ -- find the parent road of the cut road parts
CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, place TEXT,
partition INTEGER, centroid GEOMETRY, geom GEOMETRY)
RETURNS BIGINT AS $$
$$
LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT,
- parent_id BIGINT, partition INTEGER,
- country_code TEXT, geometry_sector INTEGER,
- defpostalcode TEXT, geom GEOMETRY) RETURNS INTEGER
- AS $$
+
+ CREATE OR REPLACE FUNCTION insert_osmline(wayid BIGINT, interpolationtype TEXT,
+ street TEXT, addr_place TEXT,
+ defpostalcode TEXT, country_code TEXT,
+ geom GEOMETRY)
+ RETURNS INTEGER AS $$
DECLARE
newpoints INTEGER;
nextnode RECORD;
startnumber INTEGER;
endnumber INTEGER;
- stepsize INTEGER;
- orginalstartnumber INTEGER;
- originalnumberrange INTEGER;
housenum INTEGER;
linegeo GEOMETRY;
splitline GEOMETRY;
sectiongeo GEOMETRY;
pointgeo GEOMETRY;
+ place_centroid GEOMETRY;
+ calculated_country_code VARCHAR(2);
+ partition INTEGER;
+ geometry_sector INTEGER;
BEGIN
- delete from placex where osm_type = 'W' and osm_id = wayid
- and class = 'place' and type = 'address';
-
- IF interpolationtype = 'odd' OR interpolationtype = 'even' THEN
- stepsize := 2;
- ELSEIF interpolationtype = 'all' THEN
- stepsize := 1;
- ELSEIF interpolationtype ~ '^\d+$' THEN
- stepsize := interpolationtype::INTEGER;
- ELSE
+ place_centroid := ST_PointOnSurface(geom);
+ calculated_country_code := lower(get_country_code(place_centroid));
+ partition := get_partition(calculated_country_code);
+ geometry_sector := geometry_sector(partition, place_centroid);
+
+ IF interpolationtype != 'odd' AND interpolationtype != 'even' AND interpolationtype!='all' THEN
+ -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported
RETURN 0;
END IF;
linegeo := geom;
startnumber := NULL;
- newpoints := 0;
FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
- -- If there is a place of a type other than place/house, use that because
- -- it is guaranteed to be the original node. For place/house types use the
- -- one with the smallest id because the original node was created first.
- -- Ignore all nodes marked for deletion. (Might happen when the type changes.)
- select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
- and indexed_status < 100 and housenumber is not NULL
- order by (type = 'address'),place_id limit 1 INTO nextnode;
- IF nextnode.place_id IS NOT NULL THEN
-
- IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
- -- Make sure that the point is actually on the line. That might
- -- be a bit paranoid but ensures that the algorithm still works
- -- should osm2pgsql attempt to repair geometries.
- splitline := split_line_on_node(linegeo, nextnode.geometry);
- sectiongeo := ST_GeometryN(splitline, 1);
- linegeo := ST_GeometryN(splitline, 2);
- ELSE
- sectiongeo = linegeo;
- END IF;
- endnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
+ select * from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
+ and housenumber is not NULL limit 1 INTO nextnode;
+ --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
+ IF nextnode.osm_id IS NOT NULL THEN
+ --RAISE NOTICE 'place_id is not null';
+ IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
+ -- Make sure that the point is actually on the line. That might
+ -- be a bit paranoid but ensures that the algorithm still works
+ -- should osm2pgsql attempt to repair geometries.
+ splitline := split_line_on_node(linegeo, nextnode.geometry);
+ sectiongeo := ST_GeometryN(splitline, 1);
+ linegeo := ST_GeometryN(splitline, 2);
+ ELSE
+ sectiongeo = linegeo;
+ END IF;
+ endnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
- IF startnumber IS NOT NULL AND endnumber IS NOT NULL
- AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber
- AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
+ IF startnumber IS NOT NULL AND endnumber IS NOT NULL
+ AND startnumber != endnumber
+ AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
- IF (startnumber > endnumber) THEN
- housenum := endnumber;
- endnumber := startnumber;
- startnumber := housenum;
- sectiongeo := ST_Reverse(sectiongeo);
- END IF;
- orginalstartnumber := startnumber;
- originalnumberrange := endnumber - startnumber;
-
- startnumber := startnumber + stepsize;
- -- correct for odd/even
- IF (interpolationtype = 'odd' AND startnumber%2 = 0)
- OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
- startnumber := startnumber - 1;
- END IF;
- endnumber := endnumber - 1;
-
- -- keep for compatibility with previous versions
- delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id
- and place_id != prevnode.place_id and class = 'place'
- and type = 'house';
- FOR housenum IN startnumber..endnumber BY stepsize LOOP
- pointgeo := ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float);
- insert into placex (place_id, partition, osm_type, osm_id,
- class, type, admin_level, housenumber,
- postcode,
- country_code, parent_place_id, rank_address, rank_search,
- indexed_status, indexed_date, geometry_sector,
- calculated_country_code, centroid, geometry)
- values (nextval('seq_place'), partition, 'W', wayid,
- 'place', 'address', prevnode.admin_level, housenum,
- coalesce(prevnode.postcode, defpostalcode),
- prevnode.country_code, parent_id, 30, 30,
- 0, now(), geometry_sector, country_code,
- pointgeo, pointgeo);
- newpoints := newpoints + 1;
- --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
- END LOOP;
+ IF (startnumber > endnumber) THEN
+ housenum := endnumber;
+ endnumber := startnumber;
+ startnumber := housenum;
+ sectiongeo := ST_Reverse(sectiongeo);
END IF;
- -- early break if we are out of line string,
- -- might happen when a line string loops back on itself
- IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
- RETURN newpoints;
- END IF;
+ insert into location_property_osmline
+ values (sectiongeo, nextval('seq_place'), partition, wayid, NULL,
+ startnumber, endnumber, interpolationtype,
+ coalesce(street, prevnode.street, nextnode.street),
+ coalesce(addr_place, prevnode.addr_place, nextnode.addr_place),
+ coalesce(defpostalcode, prevnode.postcode, nextnode.postcode),
+ calculated_country_code, geometry_sector, 2, now());
+ END IF;
- startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
- prevnode := nextnode;
+ -- early break if we are out of line string,
+ -- might happen when a line string loops back on itself
+ IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
+ RETURN 0;
+ END IF;
+
+ startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
+ prevnode := nextnode;
END IF;
END LOOP;
- --RAISE WARNING 'interpolation points % ',newpoints;
-
- RETURN newpoints;
+ RETURN 1;
END;
$$
LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION placex_insert() RETURNS TRIGGER
AS $$
DECLARE
default_language VARCHAR(10);
diameter FLOAT;
classtable TEXT;
+ line RECORD;
BEGIN
--DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id;
- -- ignore interpolated addresses
+ -- ignore interpolated addresses, not necessary anymore, cause interpolated addresses are now in location_property_osmline
IF NEW.class = 'place' and NEW.type = 'address' THEN
RETURN NEW;
END IF;
--DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
- RETURN NEW; -- @DIFFUPDATES@ The following is not needed until doing diff updates, and slows the main index process down
+ RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
IF NEW.rank_address > 0 THEN
IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
IF NEW.rank_search >= 26 THEN
-- roads may cause reparenting for >27 rank places
update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+ -- reparenting also for OSM Interpolation Lines (and for Tiger?)
+ update location_property_osmline set indexed_status = 2 where indexed_status = 0 and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);
ELSEIF NEW.rank_search >= 16 THEN
-- up to rank 16, street-less addresses may need reparenting
update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or addr_place is not null);
$$
LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION placex_update() RETURNS
+ CREATE OR REPLACE FUNCTION osmline_update() RETURNS
+ TRIGGER
+ AS $$
+ DECLARE
+ place_centroid GEOMETRY;
+ BEGIN
+ -- deferred delete
+ IF OLD.indexed_status = 100 THEN
+ delete from location_property_osmline where place_id = OLD.place_id;
+ RETURN NULL;
+ END IF;
+
+ IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
+ RETURN NEW;
+ END IF;
+
+ -- do the reparenting: (finally here, because ALL places in placex, that are needed for reparenting, need to be up to date)
+ -- (the osm interpolationline in location_property_osmline was marked for reparenting in placex_insert/placex_delete with index_status = 1 or 2 (1 inset, 2 delete)
+ -- => index.c: sets index_status back to 0
+ -- => triggers this function)
+ place_centroid := ST_PointOnSurface(NEW.linegeo);
+ -- marking descendants for reparenting is not needed, because there are actually no descendants for interpolation lines
+ NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place,
+ NEW.partition, place_centroid, NEW.linegeo);
+ return NEW;
+ END;
+ $$
+ LANGUAGE plpgsql;
+
+ CREATE OR REPLACE FUNCTION placex_update() RETURNS
TRIGGER
AS $$
DECLARE
result BOOLEAN;
BEGIN
-
-- deferred delete
IF OLD.indexed_status = 100 THEN
--DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
RETURN NEW;
END IF;
- -- TODO: this test is now redundant?
- IF OLD.indexed_status != 0 THEN
-
- NEW.indexed_date = now();
+ NEW.indexed_date = now();
- result := deleteSearchName(NEW.partition, NEW.place_id);
- DELETE FROM place_addressline WHERE place_id = NEW.place_id;
- result := deleteRoad(NEW.partition, NEW.place_id);
- result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
- UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+ result := deleteSearchName(NEW.partition, NEW.place_id);
+ DELETE FROM place_addressline WHERE place_id = NEW.place_id;
+ result := deleteRoad(NEW.partition, NEW.place_id);
+ result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
+ UPDATE placex set linked_place_id = null, indexed_status = 2
+ where linked_place_id = NEW.place_id;
+ -- update not necessary for osmline, cause linked_place_id does not exist
- IF NEW.linked_place_id is not null THEN
- RETURN NEW;
- END IF;
+ IF NEW.linked_place_id is not null THEN
+ RETURN NEW;
+ END IF;
- -- Speed up searches - just use the centroid of the feature
- -- cheaper but less acurate
- place_centroid := ST_PointOnSurface(NEW.geometry);
- NEW.centroid := null;
-
- -- recalculate country and partition
- IF NEW.rank_search = 4 THEN
- -- for countries, believe the mapped country code,
- -- so that we remain in the right partition if the boundaries
- -- suddenly expand.
- NEW.partition := get_partition(lower(NEW.country_code));
- IF NEW.partition = 0 THEN
- NEW.calculated_country_code := lower(get_country_code(place_centroid));
- NEW.partition := get_partition(NEW.calculated_country_code);
- ELSE
- NEW.calculated_country_code := lower(NEW.country_code);
- END IF;
- ELSE
- IF NEW.rank_search > 4 THEN
- --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
- NEW.calculated_country_code := lower(get_country_code(place_centroid));
- ELSE
- NEW.calculated_country_code := NULL;
- END IF;
+ -- Speed up searches - just use the centroid of the feature
+ -- cheaper but less acurate
+ place_centroid := ST_PointOnSurface(NEW.geometry);
+ NEW.centroid := null;
+
+ -- recalculate country and partition
+ IF NEW.rank_search = 4 THEN
+ -- for countries, believe the mapped country code,
+ -- so that we remain in the right partition if the boundaries
+ -- suddenly expand.
+ NEW.partition := get_partition(lower(NEW.country_code));
+ IF NEW.partition = 0 THEN
+ NEW.calculated_country_code := lower(get_country_code(place_centroid));
NEW.partition := get_partition(NEW.calculated_country_code);
+ ELSE
+ NEW.calculated_country_code := lower(NEW.country_code);
END IF;
- NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
-
- -- interpolations
- IF NEW.class = 'place' AND NEW.type = 'houses'THEN
- IF NEW.osm_type = 'W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
- NEW.parent_place_id := get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place,
- NEW.partition, place_centroid, NEW.geometry);
- i := create_interpolation(NEW.osm_id, NEW.housenumber, NEW.parent_place_id,
- NEW.partition, NEW.calculated_country_code,
- NEW.geometry_sector, NEW.postcode, NEW.geometry);
- END IF;
- RETURN NEW;
+ ELSE
+ IF NEW.rank_search > 4 THEN
+ --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ NEW.calculated_country_code := lower(get_country_code(place_centroid));
+ ELSE
+ NEW.calculated_country_code := NULL;
END IF;
+ NEW.partition := get_partition(NEW.calculated_country_code);
+ END IF;
- -- waterway ways are linked when they are part of a relation and have the same class/type
- IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN
- FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[]
- LOOP
- FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP
- IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN
- --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.members[i];
- FOR linked_node_id IN SELECT place_id FROM placex
- WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint
- and class = NEW.class and type = NEW.type
- and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')
- LOOP
- UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id;
- END LOOP;
- END IF;
- END LOOP;
- END LOOP;
- END IF;
+ -- waterway ways are linked when they are part of a relation and have the same class/type
+ IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN
+ FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[]
+ LOOP
+ FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP
+ IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN
+ --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.members[i];
+ FOR linked_node_id IN SELECT place_id FROM placex
+ WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint
+ and class = NEW.class and type = NEW.type
+ and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')
+ LOOP
+ UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id;
+ END LOOP;
+ END IF;
+ END LOOP;
+ END LOOP;
+ END IF;
- -- Adding ourselves to the list simplifies address calculations later
- INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address);
-
- -- What level are we searching from
- search_maxrank := NEW.rank_search;
-
- -- Thought this wasn't needed but when we add new languages to the country_name table
- -- we need to update the existing names
- IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
- default_language := get_country_language_code(NEW.calculated_country_code);
- IF default_language IS NOT NULL THEN
- IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
- NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
- ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
- NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
- END IF;
+ -- Adding ourselves to the list simplifies address calculations later
+ INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address);
+
+ -- What level are we searching from
+ search_maxrank := NEW.rank_search;
+
+ -- Thought this wasn't needed but when we add new languages to the country_name table
+ -- we need to update the existing names
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+ default_language := get_country_language_code(NEW.calculated_country_code);
+ IF default_language IS NOT NULL THEN
+ IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
+ NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
+ ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+ NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
+ END IF;
- -- Initialise the name vector using our name
- name_vector := make_keywords(NEW.name);
- nameaddress_vector := '{}'::int[];
+ -- Initialise the name vector using our name
+ name_vector := make_keywords(NEW.name);
+ nameaddress_vector := '{}'::int[];
- FOR i IN 1..28 LOOP
- address_havelevel[i] := false;
- END LOOP;
+ FOR i IN 1..28 LOOP
+ address_havelevel[i] := false;
+ END LOOP;
- NEW.importance := null;
- select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
- IF NEW.importance IS NULL THEN
- select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
- END IF;
+ NEW.importance := null;
+ select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
+ IF NEW.importance IS NULL THEN
+ select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
+ END IF;
--RAISE WARNING 'before low level% %', NEW.place_id, NEW.rank_search;
- -- For low level elements we inherit from our parent road
- IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN
+ -- ---------------------------------------------------------------------------
+ -- For low level elements we inherit from our parent road
+ IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN
--RAISE WARNING 'finding street for %', NEW;
- -- We won't get a better centroid, besides these places are too small to care
- NEW.centroid := place_centroid;
-
- NEW.parent_place_id := null;
-
- -- if we have a POI and there is no address information,
- -- see if we can get it from a surrounding building
- IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL
- AND NEW.housenumber IS NULL THEN
- FOR location IN select * from placex where ST_Covers(geometry, place_centroid)
- and (housenumber is not null or street is not null or addr_place is not null)
- and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
- limit 1
- LOOP
- NEW.housenumber := location.housenumber;
- NEW.street := location.street;
- NEW.addr_place := location.addr_place;
- END LOOP;
- END IF;
+ -- We won't get a better centroid, besides these places are too small to care
+ NEW.centroid := place_centroid;
+
+ NEW.parent_place_id := null;
+
+ -- if we have a POI and there is no address information,
+ -- see if we can get it from a surrounding building
+ IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL
+ AND NEW.housenumber IS NULL THEN
+ FOR location IN select * from placex where ST_Covers(geometry, place_centroid)
+ and (housenumber is not null or street is not null or addr_place is not null)
+ and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
+ limit 1
+ LOOP
+ NEW.housenumber := location.housenumber;
+ NEW.street := location.street;
+ NEW.addr_place := location.addr_place;
+ END LOOP;
+ END IF;
- -- We have to find our parent road.
- -- Copy data from linked items (points on ways, addr:street links, relations)
+ -- We have to find our parent road.
+ -- Copy data from linked items (points on ways, addr:street links, relations)
- -- Is this object part of a relation?
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id]
- LOOP
- -- At the moment we only process one type of relation - associatedStreet
- IF relation.tags @> ARRAY['associatedStreet'] THEN
- FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
- IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+ -- Is this object part of a relation?
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id]
+ LOOP
+ -- At the moment we only process one type of relation - associatedStreet
+ IF relation.tags @> ARRAY['associatedStreet'] THEN
+ FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
+ IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
--RAISE WARNING 'node in relation %',relation;
- SELECT place_id from placex where osm_type = 'W'
- and osm_id = substring(relation.members[i],2,200)::bigint
- and rank_search = 26 and name is not null INTO NEW.parent_place_id;
- END IF;
- END LOOP;
+ SELECT place_id from placex where osm_type = 'W'
+ and osm_id = substring(relation.members[i],2,200)::bigint
+ and rank_search = 26 and name is not null INTO NEW.parent_place_id;
END IF;
END LOOP;
+ END IF;
+ END LOOP;
- -- Note that addr:street links can only be indexed once the street itself is indexed
- IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN
- address_street_word_ids := get_name_ids(make_standard_name(NEW.street));
- IF address_street_word_ids IS NOT NULL THEN
- FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
- END IF;
+ -- Note that addr:street links can only be indexed once the street itself is indexed
+ IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN
+ address_street_word_ids := get_name_ids(make_standard_name(NEW.street));
+ IF address_street_word_ids IS NOT NULL THEN
+ FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
+ NEW.parent_place_id := location.place_id;
+ END LOOP;
END IF;
+ END IF;
- IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN
- address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place));
- IF address_street_word_ids IS NOT NULL THEN
- FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
- END IF;
+ IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN
+ address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place));
+ IF address_street_word_ids IS NOT NULL THEN
+ FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
+ NEW.parent_place_id := location.place_id;
+ END LOOP;
END IF;
+ END IF;
- IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
-
- --RAISE WARNING 'x1';
- -- Is this node part of a way?
- FOR location IN select p.* from placex p, planet_osm_ways w
- where p.osm_type = 'W' and p.rank_search >= 26
- and p.geometry && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes)
- LOOP
- --RAISE WARNING '%', location;
- -- Way IS a road then we are on it - that must be our road
- IF location.rank_search = 26 AND NEW.parent_place_id IS NULL THEN
- --RAISE WARNING 'node in way that is a street %',location;
- NEW.parent_place_id := location.place_id;
- END IF;
+ -- Is this node part of an interpolation?
+ IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
+ FOR location IN
+ SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x
+ WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes)
+ LIMIT 1
+ LOOP
+ NEW.parent_place_id := location.parent_place_id;
+ END LOOP;
+ END IF;
- -- If this way is a street interpolation line then it is probably as good as we are going to get
- IF NEW.parent_place_id IS NULL AND location.class = 'place' and location.type='houses' THEN
- NEW.parent_place_id := location.parent_place_id;
- END IF;
+ -- Is this node part of a way?
+ IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
- -- Is the WAY part of a relation
- IF NEW.parent_place_id IS NULL THEN
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
- LOOP
- -- At the moment we only process one type of relation - associatedStreet
- IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
- IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
- --RAISE WARNING 'node in way that is in a relation %',relation;
- SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
- and rank_search = 26 and name is not null INTO NEW.parent_place_id;
- END IF;
- END LOOP;
- END IF;
- END LOOP;
- END IF;
+ FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.street, p.addr_place from placex p, planet_osm_ways w
+ where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes)
+ LOOP
- -- If the way mentions a street or place address, try that for parenting.
- IF NEW.parent_place_id IS NULL AND location.street IS NOT NULL THEN
- address_street_word_ids := get_name_ids(make_standard_name(location.street));
- IF address_street_word_ids IS NOT NULL THEN
- FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
- NEW.parent_place_id := linkedplacex.place_id;
- END LOOP;
- END IF;
- END IF;
+ -- Way IS a road then we are on it - that must be our road
+ IF location.rank_search < 28 AND NEW.parent_place_id IS NULL THEN
+ --RAISE WARNING 'node in way that is a street %',location;
+ NEW.parent_place_id := location.place_id;
+ END IF;
- IF NEW.parent_place_id IS NULL AND location.addr_place IS NOT NULL THEN
- address_street_word_ids := get_name_ids(make_standard_name(location.addr_place));
- IF address_street_word_ids IS NOT NULL THEN
- FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
+ -- If the way mentions a street or place address, try that for parenting.
+ IF NEW.parent_place_id IS NULL AND location.street IS NOT NULL THEN
+ address_street_word_ids := get_name_ids(make_standard_name(location.street));
+ IF address_street_word_ids IS NOT NULL THEN
+ FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
NEW.parent_place_id := linkedplacex.place_id;
- END LOOP;
- END IF;
+ END LOOP;
END IF;
+ END IF;
- END LOOP;
+ IF NEW.parent_place_id IS NULL AND location.addr_place IS NOT NULL THEN
+ address_street_word_ids := get_name_ids(make_standard_name(location.addr_place));
+ IF address_street_word_ids IS NOT NULL THEN
+ FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
+ NEW.parent_place_id := linkedplacex.place_id;
+ END LOOP;
+ END IF;
+ END IF;
- END IF;
+ -- Is the WAY part of a relation
+ IF NEW.parent_place_id IS NULL THEN
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
+ LOOP
+ -- At the moment we only process one type of relation - associatedStreet
+ IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
+ IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+ --RAISE WARNING 'node in way that is in a relation %',relation;
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
+ and rank_search = 26 and name is not null INTO NEW.parent_place_id;
+ END IF;
+ END LOOP;
+ END IF;
+ END LOOP;
+ END IF;
+
+ END LOOP;
+
+ END IF;
--RAISE WARNING 'x4 %',NEW.parent_place_id;
- -- Still nothing, just use the nearest road
- IF NEW.parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
- END IF;
+ -- Still nothing, just use the nearest road
+ IF NEW.parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) LOOP
+ NEW.parent_place_id := location.place_id;
+ END LOOP;
+ END IF;
--return NEW;
--RAISE WARNING 'x6 %',NEW.parent_place_id;
- -- If we didn't find any road fallback to standard method
- IF NEW.parent_place_id IS NOT NULL THEN
+ -- If we didn't find any road fallback to standard method
+ IF NEW.parent_place_id IS NOT NULL THEN
- -- Get the details of the parent road
- select * from search_name where place_id = NEW.parent_place_id INTO location;
- NEW.calculated_country_code := location.country_code;
+ -- Get the details of the parent road
+ select * from search_name where place_id = NEW.parent_place_id INTO location;
+ NEW.calculated_country_code := location.country_code;
- -- Merge the postcode into the parent's address if necessary XXXX
- IF NEW.postcode IS NOT NULL THEN
- isin_tokens := '{}'::int[];
- address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
- IF address_street_word_id is not null
- and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
- address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode));
- IF address_street_word_id is not null
- and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
- IF isin_tokens != '{}'::int[] THEN
- UPDATE search_name
- SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens
- WHERE place_id = NEW.parent_place_id;
- END IF;
+ -- Merge the postcode into the parent's address if necessary XXXX
+ IF NEW.postcode IS NOT NULL THEN
+ isin_tokens := '{}'::int[];
+ address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
+ IF address_street_word_id is not null
+ and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
+ isin_tokens := isin_tokens || address_street_word_id;
END IF;
-
- --RAISE WARNING '%', NEW.name;
- -- If there is no name it isn't searchable, don't bother to create a search record
- IF NEW.name is NULL THEN
- return NEW;
+ address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode));
+ IF address_street_word_id is not null
+ and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
+ isin_tokens := isin_tokens || address_street_word_id;
END IF;
+ IF isin_tokens != '{}'::int[] THEN
+ UPDATE search_name
+ SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens
+ WHERE place_id = NEW.parent_place_id;
+ END IF;
+ END IF;
- -- Merge address from parent
- nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
- nameaddress_vector := array_merge(nameaddress_vector, location.name_vector);
-
- -- Performance, it would be more acurate to do all the rest of the import
- -- process but it takes too long
- -- Just be happy with inheriting from parent road only
+ --RAISE WARNING '%', NEW.name;
+ -- If there is no name it isn't searchable, don't bother to create a search record
+ IF NEW.name is NULL THEN
+ return NEW;
+ END IF;
- IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
- result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
- END IF;
+ -- Merge address from parent
+ nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
+ nameaddress_vector := array_merge(nameaddress_vector, location.name_vector);
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
+ -- Performance, it would be more acurate to do all the rest of the import
+ -- process but it takes too long
+ -- Just be happy with inheriting from parent road only
- return NEW;
+ IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
+ result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
+
+ return NEW;
END IF;
+ END IF;
+
-- RAISE WARNING ' INDEXING Started:';
-- RAISE WARNING ' INDEXING: %',NEW;
- IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
+ -- ---------------------------------------------------------------------------
+ -- Full indexing
- -- see if we have any special relation members
- select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
+ IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
- -- RAISE WARNING 'get_osm_rel_members, label';
- IF relation_members IS NOT NULL THEN
- FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
+ -- see if we have any special relation members
+ select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
- FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
- and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+ -- RAISE WARNING 'get_osm_rel_members, label';
+ IF relation_members IS NOT NULL THEN
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
- -- If we don't already have one use this as the centre point of the geometry
- IF NEW.centroid IS NULL THEN
- NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
- END IF;
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
- -- merge in the label name, re-init word vector
- IF NOT linkedPlacex.name IS NULL THEN
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name));
- END IF;
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ END IF;
- -- merge in extra tags
- NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
+ -- merge in the label name, re-init word vector
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name));
+ END IF;
- -- mark the linked place (excludes from search results)
- UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ -- merge in extra tags
+ NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
- -- keep a note of the node id in case we need it for wikipedia in a bit
- linked_node_id := linkedPlacex.osm_id;
- END LOOP;
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ -- keep a note of the node id in case we need it for wikipedia in a bit
+ linked_node_id := linkedPlacex.osm_id;
END LOOP;
- IF NEW.centroid IS NULL THEN
+ END LOOP;
- FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+ IF NEW.centroid IS NULL THEN
- FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
- and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
- -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
- -- But that can be fixed by explicitly setting the label in the data
- IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
- AND NEW.rank_address = linkedPlacex.rank_address THEN
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
- -- If we don't already have one use this as the centre point of the geometry
- IF NEW.centroid IS NULL THEN
- NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
- END IF;
+ -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+ -- But that can be fixed by explicitly setting the label in the data
+ IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
+ AND NEW.rank_address = linkedPlacex.rank_address THEN
- -- merge in the name, re-init word vector
- IF NOT linkedPlacex.name IS NULL THEN
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
- END IF;
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ END IF;
- -- merge in extra tags
- NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
+ -- merge in the name, re-init word vector
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+ END IF;
- -- mark the linked place (excludes from search results)
- UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ -- merge in extra tags
+ NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
- -- keep a note of the node id in case we need it for wikipedia in a bit
- linked_node_id := linkedPlacex.osm_id;
- END IF;
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- END LOOP;
+ -- keep a note of the node id in case we need it for wikipedia in a bit
+ linked_node_id := linkedPlacex.osm_id;
+ END IF;
END LOOP;
- END IF;
- END IF;
+ END LOOP;
+ END IF;
END IF;
- -- Name searches can be done for ways as well as relations
- IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN
+ END IF;
- -- not found one yet? how about doing a name search
- IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
+ -- Name searches can be done for ways as well as relations
+ IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN
- FOR linkedPlacex IN select placex.* from placex WHERE
- make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
- AND placex.rank_address = NEW.rank_address
- AND placex.place_id != NEW.place_id
- AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
- AND st_covers(NEW.geometry, placex.geometry)
- LOOP
+ -- not found one yet? how about doing a name search
+ IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
- -- If we don't already have one use this as the centre point of the geometry
- IF NEW.centroid IS NULL THEN
- NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
- END IF;
+ FOR linkedPlacex IN select placex.* from placex WHERE
+ make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
+ AND placex.rank_address = NEW.rank_address
+ AND placex.place_id != NEW.place_id
+ AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
+ AND st_covers(NEW.geometry, placex.geometry)
+ LOOP
- -- merge in the name, re-init word vector
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ END IF;
- -- merge in extra tags
- NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
+ -- merge in the name, re-init word vector
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
- -- mark the linked place (excludes from search results)
- UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ -- merge in extra tags
+ NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
- -- keep a note of the node id in case we need it for wikipedia in a bit
- linked_node_id := linkedPlacex.osm_id;
- END LOOP;
- END IF;
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- IF NEW.centroid IS NOT NULL THEN
- place_centroid := NEW.centroid;
- -- Place might have had only a name tag before but has now received translations
- -- from the linked place. Make sure a name tag for the default language exists in
- -- this case.
- IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
- default_language := get_country_language_code(NEW.calculated_country_code);
- IF default_language IS NOT NULL THEN
- IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
- NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
- ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
- NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
- END IF;
+ -- keep a note of the node id in case we need it for wikipedia in a bit
+ linked_node_id := linkedPlacex.osm_id;
+ END LOOP;
+ END IF;
+
+ IF NEW.centroid IS NOT NULL THEN
+ place_centroid := NEW.centroid;
+ -- Place might have had only a name tag before but has now received translations
+ -- from the linked place. Make sure a name tag for the default language exists in
+ -- this case.
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+ default_language := get_country_language_code(NEW.calculated_country_code);
+ IF default_language IS NOT NULL THEN
+ IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
+ NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
+ ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+ NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
END IF;
-
- -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
- IF NEW.importance is null THEN
- select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
- END IF;
- -- Still null? how about looking it up by the node id
- IF NEW.importance IS NULL THEN
- select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
- END IF;
-
END IF;
- -- make sure all names are in the word table
- IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
- perform create_country(NEW.name, lower(NEW.country_code));
+ -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
+ IF NEW.importance is null THEN
+ select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
+ END IF;
+ -- Still null? how about looking it up by the node id
+ IF NEW.importance IS NULL THEN
+ select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
END IF;
- NEW.parent_place_id = 0;
- parent_place_id_rank = 0;
-
- -- convert isin to array of tokenids
- isin_tokens := '{}'::int[];
- IF NEW.isin IS NOT NULL THEN
- isin := regexp_split_to_array(NEW.isin, E'[;,]');
- IF array_upper(isin, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin, 1) LOOP
- address_street_word_id := get_name_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
+ END IF;
- -- merge word into address vector
- address_street_word_id := get_word_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- END IF;
- END LOOP;
- END IF;
- END IF;
- IF NEW.postcode IS NOT NULL THEN
- isin := regexp_split_to_array(NEW.postcode, E'[;,]');
- IF array_upper(isin, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin, 1) LOOP
- address_street_word_id := get_name_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
+ -- make sure all names are in the word table
+ IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+ perform create_country(NEW.name, lower(NEW.country_code));
+ END IF;
- -- merge into address vector
- address_street_word_id := get_word_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- END IF;
- END LOOP;
- END IF;
- END IF;
+ NEW.parent_place_id = 0;
+ parent_place_id_rank = 0;
- -- for the USA we have an additional address table. Merge in zip codes from there too
- IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
- FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
- address_street_word_id := get_name_id(make_standard_name(location.postcode));
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
- -- also merge in the single word version
- address_street_word_id := get_word_id(make_standard_name(location.postcode));
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ -- convert isin to array of tokenids
+ isin_tokens := '{}'::int[];
+ IF NEW.isin IS NOT NULL THEN
+ isin := regexp_split_to_array(NEW.isin, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+
+ -- merge word into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
+ END LOOP;
+ END IF;
+ END IF;
+ IF NEW.postcode IS NOT NULL THEN
+ isin := regexp_split_to_array(NEW.postcode, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+
+ -- merge into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
END LOOP;
END IF;
+ END IF;
+
+ -- %NOTIGERDATA% IF 0 THEN
+ -- for the USA we have an additional address table. Merge in zip codes from there too
+ IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
+ FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
+ address_street_word_id := get_name_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+
+ -- also merge in the single word version
+ address_street_word_id := get_word_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END LOOP;
+ END IF;
+ -- %NOTIGERDATA% END IF;
-- RAISE WARNING 'ISIN: %', isin_tokens;
- -- Process area matches
- location_rank_search := 0;
- location_distance := 0;
- location_parent := NULL;
- -- added ourself as address already
- address_havelevel[NEW.rank_address] := true;
- -- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
- FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
+ -- Process area matches
+ location_rank_search := 0;
+ location_distance := 0;
+ location_parent := NULL;
+ -- added ourself as address already
+ address_havelevel[NEW.rank_address] := true;
+ -- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
+ FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
--RAISE WARNING ' AREA: %',location;
- IF location.rank_address != location_rank_search THEN
- location_rank_search := location.rank_address;
- IF location.isguess THEN
- location_distance := location.distance * 1.5;
+ IF location.rank_address != location_rank_search THEN
+ location_rank_search := location.rank_address;
+ IF location.isguess THEN
+ location_distance := location.distance * 1.5;
+ ELSE
+ IF location.rank_address <= 12 THEN
+ -- for county and above, if we have an area consider that exact
+ -- (It would be nice to relax the constraint for places close to
+ -- the boundary but we'd need the exact geometry for that. Too
+ -- expensive.)
+ location_distance = 0;
ELSE
- IF location.rank_address <= 12 THEN
- -- for county and above, if we have an area consider that exact
- -- (It would be nice to relax the constraint for places close to
- -- the boundary but we'd need the exact geometry for that. Too
- -- expensive.)
- location_distance = 0;
- ELSE
- -- Below county level remain slightly fuzzy.
- location_distance := location.distance * 0.5;
- END IF;
+ -- Below county level remain slightly fuzzy.
+ location_distance := location.distance * 0.5;
END IF;
- ELSE
- CONTINUE WHEN location.keywords <@ location_keywords;
END IF;
+ ELSE
+ CONTINUE WHEN location.keywords <@ location_keywords;
+ END IF;
- IF location.distance < location_distance OR NOT location.isguess THEN
- location_keywords := location.keywords;
-
- location_isaddress := NOT address_havelevel[location.rank_address];
- IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN
- location_isaddress := ST_Contains(location_parent,location.centroid);
- END IF;
-
- -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
- -- Add it to the list of search terms
- IF location.rank_search > 4 THEN
- nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- END IF;
- INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
+ IF location.distance < location_distance OR NOT location.isguess THEN
+ location_keywords := location.keywords;
- IF location_isaddress THEN
+ location_isaddress := NOT address_havelevel[location.rank_address];
+ IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN
+ location_isaddress := ST_Contains(location_parent,location.centroid);
+ END IF;
- address_havelevel[location.rank_address] := true;
- IF NOT location.isguess THEN
- SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
- END IF;
+ -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
+ -- Add it to the list of search terms
+ IF location.rank_search > 4 THEN
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ END IF;
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
- IF location.rank_address > parent_place_id_rank THEN
- NEW.parent_place_id = location.place_id;
- parent_place_id_rank = location.rank_address;
- END IF;
+ IF location_isaddress THEN
+ address_havelevel[location.rank_address] := true;
+ IF NOT location.isguess THEN
+ SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
END IF;
- --RAISE WARNING ' Terms: (%) %',location, nameaddress_vector;
+ IF location.rank_address > parent_place_id_rank THEN
+ NEW.parent_place_id = location.place_id;
+ parent_place_id_rank = location.rank_address;
+ END IF;
END IF;
- END LOOP;
-
- -- try using the isin value to find parent places
- IF array_upper(isin_tokens, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin_tokens, 1) LOOP
- --RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
- IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN
-
- FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+ --RAISE WARNING ' Terms: (%) %',location, nameaddress_vector;
- --RAISE WARNING ' ISIN: %',location;
+ END IF;
- IF location.rank_search > 4 THEN
- nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
- address_havelevel[location.rank_address] := true;
+ END LOOP;
- IF location.rank_address > parent_place_id_rank THEN
- NEW.parent_place_id = location.place_id;
- parent_place_id_rank = location.rank_address;
- END IF;
- END IF;
- END LOOP;
+ -- try using the isin value to find parent places
+ IF array_upper(isin_tokens, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin_tokens, 1) LOOP
+ --RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
+ IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN
- END IF;
+ FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
- END LOOP;
- END IF;
+ --RAISE WARNING ' ISIN: %',location;
- -- for long ways we should add search terms for the entire length
- IF st_length(NEW.geometry) > 0.05 THEN
+ IF location.rank_search > 4 THEN
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
+ address_havelevel[location.rank_address] := true;
- location_rank_search := 0;
- location_distance := 0;
+ IF location.rank_address > parent_place_id_rank THEN
+ NEW.parent_place_id = location.place_id;
+ parent_place_id_rank = location.rank_address;
+ END IF;
+ END IF;
+ END LOOP;
- FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP
+ END IF;
- IF location.rank_address != location_rank_search THEN
- location_rank_search := location.rank_address;
- location_distance := location.distance * 1.5;
- END IF;
+ END LOOP;
+ END IF;
- IF location.rank_search > 4 AND location.distance < location_distance THEN
+ -- for long ways we should add search terms for the entire length
+ IF st_length(NEW.geometry) > 0.05 THEN
- -- Add it to the list of search terms
- nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address);
+ location_rank_search := 0;
+ location_distance := 0;
- END IF;
+ FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP
- END LOOP;
+ IF location.rank_address != location_rank_search THEN
+ location_rank_search := location.rank_address;
+ location_distance := location.distance * 1.5;
+ END IF;
- END IF;
+ IF location.rank_search > 4 AND location.distance < location_distance THEN
- -- if we have a name add this to the name search table
- IF NEW.name IS NOT NULL THEN
+ -- Add it to the list of search terms
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address);
- IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
- result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
- IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
- result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
- END IF;
+ END LOOP;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
+ END IF;
+
+ -- if we have a name add this to the name search table
+ IF NEW.name IS NOT NULL THEN
+ IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
+ result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
- -- If we've not managed to pick up a better one - default centroid
- IF NEW.centroid IS NULL THEN
- NEW.centroid := place_centroid;
+ IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
+ result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
END IF;
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
+
END IF;
+ -- If we've not managed to pick up a better one - default centroid
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := place_centroid;
+ END IF;
+
RETURN NEW;
END;
$$
--DEBUG: RAISE WARNING 'placex_delete:06 % %',OLD.osm_type,OLD.osm_id;
update placex set indexed_status = 2 where parent_place_id = OLD.place_id and indexed_status = 0;
--DEBUG: RAISE WARNING 'placex_delete:07 % %',OLD.osm_type,OLD.osm_id;
+ -- reparenting also for OSM Interpolation Lines (and for Tiger?)
+ update location_property_osmline set indexed_status = 2 where indexed_status = 0 and parent_place_id = OLD.place_id;
END IF;
UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
-- interpolations are special
- IF OLD.class = 'place' and OLD.type = 'houses' THEN
- UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = 'place' and type = 'address';
+ IF OLD.osm_type='W' and OLD.class = 'place' and OLD.type = 'houses' THEN
+ UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id)
END IF;
RETURN OLD;
i INTEGER;
existing RECORD;
existingplacex RECORD;
+ existingline RECORD;
existinggeometry GEOMETRY;
existingplace_id BIGINT;
result BOOLEAN;
--DEBUG: RAISE WARNING '-----------------------------------------------------------------------------------';
--DEBUG: RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
-
- IF FALSE and NEW.osm_type = 'R' THEN
- select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
- --DEBUG: RAISE WARNING '%', existingplacex;
- END IF;
-
+ -- filter wrong tupels
IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN
INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.country_code,
now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
RETURN null;
END IF;
- -- Patch in additional country names
- IF NEW.admin_level = 2 AND NEW.type = 'administrative' AND NEW.country_code is not null THEN
- select coalesce(country_name.name || NEW.name,NEW.name) from country_name where country_name.country_code = lower(NEW.country_code) INTO NEW.name;
- END IF;
-
- -- Have we already done this place?
- select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
+ -- decide, whether it is an osm interpolation line => insert_osmline, or else just insert into placex
+ IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
+ -- Have we already done this place?
+ select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
- -- Get the existing place_id
- select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
+ -- Get the existing place_id
+ select * from location_property_osmline where osm_id = NEW.osm_id INTO existingline;
- -- Handle a place changing type by removing the old data
- -- My generated 'place' types are causing havok because they overlap with real keys
- -- TODO: move them to their own special purpose key/class to avoid collisions
- IF existing.osm_type IS NULL THEN
- DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
- END IF;
+ -- Handle a place changing type by removing the old data (this trigger is executed BEFORE INSERT of the NEW tupel)
+ IF existing.osm_type IS NULL THEN
+ DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
+ END IF;
- --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id;
- --DEBUG: RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;
+ DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
+ DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
- -- Log and discard
- IF existing.geometry is not null AND st_isvalid(existing.geometry)
- AND st_area(existing.geometry) > 0.02
- AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
- AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
- THEN
- INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.country_code, now(),
- 'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
- RETURN null;
- END IF;
+ -- update method for interpolation lines: delete all old interpolation lines with same osm_id (update on place) and insert the new one(s) (they can be split up, if they have > 2 nodes)
+ IF existingline.osm_id IS NOT NULL THEN
+ delete from location_property_osmline where osm_id = NEW.osm_id;
+ END IF;
- DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
- DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
+ -- for interpolations invalidate all nodes on the line
+ update placex p set indexed_status = 2
+ from planet_osm_ways w
+ where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
+ -- insert new line into location_property_osmline, use function insert_osmline
- -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
- IF existingplacex.osm_type IS NULL OR
- (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative')
- THEN
- IF existingplacex.osm_type IS NOT NULL THEN
- -- sanity check: ignore admin_level changes on places with too many active children
- -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
- --LIMIT INDEXING: SELECT count(*) FROM (SELECT 'a' FROM placex , place_addressline where address_place_id = existingplacex.place_id and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub INTO i;
- --LIMIT INDEXING: IF i > 100000 THEN
- --LIMIT INDEXING: RETURN null;
- --LIMIT INDEXING: END IF;
+ IF existing.osm_type IS NULL THEN
+ i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry);
+ return NEW;
END IF;
- IF existing.osm_type IS NOT NULL THEN
- -- pathological case caused by the triggerless copy into place during initial import
- -- force delete even for large areas, it will be reinserted later
- UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
- DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+ IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
+ OR coalesce(existing.street, '') != coalesce(NEW.street, '')
+ OR coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '')
+ OR coalesce(existing.isin, '') != coalesce(NEW.isin, '')
+ OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '')
+ OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '')
+ OR existing.geometry::text != NEW.geometry::text
+ THEN
+
+ update place set
+ name = NEW.name,
+ housenumber = NEW.housenumber,
+ street = NEW.street,
+ addr_place = NEW.addr_place,
+ isin = NEW.isin,
+ postcode = NEW.postcode,
+ country_code = NEW.country_code,
+ extratags = NEW.extratags,
+ admin_level = NEW.admin_level,
+ geometry = NEW.geometry
+ where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+
+ i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry);
END IF;
- -- No - process it as a new insertion (hopefully of low rank or it will be slow)
- insert into placex (osm_type, osm_id, class, type, name, admin_level, housenumber,
- street, addr_place, isin, postcode, country_code, extratags, geometry)
- values (NEW.osm_type
- ,NEW.osm_id
- ,NEW.class
- ,NEW.type
- ,NEW.name
- ,NEW.admin_level
- ,NEW.housenumber
- ,NEW.street
- ,NEW.addr_place
- ,NEW.isin
- ,NEW.postcode
- ,NEW.country_code
- ,NEW.extratags
- ,NEW.geometry
- );
-
- --DEBUG: RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;
-
- RETURN NEW;
- END IF;
+ RETURN NULL;
- -- Various ways to do the update
+ ELSE -- insert to placex
- -- Debug, what's changed?
- IF FALSE THEN
- IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') THEN
- RAISE WARNING 'update details, name: % % % %',NEW.osm_type,NEW.osm_id,existing.name::text,NEW.name::text;
- END IF;
- IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') THEN
- RAISE WARNING 'update details, housenumber: % % % %',NEW.osm_type,NEW.osm_id,existing.housenumber,NEW.housenumber;
- END IF;
- IF coalesce(existing.street, '') != coalesce(NEW.street, '') THEN
- RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.street,NEW.street;
- END IF;
- IF coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '') THEN
- RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.addr_place,NEW.addr_place;
+ -- Patch in additional country names
+ IF NEW.admin_level = 2 AND NEW.type = 'administrative' AND NEW.country_code is not null THEN
+ select coalesce(country_name.name || NEW.name,NEW.name) from country_name where country_name.country_code = lower(NEW.country_code) INTO NEW.name;
END IF;
- IF coalesce(existing.isin, '') != coalesce(NEW.isin, '') THEN
- RAISE WARNING 'update details, isin: % % % %',NEW.osm_type,NEW.osm_id,existing.isin,NEW.isin;
- END IF;
- IF coalesce(existing.postcode, '') != coalesce(NEW.postcode, '') THEN
- RAISE WARNING 'update details, postcode: % % % %',NEW.osm_type,NEW.osm_id,existing.postcode,NEW.postcode;
+
+ -- Have we already done this place?
+ select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
+
+ -- Get the existing place_id
+ select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
+
+ -- Handle a place changing type by removing the old data
+ -- My generated 'place' types are causing havok because they overlap with real keys
+ -- TODO: move them to their own special purpose key/class to avoid collisions
+ IF existing.osm_type IS NULL THEN
+ DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
END IF;
- IF coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN
- RAISE WARNING 'update details, country_code: % % % %',NEW.osm_type,NEW.osm_id,existing.country_code,NEW.country_code;
+
+ --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id;
+ --DEBUG: RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;
+
+ -- Log and discard
+ IF existing.geometry is not null AND st_isvalid(existing.geometry)
+ AND st_area(existing.geometry) > 0.02
+ AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
+ AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
+ THEN
+ INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.country_code, now(),
+ 'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
+ RETURN null;
END IF;
- END IF;
- -- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
- IF existing.geometry::text != NEW.geometry::text
- AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
- AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
- THEN
+ DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
+ DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
- -- Get the version of the geometry actually used (in placex table)
- select geometry from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existinggeometry;
+ -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
+ IF existingplacex.osm_type IS NULL OR
+ (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative')
+ THEN
- -- Performance limit
- IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN
+ IF existingplacex.osm_type IS NOT NULL THEN
+ -- sanity check: ignore admin_level changes on places with too many active children
+ -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
+ --LIMIT INDEXING: SELECT count(*) FROM (SELECT 'a' FROM placex , place_addressline where address_place_id = existingplacex.place_id and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub INTO i;
+ --LIMIT INDEXING: IF i > 100000 THEN
+ --LIMIT INDEXING: RETURN null;
+ --LIMIT INDEXING: END IF;
+ END IF;
- -- re-index points that have moved in / out of the polygon, could be done as a single query but postgres gets the index usage wrong
- update placex set indexed_status = 2 where indexed_status = 0 and
- (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND NOT (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
- AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+ IF existing.osm_type IS NOT NULL THEN
+ -- pathological case caused by the triggerless copy into place during initial import
+ -- force delete even for large areas, it will be reinserted later
+ UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+ DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+ END IF;
- update placex set indexed_status = 2 where indexed_status = 0 and
- (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
- AND NOT (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+ -- No - process it as a new insertion (hopefully of low rank or it will be slow)
+ insert into placex (osm_type, osm_id, class, type, name, admin_level, housenumber,
+ street, addr_place, isin, postcode, country_code, extratags, geometry)
+ values (NEW.osm_type
+ ,NEW.osm_id
+ ,NEW.class
+ ,NEW.type
+ ,NEW.name
+ ,NEW.admin_level
+ ,NEW.housenumber
+ ,NEW.street
+ ,NEW.addr_place
+ ,NEW.isin
+ ,NEW.postcode
+ ,NEW.country_code
+ ,NEW.extratags
+ ,NEW.geometry
+ );
+
+ --DEBUG: RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;
+ RETURN NEW;
END IF;
- END IF;
+ -- Various ways to do the update
+ -- Debug, what's changed?
+ IF FALSE THEN
+ IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') THEN
+ RAISE WARNING 'update details, name: % % % %',NEW.osm_type,NEW.osm_id,existing.name::text,NEW.name::text;
+ END IF;
+ IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') THEN
+ RAISE WARNING 'update details, housenumber: % % % %',NEW.osm_type,NEW.osm_id,existing.housenumber,NEW.housenumber;
+ END IF;
+ IF coalesce(existing.street, '') != coalesce(NEW.street, '') THEN
+ RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.street,NEW.street;
+ END IF;
+ IF coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '') THEN
+ RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.addr_place,NEW.addr_place;
+ END IF;
+ IF coalesce(existing.isin, '') != coalesce(NEW.isin, '') THEN
+ RAISE WARNING 'update details, isin: % % % %',NEW.osm_type,NEW.osm_id,existing.isin,NEW.isin;
+ END IF;
+ IF coalesce(existing.postcode, '') != coalesce(NEW.postcode, '') THEN
+ RAISE WARNING 'update details, postcode: % % % %',NEW.osm_type,NEW.osm_id,existing.postcode,NEW.postcode;
+ END IF;
+ IF coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN
+ RAISE WARNING 'update details, country_code: % % % %',NEW.osm_type,NEW.osm_id,existing.country_code,NEW.country_code;
+ END IF;
+ END IF;
- -- refuse to update multiplpoygons with too many objects, too much of a performance hit
- IF ST_NumGeometries(NEW.geometry) > 2000 THEN
- RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id;
- RETURN NULL;
- END IF;
+ -- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
+ IF existing.geometry::text != NEW.geometry::text
+ AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
+ AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
+ THEN
- IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
- OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
- OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
- OR coalesce(existing.street, '') != coalesce(NEW.street, '')
- OR coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '')
- OR coalesce(existing.isin, '') != coalesce(NEW.isin, '')
- OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '')
- OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '')
- OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
- OR existing.geometry::text != NEW.geometry::text
- THEN
-
- update place set
- name = NEW.name,
- housenumber = NEW.housenumber,
- street = NEW.street,
- addr_place = NEW.addr_place,
- isin = NEW.isin,
- postcode = NEW.postcode,
- country_code = NEW.country_code,
- extratags = NEW.extratags,
- admin_level = NEW.admin_level,
- geometry = NEW.geometry
- where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+ -- Get the version of the geometry actually used (in placex table)
+ select geometry from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existinggeometry;
- IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
- IF NEW.postcode IS NULL THEN
- -- postcode was deleted, no longer retain in placex
- DELETE FROM placex where place_id = existingplacex.place_id;
- RETURN NULL;
- END IF;
+ -- Performance limit
+ IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN
+
+ -- re-index points that have moved in / out of the polygon, could be done as a single query but postgres gets the index usage wrong
+ update placex set indexed_status = 2 where indexed_status = 0 and
+ (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
+ AND NOT (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
+ AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+
+ update placex set indexed_status = 2 where indexed_status = 0 and
+ (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
+ AND NOT (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
+ AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
+
+ END IF;
- NEW.name := hstore('ref', NEW.postcode);
END IF;
- update placex set
- name = NEW.name,
- housenumber = NEW.housenumber,
- street = NEW.street,
- addr_place = NEW.addr_place,
- isin = NEW.isin,
- postcode = NEW.postcode,
- country_code = NEW.country_code,
- parent_place_id = null,
- extratags = NEW.extratags,
- admin_level = CASE WHEN NEW.admin_level > 15 THEN 15 ELSE NEW.admin_level END,
- indexed_status = 2,
- geometry = NEW.geometry
- where place_id = existingplacex.place_id;
- END IF;
+ IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
+ OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
+ OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
+ OR coalesce(existing.street, '') != coalesce(NEW.street, '')
+ OR coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '')
+ OR coalesce(existing.isin, '') != coalesce(NEW.isin, '')
+ OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '')
+ OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '')
+ OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
+ OR existing.geometry::text != NEW.geometry::text
+ THEN
+
+ update place set
+ name = NEW.name,
+ housenumber = NEW.housenumber,
+ street = NEW.street,
+ addr_place = NEW.addr_place,
+ isin = NEW.isin,
+ postcode = NEW.postcode,
+ country_code = NEW.country_code,
+ extratags = NEW.extratags,
+ admin_level = NEW.admin_level,
+ geometry = NEW.geometry
+ where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
+
+
+ IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+ IF NEW.postcode IS NULL THEN
+ -- postcode was deleted, no longer retain in placex
+ DELETE FROM placex where place_id = existingplacex.place_id;
+ RETURN NULL;
+ END IF;
- -- for interpolations invalidate all nodes on the line
- IF NEW.class = 'place' and NEW.type = 'houses' and NEW.osm_type = 'W' THEN
- update placex p set indexed_status = 2 from planet_osm_ways w where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
- END IF;
+ NEW.name := hstore('ref', NEW.postcode);
+ END IF;
+
+ update placex set
+ name = NEW.name,
+ housenumber = NEW.housenumber,
+ street = NEW.street,
+ addr_place = NEW.addr_place,
+ isin = NEW.isin,
+ postcode = NEW.postcode,
+ country_code = NEW.country_code,
+ parent_place_id = null,
+ extratags = NEW.extratags,
+ admin_level = CASE WHEN NEW.admin_level > 15 THEN 15 ELSE NEW.admin_level END,
+ indexed_status = 2,
+ geometry = NEW.geometry
+ where place_id = existingplacex.place_id;
+
+ -- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => mark this line for reparenting
+ -- (already here, because interpolation lines are reindexed before nodes, so in the second call it would be too late)
+ IF NEW.osm_type='N' and NEW.class='place' and NEW.type='house' THEN
+ -- Is this node part of an interpolation line? search for it in location_property_osmline and mark the interpolation line for reparenting
+ update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes);
+ END IF;
- -- Abort the add (we modified the existing place instead)
- RETURN NULL;
+ END IF;
+
+ -- Abort the add (we modified the existing place instead)
+ RETURN NULL;
+ END IF;
- END;
+ END;
$$ LANGUAGE plpgsql;
$$
LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT
+ --housenumber only needed for tiger data
+ CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, housenumber INTEGER, languagepref TEXT[]) RETURNS TEXT
AS $$
DECLARE
result TEXT[];
result := '{}';
prevresult := '';
- FOR location IN select * from get_addressdata(for_place_id) where isaddress order by rank_address desc LOOP
+ FOR location IN select * from get_addressdata(for_place_id, housenumber) where isaddress order by rank_address desc LOOP
currresult := trim(get_name_by_language(location.name, languagepref));
IF currresult != prevresult AND currresult IS NOT NULL AND result[(100 - location.rank_address)] IS NULL THEN
result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref));
distance FLOAT
);
- CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT) RETURNS setof addressline
+ CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline
AS $$
DECLARE
for_place_id BIGINT;
countryname HSTORE;
hadcountry BOOLEAN;
BEGIN
+ -- first query osmline (interpolation lines)
+ select parent_place_id, calculated_country_code, 30, postcode, null, 'place', 'house' from location_property_osmline
+ WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
+ INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ IF for_place_id IS NOT NULL THEN
+ searchhousenumber = in_housenumber::text;
+ END IF;
- select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_tiger
- WHERE place_id = in_place_id
- INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ --then query tiger data
+ -- %NOTIGERDATA% IF 0 THEN
+ IF for_place_id IS NULL THEN
+ select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
+ WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
+ INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ IF for_place_id IS NOT NULL THEN
+ searchhousenumber = in_housenumber::text;
+ END IF;
+ END IF;
+ -- %NOTIGERDATA% END IF;
+ -- %NOAUXDATA% IF 0 THEN
IF for_place_id IS NULL THEN
select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
WHERE place_id = in_place_id
INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
END IF;
+ -- %NOAUXDATA% END IF;
IF for_place_id IS NULL THEN
select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex
CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
- admin_level, fromarea, isaddress,
+ admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,
CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
distance,calculated_country_code,postcode
from place_addressline join placex on (address_place_id = placex.place_id)
- CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id_imp ON location_property_tiger_import (parent_place_id, housenumber) {ts:aux-index};
+ --index only on parent_place_id
+ CREATE INDEX idx_location_property_tiger_parent_place_id_imp ON location_property_tiger_import (parent_place_id) {ts:aux-index};
CREATE UNIQUE INDEX idx_location_property_tiger_place_id_imp ON location_property_tiger_import (place_id) {ts:aux-index};
GRANT SELECT ON location_property_tiger_import TO "{www-user}";
-DROP TABLE IF EXISTS location_property_tiger;
-ALTER TABLE location_property_tiger_import RENAME TO location_property_tiger;
+--DROP TABLE IF EXISTS location_property_tiger;
+--ALTER TABLE location_property_tiger_import RENAME TO location_property_tiger;
- --ALTER INDEX idx_location_property_tiger_housenumber_parent_place_id_imp RENAME TO idx_location_property_tiger_housenumber_parent_place_id;
-ALTER INDEX idx_location_property_tiger_parent_place_id_imp RENAME TO idx_location_property_tiger_housenumber_parent_place_id;
-ALTER INDEX idx_location_property_tiger_place_id_imp RENAME TO idx_location_property_tiger_place_id;
++--ALTER INDEX idx_location_property_tiger_parent_place_id_imp RENAME TO idx_location_property_tiger_housenumber_parent_place_id;
+--ALTER INDEX idx_location_property_tiger_place_id_imp RENAME TO idx_location_property_tiger_place_id;
- DROP FUNCTION tigger_create_interpolation (linegeo geometry, in_startnumber integer, in_endnumber integer, interpolationtype text, in_street text, in_isin text, in_postcode text);
+ DROP FUNCTION tiger_line_import (linegeo geometry, in_startnumber integer, in_endnumber integer, interpolationtype text, in_street text, in_isin text, in_postcode text);
Scenario: bounded search remains within viewbox, even with no results
Given the request parameters
| bounded | viewbox
- | 1 | 43.54285,-5.662003,43.5403125,-5.6563282
+ | 1 | 43.5403125,-5.6563282,43.54285,-5.662003
When sending json search query "restaurant"
Then less than 1 result is returned
| 0.0
| 0.5
| 999
- | nan
Scenario Outline: Search with polygon threshold (xml)
Given the request parameters
| 0.0
| 0.5
| 999
- | nan
+
+ Scenario Outline: Search with invalid polygon threshold (xml)
+ Given the request parameters
+ | polygon_geojson | polygon_threshold
+ | 1 | <th>
+ When sending xml search query "switzerland"
+ Then a HTTP 400 is returned
+
Scenario Outline: Search with extratags
Given the request parameters
| format
| fd$#
When sending search query "Berlin"
- Then the result is valid html
+ Then a HTTP 400 is returned
Scenario Outline: Simple Searches
When sending search query "<query>"
Scenario: Empty XML search with viewbox
Given the request parameters
| viewbox
- | 12,45.13,77,33
+ | 12,45.13,13,44
When sending xml search query "xnznxvcx"
Then result header contains
| attr | value
| querystring | xnznxvcx
| polygon | false
- | viewbox | 12,45.13,77,33
+ | viewbox | 12,45.13,13,44
Scenario: Empty XML search with viewboxlbrt
Given the request parameters
| viewboxlbrt
- | 12,34.13,77,45
+ | 12,34.13,13,35
When sending xml search query "xnznxvcx"
Then result header contains
| attr | value
| querystring | xnznxvcx
| polygon | false
- | viewbox | 12,45.13,77,33
+ | viewbox | 12,34.13,13,35
Scenario: Empty XML search with viewboxlbrt and viewbox
Given the request parameters
- | viewbox | viewboxblrt
- | 12,45.13,77,33 | 1,2,3,4
+ | viewbox | viewboxblrt
+ | 12,45.13,13.5,44 | 1,0,2,1
When sending xml search query "pub"
Then result header contains
| attr | value
| querystring | pub
| polygon | false
- | viewbox | 12,45.13,77,33
+ | viewbox | 12,45.13,13.5,44
Scenario Outline: Empty XML search with polygon values
#!/usr/bin/php -Cq
<?php
- require_once(dirname(dirname(__FILE__)).'/lib/init-cmd.php');
+ require_once(dirname(dirname(__FILE__)).'/settings/settings.php');
+ require_once(CONST_BasePath.'/lib/init-cmd.php');
ini_set('memory_limit', '800M');
$aCMDOptions = array(
array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
array('ignore-errors', '', 0, 1, 0, 0, 'bool', 'Continue import even when errors in SQL are present (EXPERT)'),
- array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'),
array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
- array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
- array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'),
array('drop', '', 0, 1, 0, 0, 'bool', 'Drop tables needed for updates, making the database readonly (EXPERIMENTAL)'),
);
getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
$aDSNInfo = DB::parseDSN(CONST_Database_DSN);
if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
- $fPostgisVersion = (float) CONST_Postgis_Version;
-
if ($aCMDResult['create-db'] || $aCMDResult['all'])
{
echo "Create DB\n";
$bDidSomething = true;
- $oDB =& DB::connect(CONST_Database_DSN, false);
+ $oDB = DB::connect(CONST_Database_DSN, false);
if (!PEAR::isError($oDB))
{
fail('database already exists ('.CONST_Database_DSN.')');
$oDB =& getDB();
- $sVersionString = $oDB->getOne('select version()');
- preg_match('#PostgreSQL ([0-9]+)[.]([0-9]+)[^0-9]#', $sVersionString, $aMatches);
- if (CONST_Postgresql_Version != $aMatches[1].'.'.$aMatches[2])
+ $fPostgresVersion = getPostgresVersion($oDB);
+ echo 'Postgres version found: '.$fPostgresVersion."\n";
+
+ if ($fPostgresVersion < 9.1)
{
- echo "ERROR: PostgreSQL version is not correct. Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
- exit;
+ fail("Minimum supported version of Postgresql is 9.1.");
}
- passthru('createlang plpgsql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
- $pgver = (float) CONST_Postgresql_Version;
- if ($pgver < 9.1) {
- pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
- pgsqlRunScriptFile(CONST_BasePath.'/sql/hstore_compatability_9_0.sql');
- } else {
- pgsqlRunScript('CREATE EXTENSION hstore');
- }
+ pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS hstore');
+ pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis');
- if ($fPostgisVersion < 2.0) {
- pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
- pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
- } else {
- pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis');
+ // For extratags and namedetails the hstore_to_json converter is
+ // needed which is only available from Postgresql 9.3+. For older
+ // versions add a dummy function that returns nothing.
+ $iNumFunc = chksql($oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'"));
+
+ if ($iNumFunc == 0)
+ {
+ pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable");
+ echo "WARNING: Postgresql is too old. extratags and namedetails API not available.";
}
- if ($fPostgisVersion < 2.1) {
+
+ $fPostgisVersion = getPostgisVersion($oDB);
+ echo 'Postgis version found: '.$fPostgisVersion."\n";
+
+ if ($fPostgisVersion < 2.1)
+ {
// Function was renamed in 2.1 and throws an annoying deprecation warning
pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint');
}
- $sVersionString = $oDB->getOne('select postgis_full_version()');
- preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches);
- if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2])
- {
- echo "ERROR: PostGIS version is not correct. Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
- exit;
- }
pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
{
echo "WARNING: external UK postcode table not found.\n";
}
- pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
- pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
- pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
+ if (CONST_Use_Extra_US_Postcodes)
+ {
+ pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
+ }
if ($aCMDResult['no-partitions'])
{
if (CONST_Tablespace_Place_Index)
$osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
$osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
- $osm2pgsql .= ' -C '.$iCacheMemory;
+ $osm2pgsql .= ' -C 25000';
$osm2pgsql .= ' -P '.$aDSNInfo['port'];
$osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
passthruCheckReturn($osm2pgsql);
$oDB =& getDB();
- $x = $oDB->getRow('select * from place limit 1');
- if (PEAR::isError($x)) {
- fail($x->getMessage());
+ if (!chksql($oDB->getRow('select * from place limit 1')))
+ {
+ fail('No Data');
}
- if (!$x) fail('No Data');
}
if ($aCMDResult['create-functions'] || $aCMDResult['all'])
{
echo "Functions\n";
$bDidSomething = true;
- if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
- $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
- $sTemplate = str_replace('{modulepath}', CONST_BasePath.'/module', $sTemplate);
- if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
- if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
- if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
- pgsqlRunScript($sTemplate);
-
- if ($fPostgisVersion < 2.0) {
- echo "Helper functions for postgis < 2.0\n";
- $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_15_aux.sql');
- } else {
- echo "Helper functions for postgis >= 2.0\n";
- $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_20_aux.sql');
- }
- pgsqlRunScript($sTemplate);
- }
-
- if ($aCMDResult['create-minimal-tables'])
- {
- echo "Minimal Tables\n";
- $bDidSomething = true;
- pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
-
- $sScript = '';
-
- // Backstop the import process - easliest possible import id
- $sScript .= "insert into import_npi_log values (18022);\n";
-
- $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
- if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
-
- while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
- {
- list($sClass, $sType) = explode(' ', trim($sLine));
- $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
- $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
-
- $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
- $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
-
- $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
- $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
- }
- fclose($hFile);
- pgsqlRunScript($sScript);
+ if (!file_exists(CONST_InstallPath.'/module/nominatim.so')) fail("nominatim module not built");
+ create_sql_functions($aCMDResult);
}
if ($aCMDResult['create-tables'] || $aCMDResult['all'])
// re-run the functions
echo "Functions\n";
- $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
- $sTemplate = str_replace('{modulepath}',
- CONST_BasePath.'/module', $sTemplate);
- pgsqlRunScript($sTemplate);
+ create_sql_functions($aCMDResult);
}
if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
{
echo "Partition Tables\n";
$bDidSomething = true;
- $oDB =& getDB();
- $sSQL = 'select distinct partition from country_name';
- $aPartitions = $oDB->getCol($sSQL);
- if (PEAR::isError($aPartitions))
- {
- fail($aPartitions->getMessage());
- }
- if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
$sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
$sTemplate = replace_tablespace('{ts:address-data}',
CONST_Tablespace_Aux_Data, $sTemplate);
$sTemplate = replace_tablespace('{ts:aux-index}',
CONST_Tablespace_Aux_Index, $sTemplate);
- preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
- foreach($aMatches as $aMatch)
- {
- $sResult = '';
- foreach($aPartitions as $sPartitionName)
- {
- $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
- }
- $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
- }
- pgsqlRunScript($sTemplate);
+ pgsqlRunPartitionScript($sTemplate);
}
{
echo "Partition Functions\n";
$bDidSomething = true;
- $oDB =& getDB();
- $sSQL = 'select distinct partition from country_name';
- $aPartitions = $oDB->getCol($sSQL);
- if (PEAR::isError($aPartitions))
- {
- fail($aPartitions->getMessage());
- }
- if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
$sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
- preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
- foreach($aMatches as $aMatch)
- {
- $sResult = '';
- foreach($aPartitions as $sPartitionName)
- {
- $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
- }
- $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
- }
- pgsqlRunScript($sTemplate);
+ pgsqlRunPartitionScript($sTemplate);
}
if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
echo '.';
if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
echo '.';
+ if (!pg_query($oDB->connection, 'TRUNCATE location_property_osmline')) fail(pg_last_error($oDB->connection));
+ echo '.';
if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
echo '.';
if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
echo '.';
$sSQL = 'select distinct partition from country_name';
- $aPartitions = $oDB->getCol($sSQL);
- if (PEAR::isError($aPartitions))
- {
- fail($aPartitions->getMessage());
- }
+ $aPartitions = chksql($oDB->getCol($sSQL));
if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
foreach($aPartitions as $sPartition)
{
echo "Load Data\n";
$aDBInstances = array();
- for($i = 0; $i < $iInstances; $i++)
+ $iLoadThreads = max(1, $iInstances - 1);
+ for($i = 0; $i < $iLoadThreads; $i++)
{
$aDBInstances[$i] =& getDB(true);
$sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
$sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
- $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
+ $sSQL .= 'geometry) select * from place where osm_id % '.$iLoadThreads.' = '.$i;
+ $sSQL .= " and not (class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString')";
if ($aCMDResult['verbose']) echo "$sSQL\n";
if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
}
+ // last thread for interpolation lines
+ $aDBInstances[$iLoadThreads] =& getDB(true);
+ $sSQL = 'select insert_osmline (osm_id, housenumber, street, addr_place, postcode, country_code, ';
+ $sSQL .= 'geometry) from place where ';
+ $sSQL .= "class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString'";
+ if ($aCMDResult['verbose']) echo "$sSQL\n";
+ if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+
$bAnyBusy = true;
while($bAnyBusy)
{
$bAnyBusy = false;
- for($i = 0; $i < $iInstances; $i++)
+ for($i = 0; $i <= $iLoadThreads; $i++)
{
if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
}
$sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
$sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
$sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
- $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
+ $sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
- $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
- $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
- $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
- if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+ if (CONST_Use_Extra_US_Postcodes)
+ {
+ $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
+ $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
+ $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
+ if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+ }
}
if ($aCMDResult['osmosis-init'] || ($aCMDResult['all'] && !$aCMDResult['drop'])) // no use doing osmosis-init when dropping update tables
}
else
{
- if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
+ if (file_exists(CONST_InstallPath.'/settings/configuration.txt'))
{
echo "settings/configuration.txt already exists\n";
}
else
{
- passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
+ passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_InstallPath.'/settings');
// update osmosis configuration.txt with our settings
- passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_BasePath.'/settings/configuration.txt');
- passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_BasePath.'/settings/configuration.txt');
+ passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_InstallPath.'/settings/configuration.txt');
+ passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_InstallPath.'/settings/configuration.txt');
}
// Find the last node in the DB
echo "Getting state file: $sRepURL\n";
$sStateFile = file_get_contents($sRepURL);
if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
- file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
+ file_put_contents(CONST_InstallPath.'/settings/state.txt', $sStateFile);
echo "Updating DB status\n";
pg_query($oDB->connection, 'TRUNCATE import_status');
$sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
{
$bDidSomething = true;
$sOutputFile = '';
- if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
- $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
+ $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
passthruCheckReturn($sBaseCmd.' -R 4');
if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
{
echo "Search indices\n";
$bDidSomething = true;
- $oDB =& getDB();
- $sSQL = 'select distinct partition from country_name';
- $aPartitions = $oDB->getCol($sSQL);
- if (PEAR::isError($aPartitions))
- {
- fail($aPartitions->getMessage());
- }
- if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
$sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
$sTemplate = replace_tablespace('{ts:address-index}',
CONST_Tablespace_Search_Index, $sTemplate);
$sTemplate = replace_tablespace('{ts:aux-index}',
CONST_Tablespace_Aux_Index, $sTemplate);
- preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
- foreach($aMatches as $aMatch)
- {
- $sResult = '';
- foreach($aPartitions as $sPartitionName)
- {
- $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
- }
- $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
- }
pgsqlRunScript($sTemplate);
}
- if (isset($aCMDResult['create-website']))
- {
- $bDidSomething = true;
- $sTargetDir = $aCMDResult['create-website'];
- if (!is_dir($sTargetDir))
- {
- echo "You must create the website directory before calling this function.\n";
- fail("Target directory does not exist.");
- }
-
- @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
- @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
- @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
- @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
- @symlink(CONST_BasePath.'/website/lookup.php', $sTargetDir.'/lookup.php');
- @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
- @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
- @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
- @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
- @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
- @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
- echo "Symlinks created\n";
-
- $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
- if (!$sTestFile)
- {
- echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
- echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
- }
- }
-
if ($aCMDResult['drop'])
{
// The implementation is potentially a bit dangerous because it uses
$oDB =& getDB();
$aDropTables = array();
- $aHaveTables = $oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
- if (PEAR::isError($aHaveTables))
- {
- fail($aPartitions->getMessage());
- }
+ $aHaveTables = chksql($oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'"));
+
foreach($aHaveTables as $sTable)
{
$bFound = false;
}
}
+ function pgsqlRunPartitionScript($sTemplate)
+ {
+ global $aCMDResult;
+ $oDB =& getDB();
+
+ $sSQL = 'select distinct partition from country_name';
+ $aPartitions = chksql($oDB->getCol($sSQL));
+ if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
+
+ preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
+ foreach($aMatches as $aMatch)
+ {
+ $sResult = '';
+ foreach($aPartitions as $sPartitionName)
+ {
+ $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
+ }
+ $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
+ }
+
+ pgsqlRunScript($sTemplate);
+ }
+
function pgsqlRunRestoreData($sDumpFile)
{
// Convert database DSN to psql parameters
return $sSql;
}
+ function create_sql_functions($aCMDResult)
+ {
+ $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
+ $sTemplate = str_replace('{modulepath}', CONST_InstallPath.'/module', $sTemplate);
+ if ($aCMDResult['enable-diff-updates'])
+ {
+ $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate);
+ }
+ if ($aCMDResult['enable-debug-statements'])
+ {
+ $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
+ }
+ if (CONST_Limit_Reindexing)
+ {
+ $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
+ }
+ if (!CONST_Use_US_Tiger_Data)
+ {
+ $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate);
+ }
+ if (!CONST_Use_Aux_Location_data)
+ {
+ $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate);
+ }
+ pgsqlRunScript($sTemplate);
+
+ }
+
#!/usr/bin/php -Cq
<?php
- require_once(dirname(dirname(__FILE__)).'/lib/init-cmd.php');
- ini_set('memory_limit', '800M');
+ require_once(dirname(dirname(__FILE__)).'/settings/settings.php');
+ require_once(CONST_BasePath.'/lib/init-cmd.php');
+ ini_set('memory_limit', '800M');
$aCMDOptions = array(
"Import / update / index osm data",
array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
- array('max-load', '', 0, 1, 1, 1, 'float', 'Maximum load average - indexing is paused if this is exceeded'),
- array('max-blocking', '', 0, 1, 1, 1, 'int', 'Maximum blocking processes - indexing is aborted / paused if this is exceeded'),
-
array('import-osmosis', '', 0, 1, 0, 0, 'bool', 'Import using osmosis'),
array('import-osmosis-all', '', 0, 1, 0, 0, 'bool', 'Import using osmosis forever'),
- array('no-npi', '', 0, 1, 0, 0, 'bool', 'Do not write npi index files'),
+ array('no-npi', '', 0, 1, 0, 0, 'bool', '(obsolate)'),
array('no-index', '', 0, 1, 0, 0, 'bool', 'Do not index the new data'),
- array('import-npi-all', '', 0, 1, 0, 0, 'bool', 'Import npi pre-indexed files'),
-
- array('import-hourly', '', 0, 1, 0, 0, 'bool', 'Import hourly diffs'),
- array('import-daily', '', 0, 1, 0, 0, 'bool', 'Import daily diffs'),
array('import-all', '', 0, 1, 0, 0, 'bool', 'Import all available files'),
array('import-file', '', 0, 1, 1, 1, 'realpath', 'Re-import data from an OSM file'),
array('index', '', 0, 1, 0, 0, 'bool', 'Index'),
array('index-rank', '', 0, 1, 1, 1, 'int', 'Rank to start indexing from'),
array('index-instances', '', 0, 1, 1, 1, 'int', 'Number of indexing instances (threads)'),
- array('index-estrate', '', 0, 1, 1, 1, 'int', 'Estimated indexed items per second (def:30)'),
array('deduplicate', '', 0, 1, 0, 0, 'bool', 'Deduplicate tokens'),
);
getCmdOpt($_SERVER['argv'], $aCMDOptions, $aResult, true, true);
- if ($aResult['import-hourly'] + $aResult['import-daily'] + isset($aResult['import-diff']) > 1)
- {
- showUsage($aCMDOptions, true, 'Select either import of hourly or daily');
- }
-
+ if (!isset($aResult['index-instances'])) $aResult['index-instances'] = 1;
if (!isset($aResult['index-rank'])) $aResult['index-rank'] = 0;
- /*
- // Lock to prevent multiple copies running
- if (exec('/bin/ps uww | grep '.basename(__FILE__).' | grep -v /dev/null | grep -v grep -c', $aOutput2, $iResult) > 1)
- {
- fail("Copy already running\n");
- }
- if (!isset($aResult['max-load'])) $aResult['max-load'] = 1.9;
- if (!isset($aResult['max-blocking'])) $aResult['max-blocking'] = 3;
- if (getBlockingProcesses() > $aResult['max-blocking'])
- {
- fail("Too many blocking processes for import\n");
- }
- */
-
- // Assume osm2pgsql is in the folder above
- $sBasePath = dirname(dirname(__FILE__));
-
date_default_timezone_set('Etc/UTC');
$oDB =& getDB();
}
- $bFirst = true;
- $bContinue = $aResult['import-all'];
- while ($bContinue || $bFirst)
+ if (isset($aResult['import-diff']))
{
- $bFirst = false;
-
- if ($aResult['import-hourly'])
+ // import diff directly (e.g. from osmosis --rri)
+ $sNextFile = $aResult['import-diff'];
+ if (!file_exists($sNextFile))
{
- // Mirror the hourly diffs
- exec('wget --quiet --mirror -l 1 -P '.$sMirrorDir.' http://planet.openstreetmap.org/hourly');
- $sNextFile = $oDB->getOne('select TO_CHAR(lastimportdate,\'YYYYMMDDHH24\')||\'-\'||TO_CHAR(lastimportdate+\'1 hour\'::interval,\'YYYYMMDDHH24\')||\'.osc.gz\' from import_status');
- $sNextFile = $sMirrorDir.'planet.openstreetmap.org/hourly/'.$sNextFile;
- $sUpdateSQL = 'update import_status set lastimportdate = lastimportdate+\'1 hour\'::interval';
+ fail("Cannot open $sNextFile\n");
}
- if ($aResult['import-daily'])
- {
- // Mirror the daily diffs
- exec('wget --quiet --mirror -l 1 -P '.$sMirrorDir.' http://planet.openstreetmap.org/daily');
- $sNextFile = $oDB->getOne('select TO_CHAR(lastimportdate,\'YYYYMMDD\')||\'-\'||TO_CHAR(lastimportdate+\'1 day\'::interval,\'YYYYMMDD\')||\'.osc.gz\' from import_status');
- $sNextFile = $sMirrorDir.'planet.openstreetmap.org/daily/'.$sNextFile;
- $sUpdateSQL = 'update import_status set lastimportdate = lastimportdate::date + 1';
- }
-
- if (isset($aResult['import-diff']))
+ // Import the file
+ $sCMD = $sOsm2pgsqlCmd.' '.$sNextFile;
+ echo $sCMD."\n";
+ exec($sCMD, $sJunk, $iErrorLevel);
+
+ if ($iErrorLevel)
{
- // import diff directly (e.g. from osmosis --rri)
- $sNextFile = $aResult['import-diff'];
- if (!file_exists($sNextFile))
- {
- fail("Cannot open $sNextFile\n");
- }
- // Don't update the import status - we don't know what this file contains
- $sUpdateSQL = 'update import_status set lastimportdate = now() where false';
+ fail("Error from osm2pgsql, $iErrorLevel\n");
}
- // Missing file is not an error - it might not be created yet
- if (($aResult['import-hourly'] || $aResult['import-daily'] || isset($aResult['import-diff'])) && file_exists($sNextFile))
- {
- // Import the file
- $sCMD = $sOsm2pgsqlCmd.' '.$sNextFile;
- echo $sCMD."\n";
- exec($sCMD, $sJunk, $iErrorLevel);
+ // Don't update the import status - we don't know what this file contains
+ }
- if ($iErrorLevel)
- {
- fail("Error from osm2pgsql, $iErrorLevel\n");
- }
-
- // Move the date onwards
- $oDB->query($sUpdateSQL);
- }
- else
+ $sTemporaryFile = CONST_BasePath.'/data/osmosischange.osc';
+ $bHaveDiff = false;
+ if (isset($aResult['import-file']) && $aResult['import-file'])
+ {
+ $bHaveDiff = true;
+ $sCMD = CONST_Osmosis_Binary.' --read-xml \''.$aResult['import-file'].'\' --read-empty --derive-change --write-xml-change '.$sTemporaryFile;
+ echo $sCMD."\n";
+ exec($sCMD, $sJunk, $iErrorLevel);
+ if ($iErrorLevel)
{
- $bContinue = false;
+ fail("Error converting osm to osc, osmosis returned: $iErrorLevel\n");
}
}
- $bModifyXML = false;
- $sModifyXMLstr = '';
$bUseOSMApi = isset($aResult['import-from-main-api']) && $aResult['import-from-main-api'];
- if (isset($aResult['import-file']) && $aResult['import-file'])
- {
- $bModifyXML = true;
- }
+ $sContentURL = '';
if (isset($aResult['import-node']) && $aResult['import-node'])
{
- $bModifyXML = true;
if ($bUseOSMApi)
{
- $sModifyXMLstr = file_get_contents('http://www.openstreetmap.org/api/0.6/node/'.$aResult['import-node']);
+ $sContentURL = 'http://www.openstreetmap.org/api/0.6/node/'.$aResult['import-node'];
}
else
{
- $sModifyXMLstr = file_get_contents('http://overpass-api.de/api/interpreter?data=node('.$aResult['import-node'].');out%20meta;');
+ $sContentURL = 'http://overpass-api.de/api/interpreter?data=node('.$aResult['import-node'].');out%20meta;';
}
}
if (isset($aResult['import-way']) && $aResult['import-way'])
{
- $bModifyXML = true;
if ($bUseOSMApi)
{
- $sCmd = 'http://www.openstreetmap.org/api/0.6/way/'.$aResult['import-way'].'/full';
+ $sContentURL = 'http://www.openstreetmap.org/api/0.6/way/'.$aResult['import-way'].'/full';
}
else
{
- $sCmd = 'http://overpass-api.de/api/interpreter?data=(way('.$aResult['import-way'].');node(w););out%20meta;';
+ $sContentURL = 'http://overpass-api.de/api/interpreter?data=(way('.$aResult['import-way'].');node(w););out%20meta;';
}
- $sModifyXMLstr = file_get_contents($sCmd);
}
if (isset($aResult['import-relation']) && $aResult['import-relation'])
{
- $bModifyXML = true;
if ($bUseOSMApi)
{
- $sModifyXMLstr = file_get_contents('http://www.openstreetmap.org/api/0.6/relation/'.$aResult['import-relation'].'/full');
+ $sContentURLsModifyXMLstr = 'http://www.openstreetmap.org/api/0.6/relation/'.$aResult['import-relation'].'/full';
}
else
{
- $sModifyXMLstr = file_get_contents('http://overpass-api.de/api/interpreter?data=((rel('.$aResult['import-relation'].');way(r);node(w));node(r));out%20meta;');
+ $sContentURL = 'http://overpass-api.de/api/interpreter?data=((rel('.$aResult['import-relation'].');way(r);node(w));node(r));out%20meta;';
}
}
- if ($bModifyXML)
+ if ($sContentURL)
{
- // derive change from normal osm file with osmosis
- $sTemporaryFile = CONST_BasePath.'/data/osmosischange.osc';
- if (isset($aResult['import-file']) && $aResult['import-file'])
+ $sModifyXMLstr = file_get_contents($sContentURL);
+ $bHaveDiff = true;
+
+ $aSpec = array(
+ 0 => array("pipe", "r"), // stdin
+ 1 => array("pipe", "w"), // stdout
+ 2 => array("pipe", "w") // stderr
+ );
+ $sCMD = CONST_Osmosis_Binary.' --read-xml - --read-empty --derive-change --write-xml-change '.$sTemporaryFile;
+ echo $sCMD."\n";
+ $hProc = proc_open($sCMD, $aSpec, $aPipes);
+ if (!is_resource($hProc))
{
- $sCMD = CONST_Osmosis_Binary.' --read-xml \''.$aResult['import-file'].'\' --read-empty --derive-change --write-xml-change '.$sTemporaryFile;
- echo $sCMD."\n";
- exec($sCMD, $sJunk, $iErrorLevel);
- if ($iErrorLevel)
- {
- fail("Error converting osm to osc, osmosis returned: $iErrorLevel\n");
- }
+ fail("Error converting osm to osc, osmosis failed\n");
}
- else
+ fwrite($aPipes[0], $sModifyXMLstr);
+ fclose($aPipes[0]);
+ $sOut = stream_get_contents($aPipes[1]);
+ if ($aResult['verbose']) echo $sOut;
+ fclose($aPipes[1]);
+ $sErrors = stream_get_contents($aPipes[2]);
+ if ($aResult['verbose']) echo $sErrors;
+ fclose($aPipes[2]);
+ if ($iError = proc_close($hProc))
{
- $aSpec = array(
- 0 => array("pipe", "r"), // stdin
- 1 => array("pipe", "w"), // stdout
- 2 => array("pipe", "w") // stderr
- );
- $sCMD = CONST_Osmosis_Binary.' --read-xml - --read-empty --derive-change --write-xml-change '.$sTemporaryFile;
- echo $sCMD."\n";
- $hProc = proc_open($sCMD, $aSpec, $aPipes);
- if (!is_resource($hProc))
- {
- fail("Error converting osm to osc, osmosis failed\n");
- }
- fwrite($aPipes[0], $sModifyXMLstr);
- fclose($aPipes[0]);
- $sOut = stream_get_contents($aPipes[1]);
- if ($aResult['verbose']) echo $sOut;
- fclose($aPipes[1]);
- $sErrors = stream_get_contents($aPipes[2]);
- if ($aResult['verbose']) echo $sErrors;
- fclose($aPipes[2]);
- if ($iError = proc_close($hProc))
- {
- echo "Error converting osm to osc, osmosis returned: $iError\n";
- echo $sOut;
- echo $sErrors;
- exit(-1);
- }
+ echo $sOut;
+ echo $sErrors;
+ fail("Error converting osm to osc, osmosis returned: $iError\n");
}
+ }
+ if ($bHaveDiff)
+ {
// import generated change file
$sCMD = $sOsm2pgsqlCmd.' '.$sTemporaryFile;
echo $sCMD."\n";
if ($aResult['deduplicate'])
{
- $pgver = (float) CONST_Postgresql_Version;
- if ($pgver < 9.3) {
+ if (getPostgresVersion() < 9.3)
+ {
fail("ERROR: deduplicate is only currently supported in postgresql 9.3");
}
- $oDB =& getDB();
- $sSQL = 'select partition from country_name order by country_code';
- $aPartitions = $oDB->getCol($sSQL);
- if (PEAR::isError($aPartitions))
- {
- fail($aPartitions->getMessage());
- }
- $aPartitions[] = 0;
+ $oDB =& getDB();
+ $sSQL = 'select partition from country_name order by country_code';
+ $aPartitions = chksql($oDB->getCol($sSQL));
+ $aPartitions[] = 0;
$sSQL = "select word_token,count(*) from word where substr(word_token, 1, 1) = ' ' and class is null and type is null and country_code is null group by word_token having count(*) > 1 order by word_token";
- $aDuplicateTokens = $oDB->getAll($sSQL);
+ $aDuplicateTokens = chksql($oDB->getAll($sSQL));
foreach($aDuplicateTokens as $aToken)
{
if (trim($aToken['word_token']) == '' || trim($aToken['word_token']) == '-') continue;
echo "Deduping ".$aToken['word_token']."\n";
$sSQL = "select word_id,(select count(*) from search_name where nameaddress_vector @> ARRAY[word_id]) as num from word where word_token = '".$aToken['word_token']."' and class is null and type is null and country_code is null order by num desc";
- $aTokenSet = $oDB->getAll($sSQL);
- if (PEAR::isError($aTokenSet))
- {
- var_dump($aTokenSet, $sSQL);
- exit(1);
- }
+ $aTokenSet = chksql($oDB->getAll($sSQL));
$aKeep = array_shift($aTokenSet);
$iKeepID = $aKeep['word_id'];
$sSQL .= " name_vector = array_replace(name_vector,".$aRemove['word_id'].",".$iKeepID."),";
$sSQL .= " nameaddress_vector = array_replace(nameaddress_vector,".$aRemove['word_id'].",".$iKeepID.")";
$sSQL .= " where name_vector @> ARRAY[".$aRemove['word_id']."]";
- $x = $oDB->query($sSQL);
- if (PEAR::isError($x))
- {
- var_dump($x);
- exit(1);
- }
+ chksql($oDB->query($sSQL));
$sSQL = "update search_name set";
$sSQL .= " nameaddress_vector = array_replace(nameaddress_vector,".$aRemove['word_id'].",".$iKeepID.")";
$sSQL .= " where nameaddress_vector @> ARRAY[".$aRemove['word_id']."]";
- $x = $oDB->query($sSQL);
- if (PEAR::isError($x))
- {
- var_dump($x);
- exit(1);
- }
+ chksql($oDB->query($sSQL));
$sSQL = "update location_area_country set";
$sSQL .= " keywords = array_replace(keywords,".$aRemove['word_id'].",".$iKeepID.")";
$sSQL .= " where keywords @> ARRAY[".$aRemove['word_id']."]";
- $x = $oDB->query($sSQL);
- if (PEAR::isError($x))
- {
- var_dump($x);
- exit(1);
- }
+ chksql($oDB->query($sSQL));
foreach ($aPartitions as $sPartition)
{
$sSQL = "update search_name_".$sPartition." set";
$sSQL .= " name_vector = array_replace(name_vector,".$aRemove['word_id'].",".$iKeepID.")";
$sSQL .= " where name_vector @> ARRAY[".$aRemove['word_id']."]";
- $x = $oDB->query($sSQL);
- if (PEAR::isError($x))
- {
- var_dump($x);
- exit(1);
- }
+ chksql($oDB->query($sSQL));
$sSQL = "update location_area_country set";
$sSQL .= " keywords = array_replace(keywords,".$aRemove['word_id'].",".$iKeepID.")";
$sSQL .= " where keywords @> ARRAY[".$aRemove['word_id']."]";
- $x = $oDB->query($sSQL);
- if (PEAR::isError($x))
- {
- var_dump($x);
- exit(1);
- }
+ chksql($oDB->query($sSQL));
}
$sSQL = "delete from word where word_id = ".$aRemove['word_id'];
- $x = $oDB->query($sSQL);
- if (PEAR::isError($x))
- {
- var_dump($x);
- exit(1);
- }
+ chksql($oDB->query($sSQL));
}
-
}
}
if ($aResult['index'])
{
- if (!isset($aResult['index-instances'])) $aResult['index-instances'] = 1;
- passthru(CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$aResult['index-instances'].' -r '.$aResult['index-rank']);
+ passthru(CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$aResult['index-instances'].' -r '.$aResult['index-rank']);
}
if ($aResult['import-osmosis'] || $aResult['import-osmosis-all'])
}
$sImportFile = CONST_BasePath.'/data/osmosischange.osc';
- $sOsmosisCMD = CONST_Osmosis_Binary;
- $sOsmosisConfigDirectory = CONST_BasePath.'/settings';
- $sCMDDownload = $sOsmosisCMD.' --read-replication-interval workingDirectory='.$sOsmosisConfigDirectory.' --simplify-change --write-xml-change '.$sImportFile;
- $sCMDCheckReplicationLag = $sOsmosisCMD.' -q --read-replication-lag workingDirectory='.$sOsmosisConfigDirectory;
+ $sOsmosisConfigDirectory = CONST_InstallPath.'/settings';
+ $sCMDDownload = CONST_Osmosis_Binary.' --read-replication-interval workingDirectory='.$sOsmosisConfigDirectory.' --simplify-change --write-xml-change '.$sImportFile;
+ $sCMDCheckReplicationLag = CONST_Osmosis_Binary.' -q --read-replication-lag workingDirectory='.$sOsmosisConfigDirectory;
$sCMDImport = $sOsm2pgsqlCmd.' '.$sImportFile;
- $sCMDIndex = $sBasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'];
- if (!$aResult['no-npi']) {
- $sCMDIndex .= '-F ';
- }
+ $sCMDIndex = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$aResult['index-instances'];
+
while(true)
{
$fStartTime = time();
$iFileSize = 1001;
- // Logic behind this is that osm2pgsql locks the database quite a bit
- // So it is better to import lots of small files
- // But indexing works most efficiently on large amounts of data
- // So do lots of small imports and a BIG index
-
- // while($aResult['import-osmosis-all'] && $iFileSize > 1000)
- // {
- if (!file_exists($sImportFile))
+ if (!file_exists($sImportFile))
+ {
+ // First check if there are new updates published (except for minutelies - there's always new diffs to process)
+ if ( CONST_Replication_Update_Interval > 60 )
{
- // First check if there are new updates published (except for minutelies - there's always new diffs to process)
- if ( CONST_Replication_Update_Interval > 60 )
- {
- unset($aReplicationLag);
- exec($sCMDCheckReplicationLag, $aReplicationLag, $iErrorLevel);
- while ($iErrorLevel > 0 || $aReplicationLag[0] < 1)
+ unset($aReplicationLag);
+ exec($sCMDCheckReplicationLag, $aReplicationLag, $iErrorLevel);
+ while ($iErrorLevel > 0 || $aReplicationLag[0] < 1)
+ {
+ if ($iErrorLevel)
{
- if ($iErrorLevel)
- {
- echo "Error: $iErrorLevel. ";
- echo "Re-trying: ".$sCMDCheckReplicationLag." in ".CONST_Replication_Recheck_Interval." secs\n";
- }
- else
- {
- echo ".";
- }
- sleep(CONST_Replication_Recheck_Interval);
- unset($aReplicationLag);
- exec($sCMDCheckReplicationLag, $aReplicationLag, $iErrorLevel);
+ echo "Error: $iErrorLevel. ";
+ echo "Re-trying: ".$sCMDCheckReplicationLag." in ".CONST_Replication_Recheck_Interval." secs\n";
}
- // There are new replication files - use osmosis to download the file
- echo "\n".date('Y-m-d H:i:s')." Replication Delay is ".$aReplicationLag[0]."\n";
- }
- $fStartTime = time();
- $fCMDStartTime = time();
- echo $sCMDDownload."\n";
- exec($sCMDDownload, $sJunk, $iErrorLevel);
- while ($iErrorLevel > 0)
- {
- echo "Error: $iErrorLevel\n";
- sleep(60);
- echo 'Re-trying: '.$sCMDDownload."\n";
- exec($sCMDDownload, $sJunk, $iErrorLevel);
+ else
+ {
+ echo ".";
+ }
+ sleep(CONST_Replication_Recheck_Interval);
+ unset($aReplicationLag);
+ exec($sCMDCheckReplicationLag, $aReplicationLag, $iErrorLevel);
}
- $iFileSize = filesize($sImportFile);
- $sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
- $sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','osmosis')";
- var_Dump($sSQL);
- $oDB->query($sSQL);
- echo date('Y-m-d H:i:s')." Completed osmosis step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
+ // There are new replication files - use osmosis to download the file
+ echo "\n".date('Y-m-d H:i:s')." Replication Delay is ".$aReplicationLag[0]."\n";
}
-
- $iFileSize = filesize($sImportFile);
- $sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
-
- // Import the file
+ $fStartTime = time();
$fCMDStartTime = time();
- echo $sCMDImport."\n";
- exec($sCMDImport, $sJunk, $iErrorLevel);
- if ($iErrorLevel)
+ echo $sCMDDownload."\n";
+ exec($sCMDDownload, $sJunk, $iErrorLevel);
+ while ($iErrorLevel > 0)
{
echo "Error: $iErrorLevel\n";
- exit($iErrorLevel);
+ sleep(60);
+ echo 'Re-trying: '.$sCMDDownload."\n";
+ exec($sCMDDownload, $sJunk, $iErrorLevel);
}
- $sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','osm2pgsql')";
+ $iFileSize = filesize($sImportFile);
+ $sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
+ $sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','osmosis')";
var_Dump($sSQL);
$oDB->query($sSQL);
- echo date('Y-m-d H:i:s')." Completed osm2pgsql step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
+ echo date('Y-m-d H:i:s')." Completed osmosis step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
+ }
- // Archive for debug?
- unlink($sImportFile);
- // }
+ $iFileSize = filesize($sImportFile);
+ $sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
+
+ // Import the file
+ $fCMDStartTime = time();
+ echo $sCMDImport."\n";
+ exec($sCMDImport, $sJunk, $iErrorLevel);
+ if ($iErrorLevel)
+ {
+ echo "Error: $iErrorLevel\n";
+ exit($iErrorLevel);
+ }
+ $sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','osm2pgsql')";
+ var_Dump($sSQL);
+ $oDB->query($sSQL);
+ echo date('Y-m-d H:i:s')." Completed osm2pgsql step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
+
+ // Archive for debug?
+ unlink($sImportFile);
$sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
// Index file
- $sThisIndexCmd = $sCMDIndex;
+ if (!isset($aResult['index-instances']))
+ {
+ if (getLoadAverage() < 24)
+ $iIndexInstances = 2;
+ else
+ $iIndexInstances = 1;
+ } else
+ $iIndexInstances = $aResult['index-instances'];
+
+ $sThisIndexCmd = $sCMDIndex.' -t '.$iIndexInstances;
$fCMDStartTime = time();
- if (!$aResult['no-npi'])
- {
- $iFileID = $oDB->getOne('select nextval(\'file\')');
- if (PEAR::isError($iFileID))
- {
- echo $iFileID->getMessage()."\n";
- exit(-1);
- }
- $sFileDir = CONST_BasePath.'/export/diff/';
- $sFileDir .= str_pad(floor($iFileID/1000000), 3, '0', STR_PAD_LEFT);
- $sFileDir .= '/'.str_pad(floor($iFileID/1000) % 1000, 3, '0', STR_PAD_LEFT);
-
- if (!is_dir($sFileDir)) mkdir($sFileDir, 0777, true);
- $sThisIndexCmd .= $sFileDir;
- $sThisIndexCmd .= '/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT);
- $sThisIndexCmd .= ".npi.out";
-
- preg_match('#^([0-9]{4})-([0-9]{2})-([0-9]{2})#', $sBatchEnd, $aBatchMatch);
- $sFileDir = CONST_BasePath.'/export/index/';
- $sFileDir .= $aBatchMatch[1].'/'.$aBatchMatch[2];
-
- if (!is_dir($sFileDir)) mkdir($sFileDir, 0777, true);
- file_put_contents($sFileDir.'/'.$aBatchMatch[3].'.idx', "$sBatchEnd\t$iFileID\n", FILE_APPEND);
- }
-
if (!$aResult['no-index'])
{
echo "$sThisIndexCmd\n";
echo "Error: $iErrorLevel\n";
exit($iErrorLevel);
}
-
- if (!$aResult['no-npi'])
- {
- $sFileDir = CONST_BasePath.'/export/diff/';
- $sFileDir .= str_pad(floor($iFileID/1000000), 3, '0', STR_PAD_LEFT);
- $sFileDir .= '/'.str_pad(floor($iFileID/1000) % 1000, 3, '0', STR_PAD_LEFT);
-
- $sThisIndexCmd = 'bzip2 -z9 '.$sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.out";
- echo "$sThisIndexCmd\n";
- exec($sThisIndexCmd, $sJunk, $iErrorLevel);
- if ($iErrorLevel)
- {
- echo "Error: $iErrorLevel\n";
- exit($iErrorLevel);
- }
-
- rename($sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.out.bz2",
- $sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.bz2");
- }
}
$sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','index')";
echo date('Y-m-d H:i:s')." Sleeping $iSleep seconds\n";
sleep($iSleep);
}
-
- }
-
- if ($aResult['import-npi-all'])
- {
- $iNPIID = $oDB->getOne('select max(npiid) from import_npi_log');
- if (PEAR::isError($iNPIID))
- {
- var_dump($iNPIID);
- exit(1);
- }
- $sConfigDirectory = CONST_BasePath.'/settings';
- $sCMDImportTemplate = $sBasePath.'/nominatim/nominatim -d gazetteer -P 5433 -I -T '.$sBasePath.'/nominatim/partitionedtags.def -F ';
- while(true)
- {
- $fStartTime = time();
-
- $iNPIID++;
-
- $sImportFile = CONST_BasePath.'/export/diff/';
- $sImportFile .= str_pad(floor($iNPIID/1000000), 3, '0', STR_PAD_LEFT);
- $sImportFile .= '/'.str_pad(floor($iNPIID/1000) % 1000, 3, '0', STR_PAD_LEFT);
- $sImportFile .= '/'.str_pad($iNPIID % 1000, 3, '0', STR_PAD_LEFT);
- $sImportFile .= ".npi";
- while(!file_exists($sImportFile) && !file_exists($sImportFile.'.bz2'))
- {
- echo "sleep (waiting for $sImportFile)\n";
- sleep(10);
- }
- if (file_exists($sImportFile.'.bz2')) $sImportFile .= '.bz2';
-
- $iFileSize = filesize($sImportFile);
-
- // Import the file
- $fCMDStartTime = time();
- $sCMDImport = $sCMDImportTemplate . $sImportFile;
- echo $sCMDImport."\n";
- exec($sCMDImport, $sJunk, $iErrorLevel);
- if ($iErrorLevel)
- {
- fail("Error: $iErrorLevel\n");
- }
- $sBatchEnd = $iNPIID;
- echo "Completed for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
- $sSQL = "INSERT INTO import_npi_log values ($iNPIID, null, $iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','import')";
- var_Dump($sSQL);
- $oDB->query($sSQL);
- }
-
}
function getosmosistimestamp($sOsmosisConfigDirectory)
<?php
@define('CONST_ConnectionBucket_PageType', 'Reverse');
- require_once(dirname(dirname(__FILE__)).'/lib/init-website.php');
+ require_once(dirname(dirname(__FILE__)).'/settings/settings.php');
+ require_once(CONST_BasePath.'/lib/init-website.php');
require_once(CONST_BasePath.'/lib/log.php');
require_once(CONST_BasePath.'/lib/PlaceLookup.php');
require_once(CONST_BasePath.'/lib/ReverseGeocode.php');
-
- $bAsPoints = false;
- $bAsGeoJSON = (boolean)isset($_GET['polygon_geojson']) && $_GET['polygon_geojson'];
- $bAsKML = (boolean)isset($_GET['polygon_kml']) && $_GET['polygon_kml'];
- $bAsSVG = (boolean)isset($_GET['polygon_svg']) && $_GET['polygon_svg'];
- $bAsText = (boolean)isset($_GET['polygon_text']) && $_GET['polygon_text'];
- if ( ( ($bAsGeoJSON?1:0)
- + ($bAsKML?1:0)
- + ($bAsSVG?1:0)
- + ($bAsText?1:0)
- + ($bAsPoints?1:0)
- ) > CONST_PolygonOutput_MaximumTypes)
+ require_once(CONST_BasePath.'/lib/output.php');
+
+ $bAsGeoJSON = getParamBool('polygon_geojson');
+ $bAsKML = getParamBool('polygon_kml');
+ $bAsSVG = getParamBool('polygon_svg');
+ $bAsText = getParamBool('polygon_text');
+ if ((($bAsGeoJSON?1:0) + ($bAsKML?1:0) + ($bAsSVG?1:0)
+ + ($bAsText?1:0)) > CONST_PolygonOutput_MaximumTypes)
{
if (CONST_PolygonOutput_MaximumTypes)
{
// Polygon simplification threshold (optional)
- $fThreshold = 0.0;
- if (isset($_GET['polygon_threshold'])) $fThreshold = (float)$_GET['polygon_threshold'];
+ $fThreshold = getParamFloat('polygon_threshold', 0.0);
$oDB =& getDB();
ini_set('memory_limit', '200M');
// Format for output
- $sOutputFormat = 'xml';
- if (isset($_GET['format']) && ( $_GET['format'] == 'html' || $_GET['format'] == 'xml' || $_GET['format'] == 'json' || $_GET['format'] == 'jsonv2'))
- {
- $sOutputFormat = $_GET['format'];
- }
+ $sOutputFormat = getParamSet('format', array('html', 'xml', 'json', 'jsonv2'), 'xml');
// Preferred language
$aLangPrefOrder = getPreferredLanguages();
$hLog = logStart($oDB, 'reverse', $_SERVER['QUERY_STRING'], $aLangPrefOrder);
- if (isset($_GET['osm_type']) && isset($_GET['osm_id']) && (int)$_GET['osm_id'] && ($_GET['osm_type'] == 'N' || $_GET['osm_type'] == 'W' || $_GET['osm_type'] == 'R'))
+ $oPlaceLookup = new PlaceLookup($oDB);
+ $oPlaceLookup->setLanguagePreference($aLangPrefOrder);
+ $oPlaceLookup->setIncludeAddressDetails(getParamBool('addressdetails', true));
+ $oPlaceLookup->setIncludeExtraTags(getParamBool('extratags', false));
+ $oPlaceLookup->setIncludeNameDetails(getParamBool('namedetails', false));
+
+ $sOsmType = getParamSet('osm_type', array('N', 'W', 'R'));
+ $iOsmId = getParamInt('osm_id', -1);
+ $fLat = getParamFloat('lat');
+ $fLon = getParamFloat('lon');
+ if ($sOsmType && $iOsmId > 0)
{
- $aLookup = array('osm_type' => $_GET['osm_type'], 'osm_id' => $_GET['osm_id']);
+ $aPlace = $oPlaceLookup->lookupOSMID($sOsmType, $iOsmId);
}
- else if (isset($_GET['lat']) && isset($_GET['lon']) && preg_match('/^[+-]?[0-9]*\.?[0-9]+$/', $_GET['lat']) && preg_match('/^[+-]?[0-9]*\.?[0-9]+$/', $_GET['lon']))
+ else if ($fLat !== false && $fLon !== false)
{
$oReverseGeocode = new ReverseGeocode($oDB);
- $oReverseGeocode->setLanguagePreference($aLangPrefOrder);
-
- $oReverseGeocode->setLatLon($_GET['lat'], $_GET['lon']);
- $oReverseGeocode->setZoom(@$_GET['zoom']);
+ $oReverseGeocode->setZoom(getParamInt('zoom', 18));
- $aLookup = $oReverseGeocode->lookup();
+ $aLookup = $oReverseGeocode->lookup($fLat, $fLon);
if (CONST_Debug) var_dump($aLookup);
+
+ $aPlace = $oPlaceLookup->lookup((int)$aLookup['place_id'],
+ $aLookup['type'], $aLookup['fraction']);
}
- else
+ else if ($sOutputFormat != 'html')
{
- $aLookup = null;
+ userError("Need coordinates or OSM object to lookup.");
}
- if ($aLookup)
+ if ($aPlace)
{
- $oPlaceLookup = new PlaceLookup($oDB);
- $oPlaceLookup->setLanguagePreference($aLangPrefOrder);
- $oPlaceLookup->setIncludeAddressDetails(getParamBool('addressdetails', true));
- $oPlaceLookup->setIncludeExtraTags(getParamBool('extratags', false));
- $oPlaceLookup->setIncludeNameDetails(getParamBool('namedetails', false));
-
- $aPlace = $oPlaceLookup->lookupPlace($aLookup);
-
- $oPlaceLookup->setIncludePolygonAsPoints($bAsPoints);
+ $oPlaceLookup->setIncludePolygonAsPoints(false);
$oPlaceLookup->setIncludePolygonAsText($bAsText);
$oPlaceLookup->setIncludePolygonAsGeoJSON($bAsGeoJSON);
$oPlaceLookup->setIncludePolygonAsKML($bAsKML);
$oPlaceLookup->setPolygonSimplificationThreshold($fThreshold);
$fRadius = $fDiameter = getResultDiameter($aPlace);
- $aOutlineResult = $oPlaceLookup->getOutlines($aPlace['place_id'], $aPlace['lon'], $aPlace['lat'], $fRadius);
+ $aOutlineResult = $oPlaceLookup->getOutlines($aPlace['place_id'],
+ $aPlace['lon'], $aPlace['lat'],
+ $fRadius);
if ($aOutlineResult)
{
$aPlace = array_merge($aPlace, $aOutlineResult);
}
}
- else
- {
- $aPlace = null;
- }
+ logEnd($oDB, $hLog, sizeof($aPlace)?1:0);
if (CONST_Debug)
{
if ($sOutputFormat=='html')
{
- $sDataDate = $oDB->getOne("select TO_CHAR(lastimportdate - '2 minutes'::interval,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1");
+ $sDataDate = chksql($oDB->getOne("select TO_CHAR(lastimportdate - '2 minutes'::interval,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1"));
$sTileURL = CONST_Map_Tile_URL;
$sTileAttribution = CONST_Map_Tile_Attribution;
}
projects / nominatim.git / commitdiff