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

Last change on this file since 627 was 627, checked in by npemap, 12 years ago

Don't calculate lat+long, as takes too long

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