Search code examples
sqlgeospatialpostgisaws-glueamazon-athena

Getting all Stores in range of 5 kilometers from specified coordinates in AWS Athena

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

Solution

  • 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] ));