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

Last change on this file since 34 was 34, checked in by Nick Burch, 15 years ago

More of the import script

  • Property svn:executable set to *
File size: 3.1 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
13import os
14
15# Database settings
16dbtype = "postgres"
17dbname = "npe_postcodes"
18dbhost = "localhost"
19dbuser = "nick"
20dbpass = ""
21
22
23# Connect to the database
24dbh = None
25if dbtype == "pgsql" or dbtype == "postgres" or dbtype == "postgresql":
26        if len(dbhost):
27                dbh = PgSQL.connect(database=dbname, host=dbhost, user=dbuser, password=dbpass)
28        else:
29                dbh = PgSQL.connect(database=dbname, user=dbuser, password=dbpass)
30else:
31        raise Exception("Unknown dbtype %s" % dbtype)
32
33
34# Check what source value FreeThePostcode data will have
35ftp_source_name = "FreeThePostcode.org Importer"
36ftp_source = None
37
38sql = "SELECT id FROM sources WHERE name = %s"
39sth = dbh.cursor()
40sth.execute(sql, ftp_source_name)
41ids = sth.fetchall()
42if len(ids) == 1:
43        for id in (ids):
44                ftp_source = id.id
45else:
46        print "Unable to find ID for source '%s' - error code %d" % (ftp_source_name, len(ids))
47
48
49# Download the latest list of postcodes if needed
50download = False
51current_file = None
52if os.path.isfile("currentlist"):
53        current_file = "currentlist"
54if os.path.isfile("/tmp/currentlist"):
55        current_file = "/tmp/currentlist"
56
57if not current_file == None:
58        print "Data found, do you wish to re-download?"
59        redownload = raw_input("")
60        if redownload == "y" or redownload == "yes":
61                download = True
62else:
63        download = True
64
65if download:
66        url = "http://www.freethepostcode.org/currentlist"
67        print "Downloading from %s" % url
68        os.system("wget -O currentlist '%s'" % url)
69        current_file = "currentlist"
70print ""
71
72
73# Read in the new list
74postcodes = []
75ftpc = open(current_file, 'r')
76for line in ftpc:
77        line = line[0:-1]
78        if line[0:1] == "#":
79                continue
80
81        parts = line.split(" ")
82        if not len(parts) == 4:
83                print "Invalid line '%s'" % line
84
85        # Turn lat+long into easting+northing
86        osll = turn_wgs84_into_osgb36(parts[0], parts[1], 0)
87        en = turn_osgb36_into_eastingnorthing(osll[0], osll[1])
88
89        pc = {}
90        pc["easting"] = en[0]
91        pc["northing"] = en[1]
92        pc["outer"] = parts[2]
93        pc["inner"] = parts[3]
94        pc["raw"] = "%s %s" % (parts[2],parts[3])
95
96        postcodes.append(pc)
97
98
99# Prompt before removing all the current ones
100sql = "SELECT COUNT(*) AS count FROM postcodes WHERE source = %s"
101sth = dbh.cursor()
102sth.execute(sql, ftp_source)
103counts = sth.fetchall()
104count = counts[0][0]
105
106print "There are currently %d entries in the database from freethepostcode.org" % count
107print "The new import contains %d entries" % len(postcodes)
108print "Are you sure you wish to remove the old entries, to add new ones?"
109confirm = raw_input("")
110
111if confirm == "y" or confirm == "yes":
112        # Good, go ahead
113        pass
114else:
115        print ""
116        raise Exception("Aborting import")
117
118
119# Delete the old entries
120sql = "DELETE FROM postcodes WHERE source = %s"
121sth = dbh.cursor()
122sth.execute(sql, ftp_source)
123
124
125# Add the latest list to the database
126
127
Note: See TracBrowser for help on using the repository browser.