## Saturday, August 17, 2013

### Calculate Geo Distance in SQL query

The follow SQL query computes the geo distance between two points ([LAT_CENTER], [LON_CENTER]) and ([LAT1], [LON1]), where ([LAT1], [LON1]) is taken from a datatable zipcensus:
```SELECT ACOS(COS(centerlatrad)*COS(latrad)*COS(centerlongrad - longrad) + SIN(centerlatrad)*SIN(latrad)) * radius as distcirc FROM (SELECT latitude-([LAT_CENTER]) as difflat, longitude - ([LON_CENTER]) as difflong, latitude*PI()/180 as latrad, [LAT_CENTER]*PI()/180 as centerlatrad, longitude*PI()/180 as longrad, [LON_CENTER]*PI()/180 as centerlongrad, 3949.9 as radius FROM zipcensus zc WHERE latitude = [LAT1] AND longitude = [LON1]) zc) zc
```

The follow SQL query computes the top 10 nearby points to ([LAT_CENTER], [LON_CENTER])
```SELECT TOP 10 zc.*, ACOS(COS(centerlatrad)*COS(latrad)*COS(centerlongrad - longrad) + SIN(centerlatrad)*SIN(latrad)) * radius as distcirc FROM (SELECT latitude-([LAT_CENTER]) as difflat, longitude - ([LON_CENTER]) as difflong, latitude*PI()/180 as latrad, [LAT_CENTER]*PI()/180 as centerlatrad, longitude*PI()/180 as longrad, [LON_CENTER]*PI()/180 as centerlongrad, 3949.9 as radius FROM zipcensus zc) zc) zc ORDER BY distcirc
```

The formula uses several trigonometric functions, so the innermost query converts all the latitudes and longitudes to radians. In addition, this method uses the radius of the earth, which is taken to be 3,949.9 miles, the unit of the distance returned (i.e. distcirc) is in miles