source: trunk/npemap.org.uk/scripts/exporter/rawExport.pl @ 624

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

Raw exporter

  • Property svn:eol-style set to native
  • Property svn:executable set to *
File size: 2.9 KB
Line 
1#!/usr/bin/perl
2#                                       Raw Postcode Data Exporter
3#                                       --------------------------
4#
5# Exports the raw postcode data, including IDs.
6# Doesn't export deleted postcodes, but does export ones with open
7#  problem reports.
8# Normally used by people wishing to check our data for errors
9
10use strict;
11use DBI;
12use Geography::NationalGrid;
13use Geography::NationalGrid::GB;
14use Geography::NationalGrid::IE;
15
16# Find our private perl libraries
17use FindBin;
18use lib "$FindBin::Bin/../../perllib";
19use NPEMap::GeoConverter;
20
21# What to connect with
22my $dbname = $ENV{DBNAME} || "npemap";
23my $dbhost = $ENV{DBHOST} || "";
24my $dbuser = $ENV{DBUSER} || "npemap";
25my $dbpass = $ENV{DBPASS} || "";
26
27# Connect to the database
28my $dsn = "dbi:Pg:dbname=$dbname";
29$dsn .= ";host=$dbhost" if $dbhost;
30
31my $conn = DBI->connect($dsn, $dbuser, $dbpass,
32                                        { PrintError => 0, RaiseError => 1, AutoCommit => 1 } );
33
34# Print out our header
35print "# Generated on ".gmtime()."\n";
36print "#\n";
37print "# This information is public domain, and comes from http://www.npemap.org.uk/\n";
38print "#\n";
39print "# This is raw postcode data, and not intended for end users. See \n";
40print "#  http://www.npemap.org.uk/data/ for formatted data\n";
41print "#\n";
42print "# <internal id>,<outward>,<inward>,<easting>,<northing>,<grid>,<WGS84 lat>,<WGS84 long>,<problem reported>\n";
43
44# Get our data fetch query
45my $source = getSource("NPE Postcode web submission", $conn);
46my $query = getQuery($conn);
47
48# Process the query
49$query->execute;
50while(my ($id,$outer,$inner,$e,$n,$ie_e,$ie_n,$problem) = $query->fetchrow_array) {
51        # What grid are we on?
52        my $grid = "osgb";
53        if(!$e && $ie_e) {
54                $grid = "osie";
55                $e = $ie_e;
56                $n = $ie_n;
57        }
58       
59        # Generate lat+long
60        my ($lat,$long) = eastingNorthingToLatLong($e,$n,$grid);
61
62        # Make e+n look nicer
63        $e =~ s/^(\d+)\.\d+$/$1/;
64        $n =~ s/^(\-?\d+)\.\d+$/$1/;
65
66        # Make lat+long look nicer
67        $lat = sprintf("%0.6f", $lat);
68        $long = sprintf("%0.6f", $long);
69
70        print "$id,$outer,$inner,$e,$n,$grid,$lat,$long,$problem\n";
71}
72
73# Close down
74$conn->disconnect;
75
76
77############################################################################
78
79# Get a query returning
80#               id, outer, inner, easting, northing
81sub getQuery($) {
82        my $sql = 
83                "SELECT id, outward, inward, easting, northing, ie_easting, ie_northing, reason IS NOT NULL AS problem ".
84                "FROM postcodes ".
85                "LEFT OUTER JOIN bad_postcodes ON (id = postcode AND NOT actioned) ".
86                "WHERE source = ? AND NOT deleted ".
87                "ORDER BY outward, inward ";
88
89        my $sth = $conn->prepare($sql);
90        $sth->bind_param(1, $source);
91        return $sth;
92}
93
94sub getSource($$) {
95        my ($source,$conn) = @_;
96
97        my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
98        $sth->bind_param(1, $source);
99        $sth->execute;
100
101        my $s = undef;
102        my @row = $sth->fetchrow_array;
103        if($sth->fetchrow_array) {
104                die("Should only find one source with name '$source', got more than one!");
105        }
106
107        unless(@row) {
108                die("Can't find id for source with name '$source'");
109        }
110
111        return $row[0]; 
112}
Note: See TracBrowser for help on using the repository browser.