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

Last change on this file since 673 was 673, checked in by sheldon, 12 years ago

Handle error with postcode and output message to stderr rather than truncating the output

  • Property svn:executable set to *
File size: 6.4 KB
Line 
1#!/usr/bin/perl
2#
3# Copyright (c) 2006-2007 Nick Burch
4# Permission is hereby granted, free of charge, to any person obtaining a
5# copy of this software and associated documentation files (the "Software"),
6# to deal in the Software without restriction, including without limitation
7# the rights to use, copy, modify, merge, publish, distribute, sublicense,
8# and/or sell copies of the Software, and to permit persons to whom the
9# Software is furnished to do so, subject to the following conditions:
10#
11# The above copyright notice and this permission notice shall be included in
12# all copies or substantial portions of the Software.
13#
14# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
17# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
19# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
20# IN THE SOFTWARE.
21#
22#                                       Postcode Data Exporter
23#                                       ----------------------
24#
25# Exports various parts of the data, in various formats
26# Normally these are of the form:
27#   <postcode|part postcode>    <easting>       <northing>      <lat>   <long>
28#
29# Supported outputs are:
30#   -gpsonly
31#       Only export GPS sourced postcodes, averaged if several for a postcode
32#   -outer
33#       Only export the outer parts of the postcodes, averaged over all
34#   -outer1
35#       Only export the outer part + 1st inner digit, averaged over all
36#       (Excludes entries where we only have an outward, but no inward)
37#   -npeonly
38#       Only export NPE sourced postcodes, averaged if several for a postcode
39#   -postboxonly
40#       Only export dracos postbox data
41#   -all
42#       Outputs all postcodes, averaged if several for a postcode, along
43#       with their source
44
45use strict;
46use DBI;
47use Geography::NationalGrid;
48use Geography::NationalGrid::GB;
49use Geography::NationalGrid::IE;
50
51# Find our private perl libraries
52use FindBin;
53use lib "$FindBin::Bin/../../perllib";
54use NPEMap::GeoConverter;
55
56my $mode = shift;
57unless($mode) {
58        die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly|-all>\n");
59}
60
61# What to connect with
62my $dbname = $ENV{DBNAME} || "npemap";
63my $dbhost = $ENV{DBHOST} || "";
64my $dbuser = $ENV{DBUSER} || "npemap";
65my $dbpass = $ENV{DBPASS} || "";
66
67# Connect to the database
68my $dsn = "dbi:Pg:dbname=$dbname";
69$dsn .= ";host=$dbhost" if $dbhost;
70
71my $conn = DBI->connect($dsn, $dbuser, $dbpass,
72                                        { PrintError => 0, RaiseError => 1, AutoCommit => 1 } );
73
74# Print out our header
75print "# Generated on ".gmtime()."\n";
76print "#\n";
77print "# This information is public domain, and comes from http://www.npemap.org.uk/\n";
78print "#\n";
79print "# <outward>,<inward>,<easting>,<northing>,<WGS84 lat>,<WGS84 long>,<2+6 NGR>,<grid>,<sources>\n";
80
81# Get our data fetch query
82my $query = getQuery($mode,$conn);
83
84# Process the query
85$query->execute;
86while(my ($outer,$inner,$e,$n,$ie_e,$ie_n,$source) = $query->fetchrow_array) {
87        # What grid are we on?
88        my $grid = "osgb";
89        if(!$e && $ie_e) {
90                $grid = "osie";
91                $e = $ie_e;
92                $n = $ie_n;
93        }
94       
95        eval {
96                # Generate lat+long
97                my ($lat,$long) = eastingNorthingToLatLong($e,$n,$grid);
98                # Generate 2+6 NGR
99                my $ngr = eastingNorthingToNGR($e,$n,$grid);
100
101                # Make e+n look nicer
102                $e =~ s/^(\d+)\.\d+$/$1/;
103                $n =~ s/^(\-?\d+)\.\d+$/$1/;
104
105                # Make lat+long look nicer
106                $lat = sprintf("%0.6f", $lat);
107                $long = sprintf("%0.6f", $long);
108
109                # Make Source Nicer
110                $source =~ s/^\{//;
111                $source =~ s/\}$//;
112                $source =~ s/\"//g;
113                $source =~ s/,/;/g;
114
115                print "$outer,$inner,$e,$n,$lat,$long,$ngr,$grid,$source\n";
116        };
117        if ($@) {
118           print STDERR "Error processing $outer $inner\n";
119        };
120}
121
122# Close down
123$conn->disconnect;
124
125
126############################################################################
127
128# Get a query returning
129#               outer, inner, easting, northing
130# for the given mode
131sub getQuery($$) {
132        my ($mode,$conn) = @_;
133
134        if($mode eq "-gpsonly" || $mode eq "-npeonly" || $mode eq "-postboxonly" || $mode eq "-all") {
135                my $source = -1;
136                if($mode eq "-gpsonly") {
137                        $source = getSource("FreeThePostcode.org Importer", $conn);
138                } elsif($mode eq "-postboxonly") {
139                        $source = getSource("Dracos.co.uk Postbox Importer", $conn);
140                } else {
141                        $source = getSource("NPE Postcode web submission", $conn);
142                }
143
144                my $source_sql = "";
145                if($source >= 0) {
146                        $source_sql = " AND source = ? ";
147                }
148
149                my $sql = 
150                        "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
151                        "FROM postcodes   ".
152                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
153                        "WHERE NOT deleted ".$source_sql.
154                        "GROUP BY outward, inward ".
155                        "ORDER BY outward, inward ";
156
157                my $sth = $conn->prepare($sql);
158                if($source >= 0) {
159                        $sth->bind_param(1, $source);
160                }
161                return $sth;
162        } elsif($mode eq "-outer") {
163                my $sql = 
164                        "SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
165                        "FROM postcodes ".
166                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
167                        "WHERE NOT deleted ".
168                        "GROUP BY outward ".
169                        "ORDER BY outward ";
170
171                my $sth = $conn->prepare($sql);
172                return $sth;
173        } elsif($mode eq "-outer1") {
174                # Not - will exclude data where we only have an outward, and no inward
175                my $sql = 
176                        "SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
177                        "FROM postcodes ".
178                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
179                        "WHERE NOT deleted AND length(inward) > 0 ".
180                        "GROUP BY outward, substr(inward,1,1) ".
181                        "ORDER BY outward, inward";
182
183                my $sth = $conn->prepare($sql);
184                return $sth;
185        } else {
186                die("Invalid mode '$mode'");
187        }
188}
189
190sub getSource($$) {
191        my ($source,$conn) = @_;
192
193        my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
194        $sth->bind_param(1, $source);
195        $sth->execute;
196
197        my $s = undef;
198        my @row = $sth->fetchrow_array;
199        if($sth->fetchrow_array) {
200                die("Should only find one source with name '$source', got more than one!");
201        }
202
203        unless(@row) {
204                die("Can't find id for source with name '$source'");
205        }
206
207        return $row[0]; 
208}
Note: See TracBrowser for help on using the repository browser.