Changeset 204
- Timestamp:
- Oct 27, 2006, 10:00:26 AM (14 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi
r199 r204 5 5 # 6 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 7 10 8 11 use strict; … … 56 59 my $pcgroup = "outward"; 57 60 if($doing_outer1) { 58 $pcpart = "outward || substr(inward,1,1)";61 $pcpart = "outward || ' ' || substr(inward,1,1)"; 59 62 $pcgroup = "outward, substr(inward,1,1)"; 60 63 } … … 69 72 # Get our sql 70 73 # 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 "; 72 75 73 76 # Then join to find problem ones 74 77 my $sql .= "SELECT id, outward || ' ' || inward AS postcode, $pcpart AS pcpart, easting, northing, avg_easting, avg_northing FROM postcodes "; 75 78 $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 "; 79 83 80 84 # Build the temp table
Note: See TracChangeset
for help on using the changeset viewer.