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

Last change on this file since 553 was 553, checked in by Nick Burch, 13 years ago

Update the export script to handle OSIE as well as OSGB

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