source: trunk/npemap.org.uk/scripts/exporter/export.pl @ 181

#!/usr/bin/perl
#                                       Postcode Data Exporter
#                                       ----------------------
#
# Exports various parts of the data, in various formats
# Normally these are of the form:
#   <postcode|part postcode>    <easting>       <northing>      <lat>   <long>
#
# Supported outputs are:
#   -gpsonly
#       Only export GPS sourced postcodes, averaged if several for a postcode
#   -outer
#       Only export the outer parts of the postcodes, averaged over all
#   -outer1
#       Only export the outer part + 1st inner digit, averaged over all
#   -npeonly
#       Only export NPE sourced postcodes, averaged if several for a postcode

use strict;
use DBI;
use Geo::HelmertTransform;
use Geography::NationalGrid;
use Geography::NationalGrid::GB;

my $mode = shift;
unless($mode) {
        die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly>\n");
}

# What to connect with
my $dbname = $ENV{DBNAME} || "npemap";
my $dbhost = $ENV{DBHOST} || "";
my $dbuser = $ENV{DBUSER} || "npemap";
my $dbpass = $ENV{DBPASS} || "";

# Connect to the database
my $dsn = "dbi:Pg:dbname=$dbname";
$dsn .= ";host=$dbhost" if $dbhost;

my $conn = DBI->connect($dsn, $dbuser, $dbpass,
                                        { PrintError => 0, RaiseError => 1, AutoCommit => 1 } );

# Print out our header
print "# Generated on ".gmtime()."\n";
print "#\n";
print "# This information is public domain, and comes from http://www.npemap.org.uk/\n";
print "#\n";
print "# <outward> <inward> <easting> <northing> <WGS84 lat> <WGS84 long>\n";

# Get our data fetch query
my $query = getQuery($mode,$conn);

# Process the query
$query->execute;
while(my ($outer,$inner,$e,$n) = $query->fetchrow_array) {
        # Generate lat+long
        my ($lat,$long) = makeLatLong($e,$n);

        # Make e+n look nicer
        $e =~ s/^(\d+)\.\d+$/$1/;
        $n =~ s/^(\d+)\.\d+$/$1/;

        # Make lat+long look nicer
        $lat = sprintf("%0.6f", $lat);
        $long = sprintf("%0.6f", $long);

        print "$outer $inner $e $n $lat $long\n";
}

# Close down
$conn->disconnect;


############################################################################

sub makeLatLong {
        my ($e,$n) = @_;

        # Turn e+n into OSGB lat+long
        my $point = Geography::NationalGrid::GB->new( Easting=>$e, Northing=>$n );
        my $oslat = $point->latitude;
        my $oslong = $point->longitude;

        # Turn OSGB lat+long into WGS84 lat+long
        my $osgb_helper = Geo::HelmertTransform::datum("Airy1830");
        my $wgs84_helper = Geo::HelmertTransform::datum('WGS84');

        my ($lat,$long,$h) = 
                Geo::HelmertTransform::convert_datum($osgb_helper, $wgs84_helper, $oslat, $oslong, 0);

        return ($lat,$long);
}

############################################################################

# Get a query returning 
#               outer, inner, easting, northing
# for the given mode
sub getQuery($$) {
        my ($mode,$conn) = @_;

        if($mode eq "-gpsonly" || $mode eq "-npeonly") {
                my $source;
                if($mode eq "-gpsonly") {
                        $source = getSource("FreeThePostcode.org Importer", $conn);
                } else {
                        $source = getSource("NPE Postcode web submission", $conn);
                }

                my $sql = 
                        "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
                        "FROM postcodes   ".
                        "WHERE source = ? AND NOT deleted ".
                        "GROUP BY outward, inward ".
                        "ORDER BY outward, inward ";

                my $sth = $conn->prepare($sql);
                $sth->bind_param(1, $source);
                return $sth;
        } elsif($mode eq "-outer") {
                my $sql = 
                        "SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
                        "FROM postcodes WHERE NOT deleted ".
                        "GROUP BY outward ".
                        "ORDER BY outward ";

                my $sth = $conn->prepare($sql);
                return $sth;
        } elsif($mode eq "-outer1") {
                my $sql = 
                        "SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
                        "FROM postcodes WHERE NOT deleted ".
                        "GROUP BY outward, substr(inward,1,1) ".
                        "ORDER BY outward, inward";

                my $sth = $conn->prepare($sql);
                return $sth;
        } else {
                die("Invalid mode '$mode'");
        }
}

sub getSource($$) {
        my ($source,$conn) = @_;

        my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
        $sth->bind_param(1, $source);
        $sth->execute;

        my $s = undef;
        my @row = $sth->fetchrow_array;
        if($sth->fetchrow_array) {
                die("Should only find one source with name '$source', got more than one!");
        }

        unless(@row) {
                die("Can't find id for source with name '$source'");
        }

        return $row[0]; 
}