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:

CodePudding user response：

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:

CodePudding user response：

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