lukassz - 1 month ago 10x

SQL 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

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.

Source (Stackoverflow)

Comments