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

Last change on this file since 635 was 635, checked in by Dominic Hargreaves, 12 years ago

licence tastic

  • Property svn:executable set to *
File size: 5.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
40use strict;
41use DBI;
42use Geography::NationalGrid;
43use Geography::NationalGrid::GB;
44use Geography::NationalGrid::IE;
45
46# Find our private perl libraries
47use FindBin;
48use lib "$FindBin::Bin/../../perllib";
49use NPEMap::GeoConverter;
50
51my $mode = shift;
52unless($mode) {
53        die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly>\n");
54}
55
56# What to connect with
57my $dbname = $ENV{DBNAME} || "npemap";
58my $dbhost = $ENV{DBHOST} || "";
59my $dbuser = $ENV{DBUSER} || "npemap";
60my $dbpass = $ENV{DBPASS} || "";
61
62# Connect to the database
63my $dsn = "dbi:Pg:dbname=$dbname";
64$dsn .= ";host=$dbhost" if $dbhost;
65
66my $conn = DBI->connect($dsn, $dbuser, $dbpass,
67                                        { PrintError => 0, RaiseError => 1, AutoCommit => 1 } );
68
69# Print out our header
70print "# Generated on ".gmtime()."\n";
71print "#\n";
72print "# This information is public domain, and comes from http://www.npemap.org.uk/\n";
73print "#\n";
74print "# <outward>,<inward>,<easting>,<northing>,<WGS84 lat>,<WGS84 long>,<2+6 NGR>,<grid>\n";
75
76# Get our data fetch query
77my $query = getQuery($mode,$conn);
78
79# Process the query
80$query->execute;
81while(my ($outer,$inner,$e,$n,$ie_e,$ie_n) = $query->fetchrow_array) {
82        # What grid are we on?
83        my $grid = "osgb";
84        if(!$e && $ie_e) {
85                $grid = "osie";
86                $e = $ie_e;
87                $n = $ie_n;
88        }
89       
90        # Generate lat+long
91        my ($lat,$long) = eastingNorthingToLatLong($e,$n,$grid);
92        # Generate 2+6 NGR
93        my $ngr = eastingNorthingToNGR($e,$n,$grid);
94
95        # Make e+n look nicer
96        $e =~ s/^(\d+)\.\d+$/$1/;
97        $n =~ s/^(\-?\d+)\.\d+$/$1/;
98
99        # Make lat+long look nicer
100        $lat = sprintf("%0.6f", $lat);
101        $long = sprintf("%0.6f", $long);
102
103        print "$outer,$inner,$e,$n,$lat,$long,$ngr,$grid\n";
104}
105
106# Close down
107$conn->disconnect;
108
109
110############################################################################
111
112# Get a query returning
113#               outer, inner, easting, northing
114# for the given mode
115sub getQuery($$) {
116        my ($mode,$conn) = @_;
117
118        if($mode eq "-gpsonly" || $mode eq "-npeonly") {
119                my $source;
120                if($mode eq "-gpsonly") {
121                        $source = getSource("FreeThePostcode.org Importer", $conn);
122                } else {
123                        $source = getSource("NPE Postcode web submission", $conn);
124                }
125
126                my $sql = 
127                        "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 ".
128                        "FROM postcodes   ".
129                        "WHERE source = ? AND NOT deleted ".
130                        "GROUP BY outward, inward ".
131                        "ORDER BY outward, inward ";
132
133                my $sth = $conn->prepare($sql);
134                $sth->bind_param(1, $source);
135                return $sth;
136        } elsif($mode eq "-outer") {
137                my $sql = 
138                        "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 ".
139                        "FROM postcodes WHERE NOT deleted ".
140                        "GROUP BY outward ".
141                        "ORDER BY outward ";
142
143                my $sth = $conn->prepare($sql);
144                return $sth;
145        } elsif($mode eq "-outer1") {
146                # Not - will exclude data where we only have an outward, and no inward
147                my $sql = 
148                        "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 ".
149                        "FROM postcodes ".
150                        "WHERE NOT deleted AND length(inward) > 0 ".
151                        "GROUP BY outward, substr(inward,1,1) ".
152                        "ORDER BY outward, inward";
153
154                my $sth = $conn->prepare($sql);
155                return $sth;
156        } else {
157                die("Invalid mode '$mode'");
158        }
159}
160
161sub getSource($$) {
162        my ($source,$conn) = @_;
163
164        my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
165        $sth->bind_param(1, $source);
166        $sth->execute;
167
168        my $s = undef;
169        my @row = $sth->fetchrow_array;
170        if($sth->fetchrow_array) {
171                die("Should only find one source with name '$source', got more than one!");
172        }
173
174        unless(@row) {
175                die("Can't find id for source with name '$source'");
176        }
177
178        return $row[0]; 
179}
Note: See TracBrowser for help on using the repository browser.