source: trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi @ 196

Last change on this file since 196 was 196, checked in by Nick Burch, 14 years ago

Nearly working cgi for getting distant postcodes

  • Property svn:executable set to *
File size: 3.3 KB
Line 
1#!/usr/bin/perl
2#
3# Copyright (c) 2006 Dominic Hargreaves
4# See accompanying file "LICENCE" for licence details
5#
6# Find postcodes that seem to be a long way from their friends
7
8use strict;
9use warnings;
10
11use DBI;
12use CGI::Fast qw/:standard -debug/;
13
14use vars qw($dbname $dbhost $dbuser $dbpass);
15
16sub print_err;
17sub setup_dbh;
18
19# Read in database config
20my $config = 'npemap.conf';
21do $config or die "Can't read $config!\n";
22
23
24# Set up database handler to try and make sure it's ready for the first
25# request
26# No point in handling errors here since they'll get handled by the request
27# handler
28my $dbh;
29setup_dbh();
30
31
32my $cgi;
33# Process incoming requests
34REQUEST: while ($cgi = new CGI::Fast) {
35
36    # In case the database went away, make sure we have a connection
37    unless (setup_dbh()) {
38        print_internal_err('Error setting up database connection');
39        next REQUEST;
40    }
41
42    # Input validation
43        my $doing_outer1 = 0;
44    if (defined $cgi->param("outer1") || defined $cgi->param("outerone")) {
45        $doing_outer1 = 1;
46    }
47
48        # How big a distance before we flag it?
49        # 10km / 10km
50        my $flag_distance = 100 * 1000;
51        if($doing_outer1) {
52                $flag_distance = 10 * 1000;
53        }
54
55        my $pcpart = "outward";
56        my $pcgroup = "outward";
57        if($doing_outer1) {
58                $pcpart = "outward || substr(inward,1,1)";
59                $pcgroup = "outward, substr(inward,1,1)";
60        }
61
62        # Tidy up from another run
63        my $checksql = "SELECT tablename FROM pg_tables WHERE tablename = 'averages'";
64        my $check = $dbh->prepare($checksql);
65        $check->execute;
66        if($check->fetchrow_array) {
67                $dbh->do("DROP TABLE averages");
68        }
69
70        # Get our sql
71        # First calculate the average
72        my $prepsql = "CREATE TABLE averages (pcpart VARCHAR(10),avg_easting INTEGER, avg_northing INTEGER);";
73        $prepsql .= "SELECT $pcpart AS pcpart, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing INTO averages FROM postcodes GROUP BY $pcgroup ";
74
75        # Then join to find problem ones
76        my $sql .= "SELECT outward || ' ' || inward AS postcode, $pcpart AS pcpart, easting, northing, avg_easting, avg_northing, id FROM postcodes ";
77        $sql .= "INNER JOIN averages ON ($pcpart = pcpart) ";
78        $sql .= "WHERE abs(avg_easting-easting) > $flag_distance ";
79        $sql .= "OR abs(avg_northing-northing) > $flag_distance ";
80        $sql .= "ORDER BY outward, inward ";
81
82        # Build the temp table
83    $dbh->do($prepsql);
84
85        # Do the query
86    my $sth = $dbh->prepare($sql);
87        print $sql."\n";
88   
89    if ($sth->execute()) {
90        print "Content-type: text/javascript\n\n";
91
92        while(my @row = $sth->fetchrow_array) {
93                        # Tidy up avg
94                        my $avg_e = int($row[4]);
95                        my $avg_n = int($row[5]);
96
97                        # Print
98                        print "addProblem('$row[0]','$row[1]', $row[2], $row[3], $avg_e, $avg_n);\n";
99        }
100    } else {
101        print_internal_err("Database error retrieving data");
102    }
103
104        # Tidy up
105        $check->execute;
106        if($check->fetchrow_array) {
107                $dbh->do("DROP TABLE averages");
108        }
109}
110
111# No more requests to serve, so tidy up
112$dbh->disconnect;
113
114# Helper routines
115sub print_err {
116    my $err = shift;
117    print header("text/plain", "400 $err");
118    print "Error: $err\n";
119}
120
121sub print_internal_err {
122    my $err = shift;
123    print header("text/plain", "500 $err");
124    print "Error: $err\n";
125}
126
127sub setup_dbh {
128    # $dbh is global
129    my $data_source = "dbi:Pg:dbname=$dbname";
130    $data_source .= ";host=$dbhost" if $dbhost;
131    return $dbh = DBI->connect_cached($data_source, $dbuser, $dbpass);
132}
Note: See TracBrowser for help on using the repository browser.