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

Last change on this file since 635 was 635, checked in by Dominic Hargreaves, 13 years ago

licence tastic

  • Property svn:executable set to *
File size: 2.7 KB
Line 
1#!/usr/bin/perl
2
3# Copyright (c) 2006-2007 David Sheldon
4# Permission is hereby granted, free of charge, to any person obtaining a
5# copy of this software and associated documentation files (the "Software"),
6# to deal in the Software without restriction, including without limitation
7# the rights to use, copy, modify, merge, publish, distribute, sublicense,
8# and/or sell copies of the Software, and to permit persons to whom the
9# Software is furnished to do so, subject to the following conditions:
10#
11# The above copyright notice and this permission notice shall be included in
12# all copies or substantial portions of the Software.
13#
14# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
17# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
19# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
20# IN THE SOFTWARE.
21#
22#                                       Postcode Data Exporter
23
24
25
26use strict;
27use DBI;
28use FindBin;
29use Sys::Hostname;
30my $POSTCODE_CACHE ;
31my $POSTCODE_DIR ;
32my $IMAGE_CACHE_DIR ;
33
34if (hostname eq 'urchin') {
35  $POSTCODE_CACHE = "/srv/www/npemap.org.uk/postcodeine/postcodes.dat";
36  $POSTCODE_DIR =   "/srv/www/npemap.org.uk/postcodeine/";
37  $IMAGE_CACHE_DIR ="/srv/www/npemap.org.uk/postcodeine/cache";
38} else {
39  $POSTCODE_CACHE = "/tmp/postcodes.dat";
40  $POSTCODE_DIR =   "/tmp/";
41  $IMAGE_CACHE_DIR ="/tmp/cache";
42}
43
44use lib "$FindBin::Bin/../../perllib";
45use NPEMap::Postcodes;
46use NPEMap;
47
48sub setup_dbh;
49
50my $dbh;
51
52unless ($dbh = setup_dbh()) {
53    die "Unable to connect to database";
54}
55
56my $csv = $POSTCODE_DIR . "NPE.csv";
57
58open OUT, ">$csv" or die "unable to open $csv for writing.";
59
60my $query = getQuery($dbh);
61
62# Process the query
63$query->execute;
64while(my ($outer,$e,$n) = $query->fetchrow_array) {
65    $e =~ s/^(\d+)\.\d+$/$1/;
66    $n =~ s/^(\d+)\.\d+$/$1/;
67    my $pcode = $outer;
68    if (length($pcode) > 2 && $e && $n) {
69            print OUT "\"$pcode\",$e,$n\n";
70    }
71}
72close OUT;
73
74$dbh->disconnect;
75
76if (-f $POSTCODE_CACHE) {
77    unlink $POSTCODE_CACHE or die "Unable to delete $POSTCODE_CACHE: $!";
78}
79
80foreach my $file (glob("$IMAGE_CACHE_DIR/*.png")) {
81    unlink $file or die "unable to delete $file: $!";
82}
83
84# Get a query returning
85#               outer . inner, easting, northing
86sub getQuery {
87        my ($conn) = @_;
88
89    my $sql = 
90        "SELECT outward || inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
91        "FROM postcodes  ".
92        "WHERE NOT deleted ".
93        "GROUP BY outward || inward ".
94        "ORDER BY outward || inward ";
95
96    my $sth = $conn->prepare($sql);
97    return $sth;
98}
99
Note: See TracBrowser for help on using the repository browser.