From: Markus Gail Date: Mon, 25 Apr 2016 07:44:01 +0000 (+0200) Subject: Remove interpolation lines from placex and save them in an extra table. X-Git-Tag: v3.0.0~170^2~11^2~8 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/7879ad44cd1e36ae1235c3dcdfd9e477491813e4?hp=-c Remove interpolation lines from placex and save them in an extra table. --- 7879ad44cd1e36ae1235c3dcdfd9e477491813e4 diff --git a/lib/Geocode.php b/lib/Geocode.php index d699263e..620e0e50 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -433,21 +433,44 @@ if (30 >= $this->iMinAddressRank && 30 <= $this->iMaxAddressRank) { - //query also location_property_tiger and location_property_aux + //query also location_property_osmline and 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 .= ", "; } + // osmline, osm_type is 'I' for Interpolation Line + $sSQL .= " union "; + $sSQL .= "select 'I' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, calculated_country_code as country_code, "; + $sSQL .= "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 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 "; - $sSQL .= "union "; + // tiger + $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"; @@ -460,13 +483,16 @@ $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 .= ", 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_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 "; + // aux $sSQL .= " union "; $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 0 as rank_search, 0 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code, "; $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, "; @@ -875,7 +901,7 @@ // 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']; } @@ -1303,7 +1329,11 @@ 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 and transliteration(housenumber) ~* E'".$sHouseNumberRegex."' limit 1) "; + // also housenumbers from interpolation lines table are needed + $aOrder[0] .= " or exists(select place_id from location_property_osmline where parent_place_id = search_name.place_id and ".$aSearch['sHouseNumber'].">=startnumber and ".$aSearch['sHouseNumber']."<=endnumber limit 1)"; + $aOrder[0] .= " desc"; } // TODO: filter out the pointless search terms (2 letter name tokens and less) @@ -1418,6 +1448,7 @@ //now search for housenumber, if housenumber provided if ($aSearch['sHouseNumber'] && sizeof($aPlaceIDs)) { + $searchedHousenumber = intval($aSearch['sHouseNumber']); $aRoadPlaceIDs = $aPlaceIDs; $sPlaceIDs = join(',',$aPlaceIDs); @@ -1431,21 +1462,43 @@ $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 (!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 nothing found search in Tiger data for this housenumber(location_property_tiger) if (!sizeof($aPlaceIDs)) { //new query for lines, not housenumbers anymore @@ -1614,6 +1667,7 @@ { // 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'"; @@ -1621,7 +1675,8 @@ $sSQL .= ") UNION select place_id from location_property_tiger where place_id in (".join(',',array_keys($aResultPlaceIDs)).") "; $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank "; if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',',$this->aAddressRankList).")"; - $sSQL .= ")"; + $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(); diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php index 817d0ec5..07988927 100644 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@ -117,7 +117,7 @@ { $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(); } @@ -140,12 +140,33 @@ 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, 'I' 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 .= " coalesce(null,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, "; @@ -172,7 +193,7 @@ if ($this->bAddressDetails) { - if($this->sType == 'tiger') // to get addressdetails for tiger data, the housenumber is needed + if ($this->sType == 'tiger' || $this->sType == 'interpolation') // to get addressdetails for interpolation lines and tiger data, the housenumber is needed $aAddress = $this->getAddressNames($aPlace['housenumber']); else $aAddress = $this->getAddressNames(); diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index 3dff161e..57131434 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -121,6 +121,7 @@ $fMaxAreaDistance = 1; $bIsInUnitedStates = false; $bPlaceIsTiger = false; + $bPlaceIsLine = false; while(!$iPlaceID && $fSearchDiam < $fMaxAreaDistance) { $fSearchDiam = $fSearchDiam * 2; @@ -156,7 +157,107 @@ $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 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 || $aPlace['rank_search'] == 30)) + { + $fSearchDiam = 0.001; + if ($aPlace['rank_search'] == 30) + { + // if a house was found, the closest road needs to be searched, to use its place id as parent_place_id of the interpolation line + // because a road can be closer to the point than the house from above + $iRoadID = null; + while(!$iRoadID && $fSearchDiam < $fMaxAreaDistance) + { + $fSearchDiam = $fSearchDiam * 2; + $sSQL = 'select place_id '; + $sSQL .= ' FROM placex'; + $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')'; + $sSQL .= ' and (rank_search = 26 or rank_search = 27)'; + $sSQL .= ' and class not in (\'waterway\',\'railway\',\'tunnel\',\'bridge\',\'man_made\')'; + $sSQL .= ' and indexed_status = 0 '; + $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', geometry) ASC limit 1'; + $aPlaceRoad = $this->oDB->getRow($sSQL); + if (PEAR::IsError($aPlace)) + { + failInternalError("Could not determine closest place.", $sSQL, $aPlace); + } + $iRoadID = $aPlaceRoad['place_id']; + $iTempPlaceID = $iRoadID; + } + } + else + { + // if a street was found, we can take its place_id as parent_place_id + $iTempPlaceID = $iPlaceID; + } + $sSQL = 'SELECT place_id, parent_place_id, 30 as rank_search, ST_line_locate_point(linegeo,'.$sPointSQL.') as fraction'; + //if (CONST_Debug) { $sSQL .= ', housenumber, ST_distance('.$sPointSQL.', centroid) as distance, st_y(centroid) as lat, st_x(centroid) as lon'; } + $sSQL .= ' FROM location_property_osmline WHERE parent_place_id = '.$iTempPlaceID; + $sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.') 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']. ' | '. "
\n"; + } + } + $aPlaceLine = $this->oDB->getRow($sSQL); + if (PEAR::IsError($aPlaceLine)) + { + failInternalError("Could not determine closest housenumber on an osm interpolation line.", $sSQL, $aPlaceLine); + } + $iInterpolationLinePlaceID = $aPlaceLine['place_id']; + 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 = '.$iInterpolationLinePlaceID; + $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 = $iInterpolationLinePlaceID; + $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 ($bIsInUnitedStates && $iMaxRank_orig >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 )) { @@ -180,25 +281,25 @@ } $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; } @@ -217,10 +318,9 @@ $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); } } diff --git a/lib/template/address-json.php b/lib/template/address-json.php index 6fd101bb..6254357a 100644 --- a/lib/template/address-json.php +++ b/lib/template/address-json.php @@ -12,7 +12,8 @@ { 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; diff --git a/lib/template/address-jsonv2.php b/lib/template/address-jsonv2.php index 89403875..64c871c1 100644 --- a/lib/template/address-jsonv2.php +++ b/lib/template/address-jsonv2.php @@ -12,7 +12,8 @@ { 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; diff --git a/lib/template/address-xml.php b/lib/template/address-xml.php index 109e66a1..a3369d34 100644 --- a/lib/template/address-xml.php +++ b/lib/template/address-xml.php @@ -22,7 +22,8 @@ { echo ""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; diff --git a/lib/template/search-jsonv2.php b/lib/template/search-jsonv2.php index 6f23ae26..251388b5 100644 --- a/lib/template/search-jsonv2.php +++ b/lib/template/search-jsonv2.php @@ -7,7 +7,7 @@ '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; diff --git a/lib/template/search-xml.php b/lib/template/search-xml.php index 54217933..dd02b7c7 100644 --- a/lib/template/search-xml.php +++ b/lib/template/search-xml.php @@ -25,7 +25,8 @@ foreach($aSearchResults as $iResNum => $aResult) { echo " 0 + * triggers placex_update and osmline_update */ #include @@ -70,6 +72,16 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co 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", @@ -104,7 +116,28 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co 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++) @@ -126,6 +159,17 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co 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) @@ -147,8 +191,156 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co fprintf(stderr, "Starting indexing rank (%i to %i) using %i threads\n", rank_min, rank_max, num_threads); + // first for the placex table for (rank = rank_min; rank <= rank_max; rank++) { + // 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, "Starting indexing interpolation lines (location_property_osmline)\n"); + rankCountTuples = 0; + rankTotalTuples = 0; + resSectors = PQexecPrepared(conn, "index_sectors_osmline", 0, NULL, 0, NULL, 1); + if (PQresultStatus(resSectors) != PGRES_TUPLES_OK) + { + fprintf(stderr, "index_sectors_osmline: 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); + } + rankStartTime = time(0); + for (iSector = 0; iSector < PQntuples(resSectors); iSector++) + { + rankTotalTuples += PGint64(*((uint64_t *)PQgetvalue(resSectors, iSector, 1))); + } + // do it only if tuples with indexed_status > 0 were found in osmline + int nTuples = PQntuples(resSectors); + if (nTuples > 0) + { + for (iSector = 0; iSector <= nTuples; iSector++) + { + if (iSector > 0) + { + resPlaces = PQgetResult(conn); + if (PQresultStatus(resPlaces) != PGRES_TUPLES_OK) + { + fprintf(stderr, "index_sector_places: SELECT failed: %s\n", 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 < nTuples) + { + 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 + paramSector = PGint32(sector); + paramValues[0] = (char *)¶mSector; + paramLengths[0] = sizeof(paramSector); + paramFormats[0] = 1; + if (rankTotalTuples-rankCountTuples < num_threads*1000) + { + // no sectors + iResult = PQsendQueryPrepared(conn, "index_nosector_places_osmline", 0, NULL, 0, NULL, 1); + } + else + { + iResult = PQsendQueryPrepared(conn, "index_sector_places_osmline", 1, paramValues, paramLengths, paramFormats, 1); + } + if (!iResult) + { + fprintf(stderr, "index_sector_places_osmline: 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; + thread_data[i].table = 0; // use osmline 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); + 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)))/(float)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 < nTuples) + { + iSector = nTuples - 1; + } + } + PQclear(resSectors); + } + // Finished rank + fprintf(stderr, "\r Done %i tuples in %i seconds- FINISHED\n", rankCountTuples,(int)(difftime(time(0), rankStartTime))); + if (writer) + { + nominatim_exportXMLEnd(writer); + } + } fprintf(stderr, "Starting rank %d\n", rank); rankCountTuples = 0; rankPerSecond = 0; @@ -180,7 +372,7 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co PQclear(resSectors); exit(EXIT_FAILURE); } - + rankTotalTuples = 0; for (iSector = 0; iSector < PQntuples(resSectors); iSector++) { @@ -188,6 +380,7 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co } rankStartTime = time(0); + for (iSector = 0; iSector <= PQntuples(resSectors); iSector++) { if (iSector > 0) @@ -228,13 +421,13 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co 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)); @@ -260,6 +453,7 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co thread_data[i].count_mutex = &count_mutex; thread_data[i].writer = writer; thread_data[i].writer_mutex = &writer_mutex; + thread_data[i].table = 1; // use placex table pthread_create(&thread_data[i].thread, NULL, &nominatim_indexThread, (void *)&thread_data[i]); } @@ -303,18 +497,17 @@ void nominatim_index(int rank_min, int rank_max, int num_threads, const char *co PQclear(resSectors); } + - if (writer) + if (rank_max == 30) { - nominatim_exportXMLEnd(writer); - } - - // Close all connections - for (i = 0; i < num_threads; i++) - { - PQfinish(thread_data[i].conn); + // Close all connections + for (i = 0; i < num_threads; i++) + { + PQfinish(thread_data[i].conn); + } + PQfinish(conn); } - PQfinish(conn); } void *nominatim_indexThread(void * thread_data_in) @@ -324,12 +517,15 @@ 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) { @@ -348,37 +544,58 @@ void *nominatim_indexThread(void * thread_data_in) 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); diff --git a/nominatim/index.h b/nominatim/index.h index 1b698eed..46c8bb8a 100644 --- a/nominatim/index.h +++ b/nominatim/index.h @@ -14,6 +14,7 @@ struct index_thread_data 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); diff --git a/sql/functions.sql b/sql/functions.sql index b88a7f81..66b53d51 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -567,8 +567,7 @@ $$ 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 $$ @@ -635,11 +634,12 @@ END; $$ 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; @@ -649,26 +649,24 @@ DECLARE 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; @@ -680,7 +678,6 @@ BEGIN linegeo := geom; startnumber := NULL; - newpoints := 0; FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP @@ -691,82 +688,62 @@ BEGIN 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; + --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id; IF nextnode.place_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 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) < 1000 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; + + -- a correction for odd/even is NOT necessary anymore (e.g. if interpolationtype=even, but start/endnumber != even) + -- see PlaceLookup.php + + -- 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'; + insert into location_property_osmline + values (sectiongeo, nextval('seq_place'), partition, wayid, NULL, startnumber, endnumber, interpolationtype, prevnode.admin_level, + street, coalesce(prevnode.postcode, defpostalcode), + calculated_country_code, geometry_sector, 2, now()); + 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; + -- 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; + 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 @@ -777,10 +754,11 @@ 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; @@ -1053,6 +1031,8 @@ BEGIN 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); @@ -1081,7 +1061,38 @@ END; $$ 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; + + IF OLD.indexed_status = 2 and NEW.indexed_status=0 THEN + -- do the reparenting: (finally here, so that ALL places in placex, that are needed for reparenting, are up to date) + -- (the osm interpolationline in location_property_osmline was marked for reparenting in placex_insert/placex_delete with index_status = 2 + -- => index.c: sets index_status back to 0 + -- => triggers this function) + place_centroid := ST_PointOnSurface(NEW.linegeo); + -- mark descendants for reparenting + UPDATE placex SET indexed_status = 2 WHERE parent_place_id = OLD.place_id; + NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.street, null, NEW.partition, place_centroid, NEW.linegeo); -- addr_place (3rd param) is not necessarily needed + return NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION placex_update() RETURNS TRIGGER AS $$ DECLARE @@ -1125,7 +1136,6 @@ DECLARE result BOOLEAN; BEGIN - -- deferred delete IF OLD.indexed_status = 100 THEN --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id; @@ -1134,6 +1144,14 @@ BEGIN END IF; IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN + + -- 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) + -- needed for test case features/db/import: Scenario: addr:street added to housenumbers + 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; RETURN NEW; END IF; @@ -1152,647 +1170,634 @@ BEGIN 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 + 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 + -- Is this node part of a way? search for the way in placex AND location_property_osmline (for interpolation lines) + FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.class, p.type, 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) + UNION + select q.place_id, q.osm_id, q.parent_place_id, 'place' as class, 'houses' as type, 30 as rank_search, null as street, + null as addr_place 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) + 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 + -- 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; + NEW.parent_place_id := location.place_id; + 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; + -- 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 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; + -- 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; - -- 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; + -- 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; - 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; + 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 LOOP; + END LOOP; - END IF; + 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 + IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN - -- see if we have any special relation members - select members from planet_osm_rels where id = NEW.osm_id INTO relation_members; + -- see if we have any special relation members + select members from planet_osm_rels where id = NEW.osm_id INTO relation_members; -- 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 - - 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 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; + -- 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; - -- 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; + -- 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; - -- 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 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; + + -- 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; -- 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; $$ @@ -1827,6 +1832,8 @@ BEGIN --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; @@ -1885,7 +1892,7 @@ BEGIN -- 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'; + UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id) END IF; RETURN OLD; @@ -1900,6 +1907,7 @@ DECLARE i INTEGER; existing RECORD; existingplacex RECORD; + existingline RECORD; existinggeometry GEOMETRY; existingplace_id BIGINT; result BOOLEAN; @@ -1908,214 +1916,277 @@ BEGIN --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; - + RAISE WARNING 'X3366 - place_isnert'; + -- 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); -- RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; 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; + + -- decide, whether its 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; - -- 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; - - --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; - - 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; - - 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; + -- 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 (this trigger is executed BEFORE INSERT of the NEW tupel) + -- 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.housenumber, '') != coalesce(NEW.housenumber, '') THEN - RAISE WARNING 'update details, housenumber: % % % %',NEW.osm_type,NEW.osm_id,existing.housenumber,NEW.housenumber; + + 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 + IF existingline.osm_id IS NULL THEN + 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; + -- insert new line into location_property_osmline, use function insert_osmline + 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; + 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; + + -- 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) + delete from location_property_osmline where osm_id = NEW.osm_id; + 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.addr_place, '') != coalesce(NEW.addr_place, '') THEN - RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.addr_place,NEW.addr_place; + + -- 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); + RAISE WARNING 'X3399 - updated nodes of interpolation line'; + RETURN NULL; + + ELSE -- insert to placex + + 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; - IF coalesce(existing.isin, '') != coalesce(NEW.isin, '') THEN - RAISE WARNING 'update details, isin: % % % %',NEW.osm_type,NEW.osm_id,existing.isin,NEW.isin; + -- 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); + 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; + + -- 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; $$ LANGUAGE plpgsql; @@ -2228,7 +2299,7 @@ create type addressline as ( 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; @@ -2244,20 +2315,31 @@ DECLARE countryname HSTORE; hadcountry BOOLEAN; BEGIN - --first query tiger data - 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 + 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; + IF for_place_id IS NULL THEN select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux WHERE place_id = in_place_id INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; END IF; - + IF for_place_id IS NULL THEN select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex WHERE place_id = in_place_id and rank_address = 30 diff --git a/sql/tables.sql b/sql/tables.sql index 0434e13a..3ec3c318 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -109,6 +109,27 @@ 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}"; +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, + admin_level INTEGER, + street TEXT, + postcode TEXT, + calculated_country_code VARCHAR(2), + geometry_sector INTEGER, + indexed_status INTEGER, + indexed_date TIMESTAMP); +CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline (place_id) {ts:search-index}; +CREATE INDEX idx_osmline_parent_place_id ON location_property_osmline (parent_place_id) {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, @@ -167,13 +188,15 @@ GRANT SELECT ON planet_osm_ways to "{www-user}" ; 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 diff --git a/tests/features/db/import/interpolation.feature b/tests/features/db/import/interpolation.feature index 195cbfdf..356c7e16 100644 --- a/tests/features/db/import/interpolation.feature +++ b/tests/features/db/import/interpolation.feature @@ -2,41 +2,23 @@ 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 @@ -48,77 +30,9 @@ Feature: Import of address interpolations | 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 @@ -132,29 +46,9 @@ Feature: Import of address interpolations | 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 @@ -168,27 +62,11 @@ Feature: Import of address interpolations | 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 @@ -200,14 +78,11 @@ Feature: Import of address interpolations | 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 @@ -219,13 +94,11 @@ Feature: Import of address interpolations | 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 @@ -238,14 +111,12 @@ Feature: Import of address interpolations | 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 @@ -259,15 +130,13 @@ Feature: Import of address interpolations | 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 @@ -280,14 +149,12 @@ Feature: Import of address interpolations | 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 @@ -300,12 +167,12 @@ Feature: Import of address interpolations | 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 @@ -318,12 +185,12 @@ Feature: Import of address interpolations | 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 - 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 @@ -335,24 +202,24 @@ Feature: Import of address interpolations | 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 @@ -360,49 +227,49 @@ Feature: Import of address interpolations | 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 @@ -417,10 +284,10 @@ Feature: Import of address interpolations | 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 @@ -435,7 +302,6 @@ Feature: Import of address interpolations | 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 diff --git a/tests/features/db/update/interpolation.feature b/tests/features/db/update/interpolation.feature index bfc93b12..4075ba47 100644 --- a/tests/features/db/update/interpolation.feature +++ b/tests/features/db/update/interpolation.feature @@ -23,17 +23,19 @@ Feature: Update of address interpolations | 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 @@ -57,18 +59,20 @@ Feature: Update of address interpolations | 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 @@ -92,10 +96,11 @@ Feature: Update of address interpolations | 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 @@ -122,17 +127,19 @@ Feature: Update of address interpolations | 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 @@ -156,12 +163,14 @@ Feature: Update of address interpolations | 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 diff --git a/tests/steps/db_results.py b/tests/steps/db_results.py index 71a30927..7fe4da30 100644 --- a/tests/steps/db_results.py +++ b/tests/steps/db_results.py @@ -35,6 +35,7 @@ def check_placex_names(step, osmtyp, osmid): + @step(u'table ([a-z_]+) contains$') def check_placex_content(step, tablename): """ check that the given lines are in the given table @@ -49,13 +50,21 @@ def check_placex_content(step, tablename): 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 + ' 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: @@ -92,6 +101,18 @@ def check_placex_missing(step, tablename, osmtyp, osmid, placeclass): 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) @@ -134,12 +155,35 @@ def check_interpolated_housenumbers(step, nodeid): FROM placex WHERE osm_type = 'W' and osm_id = %s and class = 'place' and type = 'address'""", (int(nodeid),)) + assert_equals(len(numbers), 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"]] +@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 + """ + lines = {} + for line in step.hashes: + assert line["startnumber"] not in lines + lines[line["startnumber"]] = {'endnumber': line["endnumber"], 'geometry': line["geometry"]} + cur = world.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) + 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["startnumber"], lines) + assert_equals(r["endnumber"], lines[r["startnumber"]]["endnumber"]) + linegeo = str(str(r["geometry"].split('(')[1]).split(')')[0]).replace(',', ', ') + assert_equals(linegeo, lines[r["startnumber"]]["geometry"]) + del lines[r["startnumber"]] + @step(u'way (\d+) expands exactly to housenumbers ([0-9,]*)') def check_interpolated_housenumber_list(step, nodeid, numberlist): """ Checks that the interpolated house numbers corresponds @@ -149,7 +193,7 @@ 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]) diff --git a/tests/steps/db_setup.py b/tests/steps/db_setup.py index 23d526f8..727e6105 100644 --- a/tests/steps/db_setup.py +++ b/tests/steps/db_setup.py @@ -23,6 +23,7 @@ import os import subprocess import random import base64 +import sys psycopg2.extensions.register_type(psycopg2.extensions.UNICODE) @@ -143,8 +144,8 @@ def import_set_scene(step, scene): @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') @@ -214,18 +215,19 @@ def import_database(step): """ 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) diff --git a/tests/steps/terrain.py b/tests/steps/terrain.py index 312d76cf..0569578a 100644 --- a/tests/steps/terrain.py +++ b/tests/steps/terrain.py @@ -123,16 +123,19 @@ def match_geometry(coord, matchstring): 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): diff --git a/utils/setup.php b/utils/setup.php index a363fab3..4ed343df 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -404,6 +404,8 @@ 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)); @@ -445,27 +447,70 @@ echo "Load Data\n"; $aDBInstances = array(); + + $aQueriesPlacex = array(); + $aQueriesOsmline = array(); + // the query is divided into parcels, so that the work between the processes, i.e. the DBInstances, will be evenly distributed + $iNumberOfParcels = 100; + for($i = 0; $i < $iNumberOfParcels; $i++) + { + $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 % '.$iNumberOfParcels.' = '.$i.' and not '; + $sSQL .= '(class=\'place\' and type=\'houses\' and osm_type=\'W\' and ST_GeometryType(geometry) = \'ST_LineString\');'; + array_push($aQueriesPlacex, $sSQL); + $sSQL = 'select insert_osmline (osm_id, housenumber, street, addr_place, postcode, country_code, '; + $sSQL .= 'geometry) from place where osm_id % '.$iNumberOfParcels.' = '.$i.' and '; + $sSQL .= 'class=\'place\' and type=\'houses\' and osm_type=\'W\' and ST_GeometryType(geometry) = \'ST_LineString\''; + array_push($aQueriesOsmline, $sSQL); + } + for($i = 0; $i < $iInstances; $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; - if ($aCMDResult['verbose']) echo "$sSQL\n"; - if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection)); } - $bAnyBusy = true; - while($bAnyBusy) + // now execute the query blocks, in the first round for placex, then for osmline, + // because insert_osmline depends on the placex table + echo 'Inserting from place to placex.'; + $aQueries = $aQueriesPlacex; + for($j = 0; $j < 2; $j++) { - $bAnyBusy = false; - for($i = 0; $i < $iInstances; $i++) + $bAnyBusy = true; + while($bAnyBusy) { - if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true; + $bAnyBusy = false; + + for($i = 0; $i < $iInstances; $i++) + { + if (pg_connection_busy($aDBInstances[$i]->connection)) + { + $bAnyBusy = true; + } + else if (count($aQueries) > 0) + { + $query = array_pop($aQueries); + if (!pg_send_query($aDBInstances[$i]->connection, $query)) + { + fail(pg_last_error($oDB->connection)); + } + else + { + pg_get_result($aDBInstances[$i]->connection); + $bAnyBusy = true; + } + } + } + sleep(1); + echo '.'; + } + echo "\n"; + if ($j == 0) //for the second round with osmline + { + echo 'Inserting from place to osmline.'; + $aQueries = $aQueriesOsmline; } - sleep(1); - echo '.'; } - echo "\n"; + echo "Reanalysing database...\n"; pgsqlRunScript('ANALYSE'); } @@ -552,7 +597,6 @@ $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y "; $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x"; if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); - $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) "; $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',"; $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";