Merge pull request #782 from lonvia/rework-postcodes

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index be836c9281786e779d8116b431120de72896fa4f..a9bca5423dc03d327c3cccbd6d20cb3df4e61a65 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -265,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


@@ -318,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;


@@ -824,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


@@ -902,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;


@@ -1129,7 +1145,7 @@ BEGIN
                 NEW.startnumber := startnumber;
                 NEW.endnumber := endnumber;
                 NEW.linegeo := sectiongeo;
                 NEW.startnumber := startnumber;
                 NEW.endnumber := endnumber;
                 NEW.linegeo := sectiongeo;

-                NEW.postcode := 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,


@@ -1544,13 +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 = 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);
+      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


@@ -1568,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;
 


@@ -1929,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


@@ -1940,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;