Upgrade Travis-CI from Ubuntu 18 to 20

[nominatim.git] / sql / update-postcodes.sql

diff --git a/sql/update-postcodes.sql b/sql/update-postcodes.sql
index a90abe25471ce5d62c12b8e4cd485e75632e1b3a..d59df15b67565cc4061aabba4a34d925f6e8111a 100644 (file)
--- a/sql/update-postcodes.sql
+++ b/sql/update-postcodes.sql
@@ -6,7 +6,7 @@ SELECT country_code,
        ST_Centroid(ST_Collect(ST_Centroid(geometry))) as centroid
   FROM placex
  WHERE address ? 'postcode'
-       AND address->'postcode' NOT SIMILAR TO '%(,|;)%'
+       AND address->'postcode' NOT SIMILAR TO '%(,|;|:)%'
        AND geometry IS NOT null
 GROUP BY country_code, pc;
 
@@ -48,5 +48,11 @@ INSERT INTO location_postcode
   SELECT nextval('seq_place'), 1, country_code, pc, centroid
     FROM tmp_new_postcode_locations new;
 
+-- Remove unused word entries
+DELETE FROM word
+    WHERE class = 'place' AND type = 'postcode'
+          AND NOT EXISTS (SELECT 0 FROM location_postcode p
+                          WHERE p.postcode = word.word);
+
 -- Finally index the newly inserted postcodes
 UPDATE location_postcode SET indexed_status = 0 WHERE indexed_status > 0;