Integrated OSM views into importance computation

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

diff --git a/lib-sql/functions/importance.sql b/lib-sql/functions/importance.sql
index 0837f80f097f8d599cc86755e851daf208eb6bdb..30e778d15f6dc37f3019a687f66f0d51e136a6fa 100644 (file)
--- a/lib-sql/functions/importance.sql
+++ b/lib-sql/functions/importance.sql
@@ -1,3 +1,10 @@
+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
+

 -- Functions for interpreting wkipedia/wikidata tags and computing importance.
 
 DROP TYPE IF EXISTS wikipedia_article_match CASCADE;
 -- Functions for interpreting wkipedia/wikidata tags and computing importance.
 
 DROP TYPE IF EXISTS wikipedia_article_match CASCADE;


@@ -91,19 +98,74 @@ $$
 LANGUAGE plpgsql STABLE;
 
 
 LANGUAGE plpgsql STABLE;
 
 

+CREATE OR REPLACE FUNCTION get_osm_views(centroid GEOMETRY)
+  RETURNS BIGINT
+  AS $$
+DECLARE
+  result BIGINT;
+BEGIN
+  SELECT ST_Value(osm_views.rast, centroid)
+  FROM osm_views
+  WHERE ST_Intersects(ST_ConvexHull(osm_views.rast), centroid) LIMIT 1 INTO result;
+
+  return COALESCE(result, 0);
+END;
+$$
+LANGUAGE plpgsql STABLE;
+
+
+CREATE OR REPLACE FUNCTION normalize_osm_views(views BIGINT)
+  RETURNS FLOAT
+  AS $$
+  DECLARE
+    normalized_osm_views FLOAT;
+    max_views BIGINT;
+  BEGIN
+    IF views > 0 THEN
+      -- Get the highest view count to use it in normalizing the data
+      SELECT max_views_count FROM osm_views_stat INTO max_views;
+      normalized_osm_views := (LOG(views))/(LOG(max_views));
+    ELSE
+      normalized_osm_views := 0.0;
+    END IF;
+
+    RETURN normalized_osm_views;
+  END;
+$$
+LANGUAGE plpgsql;
+
+

 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)
+                                              osm_type varchar(1), osm_id BIGINT,
+                                              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

+ -- check if osm_views table exists
+ SELECT COUNT(table_name)
+   INTO osm_views_exists
+ FROM information_schema.tables
+ WHERE table_schema LIKE 'public' AND 
+       table_type LIKE 'BASE TABLE' AND
+       table_name = 'osm_views';
+
+  -- add importance by OSM views if osm_views table exists
+  IF osm_views_exists THEN
+    views := get_osm_views(centroid);
+    result.importance := normalize_osm_views(views) * 0.35;
+  END IF;
+
+  -- add importance by wiki data if the place has one

   FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code)
                WHERE language is not NULL
   LOOP
   FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code)
                WHERE language is not NULL
   LOOP

-    result.importance := match.importance;
+    result.importance := COALESCE(result.importance, 0) + match.importance * 0.65;

     result.wikipedia := match.language || ':' || match.title;
     RETURN result;
   END LOOP;
     result.wikipedia := match.language || ':' || match.title;
     RETURN result;
   END LOOP;


@@ -112,13 +174,13 @@ BEGIN
     FOR match IN SELECT * FROM wikipedia_article
                   WHERE wd_page_title = extratags->'wikidata'
                   ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP
     FOR match IN SELECT * FROM wikipedia_article
                   WHERE wd_page_title = extratags->'wikidata'
                   ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP

-      result.importance := match.importance;
+      result.importance := COALESCE(result.importance, 0) + match.importance * 0.65;

       result.wikipedia := match.language || ':' || match.title;
       RETURN result;
     END LOOP;
   END IF;
 
       result.wikipedia := match.language || ':' || match.title;
       RETURN result;
     END LOOP;
   END IF;
 

-  RETURN null;
+  RETURN result;

 END;
 $$
 LANGUAGE plpgsql;
 END;
 $$
 LANGUAGE plpgsql;