Changeset 401


Ignore:
Timestamp:
Dec 3, 2006, 1:45:48 PM (13 years ago)
Author:
Nick Burch
Message:

Add sql for location->postcode. Still need to add code to call it though

File:
1 edited

Legend:

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

    r372 r401  
    2727my $dbh = setup_dbh();
    2828
    29 # Build our prepared statements
     29# Build our prepared statements:
     30#   Postcode to location matchers
    3031my ($full_matcher,$outer1_matcher,$outer_matcher,$area_matcher) =
    31         &build_matchers($dbh);
     32        &build_ptl_matchers($dbh);
     33#   Location to postcode matcher
     34my $postcode_matcher = &build_ltp_matcher($dbh);
     35#   Location to area code matchers
    3236
    3337
     
    4650        unless($full_matcher) {
    4751                ($full_matcher,$outer1_matcher,$outer_matcher,$area_matcher) =
    48                         &build_matchers($dbh);
     52                        &build_ptl_matchers($dbh);
     53        }
     54        unless($postcode_matcher) {
     55                $postcode_matcher = &build_ltp_matcher($dbh);
    4956        }
    5057
     
    5966                }
    6067        }
     68
     69        # Did they want to do postcode -> location, or location -> postcode?
    6170
    6271        # What postcode were they asking about?
     
    154163
    155164# Build our various matcher prepared statements
    156 sub build_matchers {
     165
     166# Postcode to Location matchers
     167sub build_ptl_matchers {
    157168        my $dbh = shift;
    158169
     
    183194}
    184195
     196# Location to Postcode matcher
     197sub build_ltp_matcher {
     198        my $dbh = shift;
     199
     200        my $sql = "SELECT outward, inward, easting, northing ".
     201                  "FROM postcodes ".
     202                  "WHERE inward IS NOT NULL AND inward <> '' ".
     203                  "ORDER BY ( ".
     204                  "   POW(easting - ?, 2) + POW(northing - ?, 2) ".
     205                  ") LIMIT ? ";
     206
     207        my $matcher = $dbh->prepare($sql);
     208        return $matcher;
     209}
     210
    185211# Helper routines
    186212sub print_err {
Note: See TracChangeset for help on using the changeset viewer.