lib-sql/functions/importance.sql

   1 -- SPDX-License-Identifier: GPL-2.0-only
   2 --
   3 -- This file is part of Nominatim. (https://nominatim.org)
   4 --
   5 -- Copyright (C) 2022 by the Nominatim developer community.
   6 -- For a full list of authors see the git log.
   7
   8 -- Functions for interpreting wkipedia/wikidata tags and computing importance.
   9
  10 DROP TYPE IF EXISTS wikipedia_article_match CASCADE;
  11 CREATE TYPE wikipedia_article_match as (
  12   language TEXT,
  13   title TEXT,
  14   importance FLOAT
  15 );
  16
  17 DROP TYPE IF EXISTS place_importance CASCADE;
  18 CREATE TYPE place_importance as (
  19   importance FLOAT,
  20   wikipedia TEXT
  21 );
  22
  23
  24 -- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
  25 CREATE OR REPLACE FUNCTION decode_url_part(p varchar)
  26   RETURNS varchar
  27   AS $$
  28 SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
  29     SELECT CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END
  30     FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
  31 ), '') AS bytea), 'UTF8');
  32 $$
  33 LANGUAGE SQL IMMUTABLE STRICT;
  34
  35
  36 CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar)
  37   RETURNS varchar
  38   AS $$
  39 DECLARE
  40 BEGIN
  41   RETURN decode_url_part(p);
  42 EXCEPTION
  43   WHEN others THEN return null;
  44 END;
  45 $$
  46 LANGUAGE plpgsql IMMUTABLE STRICT;
  47
  48
  49 CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2))
  50   RETURNS wikipedia_article_match
  51   AS $$
  52 DECLARE
  53   langs TEXT[];
  54   i INT;
  55   wiki_article TEXT;
  56   wiki_article_title TEXT;
  57   wiki_article_language TEXT;
  58   result wikipedia_article_match;
  59 BEGIN
  60   langs := ARRAY['english','country','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'];
  61   i := 1;
  62   WHILE langs[i] IS NOT NULL LOOP
  63     wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
  64     IF wiki_article is not null THEN
  65       wiki_article := replace(wiki_article,' ','_');
  66       IF strpos(wiki_article, ':') IN (3,4) THEN
  67         wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
  68         wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
  69       ELSE
  70         wiki_article_title := trim(wiki_article);
  71         wiki_article_language := CASE WHEN langs[i] = 'english' THEN 'en' WHEN langs[i] = 'country' THEN get_country_language_code(country_code) ELSE langs[i] END;
  72       END IF;
  73
  74       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
  75         from wikipedia_article
  76         where language = wiki_article_language and
  77         (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
  78       UNION ALL
  79       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
  80         from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
  81         where wikipedia_redirect.language = wiki_article_language and
  82         (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
  83       order by importance desc limit 1 INTO result;
  84
  85       IF result.language is not null THEN
  86         return result;
  87       END IF;
  88     END IF;
  89     i := i + 1;
  90   END LOOP;
  91   RETURN NULL;
  92 END;
  93 $$
  94 LANGUAGE plpgsql STABLE;
  95
  96
  97 CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE,
  98                                               country_code varchar(2),
  99                                               rank_search SMALLINT,
 100                                               centroid GEOMETRY)
 101   RETURNS place_importance
 102   AS $$
 103 DECLARE
 104   match RECORD;
 105   result place_importance;
 106   osm_views_exists BIGINT;
 107   views BIGINT;
 108 BEGIN
 109   -- add importance by wikipedia article if the place has one
 110   FOR match IN
 111     SELECT * FROM get_wikipedia_match(extratags, country_code)
 112     WHERE language is not NULL
 113   LOOP
 114     result.importance := match.importance;
 115     result.wikipedia := match.language || ':' || match.title;
 116     RETURN result;
 117   END LOOP;
 118
 119   -- Nothing? Then try with the wikidata tag.
 120   IF result.importance is null AND extratags ? 'wikidata' THEN
 121     FOR match IN SELECT * FROM wikipedia_article
 122                   WHERE wd_page_title = extratags->'wikidata'
 123                   ORDER BY language = 'en' DESC, langcount DESC LIMIT 1
 124     LOOP
 125       result.importance := match.importance;
 126       result.wikipedia := match.language || ':' || match.title;
 127       RETURN result;
 128     END LOOP;
 129   END IF;
 130
 131   -- Still nothing? Fall back to a default.
 132   IF result.importance is null THEN
 133     result.importance := 0.75001 - (rank_search::float / 40);
 134   END IF;
 135
 136 {% if 'secondary_importance' in db.tables %}
 137   FOR match IN
 138     SELECT ST_Value(rast, centroid) as importance
 139     FROM secondary_importance
 140     WHERE ST_Intersects(ST_ConvexHull(rast), centroid) LIMIT 1
 141   LOOP
 142     -- Secondary importance as tie breaker with 0.0001 weight.
 143     result.importance := result.importance + match.importance::float / 655350000;
 144   END LOOP;
 145 {% endif %}
 146
 147   RETURN result;
 148 END;
 149 $$
 150 LANGUAGE plpgsql;
 151