From 5d98c09ee9f4a16237fd93b759efbe920185db73 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 20 Nov 2018 21:03:56 +0100 Subject: [PATCH] Add reverse-only parameter to setup Avoids creating the search_name table. Useful when only /reverse is used or the content is directly exported to photon. Fixes #939. --- lib/setup/SetupClass.php | 30 +++++++++++-- sql/functions.sql | 76 ++++++++++++++++++++++++--------- sql/indices.src.sql | 4 -- sql/indices_search.src.sql | 6 +++ sql/partition-functions.src.sql | 15 ++----- utils/setup.php | 3 +- 6 files changed, 94 insertions(+), 40 deletions(-) create mode 100644 sql/indices_search.src.sql diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php index dde834f7..f03085f8 100755 --- a/lib/setup/SetupClass.php +++ b/lib/setup/SetupClass.php @@ -230,7 +230,7 @@ class SetupFunctions $this->createSqlFunctions(); } - public function createTables() + public function createTables($bReverseOnly = false) { info('Create Tables'); @@ -268,6 +268,10 @@ class SetupFunctions ); $this->pgsqlRunScript($sTemplate, false); + + if ($bReverseOnly) { + $this->pgExec('DROP TABLE search_name'); + } } public function createPartitionTables() @@ -356,8 +360,10 @@ class SetupFunctions echo '.'; $this->pgExec('TRUNCATE location_area'); echo '.'; - $this->pgExec('TRUNCATE search_name'); - echo '.'; + if (!$this->dbReverseOnly()) { + $this->pgExec('TRUNCATE search_name'); + echo '.'; + } $this->pgExec('TRUNCATE search_name_blank'); echo '.'; $this->pgExec('DROP SEQUENCE seq_place'); @@ -608,6 +614,9 @@ class SetupFunctions info('Create Search indices'); $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql'); + if (!$this->dbReverseOnly()) { + $sTemplate .= file_get_contents(CONST_BasePath.'/sql/indices_search.src.sql'); + } $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate); $sTemplate = $this->replaceTablespace( '{ts:address-index}', @@ -748,6 +757,10 @@ class SetupFunctions if (!CONST_Use_Aux_Location_data) { $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate); } + + $sReverseOnly = $this->dbReverseOnly() ? 'true' : 'false'; + $sTemplate = str_replace('%REVERSE-ONLY%', $sReverseOnly, $sTemplate); + $this->pgsqlRunScript($sTemplate); } @@ -861,4 +874,15 @@ class SetupFunctions fail(pg_last_error($this->oDB->connection)); } } + + /** + * Check if the database is in reverse-only mode. + * + * @return True if there is no search_name table and infrastructure. + */ + private function dbReverseOnly() + { + $sSQL = "SELECT count(*) FROM pg_tables WHERE tablename = 'search_name'"; + return !(chksql($this->oDB->getOne($sSQL))); + } } diff --git a/sql/functions.sql b/sql/functions.sql index 4a129210..f17976ad 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -1310,6 +1310,9 @@ BEGIN NEW.indexed_date = now(); + IF NOT %REVERSE-ONLY% THEN + DELETE from search_name WHERE place_id = NEW.place_id; + END IF; result := deleteSearchName(NEW.partition, NEW.place_id); DELETE FROM place_addressline WHERE place_id = NEW.place_id; result := deleteRoad(NEW.partition, NEW.place_id); @@ -1576,8 +1579,9 @@ BEGIN IF NEW.parent_place_id IS NOT NULL THEN -- Get the details of the parent road - select s.country_code, s.name_vector, s.nameaddress_vector from search_name s - where s.place_id = NEW.parent_place_id INTO location; + SELECT p.country_code, p.postcode FROM placex p + WHERE p.place_id = NEW.parent_place_id INTO location; + NEW.country_code := location.country_code; --DEBUG: RAISE WARNING 'Got parent details from search name'; @@ -1586,7 +1590,7 @@ BEGIN IF NEW.address is not null AND NEW.address ? 'postcode' THEN NEW.postcode = upper(trim(NEW.address->'postcode')); ELSE - SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode; + NEW.postcode := location.postcode; END IF; IF NEW.postcode is null THEN NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid); @@ -1599,21 +1603,34 @@ BEGIN return NEW; 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 - IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry); --DEBUG: RAISE WARNING 'Place added to location table'; END IF; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); - --DEBUG: RAISE WARNING 'Place added to search table'; + result := insertSearchName(NEW.partition, NEW.place_id, name_vector, + NEW.rank_search, NEW.rank_address, NEW.geometry); + + IF NOT %REVERSE-ONLY% THEN + -- Merge address from parent + SELECT s.name_vector, s.nameaddress_vector FROM search_name s + WHERE s.place_id = NEW.parent_place_id INTO location; + + nameaddress_vector := array_merge(nameaddress_vector, + location.nameaddress_vector); + nameaddress_vector := array_merge(nameaddress_vector, location.name_vector); + + INSERT INTO search_name (place_id, search_rank, address_rank, + importance, country_code, name_vector, + nameaddress_vector, centroid) + VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address, + NEW.importance, NEW.country_code, name_vector, + nameaddress_vector, place_centroid); + --DEBUG: RAISE WARNING 'Place added to search table'; + END IF; return NEW; END IF; @@ -1799,9 +1816,11 @@ BEGIN IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN isin_tokens := isin_tokens || address_street_word_id; END IF; - address_street_word_id := get_word_id(make_standard_name(addr_item.value)); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + IF NOT %REVERSE-ONLY% THEN + address_street_word_id := get_word_id(make_standard_name(addr_item.value)); + IF address_street_word_id IS NOT NULL THEN + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + END IF; END IF; END IF; IF addr_item.key = 'is_in' THEN @@ -1815,16 +1834,20 @@ BEGIN 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]); + IF NOT %REVERSE-ONLY% THEN + 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 IF; END LOOP; END IF; END IF; END LOOP; END IF; - nameaddress_vector := array_merge(nameaddress_vector, isin_tokens); + IF NOT %REVERSE-ONLY% THEN + nameaddress_vector := array_merge(nameaddress_vector, isin_tokens); + END IF; -- RAISE WARNING 'ISIN: %', isin_tokens; @@ -1873,7 +1896,7 @@ BEGIN -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress; -- Add it to the list of search terms - IF location.rank_search > 4 THEN + IF NOT %REVERSE-ONLY% AND location.rank_search > 4 THEN nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); END IF; INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address) @@ -1927,8 +1950,18 @@ BEGIN --DEBUG: RAISE WARNING 'insert into road location table (full)'; END IF; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); - --DEBUG: RAISE WARNING 'added to serach name (full)'; + result := insertSearchName(NEW.partition, NEW.place_id, name_vector, + NEW.rank_search, NEW.rank_address, NEW.geometry); + --DEBUG: RAISE WARNING 'added to search name (full)'; + + IF NOT %REVERSE-ONLY% THEN + INSERT INTO search_name (place_id, search_rank, address_rank, + importance, country_code, name_vector, + nameaddress_vector, centroid) + VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address, + NEW.importance, NEW.country_code, name_vector, + nameaddress_vector, place_centroid); + END IF; END IF; @@ -1987,6 +2020,9 @@ BEGIN --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id; IF OLD.name is not null THEN + IF NOT %REVERSE-ONLY% THEN + DELETE from search_name WHERE place_id = OLD.place_id; + END IF; b := deleteSearchName(OLD.partition, OLD.place_id); END IF; diff --git a/sql/indices.src.sql b/sql/indices.src.sql index 32ac8a3d..dd16affb 100644 --- a/sql/indices.src.sql +++ b/sql/indices.src.sql @@ -3,10 +3,6 @@ CREATE INDEX idx_word_word_id on word USING BTREE (word_id) {ts:search-index}; -CREATE INDEX idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off) {ts:search-index}; -CREATE INDEX idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off) {ts:search-index}; -CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid) {ts:search-index}; - CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id) {ts:search-index}; DROP INDEX IF EXISTS idx_placex_rank_search; diff --git a/sql/indices_search.src.sql b/sql/indices_search.src.sql new file mode 100644 index 00000000..d1363fc6 --- /dev/null +++ b/sql/indices_search.src.sql @@ -0,0 +1,6 @@ +-- Indices used for /search API. +-- These indices are created only after the indexing process is done. + +CREATE INDEX idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off) {ts:search-index}; +CREATE INDEX idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off) {ts:search-index}; +CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid) {ts:search-index}; diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index efb59542..3959661e 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -142,17 +142,11 @@ LANGUAGE plpgsql; create or replace function insertSearchName( - in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), - in_name_vector INTEGER[], in_nameaddress_vector INTEGER[], - in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT, - in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$ + in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[], + in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY) +RETURNS BOOLEAN AS $$ DECLARE BEGIN - - DELETE FROM search_name WHERE place_id = in_place_id; - INSERT INTO search_name (place_id, search_rank, address_rank, importance, country_code, name_vector, nameaddress_vector, centroid) - values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, in_name_vector, in_nameaddress_vector, in_centroid); - -- start IF in_partition = -partition- THEN DELETE FROM search_name_-partition- values WHERE place_id = in_place_id; @@ -173,9 +167,6 @@ LANGUAGE plpgsql; create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$ DECLARE BEGIN - - DELETE from search_name WHERE place_id = in_place_id; - -- start IF in_partition = -partition- THEN DELETE from search_name_-partition- WHERE place_id = in_place_id; diff --git a/utils/setup.php b/utils/setup.php index 39855150..0d2e6583 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -29,6 +29,7 @@ $aCMDOptions array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'), array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'), array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'), + array('reverse-only', '', 0, 1, 0, 0, 'bool', 'Do not create search tables and indexes'), array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'), 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'), @@ -104,7 +105,7 @@ if ($aCMDResult['create-functions'] || $aCMDResult['all']) { if ($aCMDResult['create-tables'] || $aCMDResult['all']) { $bDidSomething = true; - $oSetup->createTables(); + $oSetup->createTables($aCMDResult['reverse-only']); $oSetup->createFunctions(); } -- 2.39.5