Changeset 402


Ignore:
Timestamp:
Dec 3, 2006, 4:37:21 PM (13 years ago)
Author:
Nick Burch
Message:

Add in the sql for the nearby areas, but no code to use it (yet)

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/cgi/geocoder.fcgi

    r401 r402  
    3434my $postcode_matcher = &build_ltp_matcher($dbh);
    3535#   Location to area code matchers
     36my $pc_area_matcher = &build_lta_matcher($dbh);
    3637
    3738
     
    5455        unless($postcode_matcher) {
    5556                $postcode_matcher = &build_ltp_matcher($dbh);
     57        }
     58        unless($pc_area_matcher) {
     59                $pc_area_matcher = &build_lta_matcher($dbh);
    5660        }
    5761
     
    129133        my ($e, $n) = (int($easting), int($northing));
    130134          if(defined $cgi->param("callback")) {
    131           print ($cgi->param("callback") . "(");
     135          print $cgi->param("callback") . "(";
    132136      }
    133137      print "{ pc: \"$pc\", e: $e, n: $n }";
     
    209213}
    210214
     215# Location to nearby postcode areas matcher
     216sub build_lta_matcher {
     217        my $dbh = shift;
     218
     219        my $sql = "SELECT outward, avg_e AS easting, avg_n AS northing ".
     220                  "FROM ( ".
     221                  "   SELECT outward, ".
     222                  "          AVG(easting) AS avg_e, ".
     223                  "          AVG(northing) AS avg_n ".
     224                  "   FROM postcodes ".
     225                  "   GROUP BY outward ".
     226                  ") AS areas ".
     227                  "ORDER BY ( ".
     228                  "   POW(avg_e - ?, 2) + POW(avg_n - ?, 2) ".
     229                  ") LIMIT ? ";
     230
     231        my $matcher = $dbh->prepare($sql);
     232        return $matcher;
     233}
     234
     235
    211236# Helper routines
    212237sub print_err {
Note: See TracChangeset for help on using the changeset viewer.