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

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

Script to flush the postcodeine data. You then need to kill the fcgi process.

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