add indexing of artificial postcodes

diff --git a/sql/functions.sql b/sql/functions.sql
index 1cda2e98fe7a60a321f295991aff922c7935e2de..5e16a175007f85345b5dd0838b6ff54e2730e6e7 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -236,6 +236,65 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 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
 CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID
   AS $$
 DECLARE


@@ -725,51 +784,8 @@ BEGIN
         NEW.postcode := NEW.address->'postcode';
         NEW.name := hstore('ref', NEW.postcode);
 
         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
 
     ELSEIF NEW.class = 'place' THEN
       IF NEW.type in ('continent') THEN


@@ -1094,7 +1110,42 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 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
 
 CREATE OR REPLACE FUNCTION placex_update() RETURNS
 TRIGGER

diff --git a/sql/indices.src.sql b/sql/indices.src.sql
index bbd5a76aeafa3cc719a149be9a8b05e5ed65ed51..9de2c97f6ba299560bc0cb1f9096e1e58b217a45 100644 (file)
--- a/sql/indices.src.sql
+++ b/sql/indices.src.sql
@@ -26,3 +26,7 @@ CREATE UNIQUE INDEX idx_place_osm_unique on place using btree(osm_id,osm_type,cl
 
 
 CREATE INDEX idx_gb_postcode_postcode ON gb_postcode USING BTREE (postcode) {ts:search-index};
 
 
 CREATE INDEX idx_gb_postcode_postcode ON gb_postcode USING BTREE (postcode) {ts:search-index};

+
+CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) {ts:search-index};
+CREATE INDEX idx_postcode_parent_id ON location_postcode USING BTREE (parent_place_id) {ts:search-index} where parent_place_id IS NOT NULL;
+CREATE INDEX idx_postcode_postcode ON location_postcode USING BTREE (postcode, country_code) {ts:search-index};

diff --git a/sql/tables.sql b/sql/tables.sql
index 07b0ada51b461576ec71c2f13322fa59dd2a8766..334fcbc0f8ebe3b5ae160574e6204de4996bf3ef 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -197,10 +197,12 @@ CREATE TRIGGER place_before_delete BEFORE DELETE ON place
 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
     FOR EACH ROW EXECUTE PROCEDURE place_insert();
 
 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
     FOR EACH ROW EXECUTE PROCEDURE place_insert();
 

+-- Table for synthetic postcodes.

 DROP TABLE IF EXISTS location_postcode;
 CREATE TABLE location_postcode (
   place_id BIGINT,
   parent_place_id BIGINT,
 DROP TABLE IF EXISTS location_postcode;
 CREATE TABLE location_postcode (
   place_id BIGINT,
   parent_place_id BIGINT,

+  rank_search SMALLINT,

   rank_address SMALLINT,
   indexed_status SMALLINT,
   indexed_date TIMESTAMP,
   rank_address SMALLINT,
   indexed_status SMALLINT,
   indexed_date TIMESTAMP,


@@ -208,6 +210,10 @@ CREATE TABLE location_postcode (
   postcode TEXT,
   geometry GEOMETRY(Geometry, 4326)
   );
   postcode TEXT,
   geometry GEOMETRY(Geometry, 4326)
   );

+CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index};
+
+CREATE TRIGGER location_postcode_before_update BEFORE UPDATE ON location_postcode
+    FOR EACH ROW EXECUTE PROCEDURE postcode_update();

 
 DROP TABLE IF EXISTS import_polygon_error;
 CREATE TABLE import_polygon_error (
 
 DROP TABLE IF EXISTS import_polygon_error;
 CREATE TABLE import_polygon_error (

diff --git a/utils/setup.php b/utils/setup.php
index 54ddf0829e3133c0e1d09d35edf2207a8efa831a..a9597da898f09d7fa15f60063db3da8d0a9f9e53 100755 (executable)
--- a/utils/setup.php
+++ b/utils/setup.php
@@ -500,7 +500,7 @@ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all']) {
     $sSQL  = "INSERT INTO location_postcode";
     $sSQL .= " (place_id, indexed_status, country_code, postcode, geometry) ";
     $sSQL .= "SELECT nextval('seq_place'), 1, country_code,";
     $sSQL  = "INSERT INTO location_postcode";
     $sSQL .= " (place_id, indexed_status, country_code, postcode, geometry) ";
     $sSQL .= "SELECT nextval('seq_place'), 1, country_code,";

-    $sSQL .= "       lower(trim (both ' ' from address->'postcode')) as pc,";
+    $sSQL .= "       upper(trim (both ' ' from address->'postcode')) as pc,";

     $sSQL .= "       ST_Centroid(ST_Collect(ST_Centroid(geometry)))";
     $sSQL .= "  FROM placex";
     $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'";
     $sSQL .= "       ST_Centroid(ST_Collect(ST_Centroid(geometry)))";
     $sSQL .= "  FROM placex";
     $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'";


@@ -523,6 +523,10 @@ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all']) {
 
         if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
     }
 
         if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
     }

+
+    echo "Indexing postcodes....\n";
+    $sSQL = 'UPDATE location_postcode SET indexed_status = 0';
+    if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));

 }
 
 if ($aCMDResult['osmosis-init']) {
 }
 
 if ($aCMDResult['osmosis-init']) {