modifiers to encourage use of indices

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 02570811e3c9ebbbccef282f77cdb3bdbc693a82..a68a90b3c151af7da22b4df87734cf73cee8c611 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -742,7 +742,7 @@ $$
 LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE FUNCTION create_interpolation(wayid INTEGER, interpolationtype TEXT) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER
   AS $$
 DECLARE
   
@@ -1328,12 +1328,12 @@ BEGIN
       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 = decode_url_part(wiki_article_title) OR title = replace(decode_url_part(wiki_article_title),E'\\',''))
+        (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 = decode_url_part(wiki_article_title) OR from_title = replace(decode_url_part(wiki_article_title),E'\\',''))
+        (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
@@ -1355,7 +1355,7 @@ BEGIN
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
 
         -- Is this node part of a relation?
-        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]
+        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id]
         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
@@ -1371,7 +1371,7 @@ BEGIN
 
 --RAISE WARNING 'x1';
         -- Is this node part of a way?
-        FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id::integer] LOOP
+        FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id] LOOP
 --RAISE WARNING '%', way;
         FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
         LOOP
@@ -1384,7 +1384,7 @@ BEGIN
 
           -- Is the WAY part of a relation
           IF NEW.parent_place_id IS NULL THEN
-              FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] and members @> ARRAY['w'||location.osm_id]
+              FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
               LOOP
                 -- At the moment we only process one type of relation - associatedStreet
                 IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
@@ -1429,7 +1429,7 @@ BEGIN
 
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN
         -- Is this way part of a relation?
-        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['w'||NEW.osm_id]
+        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['w'||NEW.osm_id]
         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
@@ -1573,7 +1573,7 @@ BEGIN
           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
 
@@ -1614,12 +1614,12 @@ BEGIN
         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 = decode_url_part(wiki_article_title) OR title = replace(decode_url_part(wiki_article_title),E'\\',''))
+          (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 = decode_url_part(wiki_article_title) OR from_title = replace(decode_url_part(wiki_article_title),E'\\',''))
+          (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;
@@ -2766,7 +2766,20 @@ 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' || string_agg(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, '') AS bytea), 'UTF8')
-FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m);
+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;