1

i am using drupal 6 gmap and location module to save the users latitude and longitude in database. I need to create a service in which i am returned the places list in the order of nearest first. I have all the rest of the code ready and would be all set to go if you guys could share the SQL

  • If you anticipate a small number of locations, a query like Charlie S below suggests would be reasonable. But, as the number of locations you have to calculate your distance from grows, it becomes quite an expensive query to run. At that point, you have to "preprocess" your query with something like "+/-1 degree" from lat and long and then run it against only those, or those that are in the same zip code to start with, etc. – Jimajamma Feb 18 '12 at 22:30
1

You have to play around location views. Add a filter and argument to sort by proximity.

1

The location module has a function called earth_distance_sql() that will generate the needed trig functions for an SQL distance query.

0

Here is some SQL I use to achieve something like this:

$sql = "SELECT zip_code, lat, lon, SQRT( POW( 69.1 * ( lat - " . $this->session->userdata['latitude'] . " ) , 2 ) + POW( 69.1 * ( " . $this->session->userdata['longitude'] . " - lon ) * COS( lat / 57.3 ) , 2 ) ) AS distance FROM zip_code ORDER BY distance ASC LIMIT 1";

This is hitting a table with rows like:

zip_code | lat  | lon
12345    | 40.2 | -92

So you would need to amend for your table.

  • This is a trivial query when you have a small number of locations in your database, but just realize how expensive that query will be if you have an extensive number of them. As an example, the zipcode table that you can import for the locations module has ~43000 rows and I'd certainly not be wanting to hit that with this query on a popular page/load. – Jimajamma Feb 18 '12 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.