Search code examples
sqlpostgresqlpostgisprestoamazon-athena

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

Solution

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