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

Last change on this file since 240 was 240, checked in by Dominic Hargreaves, 14 years ago

Use comma-separated file

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