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

Last change on this file was 758, checked in by Dominic Hargreaves, 5 years ago

Remove (hopefully) unneeded 'use' lines

  • Property svn:executable set to *
File size: 6.5 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;
47
48# Find our private perl libraries
49use FindBin;
50use lib "$FindBin::Bin/../../perllib";
51use NPEMap::GeoConverter;
52
53my $mode = shift;
54unless($mode) {
55        die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly|-all>\n");
56}
57
58# What to connect with
59my $dbname = $ENV{DBNAME} || "npemap";
60my $dbhost = $ENV{DBHOST} || "";
61my $dbuser = $ENV{DBUSER} || "npemap";
62my $dbpass = $ENV{DBPASS} || "";
63
64# Connect to the database
65my $dsn = "dbi:Pg:dbname=$dbname";
66$dsn .= ";host=$dbhost" if $dbhost;
67
68my $conn = DBI->connect($dsn, $dbuser, $dbpass,
69                                        { PrintError => 0, RaiseError => 1, AutoCommit => 1 } );
70
71# Print out our header
72print "# Generated on ".gmtime()."\n";
73print "#\n";
74print "# This information is made available under the Public Domain\n";
75print "# Dedication and License version 1.0 whose full text can be found\n";
76print "# at http://www.npemap.org.uk/pddl, and comes from \n";
77print "# 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                # Convert source from array ref to CSV
110                $source = join(",", @$source);
111                print "$outer,$inner,$e,$n,$lat,$long,$ngr,$grid,$source\n";
112        };
113        if ($@) {
114           print STDERR "Error processing $outer $inner\n -- $@";
115        };
116}
117
118# Close down
119$conn->disconnect;
120
121
122############################################################################
123
124# Get a query returning
125#               outer, inner, easting, northing
126# for the given mode
127sub getQuery($$) {
128        my ($mode,$conn) = @_;
129
130        if($mode eq "-gpsonly" || $mode eq "-npeonly" || $mode eq "-postboxonly" || $mode eq "-all") {
131                my $source = -1;
132                if($mode eq "-gpsonly") {
133                        $source = getSource("FreeThePostcode.org Importer", $conn);
134                } elsif($mode eq "-postboxonly") {
135                        $source = getSource("Dracos.co.uk Postbox Importer", $conn);
136                } elsif($mode eq "-npeonly") {
137                        $source = getSource("NPE Postcode web submission", $conn);
138                }
139
140                my $source_sql = "";
141                if($source >= 0) {
142                        $source_sql = " AND source = ? ";
143                }
144
145                my $sql = 
146                        "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 ".
147                        "FROM postcodes   ".
148                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
149                        "WHERE NOT deleted ".$source_sql.
150                        "GROUP BY outward, inward ".
151                        "ORDER BY outward, inward ";
152
153                my $sth = $conn->prepare($sql);
154                if($source >= 0) {
155                        $sth->bind_param(1, $source);
156                }
157                return $sth;
158        } elsif($mode eq "-outer") {
159                my $sql = 
160                        "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 ".
161                        "FROM postcodes ".
162                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
163                        "WHERE NOT deleted ".
164                        "GROUP BY outward ".
165                        "ORDER BY outward ";
166
167                my $sth = $conn->prepare($sql);
168                return $sth;
169        } elsif($mode eq "-outer1") {
170                # Not - will exclude data where we only have an outward, and no inward
171                my $sql = 
172                        "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 ".
173                        "FROM postcodes ".
174                        "INNER JOIN sources ON (postcodes.source = sources.id) ".
175                        "WHERE NOT deleted AND length(inward) > 0 ".
176                        "GROUP BY outward, substr(inward,1,1) ".
177                        "ORDER BY outward, inward";
178
179                my $sth = $conn->prepare($sql);
180                return $sth;
181        } else {
182                die("Invalid mode '$mode'");
183        }
184}
185
186sub getSource($$) {
187        my ($source,$conn) = @_;
188
189        my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
190        $sth->bind_param(1, $source);
191        $sth->execute;
192
193        my $s = undef;
194        my @row = $sth->fetchrow_array;
195        if($sth->fetchrow_array) {
196                die("Should only find one source with name '$source', got more than one!");
197        }
198
199        unless(@row) {
200                die("Can't find id for source with name '$source'");
201        }
202
203        return $row[0]; 
204}
Note: See TracBrowser for help on using the repository browser.