![]() ![]() IMPORTANT! Anyone using or copying these calculations MAKE SURE to use least(1.0, (.)) when passing the calculation to the acos() function. Please check out the answer of Naresh Kumar. It uses ATAN2() rather than ACOS() so it's less susceptible to epsilon problems.Īs of today the modern solution should be the following short code: select ST_Distance_Sphere( There's a better way, a formula by Thaddeus Vincenty. The LEAST(1.0, dirty-great-expression) call copes with that problem. When the two points in question are very close together, the expression with the COS() and SIN() computations can sometimes yield a value slightly greater than 1 due to floating-point epsilon (inaccuracy). Why? Because the ACOS() function throws an error if its argument is even slightly greater than 1. In that case you need to use a bounding box computation. That is (as we say near Boston MA USA) wicked slow. If you're looking for nearby points you may be tempted to use a clause something like this: HAVING distance_in_km < 10.0 /* slow ! */ If you want statute miles instead of kilometres, use 69.0 instead. That definition is close enough for location-finder work. Notice that the constant 111.1111 is the number of kilometres per degree of latitude, based on the old Napoleonic definition of the metre as one ten-thousandth of the distance from the equator to the pole. * SIN(RADIANS(b.Latitude))))) AS distance_in_km * COS(RADIANS(a.Longitude - b.Longitude)) SELECT a.city AS from_city, b.city AS to_city,ĭEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude)) Notice that you join the table to itself so you can retrieve two coordinate pairs for the computation. It uses the spherical cosine law formula. This query will do the job for you, yielding the distance in km. ![]() I think your question says you have the city values for the two cities between which you wish to compute the distance. Now I want to get distance from one point to other point. I have searched and found following query SELECT `locations`.`city`, ( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * sin( radians( Latitude ) ) ) ) AS `distance` FROM `locations` HAVING (distance < 50)Īs for as I know this query finds distance from one point to all other points. He wants to get distance of user of any other city say it is 7. For example user having city 3 is searching. My scenario is one user having a city and latitue and longtitude is searching other users distance from his city. Say a user is having a city 3 and a user is having a city 7. Now I want to get distance between two points. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |