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

Last change on this file since 204 was 204, checked in by Nick Burch, 15 years ago

Take account of deleted

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