0

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

1

Seems to me that you'd use ST_BUFFER to buffer the original point by 5 miles, then ST_CONTAINS to find the buildings it contains.

My SQL is a bit iffy, but maybe something like:

SELECT ST_CONTAINS(SELECT ST_BUFFER(ST_Point(-84,33), 5), SELECT ST_POINT(lng, lat) FROM Buildings)

What have you tried so far?

  • 1
    This is incorrect. Since lat and lon correspond to WGS84, the coordinates are in degrees and need to be reprojected to a CRS that uses km / mi, before you can use ST_BUFFER the way you do here. – shreyasgm Nov 5 '19 at 19:39
1

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.

see: https://en.wikipedia.org/wiki/Decimal_degrees

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/

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.