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

#!/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} || "npemaps";
my $dbpass = $ENV{DBPASS} || "npemaps";

# 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";

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

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

sub getQuery($$) {
        my ($mode,$conn) = @_;

        if($mode == "-gpsonly") {
                my $source = getSource("FreeThePostcode.org Importer", $conn);

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

                my $sth = $conn->prepare($sql);
                $sth->bind_param(1, $source);
                return $sth;
        } elsif($mode == "-npeonly") {
                my $source = getSource("NPE Postcode web submission", $conn);
        } elsif($mode == "-outer") {
        } elsif($mode == "-outer1") {
        } 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;
        while(my @row = $sth->fetchrow_array) {
                if($s) {
                        die("Should only find one source with name '$source', got more than one!");
                }
                $s = $row[0];
        }
        unless($s) {
                die("Can't find id for source with name '$source'");
        }

        return $s;      
}