nominatim/tools/postcodes.py

   1 """
   2 Functions for importing, updating and otherwise maintaining the table
   3 of artificial postcode centroids.
   4 """
   5
   6 from nominatim.db.utils import execute_file
   7 from nominatim.db.connection import connect
   8
   9 def import_postcodes(dsn, project_dir):
  10     """ Set up the initial list of postcodes.
  11     """
  12
  13     with connect(dsn) as conn:
  14         conn.drop_table('gb_postcode')
  15         conn.drop_table('us_postcode')
  16
  17         with conn.cursor() as cur:
  18             cur.execute("""CREATE TABLE gb_postcode (
  19                             id integer,
  20                             postcode character varying(9),
  21                             geometry GEOMETRY(Point, 4326))""")
  22
  23         with conn.cursor() as cur:
  24             cur.execute("""CREATE TABLE us_postcode (
  25                             postcode text,
  26                             x double precision,
  27                             y double precision)""")
  28         conn.commit()
  29
  30         gb_postcodes = project_dir / 'gb_postcode_data.sql.gz'
  31         if gb_postcodes.is_file():
  32             execute_file(dsn, gb_postcodes)
  33
  34         us_postcodes = project_dir / 'us_postcode_data.sql.gz'
  35         if us_postcodes.is_file():
  36             execute_file(dsn, us_postcodes)
  37
  38         with conn.cursor() as cur:
  39             cur.execute("TRUNCATE location_postcode")
  40             cur.execute("""
  41                 INSERT INTO location_postcode
  42                  (place_id, indexed_status, country_code, postcode, geometry)
  43                 SELECT nextval('seq_place'), 1, country_code,
  44                        upper(trim (both ' ' from address->'postcode')) as pc,
  45                        ST_Centroid(ST_Collect(ST_Centroid(geometry)))
  46                   FROM placex
  47                  WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'
  48                        AND geometry IS NOT null
  49                  GROUP BY country_code, pc
  50             """)
  51
  52             cur.execute("""
  53                 INSERT INTO location_postcode
  54                  (place_id, indexed_status, country_code, postcode, geometry)
  55                 SELECT nextval('seq_place'), 1, 'us', postcode,
  56                        ST_SetSRID(ST_Point(x,y),4326)
  57                   FROM us_postcode WHERE postcode NOT IN
  58                         (SELECT postcode FROM location_postcode
  59                           WHERE country_code = 'us')
  60             """)
  61
  62             cur.execute("""
  63                 INSERT INTO location_postcode
  64                  (place_id, indexed_status, country_code, postcode, geometry)
  65                 SELECT nextval('seq_place'), 1, 'gb', postcode, geometry
  66                   FROM gb_postcode WHERE postcode NOT IN
  67                            (SELECT postcode FROM location_postcode
  68                              WHERE country_code = 'gb')
  69             """)
  70
  71             cur.execute("""
  72                     DELETE FROM word WHERE class='place' and type='postcode'
  73                     and word NOT IN (SELECT postcode FROM location_postcode)
  74             """)
  75
  76             cur.execute("""
  77                 SELECT count(getorcreate_postcode_id(v)) FROM
  78                 (SELECT distinct(postcode) as v FROM location_postcode) p
  79             """)
  80         conn.commit()