From: Sarah Hoffmann Date: Tue, 27 Sep 2022 20:12:48 +0000 (+0200) Subject: simplify use of secondary importance X-Git-Tag: v4.2.0~25^2~4 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/abf349fb0da9c1f08cbe17a70b3134b64c0e61c3 simplify use of secondary importance The values in the raster are already normalized between 0 and 2**16, so a simple conversion to [0, 1] will do. Check for existance of secondary_importance table statically when creating the SQL function. For that to work importance tables need to be created before the functions. --- diff --git a/lib-sql/functions/importance.sql b/lib-sql/functions/importance.sql index 30e778d1..44e8bc8b 100644 --- a/lib-sql/functions/importance.sql +++ b/lib-sql/functions/importance.sql @@ -98,46 +98,9 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_osm_views(centroid GEOMETRY) - RETURNS BIGINT - AS $$ -DECLARE - result BIGINT; -BEGIN - SELECT ST_Value(osm_views.rast, centroid) - FROM osm_views - WHERE ST_Intersects(ST_ConvexHull(osm_views.rast), centroid) LIMIT 1 INTO result; - - return COALESCE(result, 0); -END; -$$ -LANGUAGE plpgsql STABLE; - - -CREATE OR REPLACE FUNCTION normalize_osm_views(views BIGINT) - RETURNS FLOAT - AS $$ - DECLARE - normalized_osm_views FLOAT; - max_views BIGINT; - BEGIN - IF views > 0 THEN - -- Get the highest view count to use it in normalizing the data - SELECT max_views_count FROM osm_views_stat INTO max_views; - normalized_osm_views := (LOG(views))/(LOG(max_views)); - ELSE - normalized_osm_views := 0.0; - END IF; - - RETURN normalized_osm_views; - END; -$$ -LANGUAGE plpgsql; - - CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE, country_code varchar(2), - osm_type varchar(1), osm_id BIGINT, + rank_search SMALLINT, centroid GEOMETRY) RETURNS place_importance AS $$ @@ -147,39 +110,44 @@ DECLARE osm_views_exists BIGINT; views BIGINT; BEGIN - -- check if osm_views table exists - SELECT COUNT(table_name) - INTO osm_views_exists - FROM information_schema.tables - WHERE table_schema LIKE 'public' AND - table_type LIKE 'BASE TABLE' AND - table_name = 'osm_views'; - - -- add importance by OSM views if osm_views table exists - IF osm_views_exists THEN - views := get_osm_views(centroid); - result.importance := normalize_osm_views(views) * 0.35; - END IF; - - -- add importance by wiki data if the place has one - FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code) - WHERE language is not NULL + -- add importance by wikipedia article if the place has one + FOR match IN + SELECT * FROM get_wikipedia_match(extratags, country_code) + WHERE language is not NULL LOOP - result.importance := COALESCE(result.importance, 0) + match.importance * 0.65; + result.importance := match.importance; result.wikipedia := match.language || ':' || match.title; RETURN result; END LOOP; - IF extratags ? 'wikidata' THEN + -- Nothing? Then try with the wikidata tag. + IF result.importance is null AND 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 := COALESCE(result.importance, 0) + match.importance * 0.65; + 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; + -- Still nothing? Fall back to a default. + IF result.importance is null THEN + result.importance := 0.75001 - (rank_search::float / 40); + END IF; + +{% if 'secondary_importance' in db.tables %} + FOR match IN + SELECT ST_Value(rast, centroid) as importance + FROM secondary_importance + WHERE ST_Intersects(ST_ConvexHull(rast), centroid) LIMIT 1 + LOOP + -- Secondary importance as tie breaker with 0.0001 weight. + result.importance := result.importance + match.importance::float / 655350000; + END LOOP; +{% endif %} + RETURN result; END; $$ diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 4432fa24..367d2149 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -965,7 +965,7 @@ BEGIN NEW.importance := null; SELECT wikipedia, importance - FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id, NEW.centroid) + FROM compute_importance(NEW.extratags, NEW.country_code, NEW.rank_search, NEW.centroid) INTO NEW.wikipedia,NEW.importance; {% if debug %}RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;{% endif %} @@ -1047,7 +1047,7 @@ BEGIN IF linked_place is not null THEN -- Recompute the ranks here as the ones from the linked place might -- have been shifted to accommodate surrounding boundaries. - SELECT place_id, osm_id, class, type, extratags, + SELECT place_id, osm_id, class, type, extratags, rank_search, centroid, geometry, (compute_place_rank(country_code, osm_type, class, type, admin_level, (extratags->'capital') = 'yes', null)).* @@ -1088,7 +1088,7 @@ BEGIN SELECT wikipedia, importance FROM compute_importance(location.extratags, NEW.country_code, - 'N', location.osm_id, NEW.centroid) + location.rank_search, NEW.centroid) INTO linked_wikipedia,linked_importance; -- Use the maximum importance if one could be computed from the linked object. diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 7ef74349..d576485e 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -276,7 +276,7 @@ CREATE SEQUENCE file start 1; -- null table so it won't error -- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it -CREATE TABLE wikipedia_article ( +CREATE TABLE IF NOT EXISTS wikipedia_article ( language text NOT NULL, title text NOT NULL, langcount integer, @@ -290,15 +290,12 @@ CREATE TABLE wikipedia_article ( wd_page_title text, instance_of text ); -ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title); -CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id); -CREATE TABLE wikipedia_redirect ( +CREATE TABLE IF NOT EXISTS wikipedia_redirect ( language text, from_title text, to_title text ); -ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title); -- osm2pgsql does not create indexes on the middle tables for Nominatim -- Add one for lookup of associated street relations. diff --git a/nominatim/clicmd/setup.py b/nominatim/clicmd/setup.py index 46eee19f..344167bb 100644 --- a/nominatim/clicmd/setup.py +++ b/nominatim/clicmd/setup.py @@ -96,8 +96,6 @@ class SetupAll: drop=args.no_updates, ignore_errors=args.ignore_errors) - self._setup_tables(args.config, args.reverse_only) - LOG.warning('Importing wikipedia importance data') data_path = Path(args.config.WIKIPEDIA_DATA_PATH or args.project_dir) if refresh.import_wikipedia_articles(args.config.get_libpq_dsn(), @@ -112,6 +110,8 @@ class SetupAll: LOG.error('Secondary importance file not imported. ' 'Falling back to default ranking.') + self._setup_tables(args.config, args.reverse_only) + if args.continue_at is None or args.continue_at == 'load-data': LOG.warning('Initialise tables') with connect(args.config.get_libpq_dsn()) as conn: