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

#!/usr/bin/python
#               Importer from FreeThePostcode.org
#               ---------------------------------
#
# Imports FreeThePostcode data into the current schema, removing any
#  existing FreeThePostcode data.
# 
# You will need to tweak this script with your database settings for it
#  to work.

from pyPgSQL import PgSQL
from geo_helper import turn_wgs84_into_osgb36, turn_osgb36_into_eastingnorthing
import os

# Database settings
dbtype = "postgres"
dbname = "npemaps"
dbhost = "localhost"
dbuser = "npemaps"
dbpass = "npemaps"


# Connect to the database
dbh = None
if dbtype == "pgsql" or dbtype == "postgres" or dbtype == "postgresql":
        if len(dbhost):
                dbh = PgSQL.connect(database=dbname, host=dbhost, user=dbuser, password=dbpass)
        else:
                dbh = PgSQL.connect(database=dbname, user=dbuser, password=dbpass)
else:
        raise Exception("Unknown dbtype %s" % dbtype)


# Check what source value FreeThePostcode data will have
ftp_source_name = "FreeThePostcode.org Importer"
ftp_source = None

sql = "SELECT id FROM sources WHERE name = %s"
sth = dbh.cursor()
sth.execute(sql, ftp_source_name)
ids = sth.fetchall()
sth.close()
if len(ids) == 1:
        for id in (ids):
                ftp_source = id.id
else:
        print "Unable to find ID for source '%s' - error code %d" % (ftp_source_name, len(ids))


# Download the latest list of postcodes if needed
download = False
current_file = None
if os.path.isfile("currentlist"):
        current_file = "currentlist"
if os.path.isfile("/tmp/currentlist"):
        current_file = "/tmp/currentlist"

if not current_file == None:
        print "Data found, do you wish to re-download?"
        redownload = raw_input("")
        if redownload == "y" or redownload == "yes":
                download = True
else:
        download = True

if download:
        url = "http://www.freethepostcode.org/currentlist"
        print "Downloading from %s" % url
        os.system("wget -O currentlist '%s'" % url)
        current_file = "currentlist"
print ""


# Read in the new list
postcodes = []
ftpc = open(current_file, 'r')
for line in ftpc:
        line = line[0:-1]
        if line[0:1] == "#":
                continue

        parts = line.split(" ")
        if not len(parts) == 4:
                print "Invalid line '%s'" % line

        # Turn lat+long into easting+northing
        osll = turn_wgs84_into_osgb36(parts[0], parts[1], 0)
        en = turn_osgb36_into_eastingnorthing(osll[0], osll[1])

        pc = {}
        pc["easting"] = en[0]
        pc["northing"] = en[1]
        pc["outer"] = parts[2]
        pc["inner"] = parts[3]
        pc["raw"] = "%s %s" % (parts[2],parts[3])

        postcodes.append(pc)


# Prompt before removing all the current ones
sql = "SELECT COUNT(*) AS count FROM postcodes WHERE source = %s"
sth = dbh.cursor()
sth.execute(sql, ftp_source)
counts = sth.fetchall()
count = counts[0][0]
sth.close()

print "There are currently %d entries in the database from freethepostcode.org" % count
print "The new import contains %d entries" % len(postcodes)
print "Are you sure you wish to remove the old entries, to add new ones?"
confirm = raw_input("")
print ""

if confirm == "y" or confirm == "yes":
        # Good, go ahead
        pass
else:
        print ""
        raise Exception("Aborting import")


# Delete the old entries
sql = "DELETE FROM postcodes WHERE source = %s"
sth = dbh.cursor()
sth.execute(sql, ftp_source)
print "Deleted %d entries" % sth.rowcount
sth.close()
dbh.commit()


# Add the latest list to the database
sql = "INSERT INTO postcodes (outward, inward, raw_postcode, easting, northing, source) VALUES (%s, %s, %s, %s, %s, %s)"
sth = dbh.cursor()
worked = 0
for postcode in postcodes:
        sth.execute(sql, (postcode["outer"], postcode["inner"], postcode["raw"], postcode["easting"], postcode["northing"], ftp_source))
        worked = worked + sth.rowcount
print "Added %d entries" % worked


# All done
sth.close()
dbh.commit()
dbh.close()