]> git.openstreetmap.org Git - nominatim.git/blob - lib-sql/functions/importance.sql
30e778d15f6dc37f3019a687f66f0d51e136a6fa
[nominatim.git] / 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 := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
66       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
67       wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
68       --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
69       wiki_article := replace(wiki_article,' ','_');
70       IF strpos(wiki_article, ':') IN (3,4) THEN
71         wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
72         wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
73       ELSE
74         wiki_article_title := trim(wiki_article);
75         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;
76       END IF;
77
78       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
79         from wikipedia_article 
80         where language = wiki_article_language and 
81         (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
82       UNION ALL
83       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
84         from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
85         where wikipedia_redirect.language = wiki_article_language and 
86         (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'\\',''))
87       order by importance desc limit 1 INTO result;
88
89       IF result.language is not null THEN
90         return result;
91       END IF;
92     END IF;
93     i := i + 1;
94   END LOOP;
95   RETURN NULL;
96 END;
97 $$
98 LANGUAGE plpgsql STABLE;
99
100
101 CREATE OR REPLACE FUNCTION get_osm_views(centroid GEOMETRY)
102   RETURNS BIGINT
103   AS $$
104 DECLARE
105   result BIGINT;
106 BEGIN
107   SELECT ST_Value(osm_views.rast, centroid)
108   FROM osm_views
109   WHERE ST_Intersects(ST_ConvexHull(osm_views.rast), centroid) LIMIT 1 INTO result;
110
111   return COALESCE(result, 0);
112 END;
113 $$
114 LANGUAGE plpgsql STABLE;
115
116
117 CREATE OR REPLACE FUNCTION normalize_osm_views(views BIGINT)
118   RETURNS FLOAT
119   AS $$
120   DECLARE
121     normalized_osm_views FLOAT;
122     max_views BIGINT;
123   BEGIN
124     IF views > 0 THEN
125       -- Get the highest view count to use it in normalizing the data
126       SELECT max_views_count FROM osm_views_stat INTO max_views;
127       normalized_osm_views := (LOG(views))/(LOG(max_views));
128     ELSE
129       normalized_osm_views := 0.0;
130     END IF;
131
132     RETURN normalized_osm_views;
133   END;
134 $$
135 LANGUAGE plpgsql;
136
137
138 CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE,
139                                               country_code varchar(2),
140                                               osm_type varchar(1), osm_id BIGINT,
141                                               centroid GEOMETRY)
142   RETURNS place_importance
143   AS $$
144 DECLARE
145   match RECORD;
146   result place_importance;
147   osm_views_exists BIGINT;
148   views BIGINT;
149 BEGIN
150  -- check if osm_views table exists
151  SELECT COUNT(table_name)
152    INTO osm_views_exists
153  FROM information_schema.tables
154  WHERE table_schema LIKE 'public' AND 
155        table_type LIKE 'BASE TABLE' AND
156        table_name = 'osm_views';
157
158   -- add importance by OSM views if osm_views table exists
159   IF osm_views_exists THEN
160     views := get_osm_views(centroid);
161     result.importance := normalize_osm_views(views) * 0.35;
162   END IF;
163
164   -- add importance by wiki data if the place has one
165   FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code)
166                WHERE language is not NULL
167   LOOP
168     result.importance := COALESCE(result.importance, 0) + match.importance * 0.65;
169     result.wikipedia := match.language || ':' || match.title;
170     RETURN result;
171   END LOOP;
172
173   IF extratags ? 'wikidata' THEN
174     FOR match IN SELECT * FROM wikipedia_article
175                   WHERE wd_page_title = extratags->'wikidata'
176                   ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP
177       result.importance := COALESCE(result.importance, 0) + match.importance * 0.65;
178       result.wikipedia := match.language || ':' || match.title;
179       RETURN result;
180     END LOOP;
181   END IF;
182
183   RETURN result;
184 END;
185 $$
186 LANGUAGE plpgsql;
187