Getting All Buildings in Range of 5 Miles from Specified Coordinates

Getting all Buildings in range of 5 miles from specified coordinates

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

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;

Getting all Stores in range of 5 kilometers from specified coordinates in AWS Athena

A few years back I created a radial search (as the crow flys) of all businesses X miles from a specific location. I give the longitude and latitude of my current position, and the radius size I want to use.
It uses a prepared SQL statement - you should be able to modify it for your own purposes. It used PHP but should be simple to amend.

For my project, longitude (_lng) and latitude (_lat) were in separate columns.

The following SQL creates a "_distance" column from a radius calculation and then orders it by distance ascending.

$sql = "SELECT a.id,  a._name, a._address, a._lat, a._lng, ( 3959 * acos( cos( radians(%f ) ) * cos( radians( a._lat ) ) * cos( radians( a._lng ) - radians(%f ) ) + sin( radians(%f) ) * sin( radians( a._lat ) ) ) ) AS _distance FROM {$tablename} a  HAVING _distance < %d ORDER BY _distance ASC";
$res = $conn->get_results($conn->prepare($sql, [$current_lat_pos,$current_long_pos,$current_lat_pos,$provided_radius] ));

Finding Locations Within A Specific Distance using PostGIS data within postgress

To query geometries within a given radius you have to use ST_DWithin:

SELECT * FROM t
WHERE
  ST_DWithin(
    the_geom::geography,
    ST_MakePoint(longitude,latitude),1609.344); -- 1609.34 metres = 1 mile;

The geography cast enables you to search using metres as unit, which can be easily converted to miles.

Related post: Getting all Buildings in range of 5 miles from specified coordinates

Find records by given latitude and longitude which intersects and circle within 2 mile radius using PostGIS?

What you're looking for is ST_DWithin, which will check if records intersect within a given buffer. In order to use it with miles you better cast the geometry column to geography, as it then computes distances in metres:

For geography: units are in meters and distance measurement defaults to use_spheroid=true. For faster evaluation use use_spheroid=false to measure on the sphere.

SELECT * FROM us_census_zcta 
WHERE 
  ST_DWithin(
    geom::geography, 
    ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4269)::geography,
    3218.688); -- ~2 miles

Keep in mind that this cast might affect query performance if the indexes aren't set properly.

See also: Getting all Buildings in range of 5 miles from specified coordinates

PostGIS Query always brings back all results

Using parameters of type geography you get the returned distance in meters, therefore you need to convert it to miles in case you prefer to work with this unit of measurement. If you can cope with degrees, just stick to geometry.

WITH locations (geolocation) AS (
  VALUES ('POINT (52.6323202 1.2947649)'),
         ('POINT (52.6294342 1.2936336)'),
         ('POINT (52.6277909 1.2909079)'),
         ('POINT (52.6260535 1.2952051)')
) 
SELECT *
FROM locations 
WHERE ST_DWithin(
          geoLocation::geography, 
          'POINT(52.6219322 1.2630061)'::geography, 1609*2.2) ;

         geolocation          
------------------------------
 POINT (52.6294342 1.2936336)
 POINT (52.6277909 1.2909079)
(2 Zeilen)

EDIT: @JGH pointed out that ST_Distance does not use a spatial index and my previous suggestion was to use it instead of ST_DWithin. It means I was wrong with my preference for ST_Distance :) Here is anyway how to achieve similar results with ST_Distance for those still willing to use it:

WITH locations (geolocation) AS (
  VALUES ('POINT (52.6323202 1.2947649)'),
         ('POINT (52.6294342 1.2936336)'),
         ('POINT (52.6277909 1.2909079)'),
         ('POINT (52.6260535 1.2952051)')
) 
SELECT *
FROM locations 
WHERE ST_Distance(
          geoLocation::geography, 
          'POINT(52.6219322 1.2630061)'::geography) * 0.000621371 > 2.2 ;

         geolocation          
------------------------------
 POINT (52.6323202 1.2947649)
 POINT (52.6260535 1.2952051)
(2 Zeilen)

Further reading: Getting all Buildings in range of 5 miles from specified coordinates

Get logs within a given radius with MySql or Postgres

In PostgreSQL (with PostGIS) the function you're looking for is called ST_DWithin. To use it with metres you either have to ST_Transform your coordinates to a SRS that has metre as unit or use geography instead of geometry. The example below creates a point with ST_MakePoint in query time, cast it to geography and applies the filter with ST_DWithin and the point mentioned in your question within a radius of 1000 metres.

WITH j (id,lat,lon) AS ( VALUES
  (001,19.4400570537131,-99.1270470974249),
  (002,19.437904276995 ,-99.1286576775023),
  (003,19.4360705910348,-99.1297865731994),
  (001,19.4424869116657,-99.1238332599196)
) 
SELECT 
  id,
  ST_Distance(
    ST_MakePoint(lon,lat)::geography,
    ST_MakePoint(-99.1270470974249,19.4400570537131)::geography) AS distance,
  ST_MakePoint(lon,lat)::geography AS geom
FROM j 
WHERE ST_DWithin(
        ST_MakePoint(lon,lat)::geography,
        ST_MakePoint(-99.1270470974249,19.4400570537131)::geography,1000);

 id |   distance   |                        geom                        
----+--------------+----------------------------------------------------
  1 |            0 | 0101000020E6100000781F268A21C858C067123E94A7703340
  2 | 292.22521599 | 0101000020E61000001C5069ED3BC858C0D878A47E1A703340
  3 |   526.781174 | 0101000020E61000007CD6576C4EC858C0EA3D7F52A26F3340
  1 |  431.5655003 | 0101000020E6100000C16056E2ECC758C021837ED246713340

Note: I strongly suggest you to store these points in a geometry or geography column, and to properly index them. Creating geometries out of separated latitude and longitude values in query time creates an unnecessary overhead, and it might slow down your queries significantly. Also, in case you're not working in the microscopy realm, consider reducing the precision of your points ;)

Further reading:

• ST_Distance
• Measuring accuracy of latitude and longitude
• Getting all Buildings in range of 5 miles from specified coordinates

---

Related Topics