CODEWITHSUNDEEP
sqlpostgresqlpostgisprestoamazon-athena
paul
paul

Reputation: 599

Getting all Buildings in range of 5 miles from specified coordinates

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

Upvotes: 6

Views: 4155

Answers (3)

Cristián Vargas Acevedo
Cristián Vargas Acevedo

Reputation: 713

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

Upvotes: 0

Jim Jones
Jim Jones

Reputation: 19653

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

enter image description here

ST_DWithin

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)

ST_Distance

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)
  • Mind the parameters order with 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;

Upvotes: 15

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

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.

Upvotes: 6

Related Questions