X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/2daa4ed8135ef5688ba189e6727f686d24c17816..4e792546e81844a8b4ba1026358b2115452f42ab:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 721e0b1b..3e80c2df 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -236,6 +236,65 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
+
+CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
+                                      OUT rank_search SMALLINT, OUT rank_address SMALLINT)
+AS $$
+DECLARE
+  part TEXT;
+BEGIN
+    rank_search := 30;
+    rank_address := 30;
+
+    IF country_code = 'gb' THEN
+        IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
+            rank_search := 25;
+            rank_address := 5;
+        ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
+            rank_search := 23;
+            rank_address := 5;
+        ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
+            rank_search := 21;
+            rank_address := 5;
+        END IF;
+
+    ELSEIF country_code = 'sg' THEN
+        IF postcode ~ '^([0-9]{6})$' THEN
+            rank_search := 25;
+            rank_address := 11;
+        END IF;
+
+    ELSEIF country_code = 'de' THEN
+        IF postcode ~ '^([0-9]{5})$' THEN
+            rank_search := 21;
+            rank_address := 11;
+        END IF;
+
+    ELSE
+        -- Guess at the postcode format and coverage (!)
+        IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
+            rank_search := 21;
+            rank_address := 11;
+        ELSE
+            -- Does it look splitable into and area and local code?
+            part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
+
+            IF part IS NOT NULL THEN
+                rank_search := 25;
+                rank_address := 11;
+            ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
+                rank_search := 21;
+                rank_address := 11;
+            END IF;
+        END IF;
+    END IF;
+
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+
+
 CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID
   AS $$
 DECLARE
@@ -515,36 +574,39 @@ CREATE OR REPLACE FUNCTION add_location(
     keywords INTEGER[],
     rank_search INTEGER,
     rank_address INTEGER,
+    in_postcode TEXT,
     geometry GEOMETRY
   ) 
   RETURNS BOOLEAN
   AS $$
 DECLARE
   locationid INTEGER;
-  isarea BOOLEAN;
   centroid GEOMETRY;
   diameter FLOAT;
   x BOOLEAN;
   splitGeom RECORD;
   secgeo GEOMETRY;
+  postcode TEXT;
 BEGIN
 
   IF rank_search > 25 THEN
     RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
   END IF;
 
---  RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
-
   x := deleteLocationArea(partition, place_id, rank_search);
 
-  isarea := false;
-  IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
+  -- add postcode only if it contains a single entry, i.e. ignore postcode lists
+  IF in_postcode IS NULL OR in_postcode similar to '%(,|;)%' THEN
+      postcode := NULL;
+  ELSE
+      postcode := in_postcode;
+  END IF;
 
-    isArea := true;
+  IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
     centroid := ST_Centroid(geometry);
 
     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
-      x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo);
+      x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
     END LOOP;
 
   ELSE
@@ -569,7 +631,7 @@ BEGIN
 --    RAISE WARNING 'adding % diameter %', place_id, diameter;
 
     secgeo := ST_Buffer(geometry, diameter);
-    x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);
+    x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
 
   END IF;
 
@@ -725,51 +787,8 @@ BEGIN
         NEW.postcode := NEW.address->'postcode';
         NEW.name := hstore('ref', NEW.postcode);
 
-        IF NEW.country_code = 'gb' THEN
-
-          IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
-            NEW.rank_search := 25;
-            NEW.rank_address := 5;
-          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
-            NEW.rank_search := 23;
-            NEW.rank_address := 5;
-          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
-            NEW.rank_search := 21;
-            NEW.rank_address := 5;
-          END IF;
-
-        ELSEIF NEW.country_code = 'sg' THEN
-
-          IF NEW.postcode ~ '^([0-9]{6})$' THEN
-            NEW.rank_search := 25;
-            NEW.rank_address := 11;
-          END IF;
-
-        ELSEIF NEW.country_code = 'de' THEN
-
-          IF NEW.postcode ~ '^([0-9]{5})$' THEN
-            NEW.rank_search := 21;
-            NEW.rank_address := 11;
-          END IF;
-
-        ELSE
-          -- Guess at the postcode format and coverage (!)
-          IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
-            NEW.rank_search := 21;
-            NEW.rank_address := 11;
-          ELSE
-            -- Does it look splitable into and area and local code?
-            postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
-
-            IF postcode IS NOT NULL THEN
-              NEW.rank_search := 25;
-              NEW.rank_address := 11;
-            ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN
-              NEW.rank_search := 21;
-              NEW.rank_address := 11;
-            END IF;
-          END IF;
-        END IF;
+        SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
+          INTO NEW.rank_search, NEW.rank_address;
 
     ELSEIF NEW.class = 'place' THEN
       IF NEW.type in ('continent') THEN
@@ -1094,7 +1113,42 @@ END;
 $$
 LANGUAGE plpgsql;
 
+-- Trigger for updates of location_postcode
+--
+-- Computes the parent object the postcode most likely refers to.
+-- This will be the place that determines the address displayed when
+-- searching for this postcode.
+CREATE OR REPLACE FUNCTION postcode_update() RETURNS
+TRIGGER
+  AS $$
+DECLARE
+  partition SMALLINT;
+  location RECORD;
+BEGIN
+    IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
+        RETURN NEW;
+    END IF;
+
+    NEW.indexed_date = now();
+
+    partition := get_partition(NEW.country_code);
+
+    SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
+      INTO NEW.rank_search, NEW.rank_address;
+
+    NEW.parent_place_id = 0;
+    FOR location IN
+      SELECT place_id
+        FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search, '{}'::int[])
+        WHERE NOT isguess ORDER BY rank_address DESC LIMIT 1
+    LOOP
+        NEW.parent_place_id = location.place_id;
+    END LOOP;
 
+    RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION placex_update() RETURNS
 TRIGGER
@@ -1478,7 +1532,7 @@ BEGIN
       -- 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.geometry);
+        result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
         --DEBUG: RAISE WARNING 'Place added to location table';
       END IF;
 
@@ -1850,7 +1904,7 @@ BEGIN
   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.geometry);
+      result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
       --DEBUG: RAISE WARNING 'added to location (full)';
     END IF;
 
@@ -2632,9 +2686,7 @@ BEGIN
   IF out_postcode IS NULL THEN
     SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
   END IF;
-  IF out_postcode IS NULL THEN
-    out_postcode := getNearestPostcode(out_partition, place_centroid);
-  END IF;
+  -- XXX look into postcode table
 
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
@@ -2892,7 +2944,7 @@ BEGIN
     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
       FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
-        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address > 'place'));
+        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
         AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
       END LOOP;
