2

I recently stated working on gps maps. I have a database with list of lat and longitudes. when a user access my web app, I want to display places with in 1km or specified radius from user point. I tried using mysql query

SELECT * FROM Locations
WHERE (latitude between MINLATITUDE and MAXLATITUDE )
and (longitude between MINLONGITUDE and MAXLONGITUDE)

but it displays lat and longitudes between min and max, could some one tell me how to show lat and longitudes which are only with in 1km radius from user point.
Thanks.

user1677685
  • 25
  • 1
  • 7
  • This question looks similar to this stackoverflow discussion: http://stackoverflow.com/questions/3168904/sql-query-to-query-nearby-points-of-interest-based-on-lat-long-sqlite – jeffmurphy Jan 15 '13 at 01:45
  • @jeffmurphy Thanks for your post. I wounder how can I calculate the proximity, where in my database all lat and long are pre-stored. It seems like I should run a query such that it calculates proximity by checking with each record and in db I have more than 10000 records. Could you suggest best possible way? – user1677685 Jan 15 '13 at 02:49
  • @jeffmurphy I have figured out calculating the distance between lat/long using Haversine formula. Still I couldn`t find a way to calculate the distance in quicker way, now I am running my algorithm to call each record in my DB which is taking a long time. Could you suggest me any solution for this? – user1677685 Jan 15 '13 at 06:03
  • where do you do the calculation? In your database or in your web app, what database do you use and what is your web app built with? i.e C#, Vb etc.. – 03Usr Jan 15 '13 at 16:34
  • Im using C# with MySQL, im thinking it would be better to do the calculation in database and store the result in proximity column and retrieve the records basing on proximity. Could you tell me is this a better way to run the program quickly? – user1677685 Jan 16 '13 at 00:37

1 Answers1

2

For distances around 1km, Haversine is overkill. Use the much simpler equirectangular projection to get familiar x and y. Remember, the formula below lat and lon are IN RADIANS. The distance, d, is in km because the radius of the earth (R) is in km. And lat/lon are in radians (yes, I said it again). This eliminates several trig functions compared to Haversine which should make your queries faster.

var R = 6371; // radius of the earth in km
var x = (lon2-lon1) * Math.cos((lat1+lat2)/2);
var y = (lat2-lat1);
var d = Math.sqrt(x*x + y*y) * R;  // distance in km

Now, this is a web app so I'm going to assume that Santa (north pole) and the people at the Antarctica station will not use your app. With this assumption, you can organize your lat/lon points in your database on longitude. So, if someone has a lon of -76.1, you know that longitudes in your database that are less than -78 degrees are farther than 1 km. You also know longitudes greater than -74 degrees are far away as well. This will reduce the number of points you need to check in your database.

TreyA
  • 3,339
  • 2
  • 20
  • 26
  • the result which I got is wrong when I used your formula. By using haversine formula the output is correct and thanks for your explanation about searching for coordinates. – user1677685 Jan 16 '13 at 03:02
  • Are doing Haversine on every point? You need to sort your data first, then apply the above formula to the remaining points. There is no need to use Haversine to compute the distance from New York to China. You know that is already more than 1 km. The formula above is valid (http://www.movable-type.co.uk/scripts/latlong.html). It is hard to tell without seeing some of your code. – TreyA Jan 16 '13 at 10:42
  • You are right I dont need to use haversine for farther distance, but I need to find distance of one region (ex: point1 -34.9281, 138.60132 and point2 -34.9281, 138.61932) in my database there will be more of like these. I have to show only which are within 1km – user1677685 Jan 16 '13 at 13:43