source: trunk/npemap.org.uk/scripts/exporter/rawExport.pl @ 624

#!/usr/bin/perl
#                                       Raw Postcode Data Exporter
#                                       --------------------------
#
# Exports the raw postcode data, including IDs.
# Doesn't export deleted postcodes, but does export ones with open
#  problem reports.
# Normally used by people wishing to check our data for errors

use strict;
use DBI;
use Geography::NationalGrid;
use Geography::NationalGrid::GB;
use Geography::NationalGrid::IE;

# Find our private perl libraries
use FindBin;
use lib "$FindBin::Bin/../../perllib";
use NPEMap::GeoConverter;

# 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 "# This is raw postcode data, and not intended for end users. See \n";
print "#  http://www.npemap.org.uk/data/ for formatted data\n";
print "#\n";
print "# <internal id>,<outward>,<inward>,<easting>,<northing>,<grid>,<WGS84 lat>,<WGS84 long>,<problem reported>\n";

# Get our data fetch query
my $source = getSource("NPE Postcode web submission", $conn);
my $query = getQuery($conn);

# Process the query
$query->execute;
while(my ($id,$outer,$inner,$e,$n,$ie_e,$ie_n,$problem) = $query->fetchrow_array) {
        # What grid are we on?
        my $grid = "osgb";
        if(!$e && $ie_e) {
                $grid = "osie";
                $e = $ie_e;
                $n = $ie_n;
        }
        
        # Generate lat+long
        my ($lat,$long) = eastingNorthingToLatLong($e,$n,$grid);

        # 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 "$id,$outer,$inner,$e,$n,$grid,$lat,$long,$problem\n";
}

# Close down
$conn->disconnect;


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

# Get a query returning 
#               id, outer, inner, easting, northing
sub getQuery($) {
        my $sql = 
                "SELECT id, outward, inward, easting, northing, ie_easting, ie_northing, reason IS NOT NULL AS problem ".
                "FROM postcodes ".
                "LEFT OUTER JOIN bad_postcodes ON (id = postcode AND NOT actioned) ".
                "WHERE source = ? AND NOT deleted ".
                "ORDER BY outward, inward ";

        my $sth = $conn->prepare($sql);
        $sth->bind_param(1, $source);
        return $sth;
}

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]; 
}