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

Last change on this file since 277 was 277, checked in by David Sheldon, 15 years ago

Order better.

  • Property svn:executable set to *
File size: 1.6 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,$inner,$e,$n) = $query->fetchrow_array) {
47    $e =~ s/^(\d+)\.\d+$/$1/;
48    $n =~ s/^(\d+)\.\d+$/$1/;
49    my $pcode = "$outer$inner";
50            print OUT "\"$pcode\",$e,$n\n";
51}
52close OUT;
53
54$dbh->disconnect;
55
56if (-f $POSTCODE_CACHE) {
57    unlink $POSTCODE_CACHE or die "Unable to delete $POSTCODE_CACHE: $!";
58}
59
60foreach my $file (glob("$IMAGE_CACHE_DIR/*.png")) {
61    unlink $file or die "unable to delete $file: $!";
62}
63
64# Get a query returning
65#               outer, inner, easting, northing
66sub getQuery {
67        my ($conn) = @_;
68
69    my $sql = 
70        "SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
71        "FROM postcodes  ".
72        "WHERE NOT deleted ".
73        "GROUP BY outward, inward ".
74        "ORDER BY outward || inward ";
75
76    my $sth = $conn->prepare($sql);
77    return $sth;
78}
79
Note: See TracBrowser for help on using the repository browser.