swap latitiude and longitude coordinates before inmporting.

subscribed

I can confirm UK postcode proximity search doesn't work at all.

GMap 5.x-1.0
Location 5.x-3.x-dev

In my case I created 15 nodes, with post codes spread around England, Scotland & Isle of Wight

ran a 5miles proximity of a Scotland node, but I got all 5 nodes in results, in created time sorting, not proximity sorting

Hi

I am having trouble getting proximity search to work too. I am using
location - location-5.x-3.0
gmap - gmap-5.x-1.0

Does anyone have this working with the combination above?

I have it working on another installation using
location - location-5.x-3.0-rc1 and gmap - gmap 5.x-1.0-rc1

Thanks
Krishna

Not sure about the 5.x code, but in the 6.x version the location.uk.inc file is missing code for finding the location of a postalcode that is present in the location.us.inc file.

FWIW I'm using 6.x 3.0 and am getting results when proximity searching by lat/lon, but not by postcode. I think UK based postcode geocoding might be better off with different code than the US version, given the different system and availability of postcode data here.

This code gets the postcode proximity search working at a basic level in 6.x, it might work in 5.x as well.

Added to location.uk.inc

/**
 * Returns a lat/lon pair of the approximate center of the given postal code in the given country
 *
 * @param $location
 *   An associative array $location where
 *     'street'       => the street portion of the location
 *     'supplemental' => additional street portion of the location
 *     'province'     => the province, state, or territory
 *     'country'      => lower-cased two-letter ISO code (REQUIRED)
 *     'postal_code'  => the international postal code for this location (REQUIRED)
 *
 * @return
 *   An associative array where
 *      'lat' => approximate latitude of the center of the postal code's area
 *      'lon' => approximate longitude of the center of the postal code's area
 *
 */
function location_latlon_rough_uk($location = array()) {
  if (!isset($location['postal_code'])) {
    return NULL;
  }

  $result = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE country = '%s' AND UPPER(zip) = UPPER('%s')", $location['country'], $location['postal_code']);

  if ($row = db_fetch_object($result)) {
    return array('lat' => $row->latitude, 'lon' => $row->longitude);
  }
  else {
    return NULL;
  }
}


/**
 * Returns a lat/lon pair of the approximate center of the given postal code in the given country
 *
 * @param $location
 *   An associative array $location where only postal code and country are necessary, but can have the keys:
 *     'street'       => the street portion of the location
 *     'supplemental' => additional street portion of the location
 *     'province'     => the province, state, or territory
 *     'country'      => lower-cased two-letter ISO code (REQUIRED)
 *     'postal_code'  => the international postal code for this location (REQUIRED)
 *
 * @return
 *   An associative array where
 *      'lat' => approximate latitude of the center of the postal code's area
 *      'lon' => approximate longitude of the center of the postal code's area
 *
 */
function location_get_postalcode_data_uk($location = array()) {

  // Now we query.
  $res = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE country = '%s' AND UPPER(zip) = UPPER('%s')", $location['country'], $location['postal_code']);
  if ($row = db_fetch_object($res)) {
    return array('lat' => $row->latitude, 'lon' => $row->longitude, 'city' => $row->city, 'province' => $row->state, 'country' => $row->country);
  }
  else {
    return NULL;
  }
}

This will find a location for an entered postcode if there is an exact match(upper/lower case not specific) but it would be nice to add the ability to match say BS6 5PJ to BS6 5 or BS6 if we don't have a full match. Ideally we would be able to match BS6 5PJ to an average of the locations for other postcodes of the form BS6 5xx that we had in the database.

I suppose the order of preference would be

search postcode:BS6 5PJ

1. BS6 5PJ
2. BS6 5P
3. Average of BS6 5Px positions
4. BS6 5
5. Average of BS6 5x positions
6. BS6

The full uk postcode database contains about 2,000,000 entries, but there are public domain databases which have ~40,000 entries. We should be able to get a decent level of accuracy using them without having to shell out £5k for the full commercial database from the Post Office.

if my company were to buy the database could i then allow use for the location module? Don't quite understand what the licensing terms are!

we have a BIG need for postcode matching and would potentially be willing to shell out for it.

If your company licences the postcode database you can use it for your website, so long as you're not using it to provide a geocode API for other sites.

Think the cost is about £5000 to start and then £1500 per year for updates. Details are on the Royal Mail website. If that's too much then I think the next best thing is the postcode data from http://www.npemap.org.uk/data/ 40,000 postcodes and you could probably do some kind of averaging to get more points.

Doesn't work for me as well.

We've been looking at a few alternatives; could this be integrated into the location module? Guessing it will require custom coding so an estimate on price would be great!

http://www.postcodesoftware.net/

Hi All,

This is not only a uk problem its a missing sql statement in the api function location_latlon_rough. But with some cooding you can change it in the views handler.

As long as bdragon is not changing the api function you may use the following code:

replace the code in your location_views_handler_filter_proximity.inc placed in the folder handler of location with the changed code here.

// $Id: location_views_handler_filter_proximity.inc,v 1.3 2008/12/03 22:51:23 bdragon Exp $

/**
 * General proximity filter for location latitude/longitude.
 */
class location_views_handler_filter_proximity extends views_handler_filter {
  // This is always single, because of the distance field's possible dependency
  // on it.
  var $no_single = TRUE;

  function option_definition() {
    $options = parent::option_definition();
    $options['type'] = array('default' => 'latlon');

    $options['operator'] = array('default' => 'mbr');

    $options['identifier'] = array('default' => 'dist');

    $options['value'] = array(
      'default' => array(
        'latitude' => '',
        'longitude' => '',
        'postal_code' => '',
        'country' => '',
        'search_distance' => 100,
        'search_units' => 'mile',
      ),
    );
    return $options;
  }

  function admin_summary() {
    return '';
  }

  function operator_options() {
    return array(
      'mbr' => t('Proximity (Rectangular)'),
      'dist' => t('Proximity (Circular)'),
    );
  }

  function expose_form_left(&$form, &$form_state) {
    parent::expose_form_left($form, $form_state);
    $form['expose']['type'] = array(
      '#parents' => array('options', 'type'),
      '#type' => 'select',
      '#title' => t('Form mode'), // @@@ Less stupid title?
      '#options' => array(
        'latlon' => t('Latitude / Longitude input'),
        'postal' => t('Postal Code / Country'),
        'postal_default' => t('Postal Code (assume default country)'),
      ),
      //'#id' => 'edit-options-type',
      '#description' => t('FIXME'),
      '#default_value' => $this->options['type'],
    );
  }

  function value_form(&$form, &$form_state) {
    $val = $this->value;

    // [11:44] <merlinofchaos> If you load the page from scratch, $input for your identifier will be empty.
    // [11:44] <merlinofchaos> So what's going on here is that for $_GET forms, there's no form id, no op button or anything, so they always appear to submit.
    // [11:45] <merlinofchaos> FAPI doesn't quite get along with that; sometimes it handles the input being empty right and sometimes it doesn't.
    // [11:45] <Bdragon> But if I set #default_value to a static string, it doesn't work either
    // [11:45] <merlinofchaos> Right, fapi thinks the empty input is actually input, thus it overrides the default value.
    // [11:45] <Bdragon> Ahh, hmm...
    // [11:46] <Bdragon> So where would I go to clean it up?
    // [11:55] <merlinofchaos> Bdragon: See views_handler_filter_string.inc line 174
    // [11:55] <merlinofchaos> Bdragon: That's how i address this problem.
    // [11:58] <Bdragon> Hmm, OK
    if (!empty($form_state['exposed'])) {
      $identifier = $this->options['expose']['identifier'];
      if (!isset($form_state['input'][$identifier])) {
        // We need to pretend the user already inputted the defaults, because
        // fapi will malfunction otherwise.
        $form_state['input'][$identifier] = $this->value;
      }
    }

    $form['value'] = array(
      '#tree' => TRUE,
    );

    $form['value']['latitude'] = array(
      '#type' => 'textfield',
      '#title' => t('Latitude'),
      '#default_value' => $this->value['latitude'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('latlon')),
    );
    $form['value']['longitude'] = array(
      '#type' => 'textfield',
      '#title' => t('Longitude'),
      '#default_value' => $this->value['longitude'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('latlon')),
    );

    $form['value']['postal_code'] = array(
      '#type' => 'textfield',
      '#title' => t('Postal code'),
      '#default_value' => $this->value['postal_code'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('postal', 'postal_default')),
    );

    $form['value']['country'] = array(
      '#type' => 'select',
      '#title' => t('Country'),
      '#options' => array('' => '') + location_get_iso3166_list(),
      '#default_value' => $this->value['country'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('postal')),
    );

    $form['value']['search_distance'] = array(
      '#type' => 'textfield',
      '#title' => t('Distance'),
      '#default_value' => $this->value['search_distance'],
    );

    $form['value']['search_units'] = array(
      '#type' => 'select',
      '#options' => array(
        'mile' => t('Miles'),
        'km' => t('Kilometers'),
      ),
      '#default_value' => $this->value['search_units'],
    );
  }

function exposed_form(&$form, &$form_state) {
    parent::exposed_form($form, $form_state);
    $key = $this->options['expose']['identifier'];
    $type = $this->options['type'];

    // Remove unneeded fields when exposing the form.
    // It's shorter than redefining value_form.
    if ($type != 'latlon') {
      unset($form[$key]['latitude']);
      unset($form[$key]['longitude']);
    }
    if ($type != 'postal' && $type != 'postal_default') {
      unset($form[$key]['postal_code']);
    }
    if ($type != 'postal') {
      unset($form[$key]['country']);
    }
  }

  // Used from the distance field.
function calculate_coords() {
    if (!empty($this->value['latitude']) && !empty($this->value['longitude'])) {
      // If there are already coordinates, there's no work for us.
      return TRUE;
    }
    // @@@ Switch to mock location object and rely on location more?

    if ($this->options['type'] == 'postal' || $this->options['type'] == 'postal_default') {
      // Force default for country.
      if ($this->options['type'] == 'postal_default') {
        $this->value['country'] = variable_get('location_default_country', 'us');
      }

      // Zip code lookup.
      if (!empty($this->value['postal_code']) && !empty($this->value['country'])) {
      	$location = array('country'=> $this->value['country'], 'postal_code' => $this->value['postal_code']);
 		
      	//DG senceless api call
        $coord = location_latlon_rough($this->value);
        //DG Lets get the data from the database
        $res = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE"." zip = '%s' AND country = '%s'", $location['postal_code'], $location['country']);
		if ($r = db_fetch_array($res)) {
		$this->value['latitude'] = $r['latitude'];
		$this->value['longitude'] = $r['longitude'];
		}
        if ($coord) {
          $this->value['latitude'] = $coord['lat'];
          $this->value['longitude'] = $coord['lon'];
        }
        else {
          return false;
        }
      }
      else {
        // @@@ Implement full address lookup?
        return false;
      }
    }
    if (empty($this->value['latitude']) || empty($this->value['longitude'])) {
      return false;
    }
    return true;
  }

function query() {
	
    if (empty($this->value)) {
      return;
    }
    
	//DG this makes the code not working, because $this->calculate_cords never has a value. an it allway returns
    // Coordinates available?
    /*if (!$this->calculate_coords()) {
      // Distance set?
      if (!empty($this->value['search_distance'])) {
        // Hmm, distance set but unable to resolve coordinates.
        // Force nothing to match.
        $this->query->add_where($this->options['group'], "0");
      }
      return;
    }*/
    //DG if no postal code return 
     if (empty($this->value['postal_code'])) {
     return;
     } 
     // DG if no distance set it to 1 because it is required else we will get nor results at all
	if (empty($this->value['search_distance'])) {
      $this->value['search_distance'] = 1;
     }
		$location = array('country'=> $this->value['country'], 'postal_code' => $this->value['postal_code']);
        //DG Lets get the data from the database
        $res = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE"." zip = '%s' AND country = '%s'", $location['postal_code'], $location['country']);
		if ($r = db_fetch_array($res)) {
		$this->value['latitude'] = $r['latitude'];
		$this->value['longitude'] = $r['longitude'];
		}
		
    $this->ensure_my_table();
    $lat = $this->value['latitude'];
    $lon = $this->value['longitude'];
    
    $distance_meters = _location_convert_distance_to_meters($this->value['search_distance'], $this->value['search_units']);
    
    $latrange = earth_latitude_range($lon, $lat, $distance_meters);
    $lonrange = earth_longitude_range($lon, $lat, $distance_meters);
	
    // Add MBR check (always.)
    $this->query->add_where($this->options['group'], "$this->table_alias.latitude > %f AND $this->table_alias.latitude < %f AND $this->table_alias.longitude > %f AND $this->table_alias.longitude < %f", $latrange[0], $latrange[1], $lonrange[0], $lonrange[1]);

    if ($this->operator == 'dist') {
      // Add radius check.
      $this->query->add_where($this->options['group'], earth_distance_sql($lon, $lat, $this->table_alias) .' < %f', $distance_meters);
    }

  }
  
}

Warm regards from cologne

Dirk

Is this for 5 or 6 version?

thank

Hi all,

one more change for the views handler. Its for drupal 6 and location 6x.3.0. Its running on a beta production site.

use it like my post above.

// $Id: location_views_handler_filter_proximity.inc,v 1.3 2008/12/03 22:51:23 bdragon Exp $

/**
 * General proximity filter for location latitude/longitude.
 */
class location_views_handler_filter_proximity extends views_handler_filter {
  // This is always single, because of the distance field's possible dependency
  // on it.
  var $no_single = TRUE;

  function option_definition() {
    $options = parent::option_definition();
    $options['type'] = array('default' => 'latlon');

    $options['operator'] = array('default' => 'mbr');

    $options['identifier'] = array('default' => 'dist');

    $options['value'] = array(
      'default' => array(
        'latitude' => '',
        'longitude' => '',
        'postal_code' => '',
        'country' => '',
        'search_distance' => 100,
        'search_units' => 'mile',
      ),
    );
    return $options;
  }

  function admin_summary() {
    return '';
  }

  function operator_options() {
    return array(
      'mbr' => t('Proximity (Rectangular)'),
      'dist' => t('Proximity (Circular)'),
    );
  }

  function expose_form_left(&$form, &$form_state) {
    parent::expose_form_left($form, $form_state);
    $form['expose']['type'] = array(
      '#parents' => array('options', 'type'),
      '#type' => 'select',
      '#title' => t('Form mode'), // @@@ Less stupid title?
      '#options' => array(
        'latlon' => t('Latitude / Longitude input'),
        'postal' => t('Postal Code / Country'),
        'postal_default' => t('Postal Code (assume default country)'),
      ),
      //'#id' => 'edit-options-type',
      '#description' => t('FIXME'),
      '#default_value' => $this->options['type'],
    );
  }

  function value_form(&$form, &$form_state) {
    $val = $this->value;

    // [11:44] <merlinofchaos> If you load the page from scratch, $input for your identifier will be empty.
    // [11:44] <merlinofchaos> So what's going on here is that for $_GET forms, there's no form id, no op button or anything, so they always appear to submit.
    // [11:45] <merlinofchaos> FAPI doesn't quite get along with that; sometimes it handles the input being empty right and sometimes it doesn't.
    // [11:45] <Bdragon> But if I set #default_value to a static string, it doesn't work either
    // [11:45] <merlinofchaos> Right, fapi thinks the empty input is actually input, thus it overrides the default value.
    // [11:45] <Bdragon> Ahh, hmm...
    // [11:46] <Bdragon> So where would I go to clean it up?
    // [11:55] <merlinofchaos> Bdragon: See views_handler_filter_string.inc line 174
    // [11:55] <merlinofchaos> Bdragon: That's how i address this problem.
    // [11:58] <Bdragon> Hmm, OK
    if (!empty($form_state['exposed'])) {
      $identifier = $this->options['expose']['identifier'];
      if (!isset($form_state['input'][$identifier])) {
        // We need to pretend the user already inputted the defaults, because
        // fapi will malfunction otherwise.
        $form_state['input'][$identifier] = $this->value;
      }
    }

    $form['value'] = array(
      '#tree' => TRUE,
    );

    $form['value']['latitude'] = array(
      '#type' => 'textfield',
      '#title' => t('Latitude'),
      '#default_value' => $this->value['latitude'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('latlon')),
    );
    $form['value']['longitude'] = array(
      '#type' => 'textfield',
      '#title' => t('Longitude'),
      '#default_value' => $this->value['longitude'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('latlon')),
    );

    $form['value']['postal_code'] = array(
      '#type' => 'textfield',
      '#title' => t('Postal code'),
      '#default_value' => $this->value['postal_code'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('postal', 'postal_default')),
    );

    $form['value']['country'] = array(
      '#type' => 'select',
      '#title' => t('Country'),
      '#options' => array('' => '') + location_get_iso3166_list(),
      '#default_value' => $this->value['country'],
      '#process' => array('views_process_dependency'),
      '#dependency' => array('edit-options-type' => array('postal')),
    );

    $form['value']['search_distance'] = array(
      '#type' => 'textfield',
      '#title' => t('Distance'),
      '#default_value' => $this->value['search_distance'],
    );

    $form['value']['search_units'] = array(
      '#type' => 'select',
      '#options' => array(
        'mile' => t('Miles'),
        'km' => t('Kilometers'),
      ),
      '#default_value' => $this->value['search_units'],
    );
  }

function exposed_form(&$form, &$form_state) {
    parent::exposed_form($form, $form_state);
    $key = $this->options['expose']['identifier'];
    $type = $this->options['type'];

    // Remove unneeded fields when exposing the form.
    // It's shorter than redefining value_form.
    if ($type != 'latlon') {
      unset($form[$key]['latitude']);
      unset($form[$key]['longitude']);
    }
    if ($type != 'postal' && $type != 'postal_default') {
      unset($form[$key]['postal_code']);
    }
    if ($type != 'postal') {
      unset($form[$key]['country']);
    }
  }

  // Used from the distance field.
function calculate_coords() {
    if (!empty($this->value['latitude']) && !empty($this->value['longitude'])) {
      // If there are already coordinates, there's no work for us.
      return TRUE;
    }
    // @@@ Switch to mock location object and rely on location more?

    if ($this->options['type'] == 'postal' || $this->options['type'] == 'postal_default') {
      // Force default for country.
      if ($this->options['type'] == 'postal_default') {
        $this->value['country'] = variable_get('location_default_country', 'us');
      }

      // Zip code lookup.
      if (!empty($this->value['postal_code']) && !empty($this->value['country'])) {
      $location = array('country'=> $this->value['country'], 'postal_code' => $this->value['postal_code']);
		$res = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE"." zip = '%s' AND country = '%s'", $location['postal_code'], $location['country']);
		if ($r = db_fetch_array($res)) {
		$this->value['latitude'] = $r['latitude'];
		$this->value['longitude'] = $r['longitude'];
		}
        $coord = location_latlon_rough($location);
        
        if ($coord) {
          $this->value['latitude'] = $coord['lat'];
          $this->value['longitude'] = $coord['lon'];
        }
        else {
          return false;
        }
      }
      else {
        // @@@ Implement full address lookup?
        return false;
      }
    }
    if (empty($this->value['latitude']) || empty($this->value['longitude'])) {
      return false;
    }
    return TRUE;
  }
  
function query() {
	$this->value['country'] = variable_get('location_default_country', 'us');
	
    if (empty($this->value)) {
      return;
    }
    // Coordinates available?
    //DG this is always empty
    /*if (!$this->calculate_coords()) {
      // Distance set?
      if (!empty($this->value['search_distance'])) {
        // Hmm, distance set but unable to resolve coordinates.
        // Force nothing to match.
        $this->query->add_where($this->options['group'], "0");
      }
      return;
    }*/
	if (empty($this->value['postal_code'])) {
	return;
	}
	if (!empty($this->value['postal_code'])) {
		$res = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE"." zip = '%s' AND country = '%s'", $this->value['postal_code'], $this->value['country']);
		if ($r = db_fetch_array($res)) {
		$this->value['latitude'] = $r['latitude'];
		$this->value['longitude'] = $r['longitude'];
		}
	}
	if (empty($this->value['search_distance'])) {
	$this->value['search_distance'] = 1;
	}
    $this->ensure_my_table();

    $lat = $this->value['latitude'];
    $lon = $this->value['longitude'];

    $distance_meters = _location_convert_distance_to_meters($this->value['search_distance'], $this->value['search_units']);
    
    $latrange = earth_latitude_range($lon, $lat, $distance_meters);
    $lonrange = earth_longitude_range($lon, $lat, $distance_meters);
	
    // Add MBR check (always.)
    $this->query->add_where($this->options['group'], "$this->table_alias.latitude > %f AND $this->table_alias.latitude < %f AND $this->table_alias.longitude > %f AND $this->table_alias.longitude < %f", $latrange[0], $latrange[1], $lonrange[0], $lonrange[1]);

    if ($this->operator == 'dist') {
      // Add radius check.
      $this->query->add_where($this->options['group'], earth_distance_sql($lon, $lat, $this->table_alias) .' < %f', $distance_meters);
    }
    //print_r($this->query);
  }
  
}

Dirk

duplicate of #339155: Is UK postcode proximity searching actually possible in 5.x?

some issues that might be related and might help:
#363540: UK Location information not saved to table Location

and a search for issues dealing with location.uk.inc
http://drupal.org/project/issues/location?text=location.uk.inc&status=Al...
or location.xx.inc
http://drupal.org/project/issues/location?text=location.xx.inc&status=Al...

The Wikipedia UK Postcodes article on has some useful external links to resources, including:

• "Free the Postcode", a database that aims to map UK postcodes to longitude and latitudes.
• Paul Jenkins' UK Post Code Distance Calculation in PHP information
• "Distance Between UK Postcodes" at the Free Map Tools website

See also:

• Tom Anthony's "Geocoding UK Postcodes with Google Map API"
• Hugh Newsome's "How to calculate the distance between two UK postcodes using PHP"

Thanks Dirk, that's a real help.

Swap latitiude and longitude coordinates before importing.

I worked for me.

Someone told me about this, but forgot who.

I don't know why the zipcode..mysql

would have the longitude first, then the latitude.

it should be according to the schema

latitude then longitude.

UPDATE zipcodes SET latitude =(@temp:=latitude), latitude = longitude, longitude = @temp

cheers

juicytoo,

how do you swap the latitude and longitude co-ordinates?

Where do i put this?

UPDATE zipcodes SET latitude =(@temp:=latitude), latitude = longitude, longitude = @temp

Any help is appreciated thanks!