1

I have a table with a list of users, which looks something like; 

Users..  ( id, username, lat, long )

I have a location table which looks like this:

Locations.. ( id, name, lat, lng, bound_ne_lat, bound_ne_lng, bound_sw_lat, bound_sw_lng

What I'm trying to do, is something like;

SELECT Users.*, Locations.name as NearestTown
FROM Users
INNER JOIN Locations ON ( .. lookup nearest town from Locations based on Users lat lng coords, return only the nearest result .. )

I did think about doing some kind of subquery, like;

INNER JOIN Locatios ON Locations.id IN (SELECT id FROM Locations ORDER BY (..distance query) desc limit 1

But then I found out I couldn't pass the users lat / lng into to the sub query for each result.

The current formula I do use however to calculate the distance between the 2 points is; 

 (3956 * 2 * ASIN(SQRT( POWER(SIN((@sourceLat - table.lookupLat) *  pi()/180 / 2), 2) +COS(@sourceLat * pi()/180) * COS(table.lookupLat * pi()/180) * POWER(SIN((@sourceLng - table.lookupLng) * pi()/180 / 2), 2) ))) as Distance,

However, how could I use this in a subquery [if the best option] when I cannot pass in the @sourceLat and @sourceLng for each result .. ?

Any help, most appreciated.

ManreeRist
  • 504
  • 5
  • 12
  • And this is MySQL? Instead of $sourceLat, do `Users.lat`, and the same for lng. That should work. – DanRedux May 10 '12 at 00:31
  • Yes it is MySQL, but cannot replace $sourceLat with Users.lat because the subquery would be executed separately to the main query, Users.lat doesn't exist when the subquery is run. . – ManreeRist May 10 '12 at 00:34
  • Ah, right. Well, you KNOW you can get it working if you query for users, then for every user query for their nearest location.. It won't be much faster in pure SQL, but a whole lot more confusing. – DanRedux May 10 '12 at 00:35
  • That was my thinking, query for every user, their nearest location, but how is the question ? Cannot pass the user lat/lng into the 'get nearest location' subquery.. – ManreeRist May 10 '12 at 00:39
  • I was suggesting you use whatever language is executing this query (PHP?). Do the query to get all users, then in that language, loop through the results. What you're doing is starting to sound "magical", and you should avoid magic, cause in 3 months when you come back to this code you'll wonder how the hell it works. :) – DanRedux May 10 '12 at 00:42
  • Could do the subquery totally separate in the php while looping through the original query, but this sounds nasty, has no one got any ideas to push this through into 1 query. . ? – ManreeRist May 10 '12 at 07:59
  • I have tried answering almost similar question. Hope it will help... [Get results that fall within marker radiuses from database](http://stackoverflow.com/questions/11586708/get-results-that-fall-within-marker-radiuses-from-database) – jsist Aug 03 '12 at 18:30

0 Answers0