if (30 >= $this->iMinAddressRank && 30 <= $this->iMaxAddressRank)
{
+ //only Tiger housenumbers and interpolation lines need to be interpolated, because they are saved as lines
+ // with start- and endnumber, the common osm housenumbers are usually saved as points
+ $sHousenumbers = "";
+ $i = 0;
+ $length = count($aPlaceIDs);
+ foreach($aPlaceIDs as $placeID => $housenumber)
+ {
+ $i++;
+ $sHousenumbers .= "(".$placeID.", ".$housenumber.")";
+ if($i<$length)
+ $sHousenumbers .= ", ";
+ }
if (CONST_Use_US_Tiger_Data)
{
- //query also location_property_tiger and location_property_aux
//Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
- //only Tiger housenumbers need to be interpolated, because they are saved as lines with start- and endnumber, the common osm housenumbers are usually saved as points
- $sHousenumbers = "";
- $i = 0;
- $length = count($aPlaceIDs);
- foreach($aPlaceIDs as $placeID => $housenumber)
- {
- $i++;
- $sHousenumbers .= "(".$placeID.", ".$housenumber.")";
- if($i<$length)
- $sHousenumbers .= ", ";
- }
-
- $sSQL .= "union ";
- $sSQL .= "select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code";
+ $sSQL .= " union";
+ $sSQL .= " select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code";
$sSQL .= ", get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress ";
$sSQL .= ", null as placename";
$sSQL .= ", null as ref";
$sSQL .= ", null as extra_place ";
$sSQL .= " from (select place_id";
//interpolate the Tiger housenumbers here
- $sSQL .= ", ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) as centroid, parent_place_id, housenumber_for_place ";
- $sSQL .= "from (location_property_tiger ";
+ $sSQL .= ", ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) as centroid, parent_place_id, housenumber_for_place";
+ $sSQL .= " from (location_property_tiger ";
$sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) ";
$sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here
$sSQL .= " group by place_id, housenumber_for_place"; //is this group by really needed?, place_id + housenumber (in combination) are unique
if (!$this->bDeDupe) $sSQL .= ", place_id ";
}
+ // osmline
+ // interpolation line search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
+ $sSQL .= " union ";
+ $sSQL .= "select 'W' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, calculated_country_code as country_code, ";
+ $sSQL .= "get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress, ";
+ $sSQL .= "null as placename, ";
+ $sSQL .= "null as ref, ";
+ if ($this->bIncludeExtraTags) $sSQL .= "null as extra, ";
+ if ($this->bIncludeNameDetails) $sSQL .= "null as names, ";
+ $sSQL .= " avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,";
+ $sSQL .= $sImportanceSQL."-0.1 as importance, "; // slightly smaller than the importance for normal houses with rank 30, which is 0
+ $sSQL .= " (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p";
+ $sSQL .= " where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance,";
+ $sSQL .= " null as extra_place ";
+ $sSQL .= " from (select place_id, calculated_country_code ";
+ //interpolate the housenumbers here
+ $sSQL .= ", CASE WHEN startnumber != endnumber THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) ";
+ $sSQL .= " ELSE ST_LineInterpolatePoint(linegeo, 0.5) END as centroid";
+ $sSQL .= ", parent_place_id, housenumber_for_place ";
+ $sSQL .= " from (location_property_osmline ";
+ $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) ";
+ $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here
+ $sSQL .= " group by place_id, housenumber_for_place, calculated_country_code "; //is this group by really needed?, place_id + housenumber (in combination) are unique
+ if (!$this->bDeDupe) $sSQL .= ", place_id ";
if (CONST_Use_Aux_Location_data)
{
// Do we have anything that looks like a lat/lon pair?
if ( $aLooksLike = looksLikeLatLonPair($sQuery) )
- {
+ {
$this->setNearPoint(array($aLooksLike['lat'], $aLooksLike['lon']));
$sQuery = $aLooksLike['query'];
}
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)
//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);
+
+ // 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 (sizeof($this->aExcludePlaceIDs))
+ {
+ $sSQL .= " and parent_place_id not in (".join(',', $this->aExcludePlaceIDs).")";
+ }
+ //$sSQL .= " limit $this->iLimit";
+ if (CONST_Debug) var_dump($sSQL);
+ //get place IDs
+ $aPlaceIDs = $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);
}
+
//if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger)
- $searchedHousenumber = intval($aSearch['sHouseNumber']);
if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs))
{
//new query for lines, not housenumbers anymore
//set to -1, if no housenumbers were found
$searchedHousenumber = -1;
}
- //else: housenumber was found, remains saved in searchedHousenumber
+ //else: housenumber was found, remains saved in searchedHousenumber
}
{
// Need to verify passes rank limits before dropping out of the loop (yuk!)
// reduces the number of place ids, like a filter
+ // rank_address is 30 for interpolated housenumbers
$sSQL = "select place_id from placex where place_id in (".join(',',array_keys($aResultPlaceIDs)).") ";
$sSQL .= "and (placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank ";
if (14 >= $this->iMinAddressRank && 14 <= $this->iMaxAddressRank) $sSQL .= " OR (extratags->'place') = 'city'";
$sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank ";
if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',',$this->aAddressRankList).")";
}
- $sSQL .= ")";
+ $sSQL .= ") UNION select place_id from location_property_osmline where place_id in (".join(',',array_keys($aResultPlaceIDs)).")";
+ $sSQL .= " and (30 between $this->iMinAddressRank and $this->iMaxAddressRank)";
if (CONST_Debug) var_dump($sSQL);
$aFilteredPlaceIDs = $this->oDB->getCol($sSQL);
$tempIDs = array();
foreach($aFilteredPlaceIDs as $placeID)
- {
+ {
$tempIDs[$placeID] = $aResultPlaceIDs[$placeID]; //assign housenumber to placeID
}
$aResultPlaceIDs = $tempIDs;
{
$this->setOSMID($details['osm_type'], $details['osm_id']);
}
- if (isset($details['fraction'])) $this->fTigerFraction = $details['fraction'];
+ if (isset($details['fraction'])) $this->fInterpolFraction = $details['fraction'];
return $this->lookup();
}
if ($this->bNameDetails) $sSQL .= " null as names,";
$sSQL .= " ST_X(point) as lon, ST_Y(point) as lat from (select *, ST_LineInterpolatePoint(linegeo, (housenumber-startnumber::float)/(endnumber-startnumber)::float) as point from ";
$sSQL .= " (select *, ";
- $sSQL .= " CASE WHEN interpolationtype='odd' THEN floor((".$this->fTigerFraction."*(endnumber-startnumber)+startnumber)/2)::int*2+1";
- $sSQL .= " WHEN interpolationtype='even' THEN ((".$this->fTigerFraction."*(endnumber-startnumber)+startnumber+1)/2)::int*2";
- $sSQL .= " WHEN interpolationtype='all' THEN (".$this->fTigerFraction."*(endnumber-startnumber)+startnumber)::int";
+ $sSQL .= " CASE WHEN interpolationtype='odd' THEN floor((".$this->fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2+1";
+ $sSQL .= " WHEN interpolationtype='even' THEN ((".$this->fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2";
+ $sSQL .= " WHEN interpolationtype='all' THEN (".$this->fInterpolFraction."*(endnumber-startnumber)+startnumber)::int";
$sSQL .= " END as housenumber";
$sSQL .= " from location_property_tiger where place_id = ".(int)$this->iPlaceID.") as blub1) as blub2";
}
+ else if ($this->sType == 'interpolation')
+ {
+ $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level, housenumber, null as street, null as isin, postcode,";
+ $sSQL .= " calculated_country_code as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
+ $sSQL .= " (0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, calculated_country_code, ";
+ $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";
+ $sSQL .= " null as placename,";
+ $sSQL .= " null as ref,";
+ if ($this->bExtraTags) $sSQL .= " null as extra,";
+ if ($this->bNameDetails) $sSQL .= " null as names,";
+ $sSQL .= " ST_X(point) as lon, ST_Y(point) as lat from (select *, ST_LineInterpolatePoint(linegeo, (housenumber-startnumber::float)/(endnumber-startnumber)::float) as point from ";
+ $sSQL .= " (select *, ";
+ $sSQL .= " CASE WHEN interpolationtype='odd' THEN floor((".$this->fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2+1";
+ $sSQL .= " WHEN interpolationtype='even' THEN ((".$this->fInterpolFraction."*(endnumber-startnumber)+startnumber)/2)::int*2";
+ $sSQL .= " WHEN interpolationtype='all' THEN (".$this->fInterpolFraction."*(endnumber-startnumber)+startnumber)::int";
+ $sSQL .= " END as housenumber";
+ $sSQL .= " from location_property_osmline where place_id = ".(int)$this->iPlaceID.") as blub1) as blub2";
+ // testcase: interpolationtype=odd, startnumber=1000, endnumber=1006, fInterpolFraction=1 => housenumber=1007 => error in st_lineinterpolatepoint
+ // but this will never happen, because if the searched point is that close to the endnumber, the endnumber house will be directly taken from placex (in ReverseGeocode.php line 220)
+ // and not interpolated
+ }
else
{
$sSQL = "select placex.place_id, partition, osm_type, osm_id, class, type, admin_level, housenumber, street, isin, postcode, country_code, parent_place_id, linked_place_id, rank_address, rank_search, ";
if ($this->bAddressDetails)
{
- if(CONST_Use_US_Tiger_Data && $this->sType == 'tiger') // to get addressdetails for tiger data, the housenumber is needed
+ if(CONST_Use_US_Tiger_Data && $this->sType == 'tiger' || $this->sType == 'interpolation') // to get addressdetails for tiger data, the housenumber is needed
$aAddress = $this->getAddressNames($aPlace['housenumber']);
else
$aAddress = $this->getAddressNames();
$fMaxAreaDistance = 1;
$bIsInUnitedStates = false;
$bPlaceIsTiger = false;
+ $bPlaceIsLine = false;
while(!$iPlaceID && $fSearchDiam < $fMaxAreaDistance)
{
$fSearchDiam = $fSearchDiam * 2;
$iParentPlaceID = $aPlace['parent_place_id'];
$bIsInUnitedStates = ($aPlace['calculated_country_code'] == 'us');
}
+ // if a street or house was found, look in interpolation lines table
+ if ($iMaxRank_orig >= 28 && $aPlace && $aPlace['rank_search'] >= 26)
+ {
+ // if a house was found, search the interpolation line that is at least as close as the house
+ $sSQL = 'SELECT place_id, parent_place_id, 30 as rank_search, ST_line_locate_point(linegeo,'.$sPointSQL.') as fraction';
+ $sSQL .= ' FROM location_property_osmline';
+ $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.')';
+ $sSQL .= ' and indexed_status = 0 ';
+ $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', linegeo) ASC limit 1';
+
+ if (CONST_Debug)
+ {
+ $sSQL = preg_replace('/limit 1/', 'limit 100', $sSQL);
+ var_dump($sSQL);
+ $aAllHouses = $this->oDB->getAll($sSQL);
+ foreach($aAllHouses as $i)
+ {
+ echo $i['housenumber'] . ' | ' . $i['distance'] * 1000 . ' | ' . $i['lat'] . ' | ' . $i['lon']. ' | '. "<br>\n";
+ }
+ }
+ $aPlaceLine = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aPlaceLine))
+ {
+ failInternalError("Could not determine closest housenumber on an osm interpolation line.", $sSQL, $aPlaceLine);
+ }
+ if ($aPlaceLine)
+ {
+ if (CONST_Debug) var_dump('found housenumber in interpolation lines table', $aPlaceLine);
+ if ($aPlace['rank_search'] == 30)
+ {
+ // if a house was already found in placex, we have to find out,
+ // if the placex house or the interpolated house are closer to the searched point
+ // distance between point and placex house
+ $sSQL = 'SELECT ST_distance('.$sPointSQL.', house.geometry) as distance FROM placex as house WHERE house.place_id='.$iPlaceID;
+ $aDistancePlacex = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aDistancePlacex))
+ {
+ failInternalError("Could not determine distance between searched point and placex house.", $sSQL, $aDistancePlacex);
+ }
+ $fDistancePlacex = $aDistancePlacex['distance'];
+ // distance between point and interpolated house (fraction on interpolation line)
+ $sSQL = 'SELECT ST_distance('.$sPointSQL.', ST_LineInterpolatePoint(linegeo, '.$aPlaceLine['fraction'].')) as distance';
+ $sSQL .= ' FROM location_property_osmline WHERE place_id = '.$aPlaceLine['place_id'];
+ $aDistanceInterpolation = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aDistanceInterpolation))
+ {
+ failInternalError("Could not determine distance between searched point and interpolated house.", $sSQL, $aDistanceInterpolation);
+ }
+ $fDistanceInterpolation = $aDistanceInterpolation['distance'];
+ if ($fDistanceInterpolation < $fDistancePlacex)
+ {
+ // interpolation is closer to point than placex house
+ $bPlaceIsLine = true;
+ $aPlace = $aPlaceLine;
+ $iPlaceID = $aPlaceLine['place_id'];
+ $iParentPlaceID = $aPlaceLine['parent_place_id']; // the street
+ $fFraction = $aPlaceLine['fraction'];
+ }
+ // else: nothing to do, take placex house from above
+ }
+ else
+ {
+ $bPlaceIsLine = true;
+ $aPlace = $aPlaceLine;
+ $iPlaceID = $aPlaceLine['place_id'];
+ $iParentPlaceID = $aPlaceLine['parent_place_id']; // the street
+ $fFraction = $aPlaceLine['fraction'];
+ }
+ }
+ }
+
// Only street found? If it's in the US we can check TIGER data for nearest housenumber
if (CONST_Use_US_Tiger_Data && $bIsInUnitedStates && $iMaxRank_orig >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 ))
{
}
$aPlaceTiger = $this->oDB->getRow($sSQL);
- if (PEAR::IsError($aPlace))
+ if (PEAR::IsError($aPlaceTiger))
{
failInternalError("Could not determine closest Tiger place.", $sSQL, $aPlaceTiger);
}
if ($aPlaceTiger)
{
- if (CONST_Debug) var_dump('found Tiger place', $aPlaceTiger);
+ if (CONST_Debug) var_dump('found Tiger housenumber', $aPlaceTiger);
$bPlaceIsTiger = true;
$aPlace = $aPlaceTiger;
$iPlaceID = $aPlaceTiger['place_id'];
$iParentPlaceID = $aPlaceTiger['parent_place_id']; // the street
- $iFraction = $aPlaceTiger['fraction'];
+ $fFraction = $aPlaceTiger['fraction'];
}
}
// The point we found might be too small - use the address to find what it is a child of
if ($iPlaceID && $iMaxRank < 28)
{
- if ($aPlace['rank_search'] > 28 && $iParentPlaceID && !$bPlaceIsTiger)
+ if (($aPlace['rank_search'] > 28 || $bPlaceIsTiger || $bPlaceIsLine) && $iParentPlaceID)
{
$iPlaceID = $iParentPlaceID;
}
$iPlaceID = $aPlace['place_id'];
}
}
-
return array('place_id' => $iPlaceID,
- 'type' => $bPlaceIsTiger ? 'tiger' : 'osm',
- 'fraction' => $bPlaceIsTiger ? $iFraction : -1);
+ 'type' => $bPlaceIsTiger ? 'tiger' : $bPlaceIsLine ? 'interpolation' : 'osm',
+ 'fraction' => ($bPlaceIsTiger || $bPlaceIsLine) ? $fFraction : -1);
}
}
{
if (isset($aPlace['place_id'])) $aFilteredPlaces['place_id'] = $aPlace['place_id'];
$aFilteredPlaces['licence'] = "Data © OpenStreetMap contributors, ODbL 1.0. http://www.openstreetmap.org/copyright";
- $sOSMType = ($aPlace['osm_type'] == 'N'?'node':($aPlace['osm_type'] == 'W'?'way':($aPlace['osm_type'] == 'R'?'relation':'')));
+ $sOSMType = ($aPlace['osm_type'] == 'N'?'node':($aPlace['osm_type'] == 'W'?'way':($aPlace['osm_type'] == 'R'?'relation':
+ ($aPlace['osm_type'] == 'T'?'tiger':($aPlace['osm_type'] == 'I'?'interpolation':'')))));
if ($sOSMType)
{
$aFilteredPlaces['osm_type'] = $sOSMType;
{
if ($aPlace['place_id']) $aFilteredPlaces['place_id'] = $aPlace['place_id'];
$aFilteredPlaces['licence'] = "Data © OpenStreetMap contributors, ODbL 1.0. http://www.openstreetmap.org/copyright";
- $sOSMType = ($aPlace['osm_type'] == 'N'?'node':($aPlace['osm_type'] == 'W'?'way':($aPlace['osm_type'] == 'R'?'relation':'')));
+ $sOSMType = ($aPlace['osm_type'] == 'N'?'node':($aPlace['osm_type'] == 'W'?'way':($aPlace['osm_type'] == 'R'?'relation':
+ ($aPlace['osm_type'] == 'T'?'tiger':($aPlace['osm_type'] == 'I'?'interpolation':'')))));
if ($sOSMType)
{
$aFilteredPlaces['osm_type'] = $sOSMType;
{
echo "<result";
if ($aPlace['place_id']) echo ' place_id="'.$aPlace['place_id'].'"';
- $sOSMType = ($aPlace['osm_type'] == 'N'?'node':($aPlace['osm_type'] == 'W'?'way':($aPlace['osm_type'] == 'R'?'relation':'')));
+ $sOSMType = ($aPlace['osm_type'] == 'N'?'node':($aPlace['osm_type'] == 'W'?'way':($aPlace['osm_type'] == 'R'?'relation':
+ ($aPlace['osm_type'] == 'T'?'tiger':($aPlace['osm_type'] == 'I'?'interpolation':'')))));
if ($sOSMType) echo ' osm_type="'.$sOSMType.'"'.' osm_id="'.$aPlace['osm_id'].'"';
if ($aPlace['ref']) echo ' ref="'.htmlspecialchars($aPlace['ref']).'"';
if (isset($aPlace['lat'])) echo ' lat="'.htmlspecialchars($aPlace['lat']).'"';
'licence'=>"Data © OpenStreetMap contributors, ODbL 1.0. http://www.openstreetmap.org/copyright",
);
- $sOSMType = ($aPointDetails['osm_type'] == 'N'?'node':($aPointDetails['osm_type'] == 'W'?'way':($aPointDetails['osm_type'] == 'R'?'relation':($aPointDetails['osm_type'] == 'T'?'tiger':''))));
+ $sOSMType = ($aPointDetails['osm_type'] == 'N'?'node':($aPointDetails['osm_type'] == 'W'?'way':($aPointDetails['osm_type'] == 'R'?
+ 'relation':($aPointDetails['osm_type'] == 'T'?'tiger':($aPointDetails['osm_type'] == 'I'?'interpolation':'')))));
if ($sOSMType)
{
$aPlace['osm_type'] = $sOSMType;
'licence'=>"Data © OpenStreetMap contributors, ODbL 1.0. http://www.openstreetmap.org/copyright",
);
- $sOSMType = ($aPointDetails['osm_type'] == 'N'?'node':($aPointDetails['osm_type'] == 'W'?'way':($aPointDetails['osm_type'] == 'R'?'relation':($aPointDetails['osm_type'] == 'T'?'tiger':''))));
+ $sOSMType = ($aPointDetails['osm_type'] == 'N'?'node':($aPointDetails['osm_type'] == 'W'?'way':($aPointDetails['osm_type'] == 'R'?'relation':($aPointDetails['osm_type'] == 'T'?'tiger':($aPointDetails['osm_type'] == 'I'?'interpolation':'')))));
if ($sOSMType)
{
$aPlace['osm_type'] = $sOSMType;
foreach($aSearchResults as $iResNum => $aResult)
{
echo "<place place_id='".$aResult['place_id']."'";
- $sOSMType = ($aResult['osm_type'] == 'N'?'node':($aResult['osm_type'] == 'W'?'way':($aResult['osm_type'] == 'R'?'relation':($aResult['osm_type'] == 'T'?'tiger':''))));
+ $sOSMType = ($aResult['osm_type'] == 'N'?'node':($aResult['osm_type'] == 'W'?'way':($aResult['osm_type'] == 'R'?'relation':
+ ($aResult['osm_type'] == 'T'?'tiger':($aResult['osm_type'] == 'I'?'interpolation':'')))));
if ($sOSMType)
{
echo " osm_type='$sOSMType'";
/*
+ * triggers indexing (reparenting etc.) through setting resetting indexed_status: update placex/osmline set indexed_status = 0 where indexed_status > 0
+ * triggers placex_update and osmline_update
*/
#include <stdio.h>
extern int verbose;
-void nominatim_index(int rank_min, int rank_max, int num_threads, const char *conninfo, const char *structuredoutputfile)
+void run_indexing(int rank, int interpolation, PGconn *conn, int num_threads,
+struct index_thread_data * thread_data, const char *structuredoutputfile)
{
- struct index_thread_data * thread_data;
- pthread_mutex_t count_mutex = PTHREAD_MUTEX_INITIALIZER;
int tuples, count, sleepcount;
-
+ pthread_mutex_t count_mutex = PTHREAD_MUTEX_INITIALIZER;
+
time_t rankStartTime;
int rankTotalTuples;
int rankCountTuples;
float rankPerSecond;
-
- PGconn *conn;
- PGresult * res;
+
PGresult * resSectors;
PGresult * resPlaces;
PGresult * resNULL;
-
- int rank;
+
int i;
int iSector;
int iResult;
-
+
const char *paramValues[2];
int paramLengths[2];
int paramFormats[2];
uint32_t paramRank;
uint32_t paramSector;
uint32_t sector;
+
+ xmlTextWriterPtr writer;
+ pthread_mutex_t writer_mutex = PTHREAD_MUTEX_INITIALIZER;
+
+ // Create the output file
+ writer = NULL;
+ if (structuredoutputfile)
+ {
+ writer = nominatim_exportXMLStart(structuredoutputfile);
+ }
+
+ if (interpolation)
+ {
+ fprintf(stderr, "Starting interpolation lines (location_property_osmline)\n");
+ }
+ else
+ {
+ fprintf(stderr, "Starting rank %d\n", rank);
+ }
+
+ rankCountTuples = 0;
+ rankPerSecond = 0;
+
+ paramRank = PGint32(rank);
+ paramValues[0] = (char *)¶mRank;
+ paramLengths[0] = sizeof(paramRank);
+ paramFormats[0] = 1;
+
+ if (interpolation)
+ {
+ resSectors = PQexecPrepared(conn, "index_sectors_osmline", 0, NULL, 0, NULL, 1);
+ }
+ else
+ {
+ resSectors = PQexecPrepared(conn, "index_sectors", 1, paramValues, paramLengths, paramFormats, 1);
+ }
+ if (PQresultStatus(resSectors) != PGRES_TUPLES_OK)
+ {
+ fprintf(stderr, "index_sectors: SELECT failed: %s", PQerrorMessage(conn));
+ PQclear(resSectors);
+ exit(EXIT_FAILURE);
+ }
+ if (PQftype(resSectors, 0) != PG_OID_INT4)
+ {
+ fprintf(stderr, "Sector value has unexpected type\n");
+ PQclear(resSectors);
+ exit(EXIT_FAILURE);
+ }
+ if (PQftype(resSectors, 1) != PG_OID_INT8)
+ {
+ fprintf(stderr, "Sector value has unexpected type\n");
+ PQclear(resSectors);
+ exit(EXIT_FAILURE);
+ }
+
+ rankTotalTuples = 0;
+ for (iSector = 0; iSector < PQntuples(resSectors); iSector++)
+ {
+ rankTotalTuples += PGint64(*((uint64_t *)PQgetvalue(resSectors, iSector, 1)));
+ }
+
+ rankStartTime = time(0);
+ for (iSector = 0; iSector <= PQntuples(resSectors); iSector++)
+ {
+ if (iSector > 0)
+ {
+ resPlaces = PQgetResult(conn);
+ if (PQresultStatus(resPlaces) != PGRES_TUPLES_OK)
+ {
+ fprintf(stderr, "index_sector_places: SELECT failed: %s", PQerrorMessage(conn));
+ PQclear(resPlaces);
+ exit(EXIT_FAILURE);
+ }
+ if (PQftype(resPlaces, 0) != PG_OID_INT8)
+ {
+ fprintf(stderr, "Place_id value has unexpected type\n");
+ PQclear(resPlaces);
+ exit(EXIT_FAILURE);
+ }
+ resNULL = PQgetResult(conn);
+ if (resNULL != NULL)
+ {
+ fprintf(stderr, "Unexpected non-null response\n");
+ exit(EXIT_FAILURE);
+ }
+ }
+
+ if (iSector < PQntuples(resSectors))
+ {
+ sector = PGint32(*((uint32_t *)PQgetvalue(resSectors, iSector, 0)));
+// fprintf(stderr, "\n Starting sector %d size %ld\n", sector, PGint64(*((uint64_t *)PQgetvalue(resSectors, iSector, 1))));
+
+ // Get all the place_id's for this sector
+ paramRank = PGint32(rank);
+ paramSector = PGint32(sector);
+ if (rankTotalTuples-rankCountTuples < num_threads*1000)
+ {
+ // no sectors
+ if (interpolation)
+ {
+ iResult = PQsendQueryPrepared(conn, "index_nosector_places_osmline", 0, NULL, 0, NULL, 1);
+ }
+ else
+ {
+ paramValues[0] = (char *)¶mRank;
+ paramLengths[0] = sizeof(paramRank);
+ paramFormats[0] = 1;
+ iResult = PQsendQueryPrepared(conn, "index_nosector_places", 1, paramValues, paramLengths, paramFormats, 1);
+ }
+ }
+ else
+ {
+ if (interpolation)
+ {
+ iResult = PQsendQueryPrepared(conn, "index_sector_places_osmline", 1, paramValues, paramLengths, paramFormats, 1);
+ paramValues[0] = (char *)¶mSector;
+ paramLengths[0] = sizeof(paramSector);
+ paramFormats[0] = 1;
+ }
+ else
+ {
+ paramValues[0] = (char *)¶mRank;
+ paramLengths[0] = sizeof(paramRank);
+ paramFormats[0] = 1;
+ paramValues[1] = (char *)¶mSector;
+ paramLengths[1] = sizeof(paramSector);
+ paramFormats[1] = 1;
+ iResult = PQsendQueryPrepared(conn, "index_sector_places", 2, paramValues, paramLengths, paramFormats, 1);
+ }
+ }
+ if (!iResult)
+ {
+ fprintf(stderr, "index_sector_places: SELECT failed: %s", PQerrorMessage(conn));
+ PQclear(resPlaces);
+ exit(EXIT_FAILURE);
+ }
+ }
+ if (iSector > 0)
+ {
+ count = 0;
+ rankPerSecond = 0;
+ tuples = PQntuples(resPlaces);
+
+ if (tuples > 0)
+ {
+ // Spawn threads
+ for (i = 0; i < num_threads; i++)
+ {
+ thread_data[i].res = resPlaces;
+ thread_data[i].tuples = tuples;
+ thread_data[i].count = &count;
+ thread_data[i].count_mutex = &count_mutex;
+ thread_data[i].writer = writer;
+ thread_data[i].writer_mutex = &writer_mutex;
+ if (interpolation)
+ {
+ thread_data[i].table = 0; // use interpolations table
+ }
+ else
+ {
+ thread_data[i].table = 1; // use placex table
+ }
+ pthread_create(&thread_data[i].thread, NULL, &nominatim_indexThread, (void *)&thread_data[i]);
+ }
+
+ // Monitor threads to give user feedback
+ sleepcount = 0;
+ while (count < tuples)
+ {
+ usleep(1000);
+
+ // Aim for one update per second
+ if (sleepcount++ > 500)
+ {
+ rankPerSecond = ((float)rankCountTuples + (float)count) / MAX(difftime(time(0), rankStartTime),1);
+ if(interpolation)
+ {
+ fprintf(stderr, " Done %i in %i @ %f per second - Interpolation lines ETA (seconds): %f\n", (rankCountTuples + count), (int)(difftime(time(0), rankStartTime)), rankPerSecond, ((float)(rankTotalTuples - (rankCountTuples + count)))/rankPerSecond);
+ }
+ else
+ {
+ fprintf(stderr, " Done %i in %i @ %f per second - Rank %i ETA (seconds): %f\n", (rankCountTuples + count), (int)(difftime(time(0), rankStartTime)), rankPerSecond, rank, ((float)(rankTotalTuples - (rankCountTuples + count)))/rankPerSecond);
+ }
+
+ sleepcount = 0;
+ }
+ }
+
+ // Wait for everything to finish
+ for (i = 0; i < num_threads; i++)
+ {
+ pthread_join(thread_data[i].thread, NULL);
+ }
+
+ rankCountTuples += tuples;
+ }
+
+ // Finished sector
+ rankPerSecond = (float)rankCountTuples / MAX(difftime(time(0), rankStartTime),1);
+ fprintf(stderr, " Done %i in %i @ %f per second - ETA (seconds): %f\n", rankCountTuples, (int)(difftime(time(0), rankStartTime)), rankPerSecond, ((float)(rankTotalTuples - rankCountTuples))/rankPerSecond);
+
+ PQclear(resPlaces);
+ }
+ if (rankTotalTuples-rankCountTuples < num_threads*20 && iSector < PQntuples(resSectors))
+ {
+ iSector = PQntuples(resSectors) - 1;
+ }
+ }
+ // Finished rank
+ fprintf(stderr, "\r Done %i in %i @ %f per second - FINISHED\n\n", rankCountTuples, (int)(difftime(time(0), rankStartTime)), rankPerSecond);
+
+ PQclear(resSectors);
+}
+
+void nominatim_index(int rank_min, int rank_max, int num_threads, const char *conninfo, const char *structuredoutputfile)
+{
+ struct index_thread_data * thread_data;
+
+ PGconn *conn;
+ PGresult * res;
+
+ int rank;
+
+ int i;
xmlTextWriterPtr writer;
pthread_mutex_t writer_mutex = PTHREAD_MUTEX_INITIALIZER;
exit(EXIT_FAILURE);
}
PQclear(res);
+
+ res = PQprepare(conn, "index_sectors_osmline",
+ "select geometry_sector,count(*) from location_property_osmline where indexed_status > 0 group by geometry_sector order by geometry_sector",
+ 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "Failed preparing index_sectors: %s\n", PQerrorMessage(conn));
+ exit(EXIT_FAILURE);
+ }
+ PQclear(res);
pg_prepare_params[0] = PG_OID_INT4;
res = PQprepare(conn, "index_nosectors",
exit(EXIT_FAILURE);
}
PQclear(res);
-
+
+ pg_prepare_params[0] = PG_OID_INT4;
+ res = PQprepare(conn, "index_sector_places_osmline",
+ "select place_id from location_property_osmline where geometry_sector = $1 and indexed_status > 0",
+ 1, pg_prepare_params);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "Failed preparing index_sector_places: %s\n", PQerrorMessage(conn));
+ exit(EXIT_FAILURE);
+ }
+ PQclear(res);
+
+ res = PQprepare(conn, "index_nosector_places_osmline",
+ "select place_id from location_property_osmline where indexed_status > 0 order by geometry_sector",
+ 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "Failed preparing index_nosector_places: %s\n", PQerrorMessage(conn));
+ exit(EXIT_FAILURE);
+ }
+ PQclear(res);
+
// Build the data for each thread
thread_data = (struct index_thread_data *)malloc(sizeof(struct index_thread_data)*num_threads);
for (i = 0; i < num_threads; i++)
exit(EXIT_FAILURE);
}
PQclear(res);
+
+ pg_prepare_params[0] = PG_OID_INT8;
+ res = PQprepare(thread_data[i].conn, "index_osmline",
+ "update location_property_osmline set indexed_status = 0 where place_id = $1",
+ 1, pg_prepare_params);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "Failed preparing index_osmline: %s\n", PQerrorMessage(conn));
+ exit(EXIT_FAILURE);
+ }
+ PQclear(res);
/*res = PQexec(thread_data[i].conn, "set enable_seqscan = false");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
nominatim_exportCreatePreparedQueries(thread_data[i].conn);
}
- // Create the output file
- writer = NULL;
- if (structuredoutputfile)
- {
- writer = nominatim_exportXMLStart(structuredoutputfile);
- }
fprintf(stderr, "Starting indexing rank (%i to %i) using %i threads\n", rank_min, rank_max, num_threads);
for (rank = rank_min; rank <= rank_max; rank++)
{
- fprintf(stderr, "Starting rank %d\n", rank);
- rankCountTuples = 0;
- rankPerSecond = 0;
-
- paramRank = PGint32(rank);
- paramValues[0] = (char *)¶mRank;
- paramLengths[0] = sizeof(paramRank);
- paramFormats[0] = 1;
-// if (rank < 16)
-// resSectors = PQexecPrepared(conn, "index_nosectors", 1, paramValues, paramLengths, paramFormats, 1);
-// else
- resSectors = PQexecPrepared(conn, "index_sectors", 1, paramValues, paramLengths, paramFormats, 1);
-
- if (PQresultStatus(resSectors) != PGRES_TUPLES_OK)
- {
- fprintf(stderr, "index_sectors: SELECT failed: %s", PQerrorMessage(conn));
- PQclear(resSectors);
- exit(EXIT_FAILURE);
- }
- if (PQftype(resSectors, 0) != PG_OID_INT4)
- {
- fprintf(stderr, "Sector value has unexpected type\n");
- PQclear(resSectors);
- exit(EXIT_FAILURE);
- }
- if (PQftype(resSectors, 1) != PG_OID_INT8)
+ // OSMLINE: do reindexing (=> reparenting) for interpolation lines at rank 30, but before all other objects of rank 30
+ // reason: houses (rank 30) depend on the updated interpolation line, when reparenting (see placex_update in functions.sql)
+ if (rank == 30)
{
- fprintf(stderr, "Sector value has unexpected type\n");
- PQclear(resSectors);
- exit(EXIT_FAILURE);
- }
-
- rankTotalTuples = 0;
- for (iSector = 0; iSector < PQntuples(resSectors); iSector++)
- {
- rankTotalTuples += PGint64(*((uint64_t *)PQgetvalue(resSectors, iSector, 1)));
+ run_indexing(rank, 1, conn, num_threads, thread_data, structuredoutputfile);
}
-
- rankStartTime = time(0);
- for (iSector = 0; iSector <= PQntuples(resSectors); iSector++)
- {
- if (iSector > 0)
- {
- resPlaces = PQgetResult(conn);
- if (PQresultStatus(resPlaces) != PGRES_TUPLES_OK)
- {
- fprintf(stderr, "index_sector_places: SELECT failed: %s", PQerrorMessage(conn));
- PQclear(resPlaces);
- exit(EXIT_FAILURE);
- }
- if (PQftype(resPlaces, 0) != PG_OID_INT8)
- {
- fprintf(stderr, "Place_id value has unexpected type\n");
- PQclear(resPlaces);
- exit(EXIT_FAILURE);
- }
- resNULL = PQgetResult(conn);
- if (resNULL != NULL)
- {
- fprintf(stderr, "Unexpected non-null response\n");
- exit(EXIT_FAILURE);
- }
- }
-
- if (iSector < PQntuples(resSectors))
- {
- sector = PGint32(*((uint32_t *)PQgetvalue(resSectors, iSector, 0)));
-// fprintf(stderr, "\n Starting sector %d size %ld\n", sector, PGint64(*((uint64_t *)PQgetvalue(resSectors, iSector, 1))));
-
- // Get all the place_id's for this sector
- paramRank = PGint32(rank);
- paramValues[0] = (char *)¶mRank;
- paramLengths[0] = sizeof(paramRank);
- paramFormats[0] = 1;
- paramSector = PGint32(sector);
- paramValues[1] = (char *)¶mSector;
- paramLengths[1] = sizeof(paramSector);
- paramFormats[1] = 1;
- if (rankTotalTuples-rankCountTuples < num_threads*1000)
- {
- iResult = PQsendQueryPrepared(conn, "index_nosector_places", 1, paramValues, paramLengths, paramFormats, 1);
- }
- else
- {
- iResult = PQsendQueryPrepared(conn, "index_sector_places", 2, paramValues, paramLengths, paramFormats, 1);
- }
- if (!iResult)
- {
- fprintf(stderr, "index_sector_places: SELECT failed: %s", PQerrorMessage(conn));
- PQclear(resPlaces);
- exit(EXIT_FAILURE);
- }
- }
-
- if (iSector > 0)
- {
- count = 0;
- rankPerSecond = 0;
- tuples = PQntuples(resPlaces);
-
- if (tuples > 0)
- {
- // Spawn threads
- for (i = 0; i < num_threads; i++)
- {
- thread_data[i].res = resPlaces;
- thread_data[i].tuples = tuples;
- thread_data[i].count = &count;
- thread_data[i].count_mutex = &count_mutex;
- thread_data[i].writer = writer;
- thread_data[i].writer_mutex = &writer_mutex;
- pthread_create(&thread_data[i].thread, NULL, &nominatim_indexThread, (void *)&thread_data[i]);
- }
-
- // Monitor threads to give user feedback
- sleepcount = 0;
- while (count < tuples)
- {
- usleep(1000);
-
- // Aim for one update per second
- if (sleepcount++ > 500)
- {
- rankPerSecond = ((float)rankCountTuples + (float)count) / MAX(difftime(time(0), rankStartTime),1);
- fprintf(stderr, " Done %i in %i @ %f per second - Rank %i ETA (seconds): %f\n", (rankCountTuples + count), (int)(difftime(time(0), rankStartTime)), rankPerSecond, rank, ((float)(rankTotalTuples - (rankCountTuples + count)))/rankPerSecond);
- sleepcount = 0;
- }
- }
-
- // Wait for everything to finish
- for (i = 0; i < num_threads; i++)
- {
- pthread_join(thread_data[i].thread, NULL);
- }
-
- rankCountTuples += tuples;
- }
-
- // Finished sector
- rankPerSecond = (float)rankCountTuples / MAX(difftime(time(0), rankStartTime),1);
- fprintf(stderr, " Done %i in %i @ %f per second - ETA (seconds): %f\n", rankCountTuples, (int)(difftime(time(0), rankStartTime)), rankPerSecond, ((float)(rankTotalTuples - rankCountTuples))/rankPerSecond);
-
- PQclear(resPlaces);
- }
- if (rankTotalTuples-rankCountTuples < num_threads*20 && iSector < PQntuples(resSectors))
- {
- iSector = PQntuples(resSectors) - 1;
- }
- }
- // Finished rank
- fprintf(stderr, "\r Done %i in %i @ %f per second - FINISHED \n\n", rankCountTuples, (int)(difftime(time(0), rankStartTime)), rankPerSecond);
-
- PQclear(resSectors);
- }
-
- if (writer)
- {
- nominatim_exportXMLEnd(writer);
- }
-
- // Close all connections
- for (i = 0; i < num_threads; i++)
- {
- PQfinish(thread_data[i].conn);
+ run_indexing(rank, 0, conn, num_threads, thread_data, structuredoutputfile);
}
- PQfinish(conn);
+ // Close all connections
+ for (i = 0; i < num_threads; i++)
+ {
+ PQfinish(thread_data[i].conn);
+ }
+ PQfinish(conn);
}
void *nominatim_indexThread(void * thread_data_in)
PGresult *res;
- const char *paramValues[1];
+ const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint64_t paramPlaceID;
- uint64_t place_id;
- time_t updateStartTime;
+ uint64_t place_id;
+ time_t updateStartTime;
+ uint table;
+
+ table = (uint)(thread_data->table);
while (1)
{
if (verbose) fprintf(stderr, " Processing place_id %ld\n", place_id);
updateStartTime = time(0);
- int done = 0;
+ int done = 0;
if (thread_data->writer)
{
nominatim_exportPlaceQueries(place_id, thread_data->conn, &querySet);
}
- while(!done)
- {
- paramPlaceID = PGint64(place_id);
- paramValues[0] = (char *)¶mPlaceID;
- paramLengths[0] = sizeof(paramPlaceID);
- paramFormats[0] = 1;
- res = PQexecPrepared(thread_data->conn, "index_placex", 1, paramValues, paramLengths, paramFormats, 1);
- if (PQresultStatus(res) == PGRES_COMMAND_OK)
- done = 1;
- else
- {
- if (!strncmp(PQerrorMessage(thread_data->conn), "ERROR: deadlock detected", 25))
- {
- fprintf(stderr, "index_placex: UPDATE failed - deadlock, retrying (%ld)\n", place_id);
- PQclear(res);
- sleep(rand() % 10);
- }
- else
- {
- fprintf(stderr, "index_placex: UPDATE failed: %s", PQerrorMessage(thread_data->conn));
- PQclear(res);
- exit(EXIT_FAILURE);
- }
- }
+ while(!done)
+ {
+ paramPlaceID = PGint64(place_id);
+ paramValues[0] = (char *)¶mPlaceID;
+ paramLengths[0] = sizeof(paramPlaceID);
+ paramFormats[0] = 1;
+ if (table == 1) // table=1 for placex
+ {
+ res = PQexecPrepared(thread_data->conn, "index_placex", 1, paramValues, paramLengths, paramFormats, 1);
+ }
+ else // table=0 for osmline
+ {
+ res = PQexecPrepared(thread_data->conn, "index_osmline", 1, paramValues, paramLengths, paramFormats, 1);
+ }
+ if (PQresultStatus(res) == PGRES_COMMAND_OK)
+ done = 1;
+ else
+ {
+ if (!strncmp(PQerrorMessage(thread_data->conn), "ERROR: deadlock detected", 25))
+ {
+ if (table == 1)
+ {
+ fprintf(stderr, "index_placex: UPDATE failed - deadlock, retrying (%ld)\n", place_id);
+ }
+ else
+ {
+ fprintf(stderr, "index_osmline: UPDATE failed - deadlock, retrying (%ld)\n", place_id);
+ }
+ PQclear(res);
+ sleep(rand() % 10);
+ }
+ else
+ {
+ if (table == 1)
+ {
+ fprintf(stderr, "index_placex: UPDATE failed: %s", PQerrorMessage(thread_data->conn));
+ }
+ else
+ {
+ fprintf(stderr, "index_osmline: UPDATE failed: %s", PQerrorMessage(thread_data->conn));
+ }
+ PQclear(res);
+ exit(EXIT_FAILURE);
+ }
+ }
}
PQclear(res);
if (difftime(time(0), updateStartTime) > 1) fprintf(stderr, " Slow place_id %ld\n", place_id);
pthread_mutex_t * count_mutex;
xmlTextWriterPtr writer;
pthread_mutex_t * writer_mutex;
+ uint table;
};
void nominatim_index(int rank_min, int rank_max, int num_threads, const char *conninfo, const char *structuredoutputfile);
void *nominatim_indexThread(void * thread_data_in);
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;
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, indexed_status = 2
+ where linked_place_id = NEW.place_id;
+ -- update not necessary for osmline, cause linked_place_id does not exist
- 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;
+ 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;
- -- 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;
+ -- 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;
- 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;
+ 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;
- -- 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);
+ -- 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;
+ -- 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;
+ -- 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;
+ END IF;
- -- 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;
+ -- 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 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;
+ NEW.parent_place_id = 0;
+ parent_place_id_rank = 0;
- -- 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;
+ -- 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;
- -- 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]);
+ -- 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;
- -- %NOTIGERDATA% 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;
-
- -- 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;
- 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;
- 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;
+ -- 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
- RETURN NEW;
- 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;
+ 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 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;
+
+ 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;
- IF coalesce(existing.isin, '') != coalesce(NEW.isin, '') THEN
- RAISE WARNING 'update details, isin: % % % %',NEW.osm_type,NEW.osm_id,existing.isin,NEW.isin;
+
+ RETURN NULL;
+
+ ELSE -- insert to placex
+
+ -- 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.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;
- 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
+ -- 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
- 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;
CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline
AS $$
DECLARE
- for_place_id BIGINT;--parent_place_id
+ for_place_id BIGINT;
result TEXT[];
search TEXT[];
found INTEGER;
countryname HSTORE;
hadcountry BOOLEAN;
BEGIN
- --first query tiger data
- -- %NOTIGERDATA% IF 0 THEN
- select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
+ -- 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;
+
+ --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
CREATE INDEX idx_placex_reverse_geometry ON placex USING gist (geometry) {ts:search-index} where rank_search != 28 and (name is not null or housenumber is not null) and class not in ('waterway','railway','tunnel','bridge','man_made');
CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) {ts:address-index};
+CREATE INDEX idx_osmline_parent_place_id ON location_property_osmline USING BTREE (parent_place_id) {ts:search-index};
+
CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid) {ts:address-index};
DROP INDEX IF EXISTS place_id_idx;
);
SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2);
+CREATE TABLE location_property_aux () INHERITS (location_property);
+CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
+CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
+CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
+GRANT SELECT ON location_property_aux TO "{www-user}";
+
+CREATE TABLE location_property_tiger (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT);
+GRANT SELECT ON location_property_tiger TO "{www-user}";
+
+drop table if exists location_property_osmline;
+CREATE TABLE location_property_osmline (
+ linegeo GEOMETRY,
+ place_id BIGINT NOT NULL,
+ partition INTEGER,
+ osm_id BIGINT,
+ parent_place_id BIGINT,
+ startnumber INTEGER,
+ endnumber INTEGER,
+ interpolationtype TEXT,
+ street TEXT,
+ addr_place TEXT,
+ postcode TEXT,
+ calculated_country_code VARCHAR(2),
+ geometry_sector INTEGER,
+ indexed_status INTEGER,
+ indexed_date TIMESTAMP){ts:search-data};
+CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {ts:search-index};
+CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {ts:address-index};
+CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {ts:search-index};
+GRANT SELECT ON location_property_osmline TO "{www-user}";
+
drop table IF EXISTS search_name;
CREATE TABLE search_name (
place_id BIGINT,
GRANT SELECT ON planet_osm_rels to "{www-user}" ;
GRANT SELECT on location_area to "{www-user}" ;
--- insert creates the location tagbles, creates location indexes if indexed == true
+-- insert creates the location tables, creates location indexes if indexed == true
CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
FOR EACH ROW EXECUTE PROCEDURE placex_insert();
-- update insert creates the location tables
CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex
FOR EACH ROW EXECUTE PROCEDURE placex_update();
+CREATE TRIGGER osmline_before_update BEFORE UPDATE ON location_property_osmline
+ FOR EACH ROW EXECUTE PROCEDURE osmline_update();
-- diff update triggers
CREATE TRIGGER placex_before_delete AFTER DELETE ON placex
| xml | 4
When looking up coordinates 53.9788769,13.0830313
And results contain valid boundingboxes
-
+
+ Scenario: Reverse geocoding for odd interpolated housenumber
+
+ Scenario: Reverse geocoding for even interpolated housenumber
@Tiger
Scenario: TIGER house number
Feature: Import of address interpolations
Tests that interpolated addresses are added correctly
- Scenario: Simple even two point interpolation
+ Scenario: Simple even interpolation line with two points
Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 2 | 1 1
- | 2 | place | house | 6 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | even | 1 1, 1 1.001
- And the ways
- | id | nodes
- | 1 | 1,2
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.0005
-
- Scenario: Simple even two point interpolation with zero beginning
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 0 | 1 1
- | 2 | place | house | 8 | 1 1.001
+ | osm_id | osm_type | class | type | housenumber | geometry
+ | 1 | N | place | house | 2 | 1 1
+ | 2 | N | place | house | 6 | 1 1.001
And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | even | 1 1, 1 1.001
+ | osm_id | osm_type | class | type | housenumber | geometry
+ | 1 | W | place | houses | even | 1 1, 1 1.001
And the ways
| id | nodes
| 1 | 1,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 2 | 1,1.00025
- | 4 | 1,1.0005
- | 6 | 1,1.00075
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 6 | 1 1, 1 1.001
- Scenario: Backwards even two point interpolation
+ Scenario: Backwards even two point interpolation line
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 2,1
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.0005
-
- Scenario: Even two point interpolation with odd beginning
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 11 | 1 1
- | 2 | place | house | 16 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | even | 1 1, 1 1.001
- And the ways
- | id | nodes
- | 1 | 1,2
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 12 | 1,1.0002
- | 14 | 1,1.0006
-
- Scenario: Even two point interpolation with odd end
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 10 | 1 1
- | 2 | place | house | 15 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | even | 1 1, 1 1.001
- And the ways
- | id | nodes
- | 1 | 1,2
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 12 | 1,1.0004
- | 14 | 1,1.0008
-
- Scenario: Reverse even two point interpolation with odd beginning
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 11 | 1 1
- | 2 | place | house | 16 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | even | 1 1.001, 1 1
- And the ways
- | id | nodes
- | 1 | 2,1
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 12 | 1,1.0002
- | 14 | 1,1.0006
-
- Scenario: Reverse even two point interpolation with odd end
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 10 | 1 1
- | 2 | place | house | 15 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | even | 1 1.001, 1 1
- And the ways
- | id | nodes
- | 1 | 2,1
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 12 | 1,1.0004
- | 14 | 1,1.0008
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 6 | 1 1, 1 1.001
Scenario: Simple odd two point interpolation
Given the place nodes
| id | nodes
| 1 | 1,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 3 | 1,1.0002
- | 5 | 1,1.0004
- | 7 | 1,1.0006
- | 9 | 1,1.0008
-
- Scenario: Odd two point interpolation with even beginning
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 2 | 1 1
- | 2 | place | house | 7 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | odd | 1 1, 1 1.001
- And the ways
- | id | nodes
- | 1 | 1,2
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 3 | 1,1.0002
- | 5 | 1,1.0006
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 1 | 11 | 1 1, 1 1.001
Scenario: Simple all two point interpolation
Given the place nodes
| id | nodes
| 1 | 1,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 2 | 1,1.0005
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 1 | 3 | 1 1, 1 1.001
- Scenario: Simple numbered two point interpolation
- Given the place nodes
- | osm_id | class | type | housenumber | geometry
- | 1 | place | house | 3 | 1 1
- | 2 | place | house | 9 | 1 1.001
- And the place ways
- | osm_id | class | type | housenumber | geometry
- | 1 | place | houses | 3 | 1 1, 1 1.001
- And the ways
- | id | nodes
- | 1 | 1,2
- When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 6 | 1,1.0005
-
- Scenario: Even two point interpolation with intermediate empty node
+ Scenario: Even two point interpolation line with intermediate empty node
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 1,3,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.0005
- | 6 | 1,1.001
- | 8 | 1.0005,1.001
-
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 10 | 1 1, 1 1.001, 1.001 1.001
- Scenario: Even two point interpolation with intermediate duplicated empty node
+ Scenario: Even two point interpolation line with intermediate duplicated empty node
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 1,3,3,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.0005
- | 6 | 1,1.001
- | 8 | 1.0005,1.001
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 10 | 1 1, 1 1.001, 1.001 1.001
- Scenario: Simple even three point interpolation
+ Scenario: Simple even three point interpolation line
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 1,3,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.00025
- | 6 | 1,1.0005
- | 8 | 1,1.00075
- | 12 | 1.0005,1.001
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 10 | 1 1, 1 1.001
+ | 10 | 14 | 1 1.001, 1.001 1.001
- Scenario: Simple even four point interpolation
+ Scenario: Simple even four point interpolation line
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 1,3,2,4
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.00025
- | 6 | 1,1.0005
- | 8 | 1,1.00075
- | 12 | 1.0005,1.001
- | 16 | 1.001,1.0015
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 10 | 1 1, 1 1.001
+ | 10 | 14 | 1 1.001, 1.001 1.001
+ | 14 | 18 | 1.001 1.001, 1.001 1.002
- Scenario: Reverse simple even three point interpolation
+ Scenario: Reverse simple even three point interpolation line
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 2,3,1
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.00025
- | 6 | 1,1.0005
- | 8 | 1,1.00075
- | 12 | 1.0005,1.001
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 10 | 1 1, 1 1.001
+ | 10 | 14 | 1 1.001, 1.001 1.001
- Scenario: Even three point interpolation with odd center point
+ Scenario: Even three point interpolation line with odd center point
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 1 1
| id | nodes
| 1 | 1,3,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 1,1.0004
- | 6 | 1,1.0008
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 7 | 1 1, 1 1.001
+ | 7 | 8 | 1 1.001, 1.001 1.001
- Scenario: Interpolation on self-intersecting way
+ Scenario: Interpolation line with self-intersecting way
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 0 0
| id | nodes
| 1 | 1,2,3,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 0,0.0005
- | 8 | 0,0.0015
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 6 | 0 0, 0 0.001
+ | 6 | 10 | 0 0.001, 0 0.002
+ | 6 | 10 | 0 0.001, 0 0.002
- Scenario: Interpolation on self-intersecting way II
+ Scenario: Interpolation line with self-intersecting way II
Given the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | 0 0
| id | nodes
| 1 | 1,2,3,2
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 0,0.0005
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 6 | 0 0, 0 0.001
- Scenario: addr:street on interpolation way
- Given the scene parallel-road
- And the place nodes
+ Scenario: addr:street on interpolation way
+ Given the scene parallel-road
+ And the place nodes
| osm_id | class | type | housenumber | geometry
| 1 | place | house | 2 | :n-middle-w
| 2 | place | house | 6 | :n-middle-e
| 3 | place | house | 12 | :n-middle-w
| 4 | place | house | 16 | :n-middle-e
- And the place ways
+ And the place ways
| osm_id | class | type | housenumber | street | geometry
| 10 | place | houses | even | | :w-middle
| 11 | place | houses | even | Cloud Street | :w-middle
- And the place ways
+ And the place ways
| osm_id | class | type | name | geometry
| 2 | highway | tertiary | 'name' : 'Sun Way' | :w-north
| 3 | highway | tertiary | 'name' : 'Cloud Street' | :w-south
| id | nodes
| 10 | 1,100,101,102,2
| 11 | 3,200,201,202,4
- When importing
- Then table placex contains
+ When importing
+ Then table placex contains
| object | parent_place_id
| N1 | W2
| N2 | W2
| N3 | W3
| N4 | W3
- | W10 | W2
- | W11 | W3
- And way 10 expands exactly to housenumbers 4
- And way 11 expands exactly to housenumbers 14
+ Then table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
+ | W11 | W3 | 12 | 16
- Scenario: addr:street on housenumber way
- Given the scene parallel-road
- And the place nodes
+ Scenario: addr:street on housenumber way
+ Given the scene parallel-road
+ And the place nodes
| osm_id | class | type | housenumber | street | geometry
| 1 | place | house | 2 | | :n-middle-w
| 2 | place | house | 6 | | :n-middle-e
| 3 | place | house | 12 | Cloud Street | :n-middle-w
| 4 | place | house | 16 | Cloud Street | :n-middle-e
- And the place ways
+ And the place ways
| osm_id | class | type | housenumber | geometry
| 10 | place | houses | even | :w-middle
| 11 | place | houses | even | :w-middle
- And the place ways
+ And the place ways
| osm_id | class | type | name | geometry
| 2 | highway | tertiary | 'name' : 'Sun Way' | :w-north
| 3 | highway | tertiary | 'name' : 'Cloud Street' | :w-south
And the ways
- | id | nodes
+ | id | nodes
| 10 | 1,100,101,102,2
| 11 | 3,200,201,202,4
- When importing
- Then table placex contains
+ When importing
+ Then table placex contains
| object | parent_place_id
| N1 | W2
| N2 | W2
| N3 | W3
| N4 | W3
- | W10 | W2
- | W11 | W3
- And way 10 expands exactly to housenumbers 4
- And way 11 expands exactly to housenumbers 14
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
+ | W11 | W3 | 12 | 16
Scenario: Geometry of points and way don't match (github #253)
Given the place nodes
| id | nodes
| 1 | 1,2,3
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 4 | 144.963016723312,-37.7629464422819+-0.000005
- | 8 | 144.9631440856,-37.762223694978+-0.000005
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 2 | 6 | 144.9629794 -37.7630755, 144.9630541 -37.7628174
+ | 6 | 10 | 144.9630541 -37.7628174, 144.9632341 -37.76163
Scenario: Place with missing address information
Given the place nodes
| id | nodes
| 1 | 1,2,3
When importing
- Then way 1 expands to housenumbers
- | housenumber | centroid
- | 25 | 0.0001,0.0002
- | 27 | 0.0001,0.0003
+ Then way 1 expands to lines
+ | startnumber | endnumber | geometry
+ | 23 | 29 | 0.0001 0.0001, 0.0001 0.0002, 0.0001 0.0004
| object | parent_place_id
| N1 | W2
| N2 | W2
- | W10 | W2
- And way 10 expands exactly to housenumbers 4
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
When updating place ways
| osm_id | class | type | housenumber | street | geometry
| 10 | place | houses | even | Cloud Street | :w-middle
- Then way 10 expands exactly to housenumbers 4
- And table placex contains
+ Then table placex contains
| object | parent_place_id
| N1 | W3
| N2 | W3
- | W10 | W3
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W3 | 2 | 6
- @Fail
Scenario: addr:street added to housenumbers
Given the scene parallel-road
And the place nodes
| object | parent_place_id
| N1 | W2
| N2 | W2
- | W10 | W2
- And way 10 expands exactly to housenumbers 4
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
When updating place nodes
| osm_id | class | type | street | housenumber | geometry
| 1 | place | house | Cloud Street| 2 | :n-middle-w
| 2 | place | house | Cloud Street| 6 | :n-middle-e
- Then way 10 expands exactly to housenumbers 4
- And table placex contains
+ Then table placex contains
| object | parent_place_id
| N1 | W3
| N2 | W3
- | W10 | W3
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W3 | 2 | 6
Scenario: interpolation tag removed
| object | parent_place_id
| N1 | W2
| N2 | W2
- | W10 | W2
- And way 10 expands exactly to housenumbers 4
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
When marking for delete W10
- Then way 10 expands to no housenumbers
+ Then table location_property_osmline has no entry for W10
And table placex contains
| object | parent_place_id
| N1 | W2
| object | parent_place_id
| N1 | W2
| N2 | W2
- | W10 | W2
- And way 10 expands exactly to housenumbers 4
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
When updating place ways
| osm_id | class | type | name | geometry
| 3 | highway | unclassified | 'name' : 'Cloud Street' | :w-south
- Then way 10 expands exactly to housenumbers 4
- And table placex contains
+ Then table placex contains
| object | parent_place_id
| N1 | W3
| N2 | W3
- | W10 | W3
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W3 | 2 | 6
Scenario: referenced road deleted
| object | parent_place_id
| N1 | W3
| N2 | W3
- | W10 | W3
- And way 10 expands exactly to housenumbers 4
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W3 | 2 | 6
When marking for delete W3
- Then way 10 expands exactly to housenumbers 4
- And table placex contains
+ Then table placex contains
| object | parent_place_id
| N1 | W2
| N2 | W2
- | W10 | W2
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W10 | W2 | 2 | 6
+
+ Scenario: building becomes interpolation
+ Given the scene building-with-parallel-streets
+ And the place ways
+ | osm_id | class | type | housenumber | geometry
+ | 1 | place | house | 3 | :w-building
+ And the place ways
+ | osm_id | class | type | name | geometry
+ | 2 | highway | unclassified | 'name' : 'Cloud Street' | :w-south
+ When importing
+ Then table placex contains
+ | object | parent_place_id
+ | W1 | W2
+ When updating place nodes
+ | osm_id | class | type | housenumber | geometry
+ | 1 | place | house | 2 | :n-north-w
+ | 2 | place | house | 6 | :n-north-e
+ And the ways
+ | id | nodes
+ | 1 | 1,100,101,102,2
+ And updating place ways
+ | osm_id | class | type | housenumber | street | geometry
+ | 1 | place | houses | even | Cloud Street| :w-north
+ Then table placex has no entry for W1
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W1 | W2 | 2 | 6
+
+
+
+ Scenario: interpolation becomes building
+ Given the scene building-with-parallel-streets
+ And the place nodes
+ | osm_id | class | type | housenumber | geometry
+ | 1 | place | house | 2 | :n-north-w
+ | 2 | place | house | 6 | :n-north-e
+ And the place ways
+ | osm_id | class | type | name | geometry
+ | 2 | highway | unclassified | 'name' : 'Cloud Street' | :w-south
+ And the ways
+ | id | nodes
+ | 1 | 1,100,101,102,2
+ And the place ways
+ | osm_id | class | type | housenumber | street | geometry
+ | 1 | place | houses | even | Cloud Street| :w-north
+ When importing
+ Then table placex has no entry for W1
+ And table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W1 | W2 | 2 | 6
+ When updating place ways
+ | osm_id | class | type | housenumber | geometry
+ | 1 | place | house | 3 | :w-building
+ Then table placex contains
+ | object | parent_place_id
+ | W1 | W2
+
+ Scenario: housenumbers added to interpolation
+ Given the scene building-with-parallel-streets
+ And the place ways
+ | osm_id | class | type | name | geometry
+ | 2 | highway | unclassified | 'name' : 'Cloud Street' | :w-south
+ And the ways
+ | id | nodes
+ | 1 | 1,100,101,102,2
+ And the place ways
+ | osm_id | class | type | housenumber | geometry
+ | 1 | place | houses | even | :w-north
+ When importing
+ Then table location_property_osmline has no entry for W1
+ When updating place nodes
+ | osm_id | class | type | housenumber | geometry
+ | 1 | place | house | 2 | :n-north-w
+ | 2 | place | house | 6 | :n-north-e
+ And updating place ways
+ | osm_id | class | type | housenumber | street | geometry
+ | 1 | place | houses | even | Cloud Street| :w-north
+ Then table location_property_osmline contains
+ | object | parent_place_id | startnumber | endnumber
+ | W1 | W2 | 2 | 6
+
+
+
$(CXX) $(CXXFLAGS) $(CXXFLAGS_WARNINGS) -o $@ $< $(LDFLAGS) $(LIB_IO)
scenarios: osm2wkt
+ ./make_scenes.sh
-#/bin/bash -e
+#!/bin/bash -e
#
# Regenerates wkts for scenarios.
#
fi
echo "Using datadir $datadir"
-pushd $datadir
+cd $datadir
# remove old wkts
-rm $datadir/*.wkt
+rm -f $datadir/*.wkt
# create wkts from SQL scripts
for fl in *.sql; do
echo "Processing $fl.."
../bin/osm2wkt $fl
done
-
-popd
reader2.close();
export_handler.close();
std::cerr << "Pass 2 done\n";
-
-
- google::protobuf::ShutdownProtobufLibrary();
-
}
--- /dev/null
+n-south-w | POINT(1.0031633 2.001023)
+n-south-e | POINT(1.0043359 2.0010068)
+n-north-w | POINT(1.0031511 2.0012655)
+n-north-e | POINT(1.0043238 2.0012493)
+w-south | LINESTRING(1.0031633 2.001023,1.0036943 2.0010149,1.0040717 2.0010203,1.0043359 2.0010068)
+w-north | LINESTRING(1.0031511 2.0012655,1.0036822 2.0012574,1.0040596 2.0012628,1.0043238 2.0012493)
+w-building | LINESTRING(1.0036157 2.0011891,1.0036166 2.0010787,1.0038457 2.0010805,1.0038448 2.001191,1.0036157 2.0011891)
cz | POINT(16.3209805488586 49.5069274902344)
de | POINT(9.30716800689697 50.2128944396973)
dj | POINT(42.969040422876 11.41542855)
- dk | POINT(9.18490123748779 55.5634002685547)
+ dk | POINT(9.18490123748779 55.9891662597656)
dm | POINT(-61.0035801928854 15.6547055)
do | POINT(-69.6285591125488 18.5884169089722)
dz | POINT(4.24749487638474 25.797215461731)
et | POINT(38.6169757843018 7.71399855613708)
fi | POINT(26.8979873657227 63.5619449615479)
fj | POINT(177.918533325195 -17.7423753738403)
- fk | POINT(-60.0855102539062 -51.6555919647217)
+ fk | POINT(-58.9904479980469 -51.3450936007813)
fm | POINT(151.9535889125 8.5045)
fo | POINT(-6.60483694084778 62.10000995)
fr | POINT(0.284105718135834 47.5104522705078)
in | POINT(88.6762087020508 27.86155515)
io | POINT(71.4274391359073 -6.14349685)
iq | POINT(42.5810985565186 34.2610359191895)
- ir | POINT(51.268892288208 34.1931705474854)
+ ir | POINT(56.0935573577881 30.4675178527832)
is | POINT(-17.5178508758545 64.7168769836426)
it | POINT(10.4263944625854 44.8790493011475)
je | POINT(-2.19261599848299 49.1245833)
mw | POINT(33.9572296142578 -12.2821822166443)
mx | POINT(-105.892219543457 25.8682699203491)
my | POINT(112.711540222168 2.10098683834076)
- mz | POINT(37.5868968963623 -15.5801844596863)
+ mz | POINT(37.5868968963623 -13.7268223762512)
na | POINT(16.6856970787048 -21.4657220840454)
nc | POINT(164.953224182129 -20.3888988494873)
ne | POINT(10.060417175293 19.0827360153198)
sm | POINT(12.4606268797657 43.9427969)
sn | POINT(-15.3711128234863 14.9947791099548)
so | POINT(46.9338359832764 9.34094429016113)
- sr | POINT(-56.4855213165283 4.5773549079895)
+ sr | POINT(-55.4286479949951 4.5698549747467)
ss | POINT(28.1357345581055 8.50933408737183)
st | POINT(6.61025854583333 0.2215)
sv | POINT(-89.3666543301004 13.4307287)
<?xml version='1.0' encoding='UTF-8'?>
<osm version='0.6' upload='false' generator='JOSM'>
- <node id='-197' action='modify' visible='true' lat='2.0006515863' lon='1.0057464449'>
+ <node id='-11' action='modify' visible='true' lat='2.0006515863' lon='1.0057464449'>
<tag k='name' v='split-road' />
</node>
- <node id='-195' action='modify' visible='true' lat='2.00053508276' lon='1.00574909433' />
- <node id='-193' action='modify' visible='true' lat='2.00054302619' lon='1.00589746199' />
- <node id='-191' action='modify' visible='true' lat='2.00054302619' lon='1.0060511285' />
- <node id='-189' action='modify' visible='true' lat='2.00053243496' lon='1.00613061118' />
- <node id='-187' action='modify' visible='true' lat='2.00052449153' lon='1.00551064629' />
- <node id='-185' action='modify' visible='true' lat='2.00056685646' lon='1.00560867493' />
- <node id='-183' action='modify' visible='true' lat='2.00056156084' lon='1.00568550818' />
- <node id='-181' action='modify' visible='true' lat='2.00050066126' lon='1.00573584721' />
- <node id='-179' action='modify' visible='true' lat='2.00050595688' lon='1.0059107091' />
- <node id='-177' action='modify' visible='true' lat='2.00051125249' lon='1.00605377792' />
- <node id='-175' action='modify' visible='true' lat='2.00049536565' lon='1.00613591002' />
- <node id='-173' action='modify' visible='true' lat='2.0005139003' lon='1.00628427769' />
- <node id='-171' action='modify' visible='true' lat='2.00047153538' lon='1.00637170863' />
- <node id='-169' action='modify' visible='true' lat='2.00029678005' lon='1.00635846152' />
- <node id='-167' action='modify' visible='true' lat='2.00026235854' lon='1.00628162826' />
- <node id='-165' action='modify' visible='true' lat='2.00033914498' lon='1.00619949616' />
- <node id='-163' action='modify' visible='true' lat='2.00035767963' lon='1.00610411695' />
- <node id='-161' action='modify' visible='true' lat='2.00034973621' lon='1.00600343889' />
- <node id='-159' action='modify' visible='true' lat='2.00032590594' lon='1.0058868643' />
- <node id='-157' action='modify' visible='true' lat='2.0002808932' lon='1.00579413451' />
- <node id='-155' action='modify' visible='true' lat='2.00027824539' lon='1.00563516915' />
- <node id='-153' action='modify' visible='true' lat='2.00036032744' lon='1.00547090495' />
- <node id='-151' action='modify' visible='true' lat='2.00072654218' lon='1.00470543134'>
+ <node id='-13' action='modify' visible='true' lat='2.00053508276' lon='1.00574909433' />
+ <node id='-15' action='modify' visible='true' lat='2.00054302619' lon='1.00589746199' />
+ <node id='-17' action='modify' visible='true' lat='2.00054302619' lon='1.0060511285' />
+ <node id='-19' action='modify' visible='true' lat='2.00053243496' lon='1.00613061118' />
+ <node id='-21' action='modify' visible='true' lat='2.00052449153' lon='1.00551064629' />
+ <node id='-23' action='modify' visible='true' lat='2.00056685646' lon='1.00560867493' />
+ <node id='-25' action='modify' visible='true' lat='2.00056156084' lon='1.00568550818' />
+ <node id='-27' action='modify' visible='true' lat='2.00050066126' lon='1.00573584721' />
+ <node id='-29' action='modify' visible='true' lat='2.00050595688' lon='1.0059107091' />
+ <node id='-31' action='modify' visible='true' lat='2.00051125249' lon='1.00605377792' />
+ <node id='-33' action='modify' visible='true' lat='2.00049536565' lon='1.00613591002' />
+ <node id='-35' action='modify' visible='true' lat='2.0005139003' lon='1.00628427769' />
+ <node id='-37' action='modify' visible='true' lat='2.00047153538' lon='1.00637170863' />
+ <node id='-39' action='modify' visible='true' lat='2.00029678005' lon='1.00635846152' />
+ <node id='-41' action='modify' visible='true' lat='2.00026235854' lon='1.00628162826' />
+ <node id='-43' action='modify' visible='true' lat='2.00033914498' lon='1.00619949616' />
+ <node id='-45' action='modify' visible='true' lat='2.00035767963' lon='1.00610411695' />
+ <node id='-47' action='modify' visible='true' lat='2.00034973621' lon='1.00600343889' />
+ <node id='-49' action='modify' visible='true' lat='2.00032590594' lon='1.0058868643' />
+ <node id='-51' action='modify' visible='true' lat='2.0002808932' lon='1.00579413451' />
+ <node id='-53' action='modify' visible='true' lat='2.00027824539' lon='1.00563516915' />
+ <node id='-55' action='modify' visible='true' lat='2.00036032744' lon='1.00547090495' />
+ <node id='-57' action='modify' visible='true' lat='2.00072654218' lon='1.00470543134'>
<tag k='name' v='points-on-road' />
</node>
- <node id='-149' action='modify' visible='true' lat='2.00051552538' lon='1.00492201384' />
- <node id='-147' action='modify' visible='true' lat='2.00051552538' lon='1.00492201384'>
+ <node id='-59' action='modify' visible='true' lat='2.00051552538' lon='1.00492201384' />
+ <node id='-61' action='modify' visible='true' lat='2.00051552538' lon='1.00492201384'>
<tag k='test:id' v='n-N-unglued' />
<tag k='test:section' v='points-on-roads' />
</node>
- <node id='-145' action='modify' visible='true' lat='2.00029485534' lon='1.00462587591'>
+ <node id='-63' action='modify' visible='true' lat='2.00029485534' lon='1.00462587591'>
<tag k='test:id' v='n-S-unglued' />
<tag k='test:section' v='points-on-roads' />
</node>
- <node id='-143' action='modify' visible='true' lat='2.00029485534' lon='1.00462587591' />
- <node id='-141' action='modify' visible='true' lat='2.00061177404' lon='1.00506613814'>
+ <node id='-65' action='modify' visible='true' lat='2.00029485534' lon='1.00462587591' />
+ <node id='-67' action='modify' visible='true' lat='2.00061177404' lon='1.00506613814'>
<tag k='test:id' v='n-NE' />
<tag k='test:section' v='points-on-roads' />
</node>
- <node id='-139' action='modify' visible='true' lat='2.00033485479' lon='1.00513387079'>
+ <node id='-69' action='modify' visible='true' lat='2.00033485479' lon='1.00513387079'>
<tag k='test:id' v='n-SE' />
<tag k='test:section' v='points-on-roads' />
</node>
- <node id='-137' action='modify' visible='true' lat='2.00062408156' lon='1.00531551745' />
- <node id='-135' action='modify' visible='true' lat='2.00040869993' lon='1.00475826245'>
+ <node id='-71' action='modify' visible='true' lat='2.00062408156' lon='1.00531551745' />
+ <node id='-73' action='modify' visible='true' lat='2.00040869993' lon='1.00475826245'>
<tag k='test:id' v='n-NW' />
<tag k='test:section' v='points-on-roads' />
</node>
- <node id='-133' action='modify' visible='true' lat='2.00038408489' lon='1.00462587591' />
- <node id='-131' action='modify' visible='true' lat='2.00043023809' lon='1.00449964688' />
- <node id='-129' action='modify' visible='true' lat='2.00036870048' lon='1.00532783248' />
- <node id='-127' action='modify' visible='true' lat='2.00026408654' lon='1.00493683035' />
- <node id='-125' action='modify' visible='true' lat='2.00029177846' lon='1.00482599511' />
- <node id='-123' action='modify' visible='true' lat='2.00035639296' lon='1.00472747489'>
+ <node id='-75' action='modify' visible='true' lat='2.00038408489' lon='1.00462587591' />
+ <node id='-77' action='modify' visible='true' lat='2.00043023809' lon='1.00449964688' />
+ <node id='-79' action='modify' visible='true' lat='2.00036870048' lon='1.00532783248' />
+ <node id='-81' action='modify' visible='true' lat='2.00026408654' lon='1.00493683035' />
+ <node id='-83' action='modify' visible='true' lat='2.00029177846' lon='1.00482599511' />
+ <node id='-85' action='modify' visible='true' lat='2.00035639296' lon='1.00472747489'>
<tag k='test:id' v='n-SW' />
<tag k='test:section' v='points-on-roads' />
</node>
- <node id='-121' action='modify' visible='true' lat='2.00022408708' lon='1.00452427693' />
- <node id='-119' action='modify' visible='true' lat='2.00071561841' lon='1.00183227343'>
+ <node id='-87' action='modify' visible='true' lat='2.00022408708' lon='1.00452427693' />
+ <node id='-89' action='modify' visible='true' lat='2.00071561841' lon='1.00183227343'>
<tag k='name' v='road-with-alley' />
</node>
- <node id='-117' action='modify' visible='true' lat='2.00072864414' lon='1.00046699629'>
+ <node id='-91' action='modify' visible='true' lat='2.00072864414' lon='1.00046699629'>
<tag k='name' v='roads-with-pois' />
</node>
- <node id='-115' action='modify' visible='true' lat='2.00035415446' lon='1.00244811443'>
+ <node id='-93' action='modify' visible='true' lat='2.00035415446' lon='1.00244811443'>
<tag k='test:id' v='n-main-east' />
<tag k='test:section' v='road-with-alley' />
</node>
- <node id='-113' action='modify' visible='true' lat='2.00026623078' lon='1.00155204948'>
+ <node id='-95' action='modify' visible='true' lat='2.00026623078' lon='1.00155204948'>
<tag k='test:id' v='n-main-west' />
<tag k='test:section' v='road-with-alley' />
</node>
- <node id='-111' action='modify' visible='true' lat='2.00054628396' lon='1.00192350914'>
+ <node id='-97' action='modify' visible='true' lat='2.00054628396' lon='1.00192350914'>
<tag k='test:id' v='n-alley' />
<tag k='test:section' v='road-with-alley' />
</node>
- <node id='-109' action='modify' visible='true' lat='2.00035415446' lon='1.00192350914'>
+ <node id='-99' action='modify' visible='true' lat='2.00035415446' lon='1.00192350914'>
<tag k='test:id' v='n-corner' />
<tag k='test:section' v='road-with-alley' />
</node>
- <node id='-107' action='modify' visible='true' lat='2.00057559185' lon='1.00195935173' />
- <node id='-105' action='modify' visible='true' lat='2.00029879511' lon='1.00260777692' />
- <node id='-103' action='modify' visible='true' lat='2.00031182084' lon='1.0023699124' />
- <node id='-101' action='modify' visible='true' lat='2.00031507727' lon='1.00212553105' />
- <node id='-99' action='modify' visible='true' lat='2.00030856441' lon='1.00195935173' />
- <node id='-97' action='modify' visible='true' lat='2.00030530797' lon='1.00167586937' />
- <node id='-95' action='modify' visible='true' lat='2.00031182084' lon='1.00134351073' />
- <node id='-93' action='modify' visible='true' lat='2.00040546963' lon='1.00011736285'>
+ <node id='-101' action='modify' visible='true' lat='2.00057559185' lon='1.00195935173' />
+ <node id='-103' action='modify' visible='true' lat='2.00029879511' lon='1.00260777692' />
+ <node id='-105' action='modify' visible='true' lat='2.00031182084' lon='1.0023699124' />
+ <node id='-107' action='modify' visible='true' lat='2.00031507727' lon='1.00212553105' />
+ <node id='-109' action='modify' visible='true' lat='2.00030856441' lon='1.00195935173' />
+ <node id='-111' action='modify' visible='true' lat='2.00030530797' lon='1.00167586937' />
+ <node id='-113' action='modify' visible='true' lat='2.00031182084' lon='1.00134351073' />
+ <node id='-115' action='modify' visible='true' lat='2.00040546963' lon='1.00011736285'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-91' action='modify' visible='true' lat='2.00039759893' lon='1.00042975784'>
+ <node id='-117' action='modify' visible='true' lat='2.00039759893' lon='1.00042975784'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-89' action='modify' visible='true' lat='2.000457941' lon='1.00066077263'>
+ <node id='-119' action='modify' visible='true' lat='2.000457941' lon='1.00066077263'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-87' action='modify' visible='true' lat='2.00054189517' lon='1.00106242333'>
+ <node id='-121' action='modify' visible='true' lat='2.00054189517' lon='1.00106242333'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-85' action='modify' visible='true' lat='2.00019033703' lon='1.00013836419'>
+ <node id='-123' action='modify' visible='true' lat='2.00019033703' lon='1.00013836419'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-83' action='modify' visible='true' lat='2.00019820773' lon='1.00072115149'>
+ <node id='-125' action='modify' visible='true' lat='2.00019820773' lon='1.00072115149'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-81' action='modify' visible='true' lat='2.00021919628' lon='1.00106767367'>
+ <node id='-127' action='modify' visible='true' lat='2.00021919628' lon='1.00106767367'>
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-79' action='modify' visible='true' lat='2.00033988043' lon='1.00039038032'>
+ <node id='-129' action='modify' visible='true' lat='2.00033988043' lon='1.00039038032'>
<tag k='test:id' v='p-N2' />
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-77' action='modify' visible='true' lat='2.0002926562' lon='1.0008104072'>
+ <node id='-131' action='modify' visible='true' lat='2.0002926562' lon='1.0008104072'>
<tag k='test:id' v='p-S1' />
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-75' action='modify' visible='true' lat='2.00052877733' lon='1.0005321394'>
+ <node id='-133' action='modify' visible='true' lat='2.00052877733' lon='1.0005321394'>
<tag k='test:id' v='p-N1' />
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-73' action='modify' visible='true' lat='2.00010638283' lon='1.00063977128'>
+ <node id='-135' action='modify' visible='true' lat='2.00010638283' lon='1.00063977128'>
<tag k='test:id' v='p-S2' />
<tag k='test:section' v='roads-with-pois' />
</node>
- <node id='-71' action='modify' visible='true' lat='2.00072116924' lon='1.00358286582'>
+ <node id='-137' action='modify' visible='true' lat='2.00072116924' lon='1.00358286582'>
<tag k='name' v='building-on-street-corner' />
</node>
- <node id='-69' action='modify' visible='true' lat='2.00023163534' lon='1.00317592051' />
- <node id='-67' action='modify' visible='true' lat='2.00022108767' lon='1.00403607904' />
- <node id='-65' action='modify' visible='true' lat='2.0002263615' lon='1.00427354612' />
- <node id='-63' action='modify' visible='true' lat='2.00010506339' lon='1.00404135609' />
- <node id='-61' action='modify' visible='true' lat='2.00063772026' lon='1.00403639915' />
- <node id='-59' action='modify' visible='true' lat='2.00043885695' lon='1.0039042871' />
- <node id='-57' action='modify' visible='true' lat='2.00043855477' lon='1.00400225362' />
- <node id='-55' action='modify' visible='true' lat='2.0002343878' lon='1.00400162309' />
- <node id='-53' action='modify' visible='true' lat='2.00023468998' lon='1.00390365657' />
- <node id='-51' action='modify' visible='true' lat='2.00032403886' lon='1.00400189996'>
+ <node id='-139' action='modify' visible='true' lat='2.00023163534' lon='1.00317592051' />
+ <node id='-141' action='modify' visible='true' lat='2.00022108767' lon='1.00403607904' />
+ <node id='-143' action='modify' visible='true' lat='2.0002263615' lon='1.00427354612' />
+ <node id='-145' action='modify' visible='true' lat='2.00010506339' lon='1.00404135609' />
+ <node id='-147' action='modify' visible='true' lat='2.00063772026' lon='1.00403639915' />
+ <node id='-149' action='modify' visible='true' lat='2.00043885695' lon='1.0039042871' />
+ <node id='-151' action='modify' visible='true' lat='2.00043855477' lon='1.00400225362' />
+ <node id='-153' action='modify' visible='true' lat='2.0002343878' lon='1.00400162309' />
+ <node id='-155' action='modify' visible='true' lat='2.00023468998' lon='1.00390365657' />
+ <node id='-157' action='modify' visible='true' lat='2.00032403886' lon='1.00400189996'>
<tag k='test:id' v='n-edge-NS' />
<tag k='test:section' v='building-on-street-corner' />
</node>
- <node id='-49' action='modify' visible='true' lat='2.00035479802' lon='1.00393848586'>
+ <node id='-159' action='modify' visible='true' lat='2.00035479802' lon='1.00393848586'>
<tag k='test:id' v='n-inner' />
<tag k='test:section' v='building-on-street-corner' />
</node>
- <node id='-47' action='modify' visible='true' lat='2.00046760515' lon='1.00394781445'>
+ <node id='-161' action='modify' visible='true' lat='2.00046760515' lon='1.00394781445'>
<tag k='test:id' v='n-outer' />
<tag k='test:section' v='building-on-street-corner' />
</node>
- <node id='-45' action='modify' visible='true' lat='2.00023451637' lon='1.00395994156'>
+ <node id='-163' action='modify' visible='true' lat='2.00023451637' lon='1.00395994156'>
<tag k='test:id' v='n-edge-WE' />
<tag k='test:section' v='building-on-street-corner' />
</node>
- <node id='-43' action='modify' visible='true' lat='2.0001892102' lon='1.00653236169' />
- <node id='-41' action='modify' visible='true' lat='2.00027856164' lon='1.00667595302' />
- <node id='-39' action='modify' visible='true' lat='2.00027856164' lon='1.00681954435' />
- <node id='-37' action='modify' visible='true' lat='2.00025148545' lon='1.00691707809' />
- <node id='-35' action='modify' visible='true' lat='2.0001892102' lon='1.00704170453' />
- <node id='-33' action='modify' visible='true' lat='2.00017296448' lon='1.00724219054' />
- <node id='-31' action='modify' visible='true' lat='2.00062243814' lon='1.00685396461'>
+ <node id='-165' action='modify' visible='true' lat='2.0001892102' lon='1.00653236169' />
+ <node id='-167' action='modify' visible='true' lat='2.00027856164' lon='1.00667595302' />
+ <node id='-169' action='modify' visible='true' lat='2.00027856164' lon='1.00681954435' />
+ <node id='-171' action='modify' visible='true' lat='2.00025148545' lon='1.00691707809' />
+ <node id='-173' action='modify' visible='true' lat='2.0001892102' lon='1.00704170453' />
+ <node id='-175' action='modify' visible='true' lat='2.00017296448' lon='1.00724219054' />
+ <node id='-177' action='modify' visible='true' lat='2.00062243814' lon='1.00685396461'>
<tag k='name' v='parallel-road' />
</node>
- <node id='-29' action='modify' visible='true' lat='2.00033813812' lon='1.00653156143'>
+ <node id='-179' action='modify' visible='true' lat='2.00033813812' lon='1.00653156143'>
<tag k='test:id' v='n-middle-w' />
<tag k='test:section' v='parallel-road' />
</node>
- <node id='-27' action='modify' visible='true' lat='2.00042478194' lon='1.00668598984' />
- <node id='-25' action='modify' visible='true' lat='2.00041665908' lon='1.00690002221' />
- <node id='-23' action='modify' visible='true' lat='2.00034084574' lon='1.00723597174'>
+ <node id='-181' action='modify' visible='true' lat='2.00042478194' lon='1.00668598984' />
+ <node id='-183' action='modify' visible='true' lat='2.00041665908' lon='1.00690002221' />
+ <node id='-185' action='modify' visible='true' lat='2.00034084574' lon='1.00723597174'>
<tag k='test:id' v='n-middle-e' />
<tag k='test:section' v='parallel-road' />
</node>
- <node id='-21' action='modify' visible='true' lat='2.00041801289' lon='1.00653968924' />
- <node id='-19' action='modify' visible='true' lat='2.00050736432' lon='1.00668328057' />
- <node id='-17' action='modify' visible='true' lat='2.00050736432' lon='1.00682687191' />
- <node id='-15' action='modify' visible='true' lat='2.00048028813' lon='1.00692440564' />
- <node id='-13' action='modify' visible='true' lat='2.00041801289' lon='1.00704903208' />
- <node id='-11' action='modify' visible='true' lat='2.00040176717' lon='1.00724951809' />
+ <node id='-187' action='modify' visible='true' lat='2.00041801289' lon='1.00653968924' />
+ <node id='-189' action='modify' visible='true' lat='2.00050736432' lon='1.00668328057' />
+ <node id='-191' action='modify' visible='true' lat='2.00050736432' lon='1.00682687191' />
+ <node id='-193' action='modify' visible='true' lat='2.00048028813' lon='1.00692440564' />
+ <node id='-195' action='modify' visible='true' lat='2.00041801289' lon='1.00704903208' />
+ <node id='-197' action='modify' visible='true' lat='2.00040176717' lon='1.00724951809' />
+ <node id='-199' action='modify' visible='true' lat='2.00102300625' lon='1.00316327416'>
+ <tag k='test:id' v='n-south-w' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </node>
+ <node id='-201' action='modify' visible='true' lat='2.00101492424' lon='1.00369431688' />
+ <node id='-203' action='modify' visible='true' lat='2.00102031225' lon='1.00407170765' />
+ <node id='-205' action='modify' visible='true' lat='2.00100684223' lon='1.00433588118'>
+ <tag k='test:id' v='n-south-e' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </node>
+ <node id='-207' action='modify' visible='true' lat='2.00148637497' lon='1.00362153438'>
+ <tag k='name' v='building-with-parallel-streets' />
+ </node>
+ <node id='-209' action='modify' visible='true' lat='2.00126546664' lon='1.00315114374'>
+ <tag k='test:id' v='n-north-w' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </node>
+ <node id='-211' action='modify' visible='true' lat='2.00125738463' lon='1.00368218646' />
+ <node id='-213' action='modify' visible='true' lat='2.00126277264' lon='1.00405957723' />
+ <node id='-215' action='modify' visible='true' lat='2.00124930262' lon='1.00432375077'>
+ <tag k='test:id' v='n-north-e' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </node>
+ <node id='-217' action='modify' visible='true' lat='2.00118914388' lon='1.00361572227' />
+ <node id='-219' action='modify' visible='true' lat='2.0010786539' lon='1.00361659971' />
+ <node id='-221' action='modify' visible='true' lat='2.001080471' lon='1.003845694' />
+ <node id='-223' action='modify' visible='true' lat='2.00119096098' lon='1.00384481656' />
<node id='100000' timestamp='2014-01-01T00:00:00Z' uid='1' user='test' visible='true' version='1' changeset='1' lat='2.0' lon='1.0' />
<node id='100001' timestamp='2014-01-01T00:00:00Z' uid='1' user='test' visible='true' version='1' changeset='1' lat='3.0' lon='1.0' />
<node id='100002' timestamp='2014-01-01T00:00:00Z' uid='1' user='test' visible='true' version='1' changeset='1' lat='3.0' lon='2.0' />
<node id='100003' timestamp='2014-01-01T00:00:00Z' uid='1' user='test' visible='true' version='1' changeset='1' lat='2.0' lon='2.0' />
- <way id='-237' action='modify' visible='true'>
- <nd ref='-183' />
- <nd ref='-185' />
- <nd ref='-187' />
+ <way id='-229' action='modify' visible='true'>
+ <nd ref='-25' />
+ <nd ref='-23' />
+ <nd ref='-21' />
<tag k='test:id' v='w-5' />
<tag k='test:section' v='split-road' />
</way>
- <way id='-235' action='modify' visible='true'>
- <nd ref='-173' />
- <nd ref='-175' />
- <nd ref='-177' />
- <nd ref='-179' />
- <nd ref='-181' />
- <nd ref='-183' />
+ <way id='-231' action='modify' visible='true'>
+ <nd ref='-35' />
+ <nd ref='-33' />
+ <nd ref='-31' />
+ <nd ref='-29' />
+ <nd ref='-27' />
+ <nd ref='-25' />
<tag k='test:id' v='w-4a' />
<tag k='test:section' v='split-road' />
</way>
<way id='-233' action='modify' visible='true'>
- <nd ref='-165' />
- <nd ref='-167' />
- <nd ref='-169' />
- <nd ref='-171' />
- <nd ref='-173' />
+ <nd ref='-43' />
+ <nd ref='-41' />
+ <nd ref='-39' />
+ <nd ref='-37' />
+ <nd ref='-35' />
<tag k='test:id' v='w-3' />
<tag k='test:section' v='split-road' />
</way>
- <way id='-231' action='modify' visible='true'>
- <nd ref='-157' />
- <nd ref='-159' />
- <nd ref='-161' />
- <nd ref='-163' />
- <nd ref='-165' />
+ <way id='-235' action='modify' visible='true'>
+ <nd ref='-51' />
+ <nd ref='-49' />
+ <nd ref='-47' />
+ <nd ref='-45' />
+ <nd ref='-43' />
<tag k='test:id' v='w-2' />
<tag k='test:section' v='split-road' />
</way>
- <way id='-229' action='modify' visible='true'>
- <nd ref='-173' />
- <nd ref='-189' />
- <nd ref='-191' />
- <nd ref='-193' />
- <nd ref='-195' />
- <nd ref='-183' />
+ <way id='-237' action='modify' visible='true'>
+ <nd ref='-35' />
+ <nd ref='-19' />
+ <nd ref='-17' />
+ <nd ref='-15' />
+ <nd ref='-13' />
+ <nd ref='-25' />
<tag k='test:id' v='w-4b' />
<tag k='test:section' v='split-road' />
</way>
- <way id='-227' action='modify' visible='true'>
- <nd ref='-153' />
- <nd ref='-155' />
- <nd ref='-157' />
+ <way id='-239' action='modify' visible='true'>
+ <nd ref='-55' />
+ <nd ref='-53' />
+ <nd ref='-51' />
<tag k='test:id' v='w-1' />
<tag k='test:section' v='split-road' />
</way>
- <way id='-225' action='modify' visible='true'>
- <nd ref='-131' />
- <nd ref='-133' />
- <nd ref='-135' />
- <nd ref='-149' />
- <nd ref='-141' />
- <nd ref='-137' />
+ <way id='-241' action='modify' visible='true'>
+ <nd ref='-77' />
+ <nd ref='-75' />
+ <nd ref='-73' />
+ <nd ref='-59' />
+ <nd ref='-67' />
+ <nd ref='-71' />
<tag k='test:id' v='w-north' />
<tag k='test:section' v='points-on-roads' />
</way>
- <way id='-223' action='modify' visible='true'>
- <nd ref='-121' />
- <nd ref='-143' />
- <nd ref='-123' />
- <nd ref='-125' />
- <nd ref='-127' />
- <nd ref='-139' />
- <nd ref='-129' />
+ <way id='-243' action='modify' visible='true'>
+ <nd ref='-87' />
+ <nd ref='-65' />
+ <nd ref='-85' />
+ <nd ref='-83' />
+ <nd ref='-81' />
+ <nd ref='-69' />
+ <nd ref='-79' />
<tag k='test:id' v='w-south' />
<tag k='test:section' v='points-on-roads' />
</way>
- <way id='-221' action='modify' visible='true'>
- <nd ref='-99' />
- <nd ref='-107' />
+ <way id='-245' action='modify' visible='true'>
+ <nd ref='-109' />
+ <nd ref='-101' />
<tag k='test:id' v='w-alley' />
<tag k='test:section' v='road-with-alley' />
</way>
- <way id='-219' action='modify' visible='true'>
- <nd ref='-95' />
- <nd ref='-97' />
- <nd ref='-99' />
- <nd ref='-101' />
- <nd ref='-103' />
+ <way id='-247' action='modify' visible='true'>
+ <nd ref='-113' />
+ <nd ref='-111' />
+ <nd ref='-109' />
+ <nd ref='-107' />
<nd ref='-105' />
+ <nd ref='-103' />
<tag k='test:id' v='w-main' />
<tag k='test:section' v='road-with-alley' />
</way>
- <way id='-217' action='modify' visible='true'>
- <nd ref='-93' />
- <nd ref='-91' />
- <nd ref='-89' />
- <nd ref='-87' />
+ <way id='-249' action='modify' visible='true'>
+ <nd ref='-115' />
+ <nd ref='-117' />
+ <nd ref='-119' />
+ <nd ref='-121' />
<tag k='test:id' v='w-north' />
<tag k='test:section' v='roads-with-pois' />
</way>
- <way id='-215' action='modify' visible='true'>
- <nd ref='-85' />
- <nd ref='-83' />
- <nd ref='-81' />
+ <way id='-251' action='modify' visible='true'>
+ <nd ref='-123' />
+ <nd ref='-125' />
+ <nd ref='-127' />
<tag k='test:id' v='w-south' />
<tag k='test:section' v='roads-with-pois' />
</way>
- <way id='-213' action='modify' visible='true'>
- <nd ref='-69' />
- <nd ref='-67' />
- <nd ref='-65' />
+ <way id='-253' action='modify' visible='true'>
+ <nd ref='-139' />
+ <nd ref='-141' />
+ <nd ref='-143' />
<tag k='test:id' v='w-WE' />
<tag k='test:section' v='building-on-street-corner' />
</way>
- <way id='-211' action='modify' visible='true'>
- <nd ref='-63' />
- <nd ref='-67' />
- <nd ref='-61' />
+ <way id='-255' action='modify' visible='true'>
+ <nd ref='-145' />
+ <nd ref='-141' />
+ <nd ref='-147' />
<tag k='test:id' v='w-NS' />
<tag k='test:section' v='building-on-street-corner' />
</way>
- <way id='-209' action='modify' visible='true'>
- <nd ref='-59' />
- <nd ref='-57' />
- <nd ref='-51' />
- <nd ref='-55' />
- <nd ref='-45' />
- <nd ref='-53' />
- <nd ref='-59' />
+ <way id='-257' action='modify' visible='true'>
+ <nd ref='-149' />
+ <nd ref='-151' />
+ <nd ref='-157' />
+ <nd ref='-153' />
+ <nd ref='-163' />
+ <nd ref='-155' />
+ <nd ref='-149' />
<tag k='area' v='yes' />
<tag k='test:id' v='w-building' />
<tag k='test:section' v='building-on-street-corner' />
</way>
- <way id='-207' action='modify' visible='true'>
- <nd ref='-43' />
- <nd ref='-41' />
- <nd ref='-39' />
- <nd ref='-37' />
- <nd ref='-35' />
- <nd ref='-33' />
+ <way id='-259' action='modify' visible='true'>
+ <nd ref='-165' />
+ <nd ref='-167' />
+ <nd ref='-169' />
+ <nd ref='-171' />
+ <nd ref='-173' />
+ <nd ref='-175' />
<tag k='test:id' v='w-south' />
<tag k='test:section' v='parallel-road' />
</way>
- <way id='-205' action='modify' visible='true'>
- <nd ref='-29' />
- <nd ref='-27' />
- <nd ref='-25' />
- <nd ref='-23' />
+ <way id='-261' action='modify' visible='true'>
+ <nd ref='-179' />
+ <nd ref='-181' />
+ <nd ref='-183' />
+ <nd ref='-185' />
<tag k='test:id' v='w-middle' />
<tag k='test:section' v='parallel-road' />
</way>
- <way id='-203' action='modify' visible='true'>
- <nd ref='-21' />
- <nd ref='-19' />
- <nd ref='-17' />
- <nd ref='-15' />
- <nd ref='-13' />
- <nd ref='-11' />
+ <way id='-263' action='modify' visible='true'>
+ <nd ref='-187' />
+ <nd ref='-189' />
+ <nd ref='-191' />
+ <nd ref='-193' />
+ <nd ref='-195' />
+ <nd ref='-197' />
<tag k='test:id' v='w-north' />
<tag k='test:section' v='parallel-road' />
</way>
+ <way id='-265' action='modify' visible='true'>
+ <nd ref='-199' />
+ <nd ref='-201' />
+ <nd ref='-203' />
+ <nd ref='-205' />
+ <tag k='test:id' v='w-south' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </way>
+ <way id='-267' action='modify' visible='true'>
+ <nd ref='-209' />
+ <nd ref='-211' />
+ <nd ref='-213' />
+ <nd ref='-215' />
+ <tag k='test:id' v='w-north' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </way>
+ <way id='-269' action='modify' visible='true'>
+ <nd ref='-217' />
+ <nd ref='-219' />
+ <nd ref='-221' />
+ <nd ref='-223' />
+ <nd ref='-217' />
+ <tag k='test:id' v='w-building' />
+ <tag k='test:section' v='building-with-parallel-streets' />
+ </way>
<way id='100000' timestamp='2014-01-01T00:00:00Z' uid='1' user='test' visible='true' version='1' changeset='1'>
<nd ref='100000' />
<nd ref='100001' />
+
@step(u'table ([a-z_]+) contains$')
def check_placex_content(step, tablename):
""" check that the given lines are in the given table
q = 'SELECT *'
if tablename == 'placex':
q = q + ", ST_X(centroid) as clat, ST_Y(centroid) as clon"
- q = q + ", ST_GeometryType(geometry) as geometrytype"
- q = q + ' FROM %s where osm_type = %%s and osm_id = %%s' % (tablename,)
+ if tablename == 'location_property_osmline':
+ q = q + ' FROM %s where osm_id = %%s' % (tablename,)
+ else:
+ q = q + ", ST_GeometryType(geometry) as geometrytype"
+ q = q + ' FROM %s where osm_type = %%s and osm_id = %%s' % (tablename,)
if cls is None:
- params = (osmtype, osmid)
+ if tablename == 'location_property_osmline':
+ params = (osmid,)
+ else:
+ params = (osmtype, osmid)
else:
q = q + ' and class = %s'
- params = (osmtype, osmid, cls)
+ if tablename == 'location_property_osmline':
+ params = (osmid, cls)
+ else:
+ params = (osmtype, osmid, cls)
cur.execute(q, params)
assert(cur.rowcount > 0)
for res in cur:
cur.close()
world.conn.commit()
+@step(u'table location_property_osmline has no entry for W(\d+)?')
+def check_osmline_missing(step, osmid):
+ cur = world.conn.cursor()
+ try:
+ q = 'SELECT count(*) FROM location_property_osmline where osm_id = %s' % (osmid, )
+ cur.execute(q)
+ numres = cur.fetchone()[0]
+ assert_equals (numres, 0)
+ finally:
+ cur.close()
+ world.conn.commit()
+
@step(u'search_name table contains$')
def check_search_name_content(step):
cur = world.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
else:
raise Exception("Cannot handle field %s in search_name table" % (k, ))
-@step(u'way (\d+) expands to housenumbers')
-def check_interpolated_housenumbers(step, nodeid):
- """Check that the exact set of housenumbers has been entered in
- placex for the given source node. Expected are two columns:
- housenumber and centroid
+@step(u'way (\d+) expands to lines')
+def check_interpolation_lines(step, wayid):
+ """Check that the correct interpolation line has been entered in
+ location_property_osmline for the given source line/nodes.
+ Expected are three columns:
+ startnumber, endnumber and linegeo
"""
- numbers = {}
+ lines = []
for line in step.hashes:
- assert line["housenumber"] not in numbers
- numbers[line["housenumber"]] = line["centroid"]
+ lines.append((line["startnumber"], line["endnumber"], line["geometry"]))
cur = world.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
- cur.execute("""SELECT DISTINCT housenumber,
- ST_X(centroid) as clat, ST_Y(centroid) as clon
- FROM placex WHERE osm_type = 'W' and osm_id = %s
- and class = 'place' and type = 'address'""",
- (int(nodeid),))
- assert_equals(len(numbers), cur.rowcount)
+ cur.execute("""SELECT startnumber::text, endnumber::text, st_astext(linegeo) as geometry
+ FROM location_property_osmline WHERE osm_id = %s""",
+ (int(wayid),))
+ assert_equals(len(lines), cur.rowcount)
for r in cur:
- assert_in(r["housenumber"], numbers)
- world.match_geometry((r['clat'], r['clon']), numbers[r["housenumber"]])
- del numbers[r["housenumber"]]
+ linegeo = str(str(r["geometry"].split('(')[1]).split(')')[0]).replace(',', ', ')
+ exp = (r["startnumber"], r["endnumber"], linegeo)
+ assert_in(exp, lines)
+ lines.remove(exp)
@step(u'way (\d+) expands exactly to housenumbers ([0-9,]*)')
def check_interpolated_housenumber_list(step, nodeid, numberlist):
cur = world.conn.cursor()
cur.execute("""SELECT housenumber FROM placex
WHERE osm_type = 'W' and osm_id = %s
- and class = 'place' and type = 'address'""", (int(nodeid),))
+ and class = 'place' and type = 'address'""", (int(nodeid),))
for r in cur:
assert_in(r[0], expected, "Unexpected house number %s for node %s." % (r[0], nodeid))
expected.remove(r[0])
import subprocess
import random
import base64
+import sys
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
@step(u'the (named )?place (node|way|area)s')
def import_place_table_nodes(step, named, osmtype):
- """Insert a list of nodes into the placex table.
- Expects a table where columns are named in the same way as placex.
+ """Insert a list of nodes into the place table.
+ Expects a table where columns are named in the same way as place.
"""
cur = world.conn.cursor()
cur.execute('ALTER TABLE place DISABLE TRIGGER place_before_insert')
""" Runs the actual indexing. """
world.run_nominatim_script('setup', 'create-functions', 'create-partition-functions')
cur = world.conn.cursor()
+ #world.db_dump_table('place')
cur.execute("""insert into placex (osm_type, osm_id, class, type, name, admin_level,
housenumber, street, addr_place, isin, postcode, country_code, extratags,
- geometry) select * from place""")
+ geometry) select * from place where not (class='place' and type='houses' and osm_type='W')""")
+ cur.execute("""select insert_osmline (osm_id, housenumber, street, addr_place, postcode, country_code, geometry) from place where class='place' and type='houses' and osm_type='W'""")
world.conn.commit()
world.run_nominatim_script('setup', 'index', 'index-noanalyse')
#world.db_dump_table('placex')
-
+ #world.db_dump_table('location_property_osmline')
@step(u'updating place (node|way|area)s')
def update_place_table_nodes(step, osmtype):
- """ Replace a geometry in place by reinsertion and reindex database.
- """
+ """ Replace a geometry in place by reinsertion and reindex database."""
world.run_nominatim_script('setup', 'create-functions', 'create-partition-functions', 'enable-diff-updates')
if osmtype == 'node':
_insert_place_table_nodes(step.hashes, False)
logger.debug("Distances expected: %f, got: %f" % (expdist, dist))
assert dist <= expdist, "Geometry too far away, expected: %f, got: %f" % (expdist, dist)
+@world.absorb
+def print_statement(element):
+ print '\n\n\n'+str(element)+'\n\n\n'
@world.absorb
def db_dump_table(table):
cur = world.conn.cursor()
cur.execute('SELECT * FROM %s' % table)
- print '<<<<<<< BEGIN OF TABLE DUMP %s' % table
+ print '\n\n\n<<<<<<< BEGIN OF TABLE DUMP %s' % table
for res in cur:
print res
- print '<<<<<<< END OF TABLE DUMP %s' % table
+ print '<<<<<<< END OF TABLE DUMP %s\n\n\n' % table
@world.absorb
def db_drop_database(name):
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 "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;
}