modifiers to encourage use of indices

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 2c81af32437be0a05a09ad3310b9b50aaaa7a1cd..a68a90b3c151af7da22b4df87734cf73cee8c611 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1225,6 +1225,10 @@ DECLARE
   name_vector INTEGER[];
   nameaddress_vector INTEGER[];
 
   name_vector INTEGER[];
   nameaddress_vector INTEGER[];
 

+  wiki_article TEXT;
+  wiki_article_title TEXT;
+  wiki_article_language TEXT;
+

   result BOOLEAN;
 BEGIN
 
   result BOOLEAN;
 BEGIN
 


@@ -1308,6 +1312,34 @@ BEGIN
       address_havelevel[i] := false;
     END LOOP;
 
       address_havelevel[i] := false;
     END LOOP;
 

+    NEW.importance := null;
+    -- WARNING: see duplicate of code below (yuk!)
+    IF NEW.extratags?'wikipedia' THEN
+      wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_');
+      wiki_article_title := split_part(wiki_article, ':', 2);
+      IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+        wiki_article_title := wiki_article;
+        wiki_article_language := 'en';
+      ELSE
+        wiki_article_language := lower(split_part(wiki_article, ':', 1));
+      END IF;
+--RAISE WARNING '% %', wiki_article_language, wiki_article_title;
+
+      select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title 
+        from wikipedia_article 
+        where language = wiki_article_language and 
+        (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+      UNION ALL
+      select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title 
+        from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
+        where wikipedia_redirect.language = wiki_article_language and 
+        (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'\\',''))
+      order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+
+    ELSE
+      select importance,language||':'||title from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+    END IF;
+

 --RAISE WARNING '% %', NEW.place_id, NEW.rank_search;
 
     -- For low level elements we inherit from our parent road
 --RAISE WARNING '% %', NEW.place_id, NEW.rank_search;
 
     -- For low level elements we inherit from our parent road


@@ -1509,7 +1541,11 @@ BEGIN
           select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
             and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
 
           select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
             and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
 

-          IF NEW.name->'name' = linkedPlacex.name->'name' AND NEW.rank_search = linkedPlacex.rank_search THEN
+          -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+          -- But that can be fixed by explicitly setting the label in the data
+          IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') 
+            AND NEW.rank_search = linkedPlacex.rank_search THEN
+

             -- If we don't already have one use this as the centre point of the geometry
             IF NEW.centroid IS NULL THEN
               NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
             -- If we don't already have one use this as the centre point of the geometry
             IF NEW.centroid IS NULL THEN
               NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));


@@ -1531,12 +1567,13 @@ BEGIN
       END IF;
 
       -- not found one yet? how about doing a name search
       END IF;
 
       -- not found one yet? how about doing a name search

-      IF NEW.centroid IS NULL THEN
+      IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
+

         FOR linkedPlacex IN select placex.* from placex WHERE
           make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
           AND placex.rank_search = NEW.rank_search
           AND placex.place_id != NEW.place_id
         FOR linkedPlacex IN select placex.* from placex WHERE
           make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
           AND placex.rank_search = NEW.rank_search
           AND placex.place_id != NEW.place_id

-          AND osm_type = 'N'
+          AND placex.osm_type = 'N' AND placex.rank_search < 26

           AND st_contains(NEW.geometry, placex.geometry)
         LOOP
 
           AND st_contains(NEW.geometry, placex.geometry)
         LOOP
 


@@ -1562,6 +1599,31 @@ BEGIN
         place_centroid := NEW.centroid;
       END IF;
 
         place_centroid := NEW.centroid;
       END IF;
 

+      -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
+      -- WARNING: duplicate of code above (yuk!)
+      IF NEW.importance is null AND NEW.extratags?'wikipedia' THEN
+        wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_');
+        wiki_article_title := split_part(wiki_article, ':', 2);
+        IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+          wiki_article_title := wiki_article;
+          wiki_article_language := 'en';
+        ELSE
+          wiki_article_language := lower(split_part(wiki_article, ':', 1));
+        END IF;
+
+        select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title 
+          from wikipedia_article 
+          where language = wiki_article_language and 
+          (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+        UNION ALL
+        select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title 
+          from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
+          where wikipedia_redirect.language = wiki_article_language and 
+          (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'\\',''))
+        order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+
+      END IF;
+

     END IF;
 
     NEW.parent_place_id = 0;
     END IF;
 
     NEW.parent_place_id = 0;


@@ -2701,4 +2763,23 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-
+-- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
+CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar 
+  AS $$
+SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
+    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
+    FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
+), '') AS bytea), 'UTF8');
+$$ 
+LANGUAGE SQL IMMUTABLE STRICT;
+
+CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar) RETURNS varchar
+  AS $$
+DECLARE
+BEGIN
+  RETURN decode_url_part(p);
+EXCEPTION
+  WHEN others THEN return null;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;