Getting all Buildings in range of 5 miles from specified coordinates

by

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;

More Related Contents:

  1. convert Postgres geometry format to WKT
  2. Optimize GROUP BY query to retrieve latest row per user
  3. Aggregate columns with additional (distinct) filters
  4. Computed / calculated / virtual / derived columns in PostgreSQL
  5. Check if value exists in Postgres array
  6. Insert data in 3 tables at a time using Postgres
  7. What is the difference between single quotes and double quotes in PostgreSQL?
  8. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  9. sql ORDER BY multiple values in specific order?
  10. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  11. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  12. Postgres unique constraint vs index
  13. How to select id with max date group by category in PostgreSQL?
  14. Permanently Set Postgresql Schema Path
  15. PostgreSQL ORDER BY issue – natural sort
  16. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  17. Slow simple update query on PostgreSQL database with 3 million rows
  18. How do I alter the position of a column in a PostgreSQL database table?
  19. Return setof record (virtual table) from function
  20. Alphanumeric sorting with PostgreSQL
  21. Transposing an sql result so that one column goes onto multiple columns
  22. How to convert PostgreSQL 9.4’s jsonb type to float
  23. postgres – where in (list) – column does not exist
  24. incomplete information from query on pg_views [duplicate]
  25. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  26. How to get a list column names and datatypes of a table in PostgreSQL?
  27. Referencing current row in FILTER clause of window function
  28. Postgresql query between date ranges
  29. Why isn’t row level security enabled for Postgres views?
  30. Is there a postgres CLOSEST operator?

Leave a Comment