Merge remote-tracking branch 'upstream/master'

[nominatim.git] / lib-sql / functions / importance.sql

diff --git a/lib-sql/functions/importance.sql b/lib-sql/functions/importance.sql
index 0837f80f097f8d599cc86755e851daf208eb6bdb..6c089d824b99565a72ad9de6202c5fd1f6ae5b2b 100644 (file)
--- a/lib-sql/functions/importance.sql
+++ b/lib-sql/functions/importance.sql
@@ -1,3 +1,10 @@
+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
+

 -- Functions for interpreting wkipedia/wikidata tags and computing importance.
 
 DROP TYPE IF EXISTS wikipedia_article_match CASCADE;
 -- Functions for interpreting wkipedia/wikidata tags and computing importance.
 
 DROP TYPE IF EXISTS wikipedia_article_match CASCADE;


@@ -55,10 +62,6 @@ BEGIN
   WHILE langs[i] IS NOT NULL LOOP
     wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
     IF wiki_article is not null THEN
   WHILE langs[i] IS NOT NULL LOOP
     wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
     IF wiki_article is not null THEN

-      wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
-      wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
-      wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
-      --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');

       wiki_article := replace(wiki_article,' ','_');
       IF strpos(wiki_article, ':') IN (3,4) THEN
         wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
       wiki_article := replace(wiki_article,' ','_');
       IF strpos(wiki_article, ':') IN (3,4) THEN
         wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));


@@ -93,32 +96,55 @@ LANGUAGE plpgsql STABLE;
 
 CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE,
                                               country_code varchar(2),
 
 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 $$
 DECLARE
   match RECORD;
   result place_importance;
   RETURNS place_importance
   AS $$
 DECLARE
   match RECORD;
   result place_importance;

+  osm_views_exists BIGINT;
+  views BIGINT;

 BEGIN
 BEGIN

-  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 := match.importance;
     result.wikipedia := match.language || ':' || match.title;
     RETURN result;
   END LOOP;
 
   LOOP
     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'
     FOR match IN SELECT * FROM wikipedia_article
                   WHERE wd_page_title = extratags->'wikidata'

-                  ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP
+                  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;
 
       result.importance := match.importance;
       result.wikipedia := match.language || ':' || match.title;
       RETURN result;
     END LOOP;
   END IF;
 

-  RETURN null;
+  -- 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;
 $$
 LANGUAGE plpgsql;
 END;
 $$
 LANGUAGE plpgsql;