From: Sarah Hoffmann Date: Fri, 30 Apr 2021 09:19:35 +0000 (+0200) Subject: Merge pull request #2303 from lonvia/remove-aux-support X-Git-Tag: v4.0.0~94 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/5c7b9ef909b3c30bbe640f4373a1127e15a7e018?hp=51d20b19b6c8e414375745a422d781913df57c71 Merge pull request #2303 from lonvia/remove-aux-support Remove support for AUX housenumber tables --- diff --git a/lib-php/Geocode.php b/lib-php/Geocode.php index ec6876fa..6cec6a85 100644 --- a/lib-php/Geocode.php +++ b/lib-php/Geocode.php @@ -829,7 +829,6 @@ class Geocode foreach ($aResults as $oResult) { if (($this->iMaxAddressRank == 30 && ($oResult->iTable == Result::TABLE_OSMLINE - || $oResult->iTable == Result::TABLE_AUX || $oResult->iTable == Result::TABLE_TIGER)) || in_array($oResult->iId, $aFilteredIDs) ) { diff --git a/lib-php/PlaceLookup.php b/lib-php/PlaceLookup.php index 6d7b6be1..b9fa3b1c 100644 --- a/lib-php/PlaceLookup.php +++ b/lib-php/PlaceLookup.php @@ -373,42 +373,6 @@ class PlaceLookup $aSubSelects[] = $sSQL; } - - if (CONST_Use_Aux_Location_data) { - $sPlaceIDs = Result::joinIdsByTable($aResults, Result::TABLE_AUX); - if ($sPlaceIDs) { - $sHousenumbers = Result::sqlHouseNumberTable($aResults, Result::TABLE_AUX); - $sSQL = ' SELECT '; - $sSQL .= " 'L' AS osm_type, "; - $sSQL .= ' place_id AS osm_id, '; - $sSQL .= " 'place' AS class,"; - $sSQL .= " 'house' AS type, "; - $sSQL .= ' null::smallint AS admin_level, '; - $sSQL .= ' 30 AS rank_search,'; - $sSQL .= ' 30 AS rank_address, '; - $sSQL .= ' place_id,'; - $sSQL .= ' parent_place_id, '; - $sSQL .= ' housenumber,'; - $sSQL .= " 'us' AS country_code, "; - $sSQL .= $this->langAddressSql('-1'); - $sSQL .= ' null::text AS placename, '; - $sSQL .= ' null::text AS ref, '; - if ($this->bExtraTags) $sSQL .= 'null::text AS extra, '; - if ($this->bNameDetails) $sSQL .= 'null::text AS names, '; - $sSQL .= ' ST_X(centroid) AS lon, '; - $sSQL .= ' ST_Y(centroid) AS lat, '; - $sSQL .= ' -1.10 AS importance, '; - $sSQL .= $this->addressImportanceSql( - 'centroid', - 'location_property_aux.parent_place_id' - ); - $sSQL .= ' null::text AS extra_place '; - $sSQL .= ' FROM location_property_aux '; - $sSQL .= " WHERE place_id in ($sPlaceIDs) "; - - $aSubSelects[] = $sSQL; - } - } } if (empty($aSubSelects)) { diff --git a/lib-php/Result.php b/lib-php/Result.php index a7747ea3..be103074 100644 --- a/lib-php/Result.php +++ b/lib-php/Result.php @@ -13,8 +13,7 @@ class Result const TABLE_PLACEX = 0; const TABLE_POSTCODE = 1; const TABLE_OSMLINE = 2; - const TABLE_AUX = 3; - const TABLE_TIGER = 4; + const TABLE_TIGER = 3; /// Database table that contains the result. public $iTable; diff --git a/lib-php/SearchDescription.php b/lib-php/SearchDescription.php index dd205502..189ffa74 100644 --- a/lib-php/SearchDescription.php +++ b/lib-php/SearchDescription.php @@ -790,20 +790,6 @@ class SearchDescription } } - // If nothing found try the aux fallback table - if (CONST_Use_Aux_Location_data && empty($aResults)) { - $sSQL = 'SELECT place_id FROM location_property_aux'; - $sSQL .= ' WHERE parent_place_id in ('.$sPlaceIDs.')'; - $sSQL .= " AND housenumber = '".$this->sHouseNumber."'"; - $sSQL .= $this->oContext->excludeSQL(' AND place_id'); - - Debug::printSQL($sSQL); - - foreach ($oDB->getCol($sSQL) as $iPlaceId) { - $aResults[$iPlaceId] = new Result($iPlaceId, Result::TABLE_AUX); - } - } - // If nothing found then search in Tiger data (location_property_tiger) if (CONST_Use_US_Tiger_Data && $bIsIntHouseNumber && empty($aResults)) { $sSQL = 'SELECT place_id FROM location_property_tiger'; diff --git a/lib-php/admin/query.php b/lib-php/admin/query.php index 35fd1184..268b87cc 100644 --- a/lib-php/admin/query.php +++ b/lib-php/admin/query.php @@ -48,7 +48,6 @@ loadSettings($aCMDResult['project-dir'] ?? getcwd()); @define('CONST_Search_BatchMode', getSettingBool('SEARCH_BATCH_MODE')); @define('CONST_Search_NameOnlySearchFrequencyThreshold', getSetting('SEARCH_NAME_ONLY_THRESHOLD')); @define('CONST_Term_Normalization_Rules', getSetting('TERM_NORMALIZATION')); -@define('CONST_Use_Aux_Location_data', getSettingBool('USE_AUX_LOCATION_DATA')); @define('CONST_Use_US_Tiger_Data', getSettingBool('USE_US_TIGER_DATA')); @define('CONST_MapIcon_URL', getSetting('MAPICON_URL', false)); diff --git a/lib-php/admin/warm.php b/lib-php/admin/warm.php index 827fd986..d7950af9 100644 --- a/lib-php/admin/warm.php +++ b/lib-php/admin/warm.php @@ -33,7 +33,6 @@ loadSettings($aCMDResult['project-dir'] ?? getcwd()); @define('CONST_Search_BatchMode', getSettingBool('SEARCH_BATCH_MODE')); @define('CONST_Search_NameOnlySearchFrequencyThreshold', getSetting('SEARCH_NAME_ONLY_THRESHOLD')); @define('CONST_Term_Normalization_Rules', getSetting('TERM_NORMALIZATION')); -@define('CONST_Use_Aux_Location_data', getSettingBool('USE_AUX_LOCATION_DATA')); @define('CONST_Use_US_Tiger_Data', getSettingBool('USE_US_TIGER_DATA')); @define('CONST_MapIcon_URL', getSetting('MAPICON_URL', false)); diff --git a/lib-php/website/details.php b/lib-php/website/details.php index bd7df12c..55a088d1 100644 --- a/lib-php/website/details.php +++ b/lib-php/website/details.php @@ -106,11 +106,6 @@ if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; $iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_postcode WHERE place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; -if (CONST_Use_Aux_Location_data) { - $iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_aux WHERE place_id = '.$iPlaceID); - if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; -} - $hLog = logStart($oDB, 'details', $_SERVER['QUERY_STRING'], $aLangPrefOrder); // Get the details for this point diff --git a/lib-sql/aux_tables.sql b/lib-sql/aux_tables.sql deleted file mode 100644 index 81054731..00000000 --- a/lib-sql/aux_tables.sql +++ /dev/null @@ -1,6 +0,0 @@ -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}"; - diff --git a/lib-sql/functions/address_lookup.sql b/lib-sql/functions/address_lookup.sql index 5ec977d1..45e49750 100644 --- a/lib-sql/functions/address_lookup.sql +++ b/lib-sql/functions/address_lookup.sql @@ -135,20 +135,6 @@ BEGIN END IF; {% endif %} - -- then additional data - {% if config.get_bool('USE_AUX_LOCATION_DATA') %} - IF place IS NULL THEN - SELECT parent_place_id as place_id, 'us' as country_code, - housenumber, postcode, - 'place' as class, 'house' as type, - null as name, null as address, - centroid - INTO place - FROM location_property_aux - WHERE place_id = in_place_id; - END IF; - {% endif %} - -- postcode table IF place IS NULL THEN SELECT parent_place_id as place_id, country_code, diff --git a/lib-sql/functions/aux_property.sql b/lib-sql/functions/aux_property.sql deleted file mode 100644 index 6dd99eb2..00000000 --- a/lib-sql/functions/aux_property.sql +++ /dev/null @@ -1,53 +0,0 @@ --- Functions for adding external data (currently unused). - -CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT, - in_street TEXT, in_isin TEXT, - in_postcode TEXT, in_countrycode char(2)) - RETURNS INTEGER - AS $$ -DECLARE - - newpoints INTEGER; - place_centroid GEOMETRY; - out_partition INTEGER; - out_parent_place_id BIGINT; - location RECORD; - address_street_word_ids INTEGER[]; - out_postcode TEXT; - -BEGIN - - place_centroid := ST_Centroid(pointgeo); - out_partition := get_partition(in_countrycode); - out_parent_place_id := null; - - address_street_word_ids := word_ids_from_name(in_street); - IF address_street_word_ids IS NOT NULL THEN - out_parent_place_id := getNearestNamedRoadPlaceId(out_partition, place_centroid, - address_street_word_ids); - END IF; - - IF out_parent_place_id IS NULL THEN - SELECT getNearestRoadPlaceId(out_partition, place_centroid) - INTO out_parent_place_id; - END LOOP; - END IF; - - out_postcode := in_postcode; - IF out_postcode IS NULL THEN - SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode; - END IF; - -- XXX look into postcode table - - newpoints := 0; - insert into location_property_aux (place_id, partition, parent_place_id, - housenumber, postcode, centroid) - values (nextval('seq_place'), out_partition, out_parent_place_id, - in_housenumber, out_postcode, place_centroid); - newpoints := newpoints + 1; - - RETURN newpoints; -END; -$$ -LANGUAGE plpgsql; - diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index aa213dba..609472ec 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -84,22 +84,6 @@ CREATE TABLE location_area_country ( CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {{db.tablespace.address_index}}; -drop table IF EXISTS location_property CASCADE; -CREATE TABLE location_property ( - place_id BIGINT, - parent_place_id BIGINT, - partition SMALLINT, - housenumber TEXT, - postcode TEXT, - centroid GEOMETRY(Point, 4326) - ); - -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 "{{config.DATABASE_WEBUSER}}"; - CREATE TABLE location_property_tiger ( place_id BIGINT, parent_place_id BIGINT, diff --git a/nominatim/tools/database_import.py b/nominatim/tools/database_import.py index 25efedb9..3618ed28 100644 --- a/nominatim/tools/database_import.py +++ b/nominatim/tools/database_import.py @@ -214,7 +214,6 @@ def truncate_data_tables(conn, max_word_frequency=None): cur.execute('TRUNCATE place_addressline') cur.execute('TRUNCATE location_area') cur.execute('TRUNCATE location_area_country') - cur.execute('TRUNCATE location_property') cur.execute('TRUNCATE location_property_tiger') cur.execute('TRUNCATE location_property_osmline') cur.execute('TRUNCATE location_postcode') diff --git a/nominatim/tools/refresh.py b/nominatim/tools/refresh.py index d38cb216..8fc0c69d 100644 --- a/nominatim/tools/refresh.py +++ b/nominatim/tools/refresh.py @@ -111,7 +111,6 @@ PHP_CONST_DEFS = ( ('Search_BatchMode', 'SEARCH_BATCH_MODE', bool), ('Search_NameOnlySearchFrequencyThreshold', 'SEARCH_NAME_ONLY_THRESHOLD', str), ('Term_Normalization_Rules', 'TERM_NORMALIZATION', str), - ('Use_Aux_Location_data', 'USE_AUX_LOCATION_DATA', bool), ('Use_US_Tiger_Data', 'USE_US_TIGER_DATA', bool), ('MapIcon_URL', 'MAPICON_URL', str), ) diff --git a/test/python/test_tools_database_import.py b/test/python/test_tools_database_import.py index 1311ef5d..280ca704 100644 --- a/test/python/test_tools_database_import.py +++ b/test/python/test_tools_database_import.py @@ -172,7 +172,7 @@ def test_import_osm_data_default_cache(temp_db_cursor,osm2pgsql_options): def test_truncate_database_tables(temp_db_conn, temp_db_cursor, table_factory): tables = ('word', 'placex', 'place_addressline', 'location_area', - 'location_area_country', 'location_property', + 'location_area_country', 'location_property_tiger', 'location_property_osmline', 'location_postcode', 'search_name', 'location_road_23') for table in tables: