Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 4f7493f3fc62873268930591f495f0e2fcbb937b..4c040fdceb4b4157f9d67c8ab998d68076d2da14 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -347,7 +347,7 @@ BEGIN
 
     w := getorcreate_word_id(s);
 
 
     w := getorcreate_word_id(s);
 

-    IF NOT (ARRAY[w] <@ result) THEN
+    IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN

       result := result || w;
     END IF;
 
       result := result || w;
     END IF;
 


@@ -415,7 +415,7 @@ BEGIN
 
   w := getorcreate_word_id(s);
 
 
   w := getorcreate_word_id(s);
 

-  IF NOT (ARRAY[w] <@ result) THEN
+  IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN

     result := result || w;
   END IF;
 
     result := result || w;
   END IF;
 


@@ -823,9 +823,13 @@ BEGIN
 
       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 
 
       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 

-        select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' INTO search_place_id;
+        -- If there is a place of a type other than place/house, use that because
+        -- it is guaranteed to be the original node. For place/house types use the
+        -- one with the smallest id because the original node was created first.
+        -- Ignore all nodes marked for deletion. (Might happen when the type changes.)
+        select place_id from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and indexed_status < 100 order by (type = 'house'),place_id limit 1 INTO search_place_id;

         IF search_place_id IS NULL THEN
         IF search_place_id IS NULL THEN

-          -- null record of right type
+          -- if no such node exists, create a record of the right type

           select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode;
           select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
           IF nextnode.geometry IS NULL THEN
           select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode;
           select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
           IF nextnode.geometry IS NULL THEN


@@ -890,7 +894,7 @@ BEGIN
                 -- ideally postcodes should move up to the way
                 insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode,
                   country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
                 -- ideally postcodes should move up to the way
                 insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode,
                   country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)

-                  values ('N',prevnode.osm_id, prevnode.class, prevnode.type, prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),
+                  values ('N',prevnode.osm_id, 'place', 'house', prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),

                   prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
                   prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;


@@ -934,10 +938,6 @@ BEGIN
   --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id;
 
   -- just block these
   --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id;
 
   -- just block these

-  IF NEW.class = 'highway' and NEW.type in ('turning_circle','traffic_signals','mini_roundabout','noexit','crossing') THEN
---    RAISE WARNING 'bad highway %',NEW.osm_id;
-    RETURN null;
-  END IF;

   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
 --    RAISE WARNING 'empty landuse %',NEW.osm_id;
     RETURN null;
   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
 --    RAISE WARNING 'empty landuse %',NEW.osm_id;
     RETURN null;


@@ -1090,10 +1090,10 @@ BEGIN
       ELSEIF NEW.type in ('national_park') THEN
         NEW.rank_search := 18;
         NEW.rank_address := 18;
       ELSEIF NEW.type in ('national_park') THEN
         NEW.rank_search := 18;
         NEW.rank_address := 18;

-      ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
+      ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN

         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;

-      ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN
+      ELSEIF NEW.type in ('farm','locality','islet','mountain_pass') THEN

         NEW.rank_search := 20;
         NEW.rank_address := 0;
         -- Irish townlands, tagged as place=locality and locality=townland
         NEW.rank_search := 20;
         NEW.rank_address := 0;
         -- Irish townlands, tagged as place=locality and locality=townland


@@ -1123,7 +1123,11 @@ BEGIN
         return NULL;
       END IF;
       NEW.rank_search := NEW.admin_level * 2;
         return NULL;
       END IF;
       NEW.rank_search := NEW.admin_level * 2;

-      NEW.rank_address := NEW.rank_search;
+      IF NEW.type = 'administrative' THEN
+        NEW.rank_address := NEW.rank_search;
+      ELSE
+        NEW.rank_address := 0;
+      END IF;

     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;
       NEW.rank_address := NEW.rank_search;


@@ -1353,13 +1357,26 @@ BEGIN
     NEW.centroid := null;
 
     -- reclaculate country and partition
     NEW.centroid := null;
 
     -- reclaculate country and partition

-    IF NEW.rank_search >= 4 THEN
-      --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
-      NEW.calculated_country_code := lower(get_country_code(place_centroid));
+    IF NEW.rank_search = 4 THEN
+      -- for countries, believe the mapped country code,
+      -- so that we remain in the right partition if the boundaries
+      -- suddenly expand.
+      NEW.partition := get_partition(place_centroid, lower(NEW.country_code));
+      IF NEW.partition = 0 THEN
+        NEW.calculated_country_code := lower(get_country_code(place_centroid));
+        NEW.partition := get_partition(place_centroid, NEW.calculated_country_code);
+      ELSE
+        NEW.calculated_country_code := lower(NEW.country_code);
+      END IF;

     ELSE
     ELSE

-      NEW.calculated_country_code := NULL;
+      IF NEW.rank_search > 4 THEN
+        --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+        NEW.calculated_country_code := lower(get_country_code(place_centroid));
+      ELSE
+        NEW.calculated_country_code := NULL;
+      END IF;
+      NEW.partition := get_partition(place_centroid, NEW.calculated_country_code);

     END IF;
     END IF;

-    NEW.partition := get_partition(place_centroid, NEW.calculated_country_code);

     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 
     -- Adding ourselves to the list simplifies address calculations later
     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 
     -- Adding ourselves to the list simplifies address calculations later


@@ -1672,6 +1689,11 @@ BEGIN
         END IF;
       END IF;
 
         END IF;
       END IF;
 

+    END IF;
+
+    -- Name searches can be done for ways as well as relations
+    IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 THEN
+

       -- not found one yet? how about doing a name search
       IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
 
       -- not found one yet? how about doing a name search
       IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
 


@@ -1828,19 +1850,21 @@ BEGIN
         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
 
         IF location_isaddress THEN
         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
 
         IF location_isaddress THEN

-            address_havelevel[location.rank_address] := true;
-            IF NOT location.isguess THEN
-                SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
-            END IF;
-        END IF;

 
 

---RAISE WARNING '  Terms: (%) %',location, nameaddress_vector;
+          address_havelevel[location.rank_address] := true;
+          IF NOT location.isguess THEN
+            SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
+          END IF;
+
+          IF location.rank_address > parent_place_id_rank THEN
+            NEW.parent_place_id = location.place_id;
+            parent_place_id_rank = location.rank_address;
+          END IF;

 
 

-        IF location.rank_address > parent_place_id_rank THEN
-          NEW.parent_place_id = location.place_id;
-          parent_place_id_rank = location.rank_address;

         END IF;
 
         END IF;
 

+--RAISE WARNING '  Terms: (%) %',location, nameaddress_vector;
+

       END IF;
 
     END LOOP;
       END IF;
 
     END LOOP;


@@ -2033,9 +2057,6 @@ BEGIN
   END IF;
 
   -- Just block these - lots and pointless
   END IF;
 
   -- Just block these - lots and pointless

-  IF NEW.class = 'highway' and NEW.type in ('turning_circle','traffic_signals','mini_roundabout','noexit','crossing') THEN
-    RETURN null;
-  END IF;

   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
     RETURN null;
   END IF;
   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
     RETURN null;
   END IF;


@@ -2086,6 +2107,9 @@ BEGIN
   IF existingplacex.osm_type IS NULL THEN
 
     IF existing.osm_type IS NOT NULL THEN
   IF existingplacex.osm_type IS NULL THEN
 
     IF existing.osm_type IS NOT NULL THEN

+      -- pathological case caused by the triggerless copy into place during initial import
+      -- force delete even for large areas, it will be reinserted later
+      UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;

       DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
     END IF;
 
       DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
     END IF;
 


@@ -2244,7 +2268,7 @@ BEGIN
       country_code = NEW.country_code,
       parent_place_id = null,
       extratags = NEW.extratags,
       country_code = NEW.country_code,
       parent_place_id = null,
       extratags = NEW.extratags,

-      admin_level = NEW.admin_level,
+      admin_level = CASE WHEN NEW.admin_level > 15 THEN 15 ELSE NEW.admin_level END,

       indexed_status = 2,    
       geometry = NEW.geometry
       where place_id = existingplacex.place_id;
       indexed_status = 2,    
       geometry = NEW.geometry
       where place_id = existingplacex.place_id;