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

#!/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, \
                                                turn_wgs84_into_osie36, turn_osie36_into_eastingnorthing
import os

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


# 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))


# See if our delete reason exists, and if no, add it
delete_reason = None
reason_text = "Gone from FTP"
while delete_reason == None:
        sql = "SELECT id FROM delete_reasons WHERE reason = %s"
        sth = dbh.cursor()
        sth.execute(sql, reason_text)
        ids = sth.fetchall()
        sth.close()
        if len(ids) == 1:
                for id in (ids):
                        delete_reason = id.id
        else:
                sth = dbh.cursor()
                sth.execute("INSERT INTO delete_reasons (reason) VALUES (%s)", reason_text)
                sth.close()


# 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

        pc = {}
        pc["outer"] = parts[2]
        pc["inner"] = parts[3]
        pc["raw"] = "%s %s" % (parts[2],parts[3])
        pc["raw_outer"] = parts[2]
        pc["raw_inner"] = parts[3]

        pc["easting"] = None
        pc["northing"] = None
        pc["ie_easting"] = None
        pc["ie_northing"] = None

        # Turn lat+long into easting+northing
        # All NI postcodes are BT
        if pc["outer"][0:2] == 'BT':
                osll = turn_wgs84_into_osie36(parts[0], parts[1], 0)
                en = turn_osie36_into_eastingnorthing(osll[0], osll[1])
                pc["ie_easting"] = en[0]
                pc["ie_northing"] = en[1]
        else:
                osll = turn_wgs84_into_osgb36(parts[0], parts[1], 0)
                en = turn_osgb36_into_eastingnorthing(osll[0], osll[1])
                pc["easting"] = en[0]
                pc["northing"] = en[1]

        postcodes.append(pc)


# Grab all of the current ones
sql = "SELECT id, outward, inward, deleted FROM postcodes WHERE source = %s AND NOT deleted"
sth = dbh.cursor()
sth.execute(sql, ftp_source)

ftps = {}
while 1:
        row = sth.fetchone()
        if row == None:
                break
        id, outward, inward, deleted = row
        ftps["%s %s" % (outward, inward)] = \
                                { 'outward':outward, 'inward':inward, 'id':id, 'deleted':deleted, 'done':False }
sth.close()
count = len(ftps.keys())

print "There are currently %d entries in the database from freethepostcode.org" % count
print "The new import contains %d entries" % len(postcodes)

# Only prompt if it's a big difference
if count == 0 or len(postcodes) == 0 or len(postcodes) < count or (len(postcodes)-count) > 50:
        print "Are you sure you wish to run an import?"
        confirm = raw_input("")
        print ""

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


# Add the latest list to the database, tweaking if already there
add_sql = "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)"
upd_sql = "UPDATE postcodes SET outward=%s, inward=%s, raw_postcode_outward=%s, raw_postcode_inward=%s, easting=%s, northing=%s, ie_easting=%s, ie_northing=%s WHERE id=%s"
del_sql = "UPDATE postcodes SET deleted='t', delete_reason=%s WHERE id=%s"
sth = dbh.cursor()
worked = 0

# Add the postcodes
for postcode in postcodes:
        pc = "%s %s" % (postcode["outer"], postcode["inner"])
        print "Processing %s" % pc

        if ftps.has_key(pc):
                # Update existing one
                ftps[pc]['done'] = True
                sth.execute(upd_sql, (postcode["outer"], postcode["inner"], postcode["raw_outer"], postcode["raw_inner"], postcode["easting"], postcode["northing"], postcode["ie_easting"], postcode["ie_northing"], ftps[pc]['id']))
                print "\tupdated record at %d" % ftps[pc]['id']
        else:
                # New record, add
                sth.execute(add_sql, (postcode["outer"], postcode["inner"], postcode["raw_outer"], postcode["raw_inner"], postcode["easting"], postcode["northing"], postcode["ie_easting"], postcode["ie_northing"], ftp_source))
                print "\tadded new postcode"
        worked = worked + 1

print "Processed %d entries" % worked


# Find ones that have gone
print "\nDeleting any postcodes no longer in FTP:"
for gone_pc in [ pc for pc in ftps.keys() if not ftps[pc]['done'] ]:
        print "\tflagging as deleted old FTP postcode %s" % gone_pc
        sth.execute(del_sql, (delete_reason, ftps[gone_pc]['id']))

# All done
print "\nAll Done"
sth.close()
dbh.commit()
dbh.close()