Getting all Buildings in range of 5 miles from specified coordinates

I have database table Building with these columns: name, lat, lng

Building

name

lat

lng

How can I get all Buildings in range of 5 miles from specified coordinates, for example these:

Buildings

-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

2 Answers
2

Why are you storing x,y in separated columns? I would sincerely recommend you to store them as geometry or geography to avoid the casting overhead in query time.

geometry

geography

That being said, you can compute the distance in miles using something like this:

(Test data)

CREATE TEMPORARY TABLE building (name text, lat numeric, long numeric);
INSERT INTO building VALUES ('foo',7.52,51.96);
INSERT INTO building VALUES ('bar',7.62,51.94);
INSERT INTO building VALUES ('far away ... ',10.62,59.94);


The function ST_Distance (with geography type parameters) will return the distance in meters. All you have to do is to convert meters to miles in the end. Something like:

ST_Distance

geography

SELECT *, ST_Distance(ST_GeographyFromText('POINT(7.62 51.93)'),
ST_MakePoint(lat,long)) * 0.000621371 AS distance
FROM building
WHERE
ST_Distance(ST_GeographyFromText('POINT(7.62 51.93)'),
ST_MakePoint(lat,long)) * 0.000621371 > 5

name | lat | long | distance
---------------+-------+-------+------------------
far away ... | 10.62 | 59.94 | 566.123267141404
(1 Zeile)


EDIT- Amazon Athena equivalent (distance in degrees):

SELECT *, ST_DISTANCE(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),
ST_POINT(lat,long)) AS distance
FROM building
WHERE
ST_Distance(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),
ST_POINT(lat,long)) > 5;


ST_MakePoint'

ST_Distance

geography

geometry

ST_GEOMETRY_FROM_TEXT

geometry

geography

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.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.