lukassz lukassz - 1 year ago 68
SQL Question

SQL Finding the coordinates that belong to a circle

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?

Answer Source

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:

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 pi()/180.

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

SELECT @dist:=sqrt(power(@fx*(lat-@lat0),2)+power(@fy*(long-@long0),2))

with current positions in lat and 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download