Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 2c7dbe754e7f8e7f265b7a3a931a33cecaab58d6..5d2b7a2216aa8a95c9e4bfbca324ca3828db0b43 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1286,7 +1286,7 @@ BEGIN
   -- cheaper but less acurate
   place_centroid := ST_PointOnSurface(NEW.geometry);
   -- For searching near features rather use the centroid
   -- cheaper but less acurate
   place_centroid := ST_PointOnSurface(NEW.geometry);
   -- For searching near features rather use the centroid

-  near_centroid := ST_Centroid(NEW.geometry);
+  near_centroid := ST_Envelope(NEW.geometry);

   NEW.centroid := null;
   NEW.postcode := null;
   --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
   NEW.centroid := null;
   NEW.postcode := null;
   --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);


@@ -1358,10 +1358,9 @@ BEGIN
   END LOOP;
 
   NEW.importance := null;
   END LOOP;
 
   NEW.importance := null;

-  select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance;
-  IF NEW.importance IS NULL THEN
-    select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
-  END IF;
+  SELECT wikipedia, importance
+    FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id)
+    INTO NEW.wikipedia,NEW.importance;

 
 --DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;
 
 
 --DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;
 


@@ -1521,7 +1520,7 @@ BEGIN
              NEW.postcode := location.postcode;
           END IF;
           IF NEW.postcode is null THEN
              NEW.postcode := location.postcode;
           END IF;
           IF NEW.postcode is null THEN

-            NEW.postcode := get_nearest_postcode(NEW.country_code, near_centroid);
+            NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry);

           END IF;
       END IF;
 
           END IF;
       END IF;
 


@@ -1600,9 +1599,10 @@ BEGIN
           -- mark the linked place (excludes from search results)
           UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
           -- mark the linked place (excludes from search results)
           UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 

-          -- keep a note of the node id in case we need it for wikipedia in a bit
-          linked_node_id := linkedPlacex.osm_id;
-          select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
+          select wikipedia, importance
+            FROM compute_importance(linkedPlacex.extratags, NEW.country_code,
+                                    'N', linkedPlacex.osm_id)
+            INTO linked_wikipedia,linked_importance;

           --DEBUG: RAISE WARNING 'Linked label member';
         END LOOP;
 
           --DEBUG: RAISE WARNING 'Linked label member';
         END LOOP;
 


@@ -1639,9 +1639,10 @@ BEGIN
               -- mark the linked place (excludes from search results)
               UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
               -- mark the linked place (excludes from search results)
               UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 

-              -- keep a note of the node id in case we need it for wikipedia in a bit
-              linked_node_id := linkedPlacex.osm_id;
-              select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
+              select wikipedia, importance
+                FROM compute_importance(linkedPlacex.extratags, NEW.country_code,
+                                        'N', linkedPlacex.osm_id)
+                INTO linked_wikipedia,linked_importance;

               --DEBUG: RAISE WARNING 'Linked admin_center';
             END IF;
 
               --DEBUG: RAISE WARNING 'Linked admin_center';
             END IF;
 


@@ -1684,9 +1685,10 @@ BEGIN
         -- mark the linked place (excludes from search results)
         UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
         -- mark the linked place (excludes from search results)
         UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 

-        -- keep a note of the node id in case we need it for wikipedia in a bit
-        linked_node_id := linkedPlacex.osm_id;
-        select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
+        select wikipedia, importance
+          FROM compute_importance(linkedPlacex.extratags, NEW.country_code,
+                                  'N', linkedPlacex.osm_id)
+          INTO linked_wikipedia,linked_importance;

         --DEBUG: RAISE WARNING 'Linked named place';
       END LOOP;
     END IF;
         --DEBUG: RAISE WARNING 'Linked named place';
       END LOOP;
     END IF;


@@ -1714,13 +1716,6 @@ BEGIN
         (NEW.importance is null or NEW.importance < linked_importance) THEN
         NEW.importance = linked_importance;
     END IF;
         (NEW.importance is null or NEW.importance < linked_importance) THEN
         NEW.importance = linked_importance;
     END IF;

-
-    -- Still null? how about looking it up by the node id
-    IF NEW.importance IS NULL THEN
-      --DEBUG: RAISE WARNING 'Looking up importance by linked node id';
-      select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
-    END IF;
-

   END IF;
 
   -- make sure all names are in the word table
   END IF;
 
   -- make sure all names are in the word table


@@ -1791,7 +1786,7 @@ BEGIN
                                   CASE WHEN NEW.rank_search >= 26
                                              AND NEW.rank_search < 30
                                        THEN NEW.geometry
                                   CASE WHEN NEW.rank_search >= 26
                                              AND NEW.rank_search < 30
                                        THEN NEW.geometry

-                                       ELSE near_centroid END,
+                                       ELSE place_centroid END,

                                   search_maxrank, isin_tokens)
   LOOP
     IF location.rank_address != location_rank_search THEN
                                   search_maxrank, isin_tokens)
   LOOP
     IF location.rank_address != location_rank_search THEN


@@ -2386,7 +2381,7 @@ BEGIN
 
   -- postcode table
   IF for_place_id IS NULL THEN
 
   -- postcode table
   IF for_place_id IS NULL THEN

-    SELECT parent_place_id, country_code, rank_address, postcode, 'place', 'postcode'
+    SELECT parent_place_id, country_code, rank_search, postcode, 'place', 'postcode'

       FROM location_postcode
       WHERE place_id = in_place_id
       INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode,
       FROM location_postcode
       WHERE place_id = in_place_id
       INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode,


@@ -2627,7 +2622,7 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-DROP TYPE wikipedia_article_match CASCADE;
+DROP TYPE IF EXISTS wikipedia_article_match CASCADE;

 create type wikipedia_article_match as (
   language TEXT,
   title TEXT,
 create type wikipedia_article_match as (
   language TEXT,
   title TEXT,


@@ -2684,6 +2679,42 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

+DROP TYPE IF EXISTS place_importance CASCADE;
+create type place_importance as (
+  importance FLOAT,
+  wikipedia TEXT
+);
+
+CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE, country_code varchar(2), osm_type varchar(1), osm_id BIGINT)
+  RETURNS place_importance
+  AS $$
+DECLARE
+  match RECORD;
+  result place_importance;
+BEGIN
+  FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code)
+               WHERE language is not NULL
+  LOOP
+    result.importance := match.importance;
+    result.wikipedia := match.language || ':' || match.title;
+    RETURN result;
+  END LOOP;
+
+  IF extratags ? 'wikidata' THEN
+    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.wikipedia := match.language || ':' || match.title;
+      RETURN result;
+    END LOOP;
+  END IF;
+
+  RETURN null;
+END;
+$$
+LANGUAGE plpgsql;
+

 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER) 
   RETURNS SETOF GEOMETRY
   AS $$
 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER) 
   RETURNS SETOF GEOMETRY
   AS $$