From: Markus Gail Date: Mon, 25 Apr 2016 09:01:04 +0000 (+0200) Subject: Merge remote-tracking branch 'origin/master' into osmline X-Git-Tag: v3.0.0~170^2~11^2~7 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/b03be15442502aa2e52bacff70fb983cf4397d70?ds=inline;hp=-c Merge remote-tracking branch 'origin/master' into osmline Conflicts: lib/Geocode.php lib/PlaceLookup.php sql/functions.sql sql/tables.sql utils/setup.php --- b03be15442502aa2e52bacff70fb983cf4397d70 diff --combined lib/Geocode.php index 620e0e50,611ca6de..de178431 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@@ -228,11 -228,9 +228,9 @@@ function loadParamArray($aParams) { if (isset($aParams['addressdetails'])) $this->bIncludeAddressDetails = (bool)$aParams['addressdetails']; - if ((float) CONST_Postgresql_Version > 9.2) - { - if (isset($aParams['extratags'])) $this->bIncludeExtraTags = (bool)$aParams['extratags']; - if (isset($aParams['namedetails'])) $this->bIncludeNameDetails = (bool)$aParams['namedetails']; - } + if (isset($aParams['extratags'])) $this->bIncludeExtraTags = (bool)$aParams['extratags']; + if (isset($aParams['namedetails'])) $this->bIncludeNameDetails = (bool)$aParams['namedetails']; + if (isset($aParams['bounded'])) $this->bBoundedSearch = (bool)$aParams['bounded']; if (isset($aParams['dedupe'])) $this->bDeDupe = (bool)$aParams['dedupe']; @@@ -433,82 -431,62 +431,84 @@@ if (30 >= $this->iMinAddressRank && 30 <= $this->iMaxAddressRank) { - //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) + if (CONST_Use_US_Tiger_Data) { - $i++; - $sHousenumbers .= "(".$placeID.", ".$housenumber.")"; - if($i<$length) - $sHousenumbers .= ", "; + //query also location_property_tiger and location_property_aux + //Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join) + //only Tiger housenumbers need to be interpolated, because they are saved as lines with start- and endnumber, the common osm housenumbers are usually saved as points + $sHousenumbers = ""; + $i = 0; + $length = count($aPlaceIDs); + foreach($aPlaceIDs as $placeID => $housenumber) + { + $i++; + $sHousenumbers .= "(".$placeID.", ".$housenumber.")"; + if($i<$length) + $sHousenumbers .= ", "; + } + + $sSQL .= "union "; + $sSQL .= "select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code"; + $sSQL .= ", get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress "; + $sSQL .= ", null as placename"; + $sSQL .= ", null as ref"; + if ($this->bIncludeExtraTags) $sSQL .= ", null as extra"; + if ($this->bIncludeNameDetails) $sSQL .= ", null as names"; + $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,"; + $sSQL .= $sImportanceSQL."-1.15 as importance "; + $sSQL .= ", (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance "; + $sSQL .= ", null as extra_place "; + $sSQL .= " from (select place_id"; + //interpolate the Tiger housenumbers here + $sSQL .= ", ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) as centroid, parent_place_id, housenumber_for_place "; + $sSQL .= "from (location_property_tiger "; + $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) "; + $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here + $sSQL .= " group by place_id, housenumber_for_place"; //is this group by really needed?, place_id + housenumber (in combination) are unique + if (!$this->bDeDupe) $sSQL .= ", place_id "; } + // osmline, 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 "; - // 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"; - $sSQL .= ", null as ref"; - if ($this->bIncludeExtraTags) $sSQL .= ", null as extra"; - if ($this->bIncludeNameDetails) $sSQL .= ", null as names"; - $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,"; - $sSQL .= $sImportanceSQL."-1.15 as importance "; - $sSQL .= ", (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance "; - $sSQL .= ", null as extra_place "; - $sSQL .= " from (select place_id"; - //interpolate the Tiger housenumbers here - $sSQL .= ", 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, "; - $sSQL .= "null as placename, "; - $sSQL .= "null as ref, "; - if ($this->bIncludeExtraTags) $sSQL .= "null as extra, "; - if ($this->bIncludeNameDetails) $sSQL .= "null as names, "; - $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, "; - $sSQL .= $sImportanceSQL."-1.10 as importance, "; - $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, "; - $sSQL .= "null as extra_place "; - $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) "; - $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank "; - $sSQL .= "group by place_id"; - if (!$this->bDeDupe) $sSQL .= ", place_id"; - $sSQL .= ", get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) "; + if (CONST_Use_Aux_Location_data) + { + $sSQL .= " union "; + $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 0 as rank_search, 0 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code, "; + $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, "; + $sSQL .= "null as placename, "; + $sSQL .= "null as ref, "; + if ($this->bIncludeExtraTags) $sSQL .= "null as extra, "; + if ($this->bIncludeNameDetails) $sSQL .= "null as names, "; + $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, "; + $sSQL .= $sImportanceSQL."-1.10 as importance, "; + $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, "; + $sSQL .= "null as extra_place "; + $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) "; + $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank "; + $sSQL .= "group by place_id"; + if (!$this->bDeDupe) $sSQL .= ", place_id"; + $sSQL .= ", get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) "; + } } $sSQL .= " order by importance desc"; @@@ -901,7 -879,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']; } @@@ -1329,11 -1307,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) @@@ -1448,7 -1422,6 +1448,7 @@@ //now search for housenumber, if housenumber provided if ($aSearch['sHouseNumber'] && sizeof($aPlaceIDs)) { + $searchedHousenumber = intval($aSearch['sHouseNumber']); $aRoadPlaceIDs = $aPlaceIDs; $sPlaceIDs = join(',',$aPlaceIDs); @@@ -1462,44 -1435,22 +1462,46 @@@ $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)) + if (CONST_Use_Aux_Location_data && !sizeof($aPlaceIDs)) { $sSQL = "select place_id from location_property_aux where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'"; if (sizeof($this->aExcludePlaceIDs)) { - $sSQL .= " and place_id not in (".join(',',$this->aExcludePlaceIDs).")"; + $sSQL .= " and parent_place_id not in (".join(',',$this->aExcludePlaceIDs).")"; } //$sSQL .= " limit $this->iLimit"; if (CONST_Debug) var_dump($sSQL); $aPlaceIDs = $this->oDB->getCol($sSQL); } - //if nothing found search in Tiger data for this housenumber(location_property_tiger) - if (!sizeof($aPlaceIDs)) ++ + //if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger) + $searchedHousenumber = intval($aSearch['sHouseNumber']); + if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs)) { //new query for lines, not housenumbers anymore if($searchedHousenumber%2 == 0){ @@@ -1667,16 -1618,17 +1669,18 @@@ { // 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'"; if ($this->aAddressRankList) $sSQL .= " OR placex.rank_address in (".join(',',$this->aAddressRankList).")"; - $sSQL .= ") UNION select place_id from location_property_tiger where place_id in (".join(',',array_keys($aResultPlaceIDs)).") "; - $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank "; - if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',',$this->aAddressRankList).")"; - $sSQL .= ") UNION select place_id from location_property_osmline where place_id in (".join(',',array_keys($aResultPlaceIDs)).") "; - $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank)"; + if (CONST_Use_US_Tiger_Data) + { + $sSQL .= ") UNION select place_id from location_property_tiger where place_id in (".join(',',array_keys($aResultPlaceIDs)).") "; + $sSQL .= "and (30 between $this->iMinAddressRank and $this->iMaxAddressRank "; + if ($this->aAddressRankList) $sSQL .= " OR 30 in (".join(',',$this->aAddressRankList).")"; + } + $sSQL .= ")"; if (CONST_Debug) var_dump($sSQL); $aFilteredPlaceIDs = $this->oDB->getCol($sSQL); $tempIDs = array(); diff --combined lib/PlaceLookup.php index 07988927,c1fec649..79c905d3 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@@ -42,18 -42,12 +42,12 @@@ function setIncludeExtraTags($bExtraTags = false) { - if ((float) CONST_Postgresql_Version > 9.2) - { - $this->bExtraTags = $bExtraTags; - } + $this->bExtraTags = $bExtraTags; } function setIncludeNameDetails($bNameDetails = false) { - if ((float) CONST_Postgresql_Version > 9.2) - { - $this->bNameDetails = $bNameDetails; - } + $this->bNameDetails = $bNameDetails; } @@@ -117,7 -111,7 +111,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(); } @@@ -128,7 -122,7 +122,7 @@@ $sLanguagePrefArraySQL = "ARRAY[".join(',',array_map("getDBQuoted", $this->aLangPrefOrder))."]"; - if ($this->sType == 'tiger') + if (CONST_Use_US_Tiger_Data && $this->sType == 'tiger') { $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, housenumber, null as street, null as isin, postcode,"; $sSQL .= " 'us' as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,"; @@@ -140,33 -134,12 +134,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, "; @@@ -193,7 -166,7 +187,7 @@@ if ($this->bAddressDetails) { - if ($this->sType == 'tiger' || $this->sType == 'interpolation') // to get addressdetails for interpolation lines and tiger data, the housenumber is needed + if(CONST_Use_US_Tiger_Data && $this->sType == 'tiger') // to get addressdetails for tiger data, the housenumber is needed $aAddress = $this->getAddressNames($aPlace['housenumber']); else $aAddress = $this->getAddressNames(); diff --combined lib/ReverseGeocode.php index 57131434,75a9b71c..0921711e --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@@ -121,7 -121,6 +121,7 @@@ $fMaxAreaDistance = 1; $bIsInUnitedStates = false; $bPlaceIsTiger = false; + $bPlaceIsLine = false; while(!$iPlaceID && $fSearchDiam < $fMaxAreaDistance) { $fSearchDiam = $fSearchDiam * 2; @@@ -157,109 -156,9 +157,109 @@@ $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 )) + if (CONST_Use_US_Tiger_Data && $bIsInUnitedStates && $iMaxRank_orig >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 )) { $fSearchDiam = 0.001; $sSQL = 'SELECT place_id,parent_place_id,30 as rank_search, ST_line_locate_point(linegeo,'.$sPointSQL.') as fraction'; @@@ -281,25 -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; } @@@ -318,9 -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 --combined sql/functions.sql index 66b53d51,4256490e..80763eaa --- a/sql/functions.sql +++ b/sql/functions.sql @@@ -1,3 -1,15 +1,15 @@@ + -- Splits the line at the given point and returns the two parts + -- in a multilinestring. + CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY) + RETURNS GEOMETRY + AS $$ + BEGIN + RETURN ST_Split(ST_Snap(line, point, 0.0005), point); + END; + $$ + LANGUAGE plpgsql; + + CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER AS $$ DECLARE @@@ -567,7 -579,8 +579,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 $$ @@@ -634,12 -647,11 +646,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,24 -661,26 +661,24 @@@ 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; @@@ -678,6 -692,7 +690,6 @@@ linegeo := geom; startnumber := NULL; - newpoints := 0; FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP @@@ -688,62 -703,82 +700,62 @@@ 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 @@@ -754,11 -789,10 +766,11 @@@ 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; @@@ -990,7 -1024,7 +1002,7 @@@ --DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; - RETURN NEW; -- @DIFFUPDATES@ The following is not needed until doing diff updates, and slows the main index process down + RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down IF NEW.rank_address > 0 THEN IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN @@@ -1031,8 -1065,6 +1043,8 @@@ 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); @@@ -1061,38 -1093,7 +1073,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 @@@ -1136,6 -1137,7 +1148,6 @@@ result BOOLEAN; BEGIN - -- deferred delete IF OLD.indexed_status = 100 THEN --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id; @@@ -1144,14 -1146,6 +1156,14 @@@ 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; @@@ -1170,634 -1164,649 +1182,637 @@@ 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; + result := deleteSearchName(NEW.partition, NEW.place_id); + DELETE FROM place_addressline WHERE place_id = NEW.place_id; + result := deleteRoad(NEW.partition, NEW.place_id); + result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search); + UPDATE placex set linked_place_id = null, indexed_status = 2 + where linked_place_id = NEW.place_id; + -- update not necessary for osmline, cause linked_place_id does not exist - IF NEW.linked_place_id is not null THEN - RETURN NEW; - END IF; + IF NEW.linked_place_id is not null THEN + RETURN NEW; + END IF; - -- Speed up searches - just use the centroid of the feature - -- cheaper but less acurate - place_centroid := ST_PointOnSurface(NEW.geometry); - NEW.centroid := null; - - -- recalculate country and partition - IF NEW.rank_search = 4 THEN - -- for countries, believe the mapped country code, - -- so that we remain in the right partition if the boundaries - -- suddenly expand. - NEW.partition := get_partition(lower(NEW.country_code)); - IF NEW.partition = 0 THEN - NEW.calculated_country_code := lower(get_country_code(place_centroid)); - NEW.partition := get_partition(NEW.calculated_country_code); - ELSE - NEW.calculated_country_code := lower(NEW.country_code); - END IF; - ELSE - IF NEW.rank_search > 4 THEN - --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code)); - NEW.calculated_country_code := lower(get_country_code(place_centroid)); - ELSE - NEW.calculated_country_code := NULL; - END IF; + -- Speed up searches - just use the centroid of the feature + -- cheaper but less acurate + place_centroid := ST_PointOnSurface(NEW.geometry); + NEW.centroid := null; + + -- recalculate country and partition + IF NEW.rank_search = 4 THEN + -- for countries, believe the mapped country code, + -- so that we remain in the right partition if the boundaries + -- suddenly expand. + NEW.partition := get_partition(lower(NEW.country_code)); + IF NEW.partition = 0 THEN + NEW.calculated_country_code := lower(get_country_code(place_centroid)); NEW.partition := get_partition(NEW.calculated_country_code); + ELSE + NEW.calculated_country_code := lower(NEW.country_code); END IF; - NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid); - - -- interpolations - IF NEW.class = 'place' AND NEW.type = 'houses'THEN - IF NEW.osm_type = 'W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN - NEW.parent_place_id := get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place, - NEW.partition, place_centroid, NEW.geometry); - i := create_interpolation(NEW.osm_id, NEW.housenumber, NEW.parent_place_id, - NEW.partition, NEW.calculated_country_code, - NEW.geometry_sector, NEW.postcode, NEW.geometry); - END IF; - RETURN NEW; + ELSE + IF NEW.rank_search > 4 THEN + --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code)); + NEW.calculated_country_code := lower(get_country_code(place_centroid)); + ELSE + NEW.calculated_country_code := NULL; END IF; + NEW.partition := get_partition(NEW.calculated_country_code); + END IF; - -- waterway ways are linked when they are part of a relation and have the same class/type - IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN - FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] - LOOP - FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP - IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN - --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.members[i]; - FOR linked_node_id IN SELECT place_id FROM placex - WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint - and class = NEW.class and type = NEW.type - and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') - LOOP - UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; - END LOOP; - END IF; - END LOOP; - END LOOP; - END IF; + -- waterway ways are linked when they are part of a relation and have the same class/type + IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN + FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] + LOOP + FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP + IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN + --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.members[i]; + FOR linked_node_id IN SELECT place_id FROM placex + WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint + and class = NEW.class and type = NEW.type + and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') + LOOP + UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; + END LOOP; + END IF; + END LOOP; + END LOOP; + END IF; - -- Adding ourselves to the list simplifies address calculations later - INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); - - -- What level are we searching from - search_maxrank := NEW.rank_search; - - -- Thought this wasn't needed but when we add new languages to the country_name table - -- we need to update the existing names - IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN - default_language := get_country_language_code(NEW.calculated_country_code); - IF default_language IS NOT NULL THEN - IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN - NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); - ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN - NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); - END IF; + -- Adding ourselves to the list simplifies address calculations later + INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); + + -- What level are we searching from + search_maxrank := NEW.rank_search; + + -- Thought this wasn't needed but when we add new languages to the country_name table + -- we need to update the existing names + IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN + default_language := get_country_language_code(NEW.calculated_country_code); + IF default_language IS NOT NULL THEN + IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN + NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); + ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN + NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); END IF; END IF; + END IF; - -- Initialise the name vector using our name - name_vector := make_keywords(NEW.name); - nameaddress_vector := '{}'::int[]; + -- Initialise the name vector using our name + name_vector := make_keywords(NEW.name); + nameaddress_vector := '{}'::int[]; - FOR i IN 1..28 LOOP - address_havelevel[i] := false; - END LOOP; + FOR i IN 1..28 LOOP + address_havelevel[i] := false; + END LOOP; - NEW.importance := null; - select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance; - IF NEW.importance IS NULL THEN - select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; - END IF; + NEW.importance := null; + select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance; + IF NEW.importance IS NULL THEN + select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; + END IF; --RAISE WARNING 'before low level% %', NEW.place_id, NEW.rank_search; - -- For low level elements we inherit from our parent road - IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN + -- For low level elements we inherit from our parent road + IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN --RAISE WARNING 'finding street for %', NEW; - -- We won't get a better centroid, besides these places are too small to care - NEW.centroid := place_centroid; - - NEW.parent_place_id := null; - - -- if we have a POI and there is no address information, - -- see if we can get it from a surrounding building - IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL - AND NEW.housenumber IS NULL THEN - FOR location IN select * from placex where ST_Covers(geometry, place_centroid) - and (housenumber is not null or street is not null or addr_place is not null) - and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') - limit 1 - LOOP - NEW.housenumber := location.housenumber; - NEW.street := location.street; - NEW.addr_place := location.addr_place; - END LOOP; - END IF; + -- We won't get a better centroid, besides these places are too small to care + NEW.centroid := place_centroid; + + NEW.parent_place_id := null; + + -- if we have a POI and there is no address information, + -- see if we can get it from a surrounding building + IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL + AND NEW.housenumber IS NULL THEN + FOR location IN select * from placex where ST_Covers(geometry, place_centroid) + and (housenumber is not null or street is not null or addr_place is not null) + and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + limit 1 + LOOP + NEW.housenumber := location.housenumber; + NEW.street := location.street; + NEW.addr_place := location.addr_place; + END LOOP; + END IF; - -- We have to find our parent road. - -- Copy data from linked items (points on ways, addr:street links, relations) + -- We have to find our parent road. + -- Copy data from linked items (points on ways, addr:street links, relations) - -- Is this object part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id] - LOOP - -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] THEN - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN + -- Is this object part of a relation? + FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id] + LOOP + -- At the moment we only process one type of relation - associatedStreet + IF relation.tags @> ARRAY['associatedStreet'] THEN + FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP + IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN --RAISE WARNING 'node in relation %',relation; - SELECT place_id from placex where osm_type = 'W' - and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 and name is not null INTO NEW.parent_place_id; - END IF; - END LOOP; + SELECT place_id from placex where osm_type = 'W' + and osm_id = substring(relation.members[i],2,200)::bigint + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; + END IF; + END LOOP; - -- Note that addr:street links can only be indexed once the street itself is indexed - IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; + -- Note that addr:street links can only be indexed once the street itself is indexed + IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; END IF; + END IF; - IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; + IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; END IF; + END IF; - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN + 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 + 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 we don't already have one use this as the centre point of the geometry - IF NEW.centroid IS NULL THEN - NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); - END IF; + FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) + and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP - -- merge in the label name, re-init word vector - IF NOT linkedPlacex.name IS NULL THEN - NEW.name := linkedPlacex.name || NEW.name; - name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name)); - END IF; + -- If we don't already have one use this as the centre point of the geometry + IF NEW.centroid IS NULL THEN + NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); + END IF; - -- merge in extra tags - NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); + -- merge in the label name, re-init word vector + IF NOT linkedPlacex.name IS NULL THEN + NEW.name := linkedPlacex.name || NEW.name; + name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name)); + END IF; - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + -- merge in extra tags + NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - END LOOP; + -- mark the linked place (excludes from search results) + UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + -- keep a note of the node id in case we need it for wikipedia in a bit + linked_node_id := linkedPlacex.osm_id; END LOOP; - IF NEW.centroid IS NULL THEN + END LOOP; - FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP + IF NEW.centroid IS NULL THEN - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) - and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP + FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP - -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york' - -- But that can be fixed by explicitly setting the label in the data - IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') - AND NEW.rank_address = linkedPlacex.rank_address THEN + FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) + and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP - -- If we don't already have one use this as the centre point of the geometry - IF NEW.centroid IS NULL THEN - NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); - END IF; + -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york' + -- But that can be fixed by explicitly setting the label in the data + IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') + AND NEW.rank_address = linkedPlacex.rank_address THEN - -- merge in the name, re-init word vector - IF NOT linkedPlacex.name IS NULL THEN - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); - END IF; + -- If we don't already have one use this as the centre point of the geometry + IF NEW.centroid IS NULL THEN + NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); + END IF; - -- merge in extra tags - NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); + -- merge in the name, re-init word vector + IF NOT linkedPlacex.name IS NULL THEN + NEW.name := linkedPlacex.name || NEW.name; + name_vector := make_keywords(NEW.name); + END IF; - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + -- merge in extra tags + NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - END IF; + -- mark the linked place (excludes from search results) + UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - END LOOP; + -- keep a note of the node id in case we need it for wikipedia in a bit + linked_node_id := linkedPlacex.osm_id; + END IF; END LOOP; - END IF; - END IF; + END LOOP; + END IF; END IF; - -- Name searches can be done for ways as well as relations - IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN + END IF; - -- not found one yet? how about doing a name search - IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN + -- Name searches can be done for ways as well as relations + IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN - FOR linkedPlacex IN select placex.* from placex WHERE - make_standard_name(name->'name') = make_standard_name(NEW.name->'name') - AND placex.rank_address = NEW.rank_address - AND placex.place_id != NEW.place_id - AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26 - AND st_covers(NEW.geometry, placex.geometry) - LOOP + -- not found one yet? how about doing a name search + IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN - -- If we don't already have one use this as the centre point of the geometry - IF NEW.centroid IS NULL THEN - NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); - END IF; + FOR linkedPlacex IN select placex.* from placex WHERE + make_standard_name(name->'name') = make_standard_name(NEW.name->'name') + AND placex.rank_address = NEW.rank_address + AND placex.place_id != NEW.place_id + AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26 + AND st_covers(NEW.geometry, placex.geometry) + LOOP - -- merge in the name, re-init word vector - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); + -- If we don't already have one use this as the centre point of the geometry + IF NEW.centroid IS NULL THEN + NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); + END IF; - -- merge in extra tags - NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); + -- merge in the name, re-init word vector + NEW.name := linkedPlacex.name || NEW.name; + name_vector := make_keywords(NEW.name); - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + -- merge in extra tags + NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - END LOOP; - END IF; + -- mark the linked place (excludes from search results) + UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - IF NEW.centroid IS NOT NULL THEN - place_centroid := NEW.centroid; - -- Place might have had only a name tag before but has now received translations - -- from the linked place. Make sure a name tag for the default language exists in - -- this case. - IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN - default_language := get_country_language_code(NEW.calculated_country_code); - IF default_language IS NOT NULL THEN - IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN - NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); - ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN - NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); - END IF; + -- keep a note of the node id in case we need it for wikipedia in a bit + linked_node_id := linkedPlacex.osm_id; + END LOOP; + END IF; + + IF NEW.centroid IS NOT NULL THEN + place_centroid := NEW.centroid; + -- Place might have had only a name tag before but has now received translations + -- from the linked place. Make sure a name tag for the default language exists in + -- this case. + IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN + default_language := get_country_language_code(NEW.calculated_country_code); + IF default_language IS NOT NULL THEN + IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN + NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); + ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN + NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); END IF; END IF; END IF; - - -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance - IF NEW.importance is null THEN - select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance; - END IF; - -- Still null? how about looking it up by the node id - IF NEW.importance IS NULL THEN - select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; - END IF; - END IF; - -- make sure all names are in the word table - IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN - perform create_country(NEW.name, lower(NEW.country_code)); + -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance + IF NEW.importance is null THEN + select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance; + END IF; + -- Still null? how about looking it up by the node id + IF NEW.importance IS NULL THEN + select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; END IF; - NEW.parent_place_id = 0; - parent_place_id_rank = 0; - - -- convert isin to array of tokenids - isin_tokens := '{}'::int[]; - IF NEW.isin IS NOT NULL THEN - isin := regexp_split_to_array(NEW.isin, E'[;,]'); - IF array_upper(isin, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(isin, 1) LOOP - address_street_word_id := get_name_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); - isin_tokens := isin_tokens || address_street_word_id; - END IF; + END IF; - -- merge word into address vector - address_street_word_id := get_word_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); - END IF; - END LOOP; - END IF; - END IF; - IF NEW.postcode IS NOT NULL THEN - isin := regexp_split_to_array(NEW.postcode, E'[;,]'); - IF array_upper(isin, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(isin, 1) LOOP - address_street_word_id := get_name_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); - isin_tokens := isin_tokens || address_street_word_id; - END IF; + -- make sure all names are in the word table + IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN + perform create_country(NEW.name, lower(NEW.country_code)); + END IF; - -- merge into address vector - address_street_word_id := get_word_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); - END IF; - END LOOP; - END IF; + NEW.parent_place_id = 0; + parent_place_id_rank = 0; + ++ + -- convert isin to array of tokenids + isin_tokens := '{}'::int[]; + IF NEW.isin IS NOT NULL THEN + isin := regexp_split_to_array(NEW.isin, E'[;,]'); + IF array_upper(isin, 1) IS NOT NULL THEN + FOR i IN 1..array_upper(isin, 1) LOOP + address_street_word_id := get_name_id(make_standard_name(isin[i])); + IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + isin_tokens := isin_tokens || address_street_word_id; + END IF; + + -- 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; - -- %NOTIGERDATA% IF 0 THEN - -- for the USA we have an additional address table. Merge in zip codes from there too - IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN - FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP - address_street_word_id := get_name_id(make_standard_name(location.postcode)); - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); - isin_tokens := isin_tokens || address_street_word_id; - - -- also merge in the single word version - address_street_word_id := get_word_id(make_standard_name(location.postcode)); - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + -- 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; - -- %NOTIGERDATA% END IF; + END IF; + ++ -- %NOTIGERDATA% IF 0 THEN + -- for the USA we have an additional address table. Merge in zip codes from there too + IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN + FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP + address_street_word_id := get_name_id(make_standard_name(location.postcode)); + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + isin_tokens := isin_tokens || address_street_word_id; + + -- also merge in the single word version + address_street_word_id := get_word_id(make_standard_name(location.postcode)); + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + END LOOP; + END IF; ++ -- %NOTIGERDATA% END IF; -- RAISE WARNING 'ISIN: %', isin_tokens; - -- Process area matches - location_rank_search := 0; - location_distance := 0; - location_parent := NULL; - -- added ourself as address already - address_havelevel[NEW.rank_address] := true; - -- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens; - FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP + -- Process area matches + location_rank_search := 0; + location_distance := 0; + location_parent := NULL; + -- added ourself as address already + address_havelevel[NEW.rank_address] := true; + -- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens; + FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP --RAISE WARNING ' AREA: %',location; - IF location.rank_address != location_rank_search THEN - location_rank_search := location.rank_address; - IF location.isguess THEN - location_distance := location.distance * 1.5; + IF location.rank_address != location_rank_search THEN + location_rank_search := location.rank_address; + IF location.isguess THEN + location_distance := location.distance * 1.5; + ELSE + IF location.rank_address <= 12 THEN + -- for county and above, if we have an area consider that exact + -- (It would be nice to relax the constraint for places close to + -- the boundary but we'd need the exact geometry for that. Too + -- expensive.) + location_distance = 0; ELSE - IF location.rank_address <= 12 THEN - -- for county and above, if we have an area consider that exact - -- (It would be nice to relax the constraint for places close to - -- the boundary but we'd need the exact geometry for that. Too - -- expensive.) - location_distance = 0; - ELSE - -- Below county level remain slightly fuzzy. - location_distance := location.distance * 0.5; - END IF; + -- Below county level remain slightly fuzzy. + location_distance := location.distance * 0.5; END IF; - ELSE - CONTINUE WHEN location.keywords <@ location_keywords; END IF; + ELSE + CONTINUE WHEN location.keywords <@ location_keywords; + END IF; - IF location.distance < location_distance OR NOT location.isguess THEN - location_keywords := location.keywords; - - location_isaddress := NOT address_havelevel[location.rank_address]; - IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN - location_isaddress := ST_Contains(location_parent,location.centroid); - END IF; - - -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress; - -- Add it to the list of search terms - IF location.rank_search > 4 THEN - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); - END IF; - INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address); + IF location.distance < location_distance OR NOT location.isguess THEN + location_keywords := location.keywords; - IF location_isaddress THEN + location_isaddress := NOT address_havelevel[location.rank_address]; + IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN + location_isaddress := ST_Contains(location_parent,location.centroid); + END IF; - address_havelevel[location.rank_address] := true; - IF NOT location.isguess THEN - SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent; - END IF; + -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress; + -- Add it to the list of search terms + IF location.rank_search > 4 THEN + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + END IF; + INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address); - IF location.rank_address > parent_place_id_rank THEN - NEW.parent_place_id = location.place_id; - parent_place_id_rank = location.rank_address; - END IF; + IF location_isaddress THEN + address_havelevel[location.rank_address] := true; + IF NOT location.isguess THEN + SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent; END IF; ---RAISE WARNING ' Terms: (%) %',location, nameaddress_vector; + IF location.rank_address > parent_place_id_rank THEN + NEW.parent_place_id = location.place_id; + parent_place_id_rank = location.rank_address; + END IF; END IF; - END LOOP; - - -- try using the isin value to find parent places - IF array_upper(isin_tokens, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(isin_tokens, 1) LOOP ---RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i]; - IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN - - FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP +--RAISE WARNING ' Terms: (%) %',location, nameaddress_vector; - --RAISE WARNING ' ISIN: %',location; + END IF; - IF location.rank_search > 4 THEN - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); - INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); - address_havelevel[location.rank_address] := true; + END LOOP; - IF location.rank_address > parent_place_id_rank THEN - NEW.parent_place_id = location.place_id; - parent_place_id_rank = location.rank_address; - END IF; - END IF; - END LOOP; + -- try using the isin value to find parent places + IF array_upper(isin_tokens, 1) IS NOT NULL THEN + FOR i IN 1..array_upper(isin_tokens, 1) LOOP +--RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i]; + IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN - END IF; + FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP - END LOOP; - END IF; +--RAISE WARNING ' ISIN: %',location; - -- for long ways we should add search terms for the entire length - IF st_length(NEW.geometry) > 0.05 THEN + IF location.rank_search > 4 THEN + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); + address_havelevel[location.rank_address] := true; - location_rank_search := 0; - location_distance := 0; + IF location.rank_address > parent_place_id_rank THEN + NEW.parent_place_id = location.place_id; + parent_place_id_rank = location.rank_address; + END IF; + END IF; + END LOOP; - FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP + END IF; - IF location.rank_address != location_rank_search THEN - location_rank_search := location.rank_address; - location_distance := location.distance * 1.5; - END IF; + END LOOP; + END IF; - IF location.rank_search > 4 AND location.distance < location_distance THEN + -- for long ways we should add search terms for the entire length + IF st_length(NEW.geometry) > 0.05 THEN - -- Add it to the list of search terms - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); - INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address); + location_rank_search := 0; + location_distance := 0; - END IF; + FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP - END LOOP; + IF location.rank_address != location_rank_search THEN + location_rank_search := location.rank_address; + location_distance := location.distance * 1.5; + END IF; - END IF; + IF location.rank_search > 4 AND location.distance < location_distance THEN - -- if we have a name add this to the name search table - IF NEW.name IS NOT NULL THEN + -- Add it to the list of search terms + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address); - IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN - result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; - IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN - result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry); - END IF; + END LOOP; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); + END IF; + -- if we have a name add this to the name search table + IF NEW.name IS NOT NULL THEN + + IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN + result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; - -- If we've not managed to pick up a better one - default centroid - IF NEW.centroid IS NULL THEN - NEW.centroid := place_centroid; + IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN + result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry); END IF; + result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); + END IF; + -- If we've not managed to pick up a better one - default centroid + IF NEW.centroid IS NULL THEN + NEW.centroid := place_centroid; + END IF; + RETURN NEW; END; $$ @@@ -1832,8 -1841,6 +1847,8 @@@ BEGI --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; @@@ -1892,7 -1899,7 +1907,7 @@@ BEGI -- 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; @@@ -1907,7 -1914,6 +1922,7 @@@ DECLAR i INTEGER; existing RECORD; existingplacex RECORD; + existingline RECORD; existinggeometry GEOMETRY; existingplace_id BIGINT; result BOOLEAN; @@@ -1916,277 -1922,214 +1931,277 @@@ BEGI --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; + + -- 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; + + 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.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; - -- 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; + -- 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; + -- Patch in additional country names + IF NEW.admin_level = 2 AND NEW.type = 'administrative' AND NEW.country_code is not null THEN + select coalesce(country_name.name || NEW.name,NEW.name) from country_name where country_name.country_code = lower(NEW.country_code) INTO NEW.name; + END IF; + + -- Have we already done this place? + select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing; - -- Get the existing place_id - select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex; + -- Get the existing place_id + select * from 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; + -- 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; - --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id; - --DEBUG: RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id; + DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id; + DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id; - -- Log and discard - IF existing.geometry is not null AND st_isvalid(existing.geometry) - AND st_area(existing.geometry) > 0.02 - AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') - AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5 + -- 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 - 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; + 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; - -- 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 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; - 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; + -- 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; - 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; + RETURN NEW; 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; + -- Various ways to do the update - RETURN NEW; - END IF; - - -- Various ways to do the update - - -- Debug, what's changed? - IF FALSE THEN - IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') THEN - RAISE WARNING 'update details, name: % % % %',NEW.osm_type,NEW.osm_id,existing.name::text,NEW.name::text; - END IF; - IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') THEN - RAISE WARNING 'update details, housenumber: % % % %',NEW.osm_type,NEW.osm_id,existing.housenumber,NEW.housenumber; - 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; + -- 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; - 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 + -- 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 - -- 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; + -- 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; - -- Performance limit - IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN + -- 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); + -- 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); + 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; + END IF; - 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 - - 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 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; - 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; + 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; - 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; - - -- 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); + -- 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; @@@ -2299,7 -2242,7 +2314,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; @@@ -2315,31 -2258,24 +2330,34 @@@ countryname HSTORE; hadcountry BOOLEAN; BEGIN - - --first query tiger data - -- %NOTIGERDATA% IF 0 THEN - select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger + -- first query osmline (interpolation lines) + select parent_place_id, calculated_country_code, 30, postcode, null, 'place', 'house' from location_property_osmline WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; IF for_place_id IS NOT NULL THEN searchhousenumber = in_housenumber::text; END IF; - ++ + --then query tiger data ++ -- %NOTIGERDATA% IF 0 THEN + IF for_place_id IS NULL THEN + select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger + WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber + INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; + IF for_place_id IS NOT NULL THEN + searchhousenumber = in_housenumber::text; + END IF; + END IF; - + -- %NOTIGERDATA% END IF; + + -- %NOAUXDATA% IF 0 THEN IF for_place_id IS NULL THEN select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux WHERE place_id = in_place_id INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; END IF; - + -- %NOAUXDATA% END IF; + IF for_place_id IS NULL THEN select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex WHERE place_id = in_place_id and rank_address = 30 diff --combined sql/tables.sql index 3ec3c318,bcc3eec1..76a4324f --- a/sql/tables.sql +++ b/sql/tables.sql @@@ -13,29 -13,6 +13,6 @@@ CREATE TABLE import_osmosis_log event text ); - drop table if exists import_npi_log; - CREATE TABLE import_npi_log ( - npiid integer, - batchend timestamp, - batchsize integer, - starttime timestamp, - endtime timestamp, - event text - ); - - --drop table IF EXISTS query_log; - CREATE TABLE query_log ( - starttime timestamp, - query text, - ipaddress text, - endtime timestamp, - results integer - ); - CREATE INDEX idx_query_log ON query_log USING BTREE (starttime); - GRANT SELECT ON query_log TO "{www-user}" ; - GRANT INSERT ON query_log TO "{www-user}" ; - GRANT UPDATE ON query_log TO "{www-user}" ; - CREATE TABLE new_query_log ( type text, starttime timestamp, @@@ -43,6 -20,7 +20,7 @@@ useragent text, language text, query text, + searchterm text, endtime timestamp, results integer, format text, @@@ -56,9 -34,6 +34,6 @@@ GRANT SELECT ON new_query_log TO "{www- GRANT SELECT ON TABLE country_name TO "{www-user}"; GRANT SELECT ON TABLE gb_postcode TO "{www-user}"; - create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as - useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC; - drop table IF EXISTS word; CREATE TABLE word ( word_id INTEGER, @@@ -100,36 -75,6 +75,35 @@@ CREATE TABLE location_property ); SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2); +CREATE TABLE location_property_aux () INHERITS (location_property); +CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id); +CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id); +CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber); +GRANT SELECT ON location_property_aux TO "{www-user}"; + +CREATE TABLE location_property_tiger (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT); +GRANT SELECT ON location_property_tiger TO "{www-user}"; + +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, @@@ -188,15 -133,13 +162,15 @@@ GRANT SELECT ON planet_osm_ways to "{ww 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 --combined tests/steps/terrain.py index 0569578a,0a4495e0..80beebd5 --- a/tests/steps/terrain.py +++ b/tests/steps/terrain.py @@@ -123,19 -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): @@@ -175,8 -172,10 +175,10 @@@ def db_template_setup() conn = psycopg2.connect(database=world.config.template_db) psycopg2.extras.register_hstore(conn, globally=False, unicode=True) cur = conn.cursor() - for table in ('gb_postcode', 'us_postcode', 'us_state', 'us_statecounty'): - cur.execute('TRUNCATE TABLE %s' % (table,)) + for table in ('gb_postcode', 'us_postcode'): + cur.execute("select * from pg_tables where tablename = '%s'" % (table, )) + if cur.rowcount > 0: + cur.execute('TRUNCATE TABLE %s' % (table,)) conn.commit() conn.close() # execute osm2pgsql on an empty file to get the right tables diff --combined utils/setup.php index 4ed343df,4a20d5dc..caaf0f91 --- a/utils/setup.php +++ b/utils/setup.php @@@ -24,7 -24,6 +24,6 @@@ array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'), array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'), array('ignore-errors', '', 0, 1, 0, 0, 'bool', 'Continue import even when errors in SQL are present (EXPERT)'), - array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'), array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'), array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'), array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'), @@@ -37,7 -36,6 +36,6 @@@ array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'), array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'), array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'), - array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'), array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'), array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'), array('drop', '', 0, 1, 0, 0, 'bool', 'Drop tables needed for updates, making the database readonly (EXPERIMENTAL)'), @@@ -92,8 -90,6 +90,6 @@@ $aDSNInfo = DB::parseDSN(CONST_Database_DSN); if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432; - $fPostgisVersion = (float) CONST_Postgis_Version; - if ($aCMDResult['create-db'] || $aCMDResult['all']) { echo "Create DB\n"; @@@ -114,39 -110,38 +110,38 @@@ $oDB =& getDB(); - $sVersionString = $oDB->getOne('select version()'); - preg_match('#PostgreSQL ([0-9]+)[.]([0-9]+)[^0-9]#', $sVersionString, $aMatches); - if (CONST_Postgresql_Version != $aMatches[1].'.'.$aMatches[2]) + $fPostgresVersion = getPostgresVersion($oDB); + echo 'Postgres version found: '.$fPostgresVersion."\n"; + + if ($fPostgresVersion < 9.1) { - echo "ERROR: PostgreSQL version is not correct. Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n"; - exit; + fail("Minimum supported version of Postgresql is 9.1."); } - passthru('createlang plpgsql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']); - $pgver = (float) CONST_Postgresql_Version; - if ($pgver < 9.1) { - pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql'); - pgsqlRunScriptFile(CONST_BasePath.'/sql/hstore_compatability_9_0.sql'); - } else { - pgsqlRunScript('CREATE EXTENSION hstore'); - } + pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS hstore'); + pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis'); - if ($fPostgisVersion < 2.0) { - pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql'); - pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql'); - } else { - pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis'); + // For extratags and namedetails the hstore_to_json converter is + // needed which is only available from Postgresql 9.3+. For older + // versions add a dummy function that returns nothing. + $iNumFunc = $oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'"); + if (PEAR::isError($iNumFunc)) + { + fail("Cannot query stored procedures.", $iNumFunc); } - if ($fPostgisVersion < 2.1) { - // Function was renamed in 2.1 and throws an annoying deprecation warning - pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint'); + if ($iNumFunc == 0) + { + pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable"); + echo "WARNING: Postgresql is too old. extratags and namedetails API not available."; } - $sVersionString = $oDB->getOne('select postgis_full_version()'); - preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches); - if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2]) + + $fPostgisVersion = getPostgisVersion($oDB); + echo 'Postgis version found: '.$fPostgisVersion."\n"; + + if ($fPostgisVersion < 2.1) { - echo "ERROR: PostGIS version is not correct. Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n"; - exit; + // Function was renamed in 2.1 and throws an annoying deprecation warning + pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint'); } pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql'); @@@ -161,9 -156,10 +156,10 @@@ { echo "WARNING: external UK postcode table not found.\n"; } - pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql'); - pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql'); - pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql'); + if (CONST_Use_Extra_US_Postcodes) + { + pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql'); + } if ($aCMDResult['no-partitions']) { @@@ -221,51 -217,7 +217,7 @@@ echo "Functions\n"; $bDidSomething = true; if (!file_exists(CONST_InstallPath.'/module/nominatim.so')) fail("nominatim module not built"); - $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql'); - $sTemplate = str_replace('{modulepath}', CONST_InstallPath.'/module', $sTemplate); - if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate); - if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate); - if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate); - pgsqlRunScript($sTemplate); - - if ($fPostgisVersion < 2.0) { - echo "Helper functions for postgis < 2.0\n"; - $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_15_aux.sql'); - } else { - echo "Helper functions for postgis >= 2.0\n"; - $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_20_aux.sql'); - } - pgsqlRunScript($sTemplate); - } - - if ($aCMDResult['create-minimal-tables']) - { - echo "Minimal Tables\n"; - $bDidSomething = true; - pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql'); - - $sScript = ''; - - // Backstop the import process - easliest possible import id - $sScript .= "insert into import_npi_log values (18022);\n"; - - $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r"); - if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def'); - - while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#') - { - list($sClass, $sType) = explode(' ', trim($sLine)); - $sScript .= "create table place_classtype_".$sClass."_".$sType." as "; - $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n"; - - $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid "; - $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n"; - - $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id "; - $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n"; - } - fclose($hFile); - pgsqlRunScript($sScript); + create_sql_functions($aCMDResult); } if ($aCMDResult['create-tables'] || $aCMDResult['all']) @@@ -291,10 -243,7 +243,7 @@@ // re-run the functions echo "Functions\n"; - $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql'); - $sTemplate = str_replace('{modulepath}', - CONST_InstallPath.'/module', $sTemplate); - pgsqlRunScript($sTemplate); + create_sql_functions($aCMDResult); } if ($aCMDResult['create-partition-tables'] || $aCMDResult['all']) @@@ -404,8 -353,6 +353,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)); @@@ -447,70 -394,27 +396,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'); } @@@ -597,10 -501,14 +546,14 @@@ $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"; - if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); + + if (CONST_Use_Extra_US_Postcodes) + { + $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) "; + $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',"; + $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode"; + if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); + } } if ($aCMDResult['osmosis-init'] || ($aCMDResult['all'] && !$aCMDResult['drop'])) // no use doing osmosis-init when dropping update tables @@@ -701,7 -609,6 +654,6 @@@ { $bDidSomething = true; $sOutputFile = ''; - if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output']; $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile; passthruCheckReturn($sBaseCmd.' -R 4'); if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE'); @@@ -714,14 -621,6 +666,6 @@@ { echo "Search indices\n"; $bDidSomething = true; - $oDB =& getDB(); - $sSQL = 'select distinct partition from country_name'; - $aPartitions = $oDB->getCol($sSQL); - if (PEAR::isError($aPartitions)) - { - fail($aPartitions->getMessage()); - } - if (!$aCMDResult['no-partitions']) $aPartitions[] = 0; $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql'); $sTemplate = replace_tablespace('{ts:address-index}', @@@ -730,16 -629,6 +674,6 @@@ CONST_Tablespace_Search_Index, $sTemplate); $sTemplate = replace_tablespace('{ts:aux-index}', CONST_Tablespace_Aux_Index, $sTemplate); - preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); - foreach($aMatches as $aMatch) - { - $sResult = ''; - foreach($aPartitions as $sPartitionName) - { - $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]); - } - $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate); - } pgsqlRunScript($sTemplate); } @@@ -1011,3 -900,31 +945,31 @@@ return $sSql; } + function create_sql_functions($aCMDResult) + { + $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql'); + $sTemplate = str_replace('{modulepath}', CONST_InstallPath.'/module', $sTemplate); + if ($aCMDResult['enable-diff-updates']) + { + $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate); + } + if ($aCMDResult['enable-debug-statements']) + { + $sTemplate = str_replace('--DEBUG:', '', $sTemplate); + } + if (CONST_Limit_Reindexing) + { + $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate); + } + if (!CONST_Use_US_Tiger_Data) + { + $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate); + } + if (!CONST_Use_Aux_Location_data) + { + $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate); + } + pgsqlRunScript($sTemplate); + + } +