fix dependencies and adapt documentation for psycopg3

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

diff --git a/lib-sql/functions/importance.sql b/lib-sql/functions/importance.sql
index ac3aa7f856cbae355f7111fdd12490a003e99a57..1de5899ca2950c1b3fc3fee3750164eb3db52091 100644 (file)
--- a/lib-sql/functions/importance.sql
+++ b/lib-sql/functions/importance.sql
@@ -20,6 +20,54 @@ CREATE TYPE place_importance as (
   wikipedia TEXT
 );
 
   wikipedia TEXT
 );
 

+{% if 'wikimedia_importance' in db.tables %}
+
+CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2))
+  RETURNS wikipedia_article_match
+  AS $$
+DECLARE
+  i INT;
+  wiki_article_title TEXT;
+  wiki_article_language TEXT;
+  result wikipedia_article_match;
+  entry RECORD;
+BEGIN
+  IF extratags ? 'wikipedia' and strpos(extratags->'wikipedia', ':') IN (3,4) THEN
+    wiki_article_language := lower(trim(split_part(extratags->'wikipedia', ':', 1)));
+    wiki_article_title := trim(substr(extratags->'wikipedia',
+                                      strpos(extratags->'wikipedia', ':') + 1));
+
+    FOR result IN
+      SELECT language, title, importance FROM wikimedia_importance
+        WHERE language = wiki_article_language
+              and title = replace(wiki_article_title, ' ', '_')
+    LOOP
+      RETURN result;
+    END LOOP;
+  END IF;
+
+  FOREACH wiki_article_language IN ARRAY ARRAY['ar','bg','ca','cs','da','de','en','es','eo','eu','fa','fr','ko','hi','hr','id','it','he','lt','hu','ms','nl','ja','no','pl','pt','kk','ro','ru','sk','sl','sr','fi','sv','tr','uk','vi','vo','war','zh']
+  LOOP
+    IF extratags ? ('wikipedia:' || wiki_article_language) THEN
+        wiki_article_title := extratags->('wikipedia:' || wiki_article_language);
+
+        FOR result IN
+          SELECT language, title, importance FROM wikimedia_importance
+            WHERE language = wiki_article_language
+                  and title = replace(wiki_article_title, ' ', '_')
+        LOOP
+          RETURN result;
+        END LOOP;
+    END IF;
+
+  END LOOP;
+
+  RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+{% else %}

 
 -- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
 CREATE OR REPLACE FUNCTION decode_url_part(p varchar)
 
 -- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
 CREATE OR REPLACE FUNCTION decode_url_part(p varchar)


@@ -62,10 +110,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)));


@@ -97,35 +141,66 @@ END;
 $$
 LANGUAGE plpgsql STABLE;
 
 $$
 LANGUAGE plpgsql STABLE;
 

+{% endif %}

 
 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
-    FOR match IN SELECT * FROM wikipedia_article
-                  WHERE wd_page_title = extratags->'wikidata'
-                  ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP
+  -- Nothing? Then try with the wikidata tag.
+  IF result.importance is null AND extratags ? 'wikidata' THEN
+    FOR match IN
+{% if 'wikimedia_importance' in db.tables %}
+      SELECT * FROM wikimedia_importance
+        WHERE wikidata = extratags->'wikidata'
+        LIMIT 1
+{% else %}
+      SELECT * FROM wikipedia_article
+        WHERE wd_page_title = extratags->'wikidata'
+        ORDER BY language = 'en' DESC, langcount DESC LIMIT 1
+{% endif %}
+    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.40001 - (rank_search::float / 75);
+  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;