Changeset 204


Ignore:
Timestamp:
Oct 27, 2006, 10:00:26 AM (13 years ago)
Author:
Nick Burch
Message:

Take account of deleted

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi

    r199 r204  
    55#
    66# 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
    710
    811use strict;
     
    5659        my $pcgroup = "outward";
    5760        if($doing_outer1) {
    58                 $pcpart = "outward || substr(inward,1,1)";
     61                $pcpart = "outward || ' ' || substr(inward,1,1)";
    5962                $pcgroup = "outward, substr(inward,1,1)";
    6063        }
     
    6972        # Get our sql
    7073        # First calculate the average
    71         my $prepsql .= "SELECT $pcpart AS pcpart, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing INTO TEMP TABLE averages FROM postcodes GROUP BY $pcgroup ";
     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 ";
    7275
    7376        # Then join to find problem ones
    7477        my $sql .= "SELECT id, outward || ' ' || inward AS postcode, $pcpart AS pcpart, easting, northing, avg_easting, avg_northing FROM postcodes ";
    7578        $sql .= "INNER JOIN averages ON ($pcpart = pcpart) ";
    76         $sql .= "WHERE abs(avg_easting-easting) > $flag_distance ";
    77         $sql .= "OR abs(avg_northing-northing) > $flag_distance ";
    78         $sql .= "ORDER BY outward, inward ";
     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 ";
    7983
    8084        # Build the temp table
Note: See TracChangeset for help on using the changeset viewer.