From: Sarah Hoffmann Date: Thu, 3 Jan 2013 21:13:41 +0000 (+0100) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~652 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/bcbba1cb5a4e5a1df3b8f5c79e2a988221adf867?hp=1c115feeb950a9be5160f7b90331818b48be7bee Merge remote-tracking branch 'upstream/master' --- diff --git a/settings/settings.php b/settings/settings.php index 5231692f..33bdad51 100644 --- a/settings/settings.php +++ b/settings/settings.php @@ -7,8 +7,11 @@ @define('CONST_Database_DSN', 'pgsql://@/nominatim'); @define('CONST_Max_Word_Frequency', '50000'); + // Software versions + @define('CONST_Postgresql_Version', '9.1'); // values: 8.3, 8.4, 9.0, 9.1, 9.2 + @define('CONST_Postgis_Version', '1.5'); // values: 1.5, 2.0 + // Paths - @define('CONST_Postgresql_Version', '9.1'); @define('CONST_Path_Postgresql_Contrib', '/usr/share/postgresql/'.CONST_Postgresql_Version.'/contrib'); @define('CONST_Path_Postgresql_Postgis', CONST_Path_Postgresql_Contrib.'/postgis-1.5'); @define('CONST_Osm2pgsql_Binary', CONST_BasePath.'/osm2pgsql/osm2pgsql'); @@ -59,6 +62,7 @@ @define('CONST_Suggestions_Enabled', false); @define('CONST_Search_TryDroppedAddressTerms', false); + @define('CONST_Search_NameOnlySearchFrequencyThreshold', false); // Set to zero to disable polygon output @define('CONST_PolygonOutput_MaximumTypes', 1); diff --git a/sql/functions.sql b/sql/functions.sql index dfb64634..0d691411 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -5,7 +5,6 @@ -- score integer --); - CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT AS $$ DECLARE @@ -1389,6 +1388,9 @@ BEGIN --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; -- to do that we have to find our parent road @@ -1707,6 +1709,16 @@ BEGIN END LOOP; END IF; END IF; + + -- for the USA we have an additional address table. Merge in zip codes from there too + IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN + FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP + address_street_word_id := get_name_id(make_standard_name(location.postcode)); + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + isin_tokens := isin_tokens || address_street_word_id; + END LOOP; + END IF; + -- RAISE WARNING 'ISIN: %', isin_tokens; -- Process area matches diff --git a/utils/setup.php b/utils/setup.php index 33de5d85..45b40fed 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -105,6 +105,15 @@ // TODO: path detection, detection memory, etc. $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]) + { + echo "ERROR: PostgreSQL version is not correct. Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n"; + exit; + } + passthru('createlang plpgsql '.$aDSNInfo['database']); $pgver = (float) CONST_Postgresql_Version; if ($pgver < 9.1) { @@ -113,7 +122,16 @@ } else { pgsqlRunScript('CREATE EXTENSION hstore'); } + pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql'); + $sVersionString = $oDB->getOne('select postgis_full_version()'); + preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches); + if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2]) + { + echo "ERROR: PostGIS version is not correct. Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n"; + exit; + } + pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql'); pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql'); pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql'); diff --git a/website/reverse.php b/website/reverse.php index 95889fc4..d43b0534 100755 --- a/website/reverse.php +++ b/website/reverse.php @@ -115,7 +115,7 @@ $sSQL .= ' and (name is not null or housenumber is not null)'; $sSQL .= ' and class not in (\'waterway\',\'railway\',\'tunnel\',\'bridge\')'; $sSQL .= ' and (ST_GeometryType(geometry) not in (\'ST_Polygon\',\'ST_MultiPolygon\') '; - $sSQL .= ' OR ST_DWithin('.$sPointSQL.', ST_Centroid(geometry), '.$fSearchDiam.'))'; + $sSQL .= ' OR ST_DWithin('.$sPointSQL.', centroid, '.$fSearchDiam.'))'; $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', geometry) ASC limit 1'; //var_dump($sSQL); $aPlace = $oDB->getRow($sSQL); @@ -152,7 +152,7 @@ $sSQL .= " get_address_by_language(place_id, $sLanguagePrefArraySQL) as langaddress,"; $sSQL .= " get_name_by_language(name, $sLanguagePrefArraySQL) as placename,"; $sSQL .= " get_name_by_language(name, ARRAY['ref']) as ref,"; - $sSQL .= " st_y(st_centroid(geometry)) as lat, st_x(st_centroid(geometry)) as lon"; + $sSQL .= " st_y(centroid) as lat, st_x(centroid) as lon"; $sSQL .= " from placex where place_id = $iPlaceID "; $aPlace = $oDB->getRow($sSQL); diff --git a/website/search.php b/website/search.php index 24f21e12..a92ac30a 100755 --- a/website/search.php +++ b/website/search.php @@ -325,7 +325,7 @@ { foreach($aSearchWords as $aSearchTerm) { - $aNewSearch = $aSearch; + $aNewSearch = $aSearch; if ($aSearchTerm['country_code']) { $aNewSearch['sCountryCode'] = strtolower($aSearchTerm['country_code']); @@ -393,7 +393,7 @@ { // Check which tokens we have, get the ID numbers - $sSQL = 'select word_id,word_token, word, class, type, location, country_code, operator'; + $sSQL = 'select word_id,word_token, word, class, type, location, country_code, operator, search_name_count'; $sSQL .= ' from word where word_token in ('.join(',',array_map("getDBQuoted",$aTokens)).')'; $sSQL .= ' and search_name_count < '.CONST_Max_Word_Frequency; // $sSQL .= ' group by word_token, word, class, type, location, country_code'; @@ -420,7 +420,7 @@ { $aValidTokens[$aToken['word_token']] = array($aToken); } - if ($aToken['word_token'][0]==' ' && !$aToken['class'] && !$aToken['country_code']) $aPossibleMainWordIDs[$aToken['word_id']] = 1; + if ($aToken['word_token'][0]==' ' && !$aToken['class'] && !$aToken['country_code']) $aPossibleMainWordIDs[$aToken['word_id']] = 1 + $aToken['search_name_count']; } if (CONST_Debug) var_Dump($aPhrases, $aValidTokens); @@ -528,7 +528,7 @@ { $aSearch = $aCurrentSearch; $aSearch['iSearchRank']++; - if (($sPhraseType == '' || $sPhraseType == 'country') && $aSearchTerm['country_code'] !== null && $aSearchTerm['country_code'] != '0') + if (($sPhraseType == '' || $sPhraseType == 'country') && !empty($aSearchTerm['country_code']) && $aSearchTerm['country_code'] != '0') { if ($aSearch['sCountryCode'] === false) { @@ -861,7 +861,7 @@ if (CONST_Debug) var_dump('
',$aSearch); if (CONST_Debug) _debugDumpGroupedSearches(array($iGroupedRank => array($aSearch)), $aValidTokens); $aPlaceIDs = array(); - + // First we need a position, either aName or fLat or both $aTerms = array(); $aOrder = array(); @@ -869,7 +869,20 @@ // TODO: filter out the pointless search terms (2 letter name tokens and less) // they might be right - but they are just too darned expensive to run if (sizeof($aSearch['aName'])) $aTerms[] = "name_vector @> ARRAY[".join($aSearch['aName'],",")."]"; - if (sizeof($aSearch['aAddress']) && $aSearch['aName'] != $aSearch['aAddress']) $aTerms[] = "nameaddress_vector @> ARRAY[".join($aSearch['aAddress'],",")."]"; + if (sizeof($aSearch['aAddress']) && $aSearch['aName'] != $aSearch['aAddress']) + { + // For infrequent name terms disable index usage for address + if (CONST_Search_NameOnlySearchFrequencyThreshold && + sizeof($aSearch['aName']) == 1 && + $aPossibleMainWordIDs[$aSearch['aName'][reset($aSearch['aName'])]] < CONST_Search_NameOnlySearchFrequencyThreshold) + { + $aTerms[] = "array_cat(nameaddress_vector,ARRAY[]::integer[]) @> ARRAY[".join($aSearch['aAddress'],",")."]"; + } + else + { + $aTerms[] = "nameaddress_vector @> ARRAY[".join($aSearch['aAddress'],",")."]"; + } + } if ($aSearch['sCountryCode']) $aTerms[] = "country_code = '".pg_escape_string($aSearch['sCountryCode'])."'"; if ($aSearch['sHouseNumber']) $aTerms[] = "address_rank in (26,27)"; if ($aSearch['fLon'] && $aSearch['fLat']) @@ -1047,13 +1060,13 @@ if ($sNearPointSQL) $sOrderBySQL = "ST_Distance($sNearPointSQL, l.centroid)"; else if ($sPlaceIDs) $sOrderBySQL = "ST_Distance(l.centroid, f.geometry)"; else if ($sPlaceGeom) $sOrderBysSQL = "ST_Distance(st_centroid('".$sPlaceGeom."'), l.centroid)"; - + $sSQL = "select distinct l.place_id".($sOrderBySQL?','.$sOrderBySQL:'')." from place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." as l"; if ($sCountryCodesSQL) $sSQL .= " join placex as lp using (place_id)"; if ($sPlaceIDs) { $sSQL .= ",placex as f where "; - $sSQL .= "f.place_id in ($sPlaceIDs) and ST_DWithin(l.centroid, st_centroid(f.geometry), $fRange) "; + $sSQL .= "f.place_id in ($sPlaceIDs) and ST_DWithin(l.centroid, f.centroid, $fRange) "; } if ($sPlaceGeom) { @@ -1080,7 +1093,7 @@ else $sOrderBySQL = "ST_Distance(l.geometry, f.geometry)"; $sSQL = "select distinct l.place_id".($sOrderBysSQL?','.$sOrderBysSQL:'')." from placex as l,placex as f where "; - $sSQL .= "f.place_id in ( $sPlaceIDs) and ST_DWithin(l.geometry, st_centroid(f.geometry), $fRange) "; + $sSQL .= "f.place_id in ( $sPlaceIDs) and ST_DWithin(l.geometry, f.centroid, $fRange) "; $sSQL .= "and l.class='".$aSearch['sClass']."' and l.type='".$aSearch['sType']."' "; if (sizeof($aExcludePlaceIDs)) { @@ -1138,7 +1151,7 @@ $sSQL .= "get_address_by_language(place_id, $sLanguagePrefArraySQL) as langaddress,"; $sSQL .= "get_name_by_language(name, $sLanguagePrefArraySQL) as placename,"; $sSQL .= "get_name_by_language(name, ARRAY['ref']) as ref,"; - $sSQL .= "avg(ST_X(ST_Centroid(geometry))) as lon,avg(ST_Y(ST_Centroid(geometry))) as lat, "; + $sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, "; // $sSQL .= $sOrderSQL." as porder, "; $sSQL .= "coalesce(importance,0.75-(rank_search::float/40)) as importance "; $sSQL .= "from placex where place_id in ($sPlaceIDs) "; @@ -1208,7 +1221,7 @@ $sSQL .= "get_address_by_language(place_id, $sLanguagePrefArraySQL) as langaddress,"; $sSQL .= "get_name_by_language(name, $sLanguagePrefArraySQL) as placename,"; $sSQL .= "get_name_by_language(name, ARRAY['ref']) as ref,"; - $sSQL .= "avg(ST_X(ST_Centroid(geometry))) as lon,avg(ST_Y(ST_Centroid(geometry))) as lat, "; + $sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, "; // $sSQL .= $sOrderSQL." as porder, "; $sSQL .= "coalesce(importance,0.75-(rank_search::float/40)) as importance "; $sSQL .= "from placex where place_id in ($sPlaceIDs) ";