Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 573e80e777a79e5c5606b3d8c0154c9a944837ea..396487b1ae1a1c3c93f5236c009324bd71a67252 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -83,6 +83,26 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

+CREATE OR REPLACE FUNCTION getorcreate_postcode_id(postcode TEXT)
+  RETURNS INTEGER
+  AS $$
+DECLARE
+  lookup_token TEXT;
+  lookup_word TEXT;
+  return_word_id INTEGER;
+BEGIN
+  lookup_word := upper(trim(postcode));
+  lookup_token := ' ' || make_standard_name(lookup_word);
+  SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='postcode' into return_word_id;
+  IF return_word_id IS NULL THEN
+    return_word_id := nextval('seq_word');
+    INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word, 'place', 'postcode', null, 0);
+  END IF;
+  RETURN return_word_id;
+END;
+$$
+LANGUAGE plpgsql;
+

 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT, lookup_country_code varchar(2))
   RETURNS INTEGER
   AS $$
 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT, lookup_country_code varchar(2))
   RETURNS INTEGER
   AS $$


@@ -245,6 +265,7 @@ DECLARE
 BEGIN
     rank_search := 30;
     rank_address := 30;
 BEGIN
     rank_search := 30;
     rank_address := 30;

+    postcode := upper(postcode);

 
     IF country_code = 'gb' THEN
         IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
 
     IF country_code = 'gb' THEN
         IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN


@@ -298,19 +319,31 @@ LANGUAGE plpgsql IMMUTABLE;
 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT
   AS $$
 DECLARE

-  item RECORD;
+  outcode TEXT;
+  cnt INTEGER;

 BEGIN
 BEGIN

-    FOR item IN
-        SELECT postcode FROM location_postcode
-        WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
-              AND location_postcode.country_code = country
-        ORDER BY ST_Distance(geom, location_postcode.geometry)
-        LIMIT 1
-    LOOP
-        RETURN item.postcode;
-    END LOOP;
+    -- If the geometry is an area then only one postcode must be within
+    -- that area, otherwise consider the area as not having a postcode.
+    IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
+        SELECT min(postcode), count(*) FROM
+              (SELECT postcode FROM location_postcode
+                WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
+          INTO outcode, cnt;
+
+        IF cnt = 1 THEN
+            RETURN outcode;
+        ELSE
+            RETURN null;
+        END IF;
+    END IF;

 
 

-    RETURN null;
+    SELECT postcode FROM location_postcode
+     WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
+          AND location_postcode.country_code = country
+     ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
+    INTO outcode;
+
+    RETURN outcode;

 END;
 $$
 LANGUAGE plpgsql;
 END;
 $$
 LANGUAGE plpgsql;


@@ -619,7 +652,7 @@ BEGIN
   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
   postcode := NULL;
   IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
   postcode := NULL;
   IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN

-      postcode := upper(trim (both ' ' from in_postcode));
+      postcode := upper(trim (in_postcode));

   END IF;
 
   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
   END IF;
 
   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN


@@ -804,9 +837,9 @@ BEGIN
             RETURN NULL;
         END IF;
 
             RETURN NULL;
         END IF;
 

-        NEW.name := hstore('ref', NEW.postcode);
+        NEW.name := hstore('ref', NEW.address->'postcode');

 
 

-        SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
+        SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode')

           INTO NEW.rank_search, NEW.rank_address;
 
     ELSEIF NEW.class = 'place' THEN
           INTO NEW.rank_search, NEW.rank_address;
 
     ELSEIF NEW.class = 'place' THEN


@@ -882,6 +915,9 @@ BEGIN
       ELSE
         NEW.rank_address := 0;
       END IF;
       ELSE
         NEW.rank_address := 0;
       END IF;

+    ELSEIF NEW.class = 'leisure' and NEW.type in ('park') THEN
+      NEW.rank_search := 24;
+      NEW.rank_address := 0;

     ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
       NEW.rank_search := 18;
       NEW.rank_address := 0;
     ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
       NEW.rank_search := 18;
       NEW.rank_address := 0;


@@ -1023,8 +1059,8 @@ DECLARE
   linegeo GEOMETRY;
   splitline GEOMETRY;
   sectiongeo GEOMETRY;
   linegeo GEOMETRY;
   splitline GEOMETRY;
   sectiongeo GEOMETRY;

+  interpol_postcode TEXT;

   postcode TEXT;
   postcode TEXT;

-  seg_postcode TEXT;

 BEGIN
   -- deferred delete
   IF OLD.indexed_status = 100 THEN
 BEGIN
   -- deferred delete
   IF OLD.indexed_status = 100 THEN


@@ -1043,9 +1079,11 @@ BEGIN
                                                  NEW.address->'place',
                                                  NEW.partition, place_centroid, NEW.linegeo);
 
                                                  NEW.address->'place',
                                                  NEW.partition, place_centroid, NEW.linegeo);
 

-
-  IF NEW.address is not NULL and NEW.address ? 'postcode' THEN
-      NEW.postcode = NEW.address->'postcode';
+  IF NEW.address is not NULL AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
+    interpol_postcode := NEW.address->'postcode';
+    housenum := getorcreate_postcode_id(NEW.address->'postcode');
+  ELSE
+    interpol_postcode := NULL;

   END IF;
 
   -- if the line was newly inserted, split the line as necessary
   END IF;
 
   -- if the line was newly inserted, split the line as necessary


@@ -1058,7 +1096,6 @@ BEGIN
 
       linegeo := NEW.linegeo;
       startnumber := NULL;
 
       linegeo := NEW.linegeo;
       startnumber := NULL;

-      postcode := NEW.postcode;

 
       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 
 
       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 


@@ -1091,15 +1128,24 @@ BEGIN
               sectiongeo := ST_Reverse(sectiongeo);
             END IF;
 
               sectiongeo := ST_Reverse(sectiongeo);
             END IF;
 

-            seg_postcode := coalesce(postcode,
-                                     prevnode.address->'postcode',
-                                     nextnode.address->'postcode');
+            -- determine postcode
+            postcode := coalesce(interpol_postcode,
+                                 prevnode.address->'postcode',
+                                 nextnode.address->'postcode',
+                                 postcode);
+
+            IF postcode is NULL THEN
+                SELECT placex.postcode FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
+            END IF;
+            IF postcode is NULL THEN
+                postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
+            END IF;

 
             IF NEW.startnumber IS NULL THEN
                 NEW.startnumber := startnumber;
                 NEW.endnumber := endnumber;
                 NEW.linegeo := sectiongeo;
 
             IF NEW.startnumber IS NULL THEN
                 NEW.startnumber := startnumber;
                 NEW.endnumber := endnumber;
                 NEW.linegeo := sectiongeo;

-                NEW.postcode := seg_postcode;
+                NEW.postcode := upper(trim(postcode));

              ELSE
               insert into location_property_osmline
                      (linegeo, partition, osm_id, parent_place_id,
              ELSE
               insert into location_property_osmline
                      (linegeo, partition, osm_id, parent_place_id,


@@ -1108,7 +1154,7 @@ BEGIN
                       geometry_sector, indexed_status)
               values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
                       startnumber, endnumber, NEW.interpolationtype,
                       geometry_sector, indexed_status)
               values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
                       startnumber, endnumber, NEW.interpolationtype,

-                      NEW.address, seg_postcode,
+                      NEW.address, postcode,

                       NEW.country_code, NEW.geometry_sector, 0);
              END IF;
           END IF;
                       NEW.country_code, NEW.geometry_sector, 0);
              END IF;
           END IF;


@@ -1266,12 +1312,17 @@ BEGIN
 
       addr_street = NEW.address->'street';
       addr_place = NEW.address->'place';
 
       addr_street = NEW.address->'street';
       addr_place = NEW.address->'place';

+
+      IF NEW.address ? 'postcode' and NEW.address->'postcode' not similar to '%(,|;)%' THEN
+        i := getorcreate_postcode_id(NEW.address->'postcode');
+      END IF;

   END IF;
 
   -- Speed up searches - just use the centroid of the feature
   -- cheaper but less acurate
   place_centroid := ST_PointOnSurface(NEW.geometry);
   NEW.centroid := null;
   END IF;
 
   -- Speed up searches - just use the centroid of the feature
   -- cheaper but less acurate
   place_centroid := ST_PointOnSurface(NEW.geometry);
   NEW.centroid := null;

+  NEW.postcode := null;

   --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
 
   -- recalculate country and partition
   --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
 
   -- recalculate country and partition


@@ -1304,7 +1355,7 @@ BEGIN
                 --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i];
                 FOR linked_node_id IN SELECT place_id FROM placex
                   WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint
                 --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i];
                 FOR linked_node_id IN SELECT place_id 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 class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch')

                   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 = linked_node_id;
                   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 = linked_node_id;


@@ -1509,34 +1560,15 @@ BEGIN
       --DEBUG: RAISE WARNING 'Got parent details from search name';
 
       -- determine postcode
       --DEBUG: RAISE WARNING 'Got parent details from search name';
 
       -- determine postcode

-      IF NEW.address is not null AND NEW.address ? 'postcode' THEN
-          NEW.postcode = NEW.address->'postcode';
-      ELSE
-         SELECT postcode FROM placex WHERE place_id = parent_place_id INTO NEW.postcode;
-      END IF;
-      IF NEW.postcode is null THEN
-        NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
-      END IF;
-
-      -- Merge the postcode into the parent's address if necessary
-      IF NEW.postcode IS NOT NULL THEN
-        --DEBUG: RAISE WARNING 'Merging postcode into parent';
-        isin_tokens := '{}'::int[];
-        address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
-        IF address_street_word_id is not null
-           and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
-           isin_tokens := isin_tokens || address_street_word_id;
-        END IF;
-        address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode));
-        IF address_street_word_id is not null
-           and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
-           isin_tokens := isin_tokens || address_street_word_id;
-        END IF;
-        IF isin_tokens != '{}'::int[] THEN
-           UPDATE search_name
-              SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens
-            WHERE place_id = NEW.parent_place_id;
-        END IF;
+      IF NEW.rank_search > 4 THEN
+          IF NEW.address is not null AND NEW.address ? 'postcode' THEN
+              NEW.postcode = upper(trim(NEW.address->'postcode'));
+          ELSE
+             SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode;
+          END IF;
+          IF NEW.postcode is null THEN
+            NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
+          END IF;

       END IF;
 
       -- If there is no name it isn't searchable, don't bother to create a search record
       END IF;
 
       -- If there is no name it isn't searchable, don't bother to create a search record


@@ -1554,7 +1586,7 @@ BEGIN
       -- Just be happy with inheriting from parent road only
 
       IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
       -- Just be happy with inheriting from parent road only
 
       IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN

-        result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
+        result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);

         --DEBUG: RAISE WARNING 'Place added to location table';
       END IF;
 
         --DEBUG: RAISE WARNING 'Place added to location table';
       END IF;
 


@@ -1725,7 +1757,7 @@ BEGIN
   END IF;
 
   -- make sure all names are in the word table
   END IF;
 
   -- make sure all names are in the word table

-  IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+  IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN

     perform create_country(NEW.name, lower(NEW.country_code));
     --DEBUG: RAISE WARNING 'Country names updated';
   END IF;
     perform create_country(NEW.name, lower(NEW.country_code));
     --DEBUG: RAISE WARNING 'Country names updated';
   END IF;


@@ -1915,7 +1947,7 @@ BEGIN
 
   IF NEW.address is not null AND NEW.address ? 'postcode' 
      AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
 
   IF NEW.address is not null AND NEW.address ? 'postcode' 
      AND NEW.address->'postcode' not similar to '%(,|;)%' THEN

-    NEW.postcode := NEW.address->'postcode';
+    NEW.postcode := upper(trim(NEW.address->'postcode'));

   END IF;
 
   IF NEW.postcode is null AND NEW.rank_search > 8 THEN
   END IF;
 
   IF NEW.postcode is null AND NEW.rank_search > 8 THEN


@@ -1926,7 +1958,7 @@ BEGIN
   IF NEW.name IS NOT NULL THEN
 
     IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
   IF NEW.name IS NOT NULL THEN
 
     IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN

-      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
+      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);

       --DEBUG: RAISE WARNING 'added to location (full)';
     END IF;
 
       --DEBUG: RAISE WARNING 'added to location (full)';
     END IF;
 


@@ -2168,7 +2200,9 @@ BEGIN
 
     -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
     IF existingplacex.osm_type IS NULL OR
 
     -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
     IF existingplacex.osm_type IS NULL OR

-      (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative')
+        (existingplacex.class = 'boundary' AND
+          ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR
+          (existingplacex.type != NEW.type)))

     THEN
 
       IF existingplacex.osm_type IS NOT NULL THEN
     THEN
 
       IF existingplacex.osm_type IS NOT NULL THEN


@@ -2406,11 +2440,19 @@ BEGIN
   -- %NOAUXDATA% IF 0 THEN
   IF for_place_id IS NULL THEN
     select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
   -- %NOAUXDATA% IF 0 THEN
   IF for_place_id IS NULL THEN
     select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux

-      WHERE place_id = in_place_id 
+      WHERE place_id = in_place_id

       INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
   END IF;
   -- %NOAUXDATA% END IF;
 
       INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
   END IF;
   -- %NOAUXDATA% END IF;
 

+  -- postcode table
+  IF for_place_id IS NULL THEN
+    select parent_place_id, country_code, rank_address, postcode, 'place', 'postcode'
+      FROM location_postcode
+      WHERE place_id = in_place_id
+      INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, searchclass, searchtype;
+  END IF;
+

   IF for_place_id IS NULL THEN
     select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex 
       WHERE place_id = in_place_id and  rank_search > 27
   IF for_place_id IS NULL THEN
     select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex 
       WHERE place_id = in_place_id and  rank_search > 27


@@ -2463,13 +2505,13 @@ BEGIN
     select placex.place_id, osm_type, osm_id, name,
       CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
       CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
     select placex.place_id, osm_type, osm_id, name,
       CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
       CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,

-      admin_level, fromarea, isaddress,
+      admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,

       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       distance,country_code,postcode
       from place_addressline join placex on (address_place_id = placex.place_id) 
       where place_addressline.place_id = for_place_id 
       and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       distance,country_code,postcode
       from place_addressline join placex on (address_place_id = placex.place_id) 
       where place_addressline.place_id = for_place_id 
       and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)

-      and address_place_id != for_place_id
+      and address_place_id != for_place_id and linked_place_id is null

       and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode)
       order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
   LOOP
       and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode)
       order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
   LOOP