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 {% if 'wikimedia_importance' in db.tables %}
  24
  25 CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2))
  26   RETURNS wikipedia_article_match
  27   AS $$
  28 DECLARE
  29   i INT;
  30   wiki_article_title TEXT;
  31   wiki_article_language TEXT;
  32   result wikipedia_article_match;
  33   entry RECORD;
  34 BEGIN
  35   IF extratags ? 'wikipedia' and strpos(extratags->'wikipedia', ':') IN (3,4) THEN
  36     wiki_article_language := lower(trim(split_part(extratags->'wikipedia', ':', 1)));
  37     wiki_article_title := trim(substr(extratags->'wikipedia',
  38                                       strpos(extratags->'wikipedia', ':') + 1));
  39
  40     FOR result IN
  41       SELECT language, title, importance FROM wikimedia_importance
  42         WHERE language = wiki_article_language
  43               and title = replace(wiki_article_title, ' ', '_')
  44     LOOP
  45       RETURN result;
  46     END LOOP;
  47   END IF;
  48
  49   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']
  50   LOOP
  51     IF extratags ? ('wikipedia:' || wiki_article_language) THEN
  52         wiki_article_title := extratags->('wikipedia:' || wiki_article_language);
  53
  54         FOR result IN
  55           SELECT language, title, importance FROM wikimedia_importance
  56             WHERE language = wiki_article_language
  57                   and title = replace(wiki_article_title, ' ', '_')
  58         LOOP
  59           RETURN result;
  60         END LOOP;
  61     END IF;
  62
  63   END LOOP;
  64
  65   RETURN NULL;
  66 END;
  67 $$
  68 LANGUAGE plpgsql IMMUTABLE;
  69
  70 {% else %}
  71
  72 -- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
  73 CREATE OR REPLACE FUNCTION decode_url_part(p varchar)
  74   RETURNS varchar
  75   AS $$
  76 SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
  77     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
  78     FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
  79 ), '') AS bytea), 'UTF8');
  80 $$
  81 LANGUAGE SQL IMMUTABLE STRICT;
  82
  83
  84 CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar)
  85   RETURNS varchar
  86   AS $$
  87 DECLARE
  88 BEGIN
  89   RETURN decode_url_part(p);
  90 EXCEPTION
  91   WHEN others THEN return null;
  92 END;
  93 $$
  94 LANGUAGE plpgsql IMMUTABLE STRICT;
  95
  96
  97 CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2))
  98   RETURNS wikipedia_article_match
  99   AS $$
 100 DECLARE
 101   langs TEXT[];
 102   i INT;
 103   wiki_article TEXT;
 104   wiki_article_title TEXT;
 105   wiki_article_language TEXT;
 106   result wikipedia_article_match;
 107 BEGIN
 108   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'];
 109   i := 1;
 110   WHILE langs[i] IS NOT NULL LOOP
 111     wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
 112     IF wiki_article is not null THEN
 113       wiki_article := replace(wiki_article,' ','_');
 114       IF strpos(wiki_article, ':') IN (3,4) THEN
 115         wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
 116         wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
 117       ELSE
 118         wiki_article_title := trim(wiki_article);
 119         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;
 120       END IF;
 121
 122       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
 123         from wikipedia_article
 124         where language = wiki_article_language and
 125         (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
 126       UNION ALL
 127       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
 128         from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
 129         where wikipedia_redirect.language = wiki_article_language and
 130         (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'\\',''))
 131       order by importance desc limit 1 INTO result;
 132
 133       IF result.language is not null THEN
 134         return result;
 135       END IF;
 136     END IF;
 137     i := i + 1;
 138   END LOOP;
 139   RETURN NULL;
 140 END;
 141 $$
 142 LANGUAGE plpgsql STABLE;
 143
 144 {% endif %}
 145
 146 CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE,
 147                                               country_code varchar(2),
 148                                               rank_search SMALLINT,
 149                                               centroid GEOMETRY)
 150   RETURNS place_importance
 151   AS $$
 152 DECLARE
 153   match RECORD;
 154   result place_importance;
 155   osm_views_exists BIGINT;
 156   views BIGINT;
 157 BEGIN
 158   -- add importance by wikipedia article if the place has one
 159   FOR match IN
 160     SELECT * FROM get_wikipedia_match(extratags, country_code)
 161     WHERE language is not NULL
 162   LOOP
 163     result.importance := match.importance;
 164     result.wikipedia := match.language || ':' || match.title;
 165     RETURN result;
 166   END LOOP;
 167
 168   -- Nothing? Then try with the wikidata tag.
 169   IF result.importance is null AND extratags ? 'wikidata' THEN
 170     FOR match IN
 171 {% if 'wikimedia_importance' in db.tables %}
 172       SELECT * FROM wikimedia_importance
 173         WHERE wikidata = extratags->'wikidata'
 174         LIMIT 1
 175 {% else %}
 176       SELECT * FROM wikipedia_article
 177         WHERE wd_page_title = extratags->'wikidata'
 178         ORDER BY language = 'en' DESC, langcount DESC LIMIT 1
 179 {% endif %}
 180     LOOP
 181       result.importance := match.importance;
 182       result.wikipedia := match.language || ':' || match.title;
 183       RETURN result;
 184     END LOOP;
 185   END IF;
 186
 187   -- Still nothing? Fall back to a default.
 188   IF result.importance is null THEN
 189     result.importance := 0.40001 - (rank_search::float / 75);
 190   END IF;
 191
 192 {% if 'secondary_importance' in db.tables %}
 193   FOR match IN
 194     SELECT ST_Value(rast, centroid) as importance
 195     FROM secondary_importance
 196     WHERE ST_Intersects(ST_ConvexHull(rast), centroid) LIMIT 1
 197   LOOP
 198     -- Secondary importance as tie breaker with 0.0001 weight.
 199     result.importance := result.importance + match.importance::float / 655350000;
 200   END LOOP;
 201 {% endif %}
 202
 203   RETURN result;
 204 END;
 205 $$
 206 LANGUAGE plpgsql;
 207