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

Last change on this file since 460 was 460, checked in by Nick Burch, 14 years ago

Shift Geo::HelmertTransform? from scripts/exporter into perllib

  • Property svn:executable set to *
File size: 4.3 KB
Line 
1#!/usr/bin/perl
2#                                       Postcode Data Exporter
3#                                       ----------------------
4#
5# Exports various parts of the data, in various formats
6# Normally these are of the form:
7#   <postcode|part postcode>    <easting>       <northing>      <lat>   <long>
8#
9# Supported outputs are:
10#   -gpsonly
11#       Only export GPS sourced postcodes, averaged if several for a postcode
12#   -outer
13#       Only export the outer parts of the postcodes, averaged over all
14#   -outer1
15#       Only export the outer part + 1st inner digit, averaged over all
16#   -npeonly
17#       Only export NPE sourced postcodes, averaged if several for a postcode
18
19use strict;
20use DBI;
21use Geography::NationalGrid;
22use Geography::NationalGrid::GB;
23
24# Find our private perl libraries
25use FindBin;
26use lib "$FindBin::Bin/../../perllib";
27use Geo::HelmertTransform;
28
29my $mode = shift;
30unless($mode) {
31        die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly>\n");
32}
33
34# What to connect with
35my $dbname = $ENV{DBNAME} || "npemap";
36my $dbhost = $ENV{DBHOST} || "";
37my $dbuser = $ENV{DBUSER} || "npemap";
38my $dbpass = $ENV{DBPASS} || "";
39
40# Connect to the database
41my $dsn = "dbi:Pg:dbname=$dbname";
42$dsn .= ";host=$dbhost" if $dbhost;
43
44my $conn = DBI->connect($dsn, $dbuser, $dbpass,
45                                        { PrintError => 0, RaiseError => 1, AutoCommit => 1 } );
46
47# Print out our header
48print "# Generated on ".gmtime()."\n";
49print "#\n";
50print "# This information is public domain, and comes from http://www.npemap.org.uk/\n";
51print "#\n";
52print "# <outward>,<inward>,<easting>,<northing>,<WGS84 lat>,<WGS84 long>\n";
53
54# Get our data fetch query
55my $query = getQuery($mode,$conn);
56
57# Process the query
58$query->execute;
59while(my ($outer,$inner,$e,$n) = $query->fetchrow_array) {
60        # Generate lat+long
61        my ($lat,$long) = makeLatLong($e,$n);
62
63        # Make e+n look nicer
64        $e =~ s/^(\d+)\.\d+$/$1/;
65        $n =~ s/^(\d+)\.\d+$/$1/;
66
67        # Make lat+long look nicer
68        $lat = sprintf("%0.6f", $lat);
69        $long = sprintf("%0.6f", $long);
70
71        print "$outer,$inner,$e,$n,$lat,$long\n";
72}
73
74# Close down
75$conn->disconnect;
76
77
78############################################################################
79
80sub makeLatLong {
81        my ($e,$n) = @_;
82
83        # Turn e+n into OSGB lat+long
84        my $point = Geography::NationalGrid::GB->new( Easting=>$e, Northing=>$n );
85        my $oslat = $point->latitude;
86        my $oslong = $point->longitude;
87
88        # Turn OSGB lat+long into WGS84 lat+long
89        my $osgb_helper = Geo::HelmertTransform::datum("Airy1830");
90        my $wgs84_helper = Geo::HelmertTransform::datum('WGS84');
91
92        my ($lat,$long,$h) = 
93                Geo::HelmertTransform::convert_datum($osgb_helper, $wgs84_helper, $oslat, $oslong, 0);
94
95        return ($lat,$long);
96}
97
98############################################################################
99
100# Get a query returning
101#               outer, inner, easting, northing
102# for the given mode
103sub getQuery($$) {
104        my ($mode,$conn) = @_;
105
106        if($mode eq "-gpsonly" || $mode eq "-npeonly") {
107                my $source;
108                if($mode eq "-gpsonly") {
109                        $source = getSource("FreeThePostcode.org Importer", $conn);
110                } else {
111                        $source = getSource("NPE Postcode web submission", $conn);
112                }
113
114                my $sql = 
115                        "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
116                        "FROM postcodes   ".
117                        "WHERE source = ? AND NOT deleted ".
118                        "GROUP BY outward, inward ".
119                        "ORDER BY outward, inward ";
120
121                my $sth = $conn->prepare($sql);
122                $sth->bind_param(1, $source);
123                return $sth;
124        } elsif($mode eq "-outer") {
125                my $sql = 
126                        "SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
127                        "FROM postcodes WHERE NOT deleted ".
128                        "GROUP BY outward ".
129                        "ORDER BY outward ";
130
131                my $sth = $conn->prepare($sql);
132                return $sth;
133        } elsif($mode eq "-outer1") {
134                my $sql = 
135                        "SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
136                        "FROM postcodes WHERE NOT deleted ".
137                        "GROUP BY outward, substr(inward,1,1) ".
138                        "ORDER BY outward, inward";
139
140                my $sth = $conn->prepare($sql);
141                return $sth;
142        } else {
143                die("Invalid mode '$mode'");
144        }
145}
146
147sub getSource($$) {
148        my ($source,$conn) = @_;
149
150        my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
151        $sth->bind_param(1, $source);
152        $sth->execute;
153
154        my $s = undef;
155        my @row = $sth->fetchrow_array;
156        if($sth->fetchrow_array) {
157                die("Should only find one source with name '$source', got more than one!");
158        }
159
160        unless(@row) {
161                die("Can't find id for source with name '$source'");
162        }
163
164        return $row[0]; 
165}
Note: See TracBrowser for help on using the repository browser.