source: trunk/npemap.org.uk/scripts/exporter/updatePostcodeine.pl @ 552

Last change on this file since 552 was 552, checked in by David Sheldon, 13 years ago

Dont output NI blanks

  • Property svn:executable set to *
File size: 1.7 KB
Line 
1#!/usr/bin/perl
2
3
4#                                       Postcode Data Exporter
5
6
7
8use strict;
9use DBI;
10use FindBin;
11use Sys::Hostname;
12my $POSTCODE_CACHE ;
13my $POSTCODE_DIR ;
14my $IMAGE_CACHE_DIR ;
15
16if (hostname eq 'urchin') {
17  $POSTCODE_CACHE = "/srv/www/npemap.org.uk/postcodeine/postcodes.dat";
18  $POSTCODE_DIR =   "/srv/www/npemap.org.uk/postcodeine/";
19  $IMAGE_CACHE_DIR ="/srv/www/npemap.org.uk/postcodeine/cache";
20} else {
21  $POSTCODE_CACHE = "/tmp/postcodes.dat";
22  $POSTCODE_DIR =   "/tmp/";
23  $IMAGE_CACHE_DIR ="/tmp/cache";
24}
25
26use lib "$FindBin::Bin/../../perllib";
27use NPEMap::Postcodes;
28use NPEMap;
29
30sub setup_dbh;
31
32my $dbh;
33
34unless ($dbh = setup_dbh()) {
35    die "Unable to connect to database";
36}
37
38my $csv = $POSTCODE_DIR . "NPE.csv";
39
40open OUT, ">$csv" or die "unable to open $csv for writing.";
41
42my $query = getQuery($dbh);
43
44# Process the query
45$query->execute;
46while(my ($outer,$e,$n) = $query->fetchrow_array) {
47    $e =~ s/^(\d+)\.\d+$/$1/;
48    $n =~ s/^(\d+)\.\d+$/$1/;
49    my $pcode = $outer;
50    if (length($pcode) > 2 && $e && $n) {
51            print OUT "\"$pcode\",$e,$n\n";
52    }
53}
54close OUT;
55
56$dbh->disconnect;
57
58if (-f $POSTCODE_CACHE) {
59    unlink $POSTCODE_CACHE or die "Unable to delete $POSTCODE_CACHE: $!";
60}
61
62foreach my $file (glob("$IMAGE_CACHE_DIR/*.png")) {
63    unlink $file or die "unable to delete $file: $!";
64}
65
66# Get a query returning
67#               outer . inner, easting, northing
68sub getQuery {
69        my ($conn) = @_;
70
71    my $sql = 
72        "SELECT outward || inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
73        "FROM postcodes  ".
74        "WHERE NOT deleted ".
75        "GROUP BY outward || inward ".
76        "ORDER BY outward || inward ";
77
78    my $sth = $conn->prepare($sql);
79    return $sth;
80}
81
Note: See TracBrowser for help on using the repository browser.