I have a SQL database set of places to which I am assigned coordinates (lat, long). I would like to ask those points that lie within a radius of 5km from my point inside. I wonder how to construct a query in a way that does not collect unnecessary records?
Since you are talking about small distances of about 5 km and we are probably not in the direct vicinity of the north or south pole we can work with an approximated grid system of longitude and latitude values. Each degree in latidude is equivalent to a distance of
km_per_lat=6371km*2*pi/360degrees = 111.195km. The distance between two longitudinal lines that are 1 degree apart depends on the actual latitude:
km_per_long=km_per_lat * cos(lat)
For areas here in North Germany (51 degrees north) this value would be around 69.98km.
So, assuming we are interested in small distances around lat0 and long0 we can safely assume that the translation factors for longitudinal and latitudinal angles will stay the same and we can simply apply the formula
SELECT 111.195*sqrt(power(lat-@lat0,2) +power(cos(pi()/180*@lat0)*(long-@long0),2)) dist_in_km FROM tbl
Since you want to use the formula in the
WHERE clause of your select you could use the following:
SELECT * FROM tbl WHERE 111.195*sqrt(power(lat-@lat0,2) +power(cos(pi()/180*@lat0)*(long-@long0),2)) < 5
The select statement will work for latitude and longitude values given in degree (in a decimal notation). Because of that we have to convert the value inside the
cos() function to radians by multiplying it with
If you have to work with larger distances (>500km) then it is probably better to apply the appropriate distance formula used in navigation like
cos(delta)=cos(lat0)*cos(lat)*cos(long-long0) + sin(lat0)*sin(lat)
After calculating the actual angle delta by applying
acos() you simply multiply that value by the earth's radius
R = 6371km = 180/pi()*111.195km and you have your desired distance (see here: Wiki: great circle distance)
Update (reply to comment):
Not sure what you intend to do. If there is only one reference position you want to compare against then you can of course precompile your distance calculation a bit like
SELECT @lat0:=51,@long0:=-9; -- assuming a base position of: 51°N 9°E SELECT @rad:=PI()/180,@fx:=@rad*6371,@fy:=@fx*cos(@rad*@lat0);
Your distance calculation will then simplify to just
with current positions in
long (no more cosine functions necessary). It is up to you whether you want to store all incoming positions in the database first or whether you want to do the calculations somewhere outside in Spring, Java or whatever language you are using. The equations are there and easy to use.