From: Sarah Hoffmann Date: Wed, 20 Nov 2019 10:53:26 +0000 (+0100) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~271 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/0c483063dd7f5b62c28e10279e22250ec861fb8f?ds=inline;hp=-c Merge remote-tracking branch 'upstream/master' --- 0c483063dd7f5b62c28e10279e22250ec861fb8f diff --combined sql/functions.sql index c7d96cdc,ad2007cb..5d2b7a22 --- a/sql/functions.sql +++ b/sql/functions.sql @@@ -1358,10 -1358,9 +1358,9 @@@ BEGI END LOOP; NEW.importance := null; - select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.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; + SELECT wikipedia, importance + FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id) + INTO NEW.wikipedia,NEW.importance; --DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance; @@@ -1600,9 -1599,10 +1599,10 @@@ -- 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; - select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance; + select wikipedia, importance + FROM compute_importance(linkedPlacex.extratags, NEW.country_code, + 'N', linkedPlacex.osm_id) + INTO linked_wikipedia,linked_importance; --DEBUG: RAISE WARNING 'Linked label member'; END LOOP; @@@ -1639,9 -1639,10 +1639,10 @@@ -- 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; - select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance; + select wikipedia, importance + FROM compute_importance(linkedPlacex.extratags, NEW.country_code, + 'N', linkedPlacex.osm_id) + INTO linked_wikipedia,linked_importance; --DEBUG: RAISE WARNING 'Linked admin_center'; END IF; @@@ -1684,9 -1685,10 +1685,10 @@@ -- 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; - select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance; + select wikipedia, importance + FROM compute_importance(linkedPlacex.extratags, NEW.country_code, + 'N', linkedPlacex.osm_id) + INTO linked_wikipedia,linked_importance; --DEBUG: RAISE WARNING 'Linked named place'; END LOOP; END IF; @@@ -1714,13 -1716,6 +1716,6 @@@ (NEW.importance is null or NEW.importance < linked_importance) THEN NEW.importance = linked_importance; END IF; - - -- Still null? how about looking it up by the node id - IF NEW.importance IS NULL THEN - --DEBUG: RAISE WARNING 'Looking up importance by linked node id'; - 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 @@@ -2449,7 -2444,7 +2444,7 @@@ BEGI SELECT placex.place_id, osm_type, osm_id, name, CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class, CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type, - admin_level, fromarea, isaddress, + admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress, CASE WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address, distance, country_code, postcode FROM place_addressline join placex on (address_place_id = placex.place_id) @@@ -2627,7 -2622,7 +2622,7 @@@ END $$ LANGUAGE plpgsql IMMUTABLE; - DROP TYPE wikipedia_article_match CASCADE; + DROP TYPE IF EXISTS wikipedia_article_match CASCADE; create type wikipedia_article_match as ( language TEXT, title TEXT, @@@ -2682,6 -2677,42 +2677,42 @@@ BEGI RETURN NULL; END; $$ + LANGUAGE plpgsql; + + DROP TYPE IF EXISTS place_importance CASCADE; + create type place_importance as ( + importance FLOAT, + wikipedia TEXT + ); + + CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE, country_code varchar(2), osm_type varchar(1), osm_id BIGINT) + RETURNS place_importance + AS $$ + DECLARE + match RECORD; + result place_importance; + BEGIN + FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code) + WHERE language is not NULL + LOOP + result.importance := match.importance; + result.wikipedia := match.language || ':' || match.title; + RETURN result; + END LOOP; + + IF extratags ? 'wikidata' THEN + FOR match IN SELECT * FROM wikipedia_article + WHERE wd_page_title = extratags->'wikidata' + ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP + result.importance := match.importance; + result.wikipedia := match.language || ':' || match.title; + RETURN result; + END LOOP; + END IF; + + RETURN null; + END; + $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER) diff --combined utils/update.php index 2809a198,4317367a..f71c4f43 --- a/utils/update.php +++ b/utils/update.php @@@ -42,13 -42,13 +42,14 @@@ $aCMDOption array('deduplicate', '', 0, 1, 0, 0, 'bool', 'Deduplicate tokens'), array('recompute-word-counts', '', 0, 1, 0, 0, 'bool', 'Compute frequency of full-word search terms'), array('update-address-levels', '', 0, 1, 0, 0, 'bool', 'Reimport address level configuration (EXPERT)'), + array('recompute-importance', '', 0, 1, 0, 0, 'bool', 'Recompute place importances'), array('no-npi', '', 0, 1, 0, 0, 'bool', '(obsolete)'), ); getCmdOpt($_SERVER['argv'], $aCMDOptions, $aResult, true, true); if (!isset($aResult['index-instances'])) $aResult['index-instances'] = 1; + if (!isset($aResult['index-rank'])) $aResult['index-rank'] = 0; date_default_timezone_set('Etc/UTC'); @@@ -321,6 -321,23 +322,23 @@@ if ($aResult['update-address-levels']) $oAlParser->createTable($oDB, 'address_levels'); } + if ($aResult['recompute-importance']) { + echo "Updating importance values for database.\n"; + $oDB = new Nominatim\DB(); + $oDB->connect(); + + $sSQL = 'ALTER TABLE placex DISABLE TRIGGER ALL;'; + $sSQL .= 'UPDATE placex SET (wikipedia, importance) ='; + $sSQL .= ' (SELECT wikipedia, importance'; + $sSQL .= ' FROM compute_importance(extratags, country_code, osm_type, osm_id));'; + $sSQL .= 'UPDATE placex s SET wikipedia = d.wikipedia, importance = d.importance'; + $sSQL .= ' FROM placex d'; + $sSQL .= ' WHERE s.place_id = d.linked_place_id and d.wikipedia is not null'; + $sSQL .= ' and (s.wikipedia is null or s.importance < d.importance);'; + $sSQL .= 'ALTER TABLE placex ENABLE TRIGGER ALL;'; + $oDB->exec($sSQL); + } + if ($aResult['import-osmosis'] || $aResult['import-osmosis-all']) { // if (strpos(CONST_Replication_Url, 'download.geofabrik.de') !== false && CONST_Replication_Update_Interval < 86400) {