# Querying Geospatial data in Athena database, getting rows in range of 5 miles from specified coordinates

I have database table Building with these columns: name, lat, lng

How can I get all Buildings in range of 5 miles from specified coordinates, for example these:

-84.38653999999998

33.72024

?

https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list.html

It is rather unnecessary complicated in Athena, because

1. it only supports Geometry (i.e. planar or Cartesian) objects, but does not support Geography objects (on Earth's sphere).
2. it only supports single WGS84 projection and thus does not have ST_TRANSFORM that would allow you to use other projections.

In systems that support Geography, you would just construct two geography points, and checked whether they are within 5 miles using ST_DWithin. E.g. in BigQuery:

... WHERE ST_DWithin(ST_GeogPoint(lon, lat), ST_GeogPoint(clon, clat), 5 * 1609.34)

In systems that support various projections, you would chose a suitable equidistant projection and work within it.

Athena has rather rudimentary GIS support, so you would need to either

1. Don't use any of their geospatial functions, just use Haversine formula directly, see e.g. https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula, or
2. if you are OK with impresize results - use Athena's ST_Distance method with suitable angular distance (but that will stretch the circle in West-East direction).

Update: Athena 2 has to_spherical_geography function that should make it much simpler.

The distance or radius value of the ST_BUFFER is a function of what units you are using for the geometry. If you are using a projected value, such as EPSG:3857, Web Mercator coordinate system popularized by Google, then your value would be in meters. So 5 miles would be about 8000 meters.

But -84.38653 33.72024 are long/lat values, so they are geographic (angular) rather than geometric(planar). Maybe think slices of an orange vs a 2D x/y plane? That means that for this work you need to know what decimal degree value approximates 5 miles at your current position on the planet. That's because DD to meters varies as a function of where you are North/South or latitude. At the equator it is about 111320 meters per DD. so to get 5 miles would be about 8046.72/111320 = .072284 DD.

Try this in the Athena query editor. SELECT ST_GEOMETRY_TO_TEXT(ST_BUFFER(ST_GEOMETRY_FROM_TEXT('POINT (-84.38653 33.72024)'),.072284))

Then past the result into something like https://clydedacruz.github.io/openstreetmap-wkt-playground/