Changeset 204

Timestamp:

Oct 27, 2006, 10:00:26 AM (15 years ago)

Author:

Nick Burch

Message:

Take account of deleted

File:

• trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi (modified) (3 diffs)

trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi

@@ -5 +5 @@
 #
 # Find postcodes that seem to be a long way from their friends
+
+# TODO: Make PostGreSQL temp tables + FastCGI play nicely together
+#       For now, we have a race condition related to the "temp" table
 
 use strict;
@@ -56 +59 @@
         my $pcgroup = "outward";
         if($doing_outer1) {
-                $pcpart = "outward || substr(inward,1,1)";
+                $pcpart = "outward || ' ' || substr(inward,1,1)";
                 $pcgroup = "outward, substr(inward,1,1)";
         }
@@ -69 +72 @@
         # Get our sql
         # First calculate the average
-        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 ";
+        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 ";
 
         # Then join to find problem ones
         my $sql .= "SELECT id, outward || ' ' || inward AS postcode, $pcpart AS pcpart, easting, northing, avg_easting, avg_northing FROM postcodes ";
         $sql .= "INNER JOIN averages ON ($pcpart = pcpart) ";
-        $sql .= "WHERE abs(avg_easting-easting) > $flag_distance ";
-        $sql .= "OR abs(avg_northing-northing) > $flag_distance ";
-        $sql .= "ORDER BY outward, inward ";
+        $sql .= "WHERE NOT deleted AND (";
+        $sql .= "       abs(avg_easting-easting) > $flag_distance ";
+        $sql .= "       OR abs(avg_northing-northing) > $flag_distance ";
+        $sql .= ") ORDER BY outward, inward ";
 
         # Build the temp table