sql/functions/importance.sql

   1 -- Functions for interpreting wkipedia/wikidata tags and computing importance.
   2
   3 DROP TYPE IF EXISTS wikipedia_article_match CASCADE;
   4 CREATE TYPE wikipedia_article_match as (
   5   language TEXT,
   6   title TEXT,
   7   importance FLOAT
   8 );
   9
  10 DROP TYPE IF EXISTS place_importance CASCADE;
  11 CREATE TYPE place_importance as (
  12   importance FLOAT,
  13   wikipedia TEXT
  14 );
  15
  16
  17 -- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
  18 CREATE OR REPLACE FUNCTION decode_url_part(p varchar)
  19   RETURNS varchar
  20   AS $$
  21 SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
  22     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
  23     FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
  24 ), '') AS bytea), 'UTF8');
  25 $$
  26 LANGUAGE SQL IMMUTABLE STRICT;
  27
  28
  29 CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar)
  30   RETURNS varchar
  31   AS $$
  32 DECLARE
  33 BEGIN
  34   RETURN decode_url_part(p);
  35 EXCEPTION
  36   WHEN others THEN return null;
  37 END;
  38 $$
  39 LANGUAGE plpgsql IMMUTABLE STRICT;
  40
  41
  42 CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2))
  43   RETURNS wikipedia_article_match
  44   AS $$
  45 DECLARE
  46   langs TEXT[];
  47   i INT;
  48   wiki_article TEXT;
  49   wiki_article_title TEXT;
  50   wiki_article_language TEXT;
  51   result wikipedia_article_match;
  52 BEGIN
  53   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'];
  54   i := 1;
  55   WHILE langs[i] IS NOT NULL LOOP
  56     wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
  57     IF wiki_article is not null THEN
  58       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
  59       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
  60       wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
  61       --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
  62       wiki_article := replace(wiki_article,' ','_');
  63       IF strpos(wiki_article, ':') IN (3,4) THEN
  64         wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
  65         wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
  66       ELSE
  67         wiki_article_title := trim(wiki_article);
  68         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;
  69       END IF;
  70
  71       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
  72         from wikipedia_article
  73         where language = wiki_article_language and
  74         (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
  75       UNION ALL
  76       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
  77         from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
  78         where wikipedia_redirect.language = wiki_article_language and
  79         (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'\\',''))
  80       order by importance desc limit 1 INTO result;
  81
  82       IF result.language is not null THEN
  83         return result;
  84       END IF;
  85     END IF;
  86     i := i + 1;
  87   END LOOP;
  88   RETURN NULL;
  89 END;
  90 $$
  91 LANGUAGE plpgsql STABLE;
  92
  93
  94 CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE,
  95                                               country_code varchar(2),
  96                                               osm_type varchar(1), osm_id BIGINT)
  97   RETURNS place_importance
  98   AS $$
  99 DECLARE
 100   match RECORD;
 101   result place_importance;
 102 BEGIN
 103   FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code)
 104                WHERE language is not NULL
 105   LOOP
 106     result.importance := match.importance;
 107     result.wikipedia := match.language || ':' || match.title;
 108     RETURN result;
 109   END LOOP;
 110
 111   IF extratags ? 'wikidata' THEN
 112     FOR match IN SELECT * FROM wikipedia_article
 113                   WHERE wd_page_title = extratags->'wikidata'
 114                   ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP
 115       result.importance := match.importance;
 116       result.wikipedia := match.language || ':' || match.title;
 117       RETURN result;
 118     END LOOP;
 119   END IF;
 120
 121   RETURN null;
 122 END;
 123 $$
 124 LANGUAGE plpgsql;
 125