1

I have database table in Athena named stores with these columns: name, location

How can I get all store names in range of 5 kilometers from specified coordinates, for example these:

enter image description here

Rahul Diggi
  • 288
  • 2
  • 16
  • Perhaps this answer is what you're looking for: https://stackoverflow.com/a/51889638/2275388 – Jim Jones Jun 15 '22 at 07:38
  • @JimJones These answers aren't working for me in Athena. I think those answer are based on PostGIS, whereas I want to achieve in Athena. Thank you. – Rahul Diggi Jun 15 '22 at 08:11
  • The very last part of my answer addresses Amazon Athena - at least the OP said it worked at that time. Check it out again https://stackoverflow.com/a/51889638/2275388 – Jim Jones Jun 15 '22 at 08:53
  • @JimJones, Amazon Athena query you mentioned works perfectly. Need a small help here, The distance is in degree, How do I need to conver it to Kilometers? multiply it by 111? Sorry to comment here as I am unable to comment it in you original answer – Rahul Diggi Jun 15 '22 at 10:12
  • The easiest way would be to cast the `geometry` column to `geography`. Can Amazon Athena handle it? e.g. `SELECT geom_column::geography FROM mytable` – Jim Jones Jun 15 '22 at 10:20
  • or in case long/lat are split in numeric columns `SELECT ST_POINT(long,lat)::geography FROM mytable` – Jim Jones Jun 15 '22 at 10:22

2 Answers2

3

Athena is using Presto internally which in turn supports geospatial functions including ST_Distance:

ST_Distance(Geometry, Geometry) -> double
Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.

ST_Distance(SphericalGeography, SphericalGeography) -> double
Returns the great-circle distance in meters between two SphericalGeography points.

And ST_Point to create a point

ST_Point(x, y) → Point Returns a geometry type point object with the given coordinate values.

Use to_spherical_geography() function to convert a geometry object to geography object. For example, ST_Distance(ST_Point(-71.0882, 42.3607), ST_Point(-74.1197, 40.6976)) returns 3.4577 in the unit of the passed-in values on the euclidean plane, while ST_Distance(to_spherical_geography(ST_Point(-71.0882, 42.3607)), to_spherical_geography(ST_Point(-74.1197, 40.6976))) returns 312822.179 in meters.

select ST_Distance(ST_Point(-71.0882, 42.3607), ST_Point(-74.1197, 40.6976)), 
    ST_Distance(to_spherical_geography(ST_Point(-71.0882, 42.3607)), to_spherical_geography(ST_Point(-74.1197, 40.6976)))

Output:

_col0 _col1
3.457729581676387 312822.1793690028
Guru Stron
  • 102,774
  • 10
  • 95
  • 132
2

A few years back I created a radial search (as the crow flys) of all businesses X miles from a specific location. I give the longitude and latitude of my current position, and the radius size I want to use. It uses a prepared SQL statement - you should be able to modify it for your own purposes. It used PHP but should be simple to amend.

For my project, longitude (_lng) and latitude (_lat) were in separate columns.

The following SQL creates a "_distance" column from a radius calculation and then orders it by distance ascending.

$sql = "SELECT a.id,  a._name, a._address, a._lat, a._lng, ( 3959 * acos( cos( radians(%f ) ) * cos( radians( a._lat ) ) * cos( radians( a._lng ) - radians(%f ) ) + sin( radians(%f) ) * sin( radians( a._lat ) ) ) ) AS _distance FROM {$tablename} a  HAVING _distance < %d ORDER BY _distance ASC";
$res = $conn->get_results($conn->prepare($sql, [$current_lat_pos,$current_long_pos,$current_lat_pos,$provided_radius] ));
Delmontee
  • 1,898
  • 2
  • 26
  • 44