Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index ac726cb331a66366a090073bcd87fa260cb5c8e5..54f46cfe933b64fbd5f33dfc9a6ac336ade93539 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1023,6 +1023,13 @@ BEGIN
             NEW.rank_address := 5;
           END IF;
 
             NEW.rank_address := 5;
           END IF;
 

+        ELSEIF NEW.calculated_country_code = 'sg' THEN
+
+          IF NEW.postcode ~ '^([0-9]{6})$' THEN
+            NEW.rank_search := 25;
+            NEW.rank_address := 11;
+          END IF;
+

         ELSEIF NEW.calculated_country_code = 'de' THEN
 
           IF NEW.postcode ~ '^([0-9]{5})$' THEN
         ELSEIF NEW.calculated_country_code = 'de' THEN
 
           IF NEW.postcode ~ '^([0-9]{5})$' THEN


@@ -1380,9 +1387,12 @@ BEGIN
         FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[]
         LOOP
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
         FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[]
         LOOP
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP

-                IF relation.members[i+1] in ('', 'main_stream') AND substring(relation.members[i],1,1) = 'w' THEN
+                IF relation.members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation.members[i],1,1) = 'w' THEN

                   --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.parts[i];
                   --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.parts[i];

-                  FOR location IN SELECT * FROM placex WHERE osm_type = 'W' and osm_id = substring(relation.members[i],2,200)::bigint and class = NEW.class and type = NEW.type
+                  FOR location IN SELECT * FROM placex
+                    WHERE osm_type = 'W' and osm_id = substring(relation.members[i],2,200)::bigint
+                    and class = NEW.class and type = NEW.type
+                    and ( relation.members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')

                   LOOP
                     UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = location.place_id;
                   END LOOP;
                   LOOP
                     UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = location.place_id;
                   END LOOP;


@@ -2097,8 +2107,8 @@ BEGIN
   -- Handle a place changing type by removing the old data
   -- My generated 'place' types are causing havok because they overlap with real keys
   -- TODO: move them to their own special purpose key/class to avoid collisions
   -- Handle a place changing type by removing the old data
   -- My generated 'place' types are causing havok because they overlap with real keys
   -- TODO: move them to their own special purpose key/class to avoid collisions

-  IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
-    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
+  IF existing.osm_type IS NULL THEN
+    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;

   END IF;
 
   --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id;
   END IF;
 
   --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id;


@@ -2349,29 +2359,29 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways
-  AS $$
-DECLARE
-  searchnodes INTEGER[];
-  location RECORD;
-  j INTEGER;
-BEGIN
-
-  searchnodes := '{}';
-  FOR j IN 1..array_upper(way_ids, 1) LOOP
-    FOR location IN 
-      select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
-    LOOP
-      IF not (ARRAY[location.nodes] <@ searchnodes) THEN
-        searchnodes := searchnodes || location.nodes;
-      END IF;
-    END LOOP;
-  END LOOP;
-
-  RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids;
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
+--CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways
+--  AS $$
+--DECLARE
+--  searchnodes INTEGER[];
+--  location RECORD;
+--  j INTEGER;
+--BEGIN
+--
+--  searchnodes := '{}';
+--  FOR j IN 1..array_upper(way_ids, 1) LOOP
+--    FOR location IN
+--      select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
+--    LOOP
+--      IF not (ARRAY[location.nodes] <@ searchnodes) THEN
+--        searchnodes := searchnodes || location.nodes;
+--      END IF;
+--    END LOOP;
+--  END LOOP;
+--
+--  RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids;
+--END;
+--$$
+--LANGUAGE plpgsql IMMUTABLE;

 
 CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT
   AS $$
 
 CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT
   AS $$


@@ -2970,14 +2980,14 @@ BEGIN
       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');

-      wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
+      --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');

       wiki_article := replace(wiki_article,' ','_');
       wiki_article := replace(wiki_article,' ','_');

-      wiki_article_title := trim(split_part(wiki_article, ':', 2));
-      IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+      IF strpos(wiki_article, ':') IN (3,4) THEN
+        wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
+        wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
+      ELSE

         wiki_article_title := trim(wiki_article);
         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;
         wiki_article_title := trim(wiki_article);
         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;

-      ELSE
-        wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));

       END IF;
 
       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
       END IF;
 
       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance