lib-sql/update-postcodes.sql

   1 -- Create a temporary table with postcodes from placex.
   2
   3 CREATE TEMP TABLE tmp_new_postcode_locations AS
   4 SELECT country_code,
   5        upper(trim (both ' ' from address->'postcode')) as pc,
   6        ST_Centroid(ST_Collect(ST_Centroid(geometry))) as centroid
   7   FROM placex
   8  WHERE address ? 'postcode'
   9        AND address->'postcode' NOT SIMILAR TO '%(,|;|:)%'
  10        AND geometry IS NOT null
  11 GROUP BY country_code, pc;
  12
  13 CREATE INDEX idx_tmp_new_postcode_locations
  14           ON tmp_new_postcode_locations (pc, country_code);
  15
  16 -- add extra US postcodes
  17 INSERT INTO tmp_new_postcode_locations (country_code, pc, centroid)
  18     SELECT 'us', postcode, ST_SetSRID(ST_Point(x,y),4326)
  19       FROM us_postcode u
  20       WHERE NOT EXISTS (SELECT 0 FROM tmp_new_postcode_locations new
  21                          WHERE new.country_code = 'us' AND new.pc = u.postcode);
  22 -- add extra UK postcodes
  23 INSERT INTO tmp_new_postcode_locations (country_code, pc, centroid)
  24     SELECT 'gb', postcode, geometry FROM gb_postcode g
  25      WHERE NOT EXISTS (SELECT 0 FROM tmp_new_postcode_locations new
  26                              WHERE new.country_code = 'gb' and new.pc = g.postcode);
  27
  28 -- Remove all postcodes that are no longer valid
  29 DELETE FROM location_postcode old
  30   WHERE NOT EXISTS(SELECT 0 FROM tmp_new_postcode_locations new
  31                    WHERE old.postcode = new.pc
  32                          AND old.country_code = new.country_code);
  33
  34 -- Update geometries where necessary
  35 UPDATE location_postcode old SET geometry = new.centroid, indexed_status = 1
  36   FROM tmp_new_postcode_locations new
  37  WHERE old.postcode = new.pc AND old.country_code = new.country_code
  38        AND ST_AsText(old.geometry) != ST_AsText(new.centroid);
  39
  40 -- Remove all postcodes that already exist from the temporary table
  41 DELETE FROM tmp_new_postcode_locations new
  42     WHERE EXISTS(SELECT 0 FROM location_postcode old
  43                  WHERE old.postcode = new.pc AND old.country_code = new.country_code);
  44
  45 -- Add newly added postcode
  46 INSERT INTO location_postcode
  47   (place_id, indexed_status, country_code, postcode, geometry)
  48   SELECT nextval('seq_place'), 1, country_code, pc, centroid
  49     FROM tmp_new_postcode_locations new;
  50
  51 -- Remove unused word entries
  52 DELETE FROM word
  53     WHERE class = 'place' AND type = 'postcode'
  54           AND NOT EXISTS (SELECT 0 FROM location_postcode p
  55                           WHERE p.postcode = word.word);
  56
  57 -- Finally index the newly inserted postcodes
  58 UPDATE location_postcode SET indexed_status = 0 WHERE indexed_status > 0;