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

Last change on this file since 587 was 587, checked in by Nick Burch, 12 years ago

Tweak outer1 export

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