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
1
-
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.– JimajammaFeb 18, 2012 at 22:30
Add a comment
|
3 Answers
You have to play around location views. Add a filter and argument to sort by proximity.
The location module has a function called earth_distance_sql() that will generate the needed trig functions for an SQL distance query.
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.
answered Oct 21, 2011 at 14:16
-
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. Feb 18, 2012 at 22:26