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
My try but it does not work:
SELECT ST_CONTAINS(
SELECT ST_BUFFER(ST_Point(-84.38653999999998,33.72024), 5),
SELECT ST_POINT(lat,lng) FROM "my_db"."Building" LIMIT 50
);
https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list.html
Why are you storing x,y in separated columns? I strongly suggest you to store them as geometry or geography to avoid unnecessary casting overhead in query time.
That being said, you can compute and check distances in miles using ST_DWithin or ST_Distance:
(Test data)
CREATE TABLE building (name text, long numeric, lat numeric);
INSERT INTO building VALUES ('Kirk Michael',-4.5896,54.2835);
INSERT INTO building VALUES ('Baldrine',-4.4077,54.2011);
INSERT INTO building VALUES ('Isle of Man Airport',-4.6283,54.0804);
ST_DWithin returns true if the given geometries are within the specified distance from another. The following query searches for geometries that are in 5 miles radius from POINT(-4.6314 54.0887):
SELECT name,long,lat,
ST_Distance('POINT(-4.6314 54.0887)'::geography,
ST_MakePoint(long,lat)) * 0.000621371 AS distance
FROM building
WHERE
ST_DWithin('POINT(-4.6314 54.0887)'::geography,
ST_MakePoint(long,lat),8046.72); -- 8046.72 metres = 5 miles;
name | long | lat | distance
---------------------+---------+---------+-------------------
Isle of Man Airport | -4.6283 | 54.0804 | 0.587728347062174
(1 row)
The function ST_Distance (with geography type parameters) will return the distance in meters. Using this function all you have to do is to convert meters to miles in the end.
Attention: Distances in queries using ST_Distance are computed in real time and therefore do not use the spatial index. So, it is not recommended to use this function in the WHERE clause! Use it rather in the SELECT clause. Nevertheless the example below shows how it could be done:
SELECT name,long,lat,
ST_Distance('POINT(-4.6314 54.0887)'::geography,
ST_MakePoint(long,lat)) * 0.000621371 AS distance
FROM building
WHERE
ST_Distance('POINT(-4.6314 54.0887)'::geography,
ST_MakePoint(long,lat)) * 0.000621371 <= 5;
name | long | lat | distance
---------------------+---------+---------+-------------------
Isle of Man Airport | -4.6283 | 54.0804 | 0.587728347062174
(1 row)
ST_MakePoint: It is longitude,latitude.. not the other way around.Demo: db<>fiddle
Amazon Athena equivalent (distance in degrees):
SELECT *, ST_DISTANCE(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),
ST_POINT(long,lat)) AS distance
FROM building
WHERE
ST_Distance(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),
ST_POINT(long,lat)) <= 5;
First thing first. If possible use Postgis not amazon-athena. Looking on documentation athena looks like the castrated version of a spatial tool.
First - Install postgis.
CREATE EXTENSION postgis SCHEMA public;
Now create geometry(if you want to use metric SRID like 3857 for example) or geography (if you want use degree SRID like 4326) column for your data.
alter table building add column geog geography;
Then transform your point data (lat,long) data to geometry/geography:
update building
set geog=(ST_SetSRID(ST_MakePoint(lat,long),4326)::geography)
Next create spatial index on it
create index on buildings using gist(geog);
Now you are ready for action
select *,
st_distance(geog, ST_makePoint(-84.386,33.72024))/1609.34 dist_miles
from building
where st_dwithin(geog, ST_makePoint(-84.38653999999998,33.72024),5*1609.34);
Few words of explenations: Index is useful if you have many records in your table. ST_Dwithin uses index when st_distance doesn't so ST_dwithin will make your query much faster on big data sets.
For aws Athena , try to use this for calculte aprox distance in degrees
decimal_degree_distance = 5000.0 * 360.0 / (2.0 * pi() * cos( radians(latitud) ) * 6400000.0)
where 5000.0 y distance in meters
is good for near ecuador places
