SQL Finding the coordinates that belong to a circle

Question

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 1

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 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.

Answer 2

I would go with Euklid. dist=sqrt(power(x1-x2,2)+power(y1-y2,2)) . It works everywhere. Maybe you have to add a conversion to the x/y-coordinates, if degrees can't be translated in km that easy.

Than you can go and select everything you like WHERE x IS BETWEEN (x-5) AND (x+5) AND y IS BETWEEN (y-5) AND (y+5) . Now you can check the results with Euklid.

With an optimisation of the result order, you can get better results at first. Maybe there's a way to take Euklid to SQL, too.