source: trunk/npemap.org.uk/scripts/freethepostcode.org-importer/importer.py @ 508

Last change on this file since 508 was 508, checked in by Nick Burch, 13 years ago

Support IE stuff

  • Property svn:executable set to *
File size: 4.4 KB
Line 
1#!/usr/bin/python
2#               Importer from FreeThePostcode.org
3#               ---------------------------------
4#
5# Imports FreeThePostcode data into the current schema, removing any
6#  existing FreeThePostcode data.
7#
8# You will need to tweak this script with your database settings for it
9#  to work.
10
11from pyPgSQL import PgSQL
12from geo_helper import turn_wgs84_into_osgb36, turn_osgb36_into_eastingnorthing, \
13                                                turn_wgs84_into_osie36, turn_osie36_into_eastingnorthing
14import os
15
16# Database settings
17dbtype = "postgres"
18dbname = "npemaps"
19dbhost = "localhost"
20dbuser = "npemaps"
21dbpass = ""
22
23
24# Connect to the database
25dbh = None
26if dbtype == "pgsql" or dbtype == "postgres" or dbtype == "postgresql":
27        if len(dbhost):
28                dbh = PgSQL.connect(database=dbname, host=dbhost, user=dbuser, password=dbpass)
29        else:
30                dbh = PgSQL.connect(database=dbname, user=dbuser, password=dbpass)
31else:
32        raise Exception("Unknown dbtype %s" % dbtype)
33
34
35# Check what source value FreeThePostcode data will have
36ftp_source_name = "FreeThePostcode.org Importer"
37ftp_source = None
38
39sql = "SELECT id FROM sources WHERE name = %s"
40sth = dbh.cursor()
41sth.execute(sql, ftp_source_name)
42ids = sth.fetchall()
43sth.close()
44if len(ids) == 1:
45        for id in (ids):
46                ftp_source = id.id
47else:
48        print "Unable to find ID for source '%s' - error code %d" % (ftp_source_name, len(ids))
49
50
51# Download the latest list of postcodes if needed
52download = False
53current_file = None
54if os.path.isfile("currentlist"):
55        current_file = "currentlist"
56if os.path.isfile("/tmp/currentlist"):
57        current_file = "/tmp/currentlist"
58
59if not current_file == None:
60        print "Data found, do you wish to re-download?"
61        redownload = raw_input("")
62        if redownload == "y" or redownload == "yes":
63                download = True
64else:
65        download = True
66
67if download:
68        url = "http://www.freethepostcode.org/currentlist"
69        print "Downloading from %s" % url
70        os.system("wget -O currentlist '%s'" % url)
71        current_file = "currentlist"
72print ""
73
74
75# Read in the new list
76postcodes = []
77ftpc = open(current_file, 'r')
78for line in ftpc:
79        line = line[0:-1]
80        if line[0:1] == "#":
81                continue
82
83        parts = line.split(" ")
84        if not len(parts) == 4:
85                print "Invalid line '%s'" % line
86
87        pc = {}
88        pc["outer"] = parts[2]
89        pc["inner"] = parts[3]
90        pc["raw"] = "%s %s" % (parts[2],parts[3])
91        pc["raw_outer"] = parts[2]
92        pc["raw_inner"] = parts[3]
93
94        pc["easting"] = None
95        pc["northing"] = None
96        pc["ie_easting"] = None
97        pc["ie_northing"] = None
98
99        # Turn lat+long into easting+northing
100        # All NI postcodes are BT
101        if pc["outer"][0:2] == 'BT':
102                osll = turn_wgs84_into_osie36(parts[0], parts[1], 0)
103                en = turn_osie36_into_eastingnorthing(osll[0], osll[1])
104                pc["ie_easting"] = en[0]
105                pc["ie_northing"] = en[1]
106        else:
107                osll = turn_wgs84_into_osgb36(parts[0], parts[1], 0)
108                en = turn_osgb36_into_eastingnorthing(osll[0], osll[1])
109                pc["easting"] = en[0]
110                pc["northing"] = en[1]
111
112        postcodes.append(pc)
113
114
115# Prompt before removing all the current ones
116sql = "SELECT COUNT(*) AS count FROM postcodes WHERE source = %s"
117sth = dbh.cursor()
118sth.execute(sql, ftp_source)
119counts = sth.fetchall()
120count = counts[0][0]
121sth.close()
122
123print "There are currently %d entries in the database from freethepostcode.org" % count
124print "The new import contains %d entries" % len(postcodes)
125
126# Only prompt if it's a big difference
127if count == 0 or len(postcodes) == 0 or len(postcodes) < count or (len(postcodes)-count) > 50:
128        print "Are you sure you wish to remove the old entries, to add new ones?"
129        confirm = raw_input("")
130        print ""
131
132        if confirm == "y" or confirm == "yes":
133                # Good, go ahead
134                pass
135        else:
136                print ""
137                raise Exception("Aborting import")
138
139
140# Delete the old entries
141sql = "DELETE FROM postcodes WHERE source = %s"
142sth = dbh.cursor()
143sth.execute(sql, ftp_source)
144print "Deleted %d entries" % sth.rowcount
145sth.close()
146dbh.commit()
147
148
149# Add the latest list to the database
150sql = "INSERT INTO postcodes (outward, inward, raw_postcode_outward, raw_postcode_inward, easting, northing, ie_easting, ie_northing, source) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
151sth = dbh.cursor()
152worked = 0
153
154# Add the postcodes
155for postcode in postcodes:
156        sth.execute(sql, (postcode["outer"], postcode["inner"], postcode["raw_outer"], postcode["raw_inner"], postcode["easting"], postcode["northing"], postcode["ie_easting"], postcode["ie_northing"], ftp_source))
157        worked = worked + sth.rowcount
158
159print "Added %d entries" % worked
160
161
162# All done
163sth.close()
164dbh.commit()
165dbh.close()
Note: See TracBrowser for help on using the repository browser.