add postcodes as special items in word table

diff --git a/data/words.sql b/data/words.sql
index f927d0003ae8d8756de654d374353938ba8ebcfc..b1aa6677748785d30c352050583c1a45564de7a3 100644 (file)
--- a/data/words.sql
+++ b/data/words.sql
@@ -29787,7 +29787,7 @@ st      5557484
 -- prefill word table
 
 select count(make_keywords(v)) from (select distinct svals(name) as v from place) as w where v is not null;
 -- prefill word table
 
 select count(make_keywords(v)) from (select distinct svals(name) as v from place) as w where v is not null;

-select count(make_keywords(v)) from (select distinct address->'postcode' as v from place where address ? 'postcode') as w where v is not null;
+select count(getorcreate_postcode_id(v)) from (select distinct address->'postcode' as v from place where address ? 'postcode' and address->'postcode' not similar to '%(,|;)%') as w where v is not null;

 select count(getorcreate_housenumber_id(make_standard_name(v))) from (select distinct address->'housenumber' as v from place where address ? 'housenumber') as w;
 
 -- copy the word frequencies
 select count(getorcreate_housenumber_id(make_standard_name(v))) from (select distinct address->'housenumber' as v from place where address ? 'housenumber') as w;
 
 -- copy the word frequencies

diff --git a/sql/functions.sql b/sql/functions.sql
index 1c4fbb64aeec938f52069d7c1f6ee823934466f5..b5ac15a3c30771913a1cc60ebc2afe55449e171b 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 $$


@@ -619,7 +639,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


@@ -1266,6 +1286,10 @@ 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
   END IF;
 
   -- Speed up searches - just use the centroid of the feature