Calculating Distance

[Chronicle]

Hello,
I have a list of locations and a list of users (in longitude / latitude). How can I make a query in mysql so it is like (in plain english here
where longitude is between x-100 and x+100 and where latitude is between y-100 and y+100
(if x was the users latitude position, y their long)

thanks

 

[joeyjojo]

Set latlow to be x-100 and lathigh to be x+100 and select all records where latitudes is greater than latlow and less than lathigh. Same for longitude. Or is that not possible?

 

[matja]

if the distance calculation is simply for working out an order, ie, the closest location to someone else, then just work out the 'distance' with pythagoras theorem and order by that :

select location.id, min(sqrt((person.longitude-location.longitude)*(person.longitude-location.longitude)+(person.latitude-location.latitude)*(person.latitude-location.latitude)) from location;

working out the distance in 3d-space between two points on the earth's surface is a bit more tricky :

person.x = sin(person.longitude*(2*pi/360)) * radius_of_earth * cos(person.latitude*(2*pi/360))
person.y = sin(person.latitude*(2*pi/360)) * radius_of_earth
person.z = cos(person.longitude*(2*pi/360)) * radius_of_earth * cos(person.latitude*(2*pi/360))

then do the same for location.x/y/z and then :

distance = sqrt((person.x-location.x)^2 + (person.y-location.y)^2 + (person.z-location.z)^2))

- in the units you use for radius_of_earth (assuming a spherical earth, which it isn't, but close enough)

The real distance you probably want is the, equation for the length of a geodesic across the surface of a sphere, but I forgot that one. Try mathworld.wolfram.com

Just a btw: why are you using -100/+100 for your units? longitude goes from -180 to 180 and latitude from -90 to 90