How to Query All Rows Within a 5-Mile Radius of My Coordinates

How do I query all rows within a 5-mile radius of my coordinates?

I did a combo of Erwin's and Patrick's answers.

-- Add geography column
ALTER TABLE googleplaces ADD COLUMN gps geography;
UPDATE googleplaces SET gps = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX googleplaces_gps ON googleplaces USING gist(gps);

SELECT *
FROM my_table
WHERE ST_DWithin(gps, ST_SetSRID(ST_MakePoint(-72.657, 42.0657), 4326), 5 * 1609);

mySQL longitude and latitude query for other rows within x mile radius

Here is the query I use on the store locator I work with:

SELECT
    `id`,
    (
        6371 *
        acos(
            cos( radians( :lat ) ) *
            cos( radians( `lat` ) ) *
            cos(
                radians( `long` ) - radians( :long )
            ) +
            sin(radians(:lat)) *
            sin(radians(`lat`))
        )
    ) `distance`
FROM
    `location`
HAVING
    `distance` < :distance
ORDER BY
    `distance`
LIMIT
    25

:lat and :long are the points the passed by the user where lat and long are the points stored in the database.

The :distance is measured in miles, in the working version of the code the :distance is actually pulled from a drop down ranging from 10-50 miles

Changing the code to work with kilometers can be accomplished by changing 3959 (the distance from the center of the earth to its surface in miles) to 6371 (3959 miles converted to kilometers) thanks to joshhendo for that solution.

How can I get all the related rows when using IN to filter?

And EXISTS subquery should do the trick:

SELECT s.id
     , s.user_id
     , s.type
     , s.latitude
     , s.longitude
     , c.category_id AS "categories:category_id"
FROM   spaces                 s
LEFT   JOIN spaces_categories c ON c.space_id = s.id
WHERE  st_dwithin(s.geometry::geography, st_setsrid(st_point($,$), 4326)::geography, $)
AND    EXISTS (
   SELECT FROM spaces_categories x
   WHERE  x.space_id = s.id
   AND    x.category_id IN ($)
   );

There are many other ways, but this should be fastest and clearest.

The cast from geometry to geography seems worrisome for performance, though. Do you have an expression index covering that?

Related:

• How do I query all rows within a 5-mile radius of my coordinates?
• How to find intersecting geographies between two tables recursively
• Why is my spatial query slower in Postgres 13 than in Postgres 11?
• ST_DWithin Calc Meters - transform or cast?

Querying MySQL for latitude and longitude coordinates that are within a given mile radius

Spherical Law of Cosines Formula

(37 and -122 are the latitude and longitude of your radius center)

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
    * cos( radians( long ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance 
FROM myTable
HAVING distance < 50
ORDER BY distance 

Features

• Fastest
• Precision similar to Harvesine Formula

Haversine Formula

SELECT id, 3959 * 2 * ASIN(SQRT(POWER(SIN((37 - abs(lat)) * pi()/180 / 2), 2)
       + COS(37 * pi()/180 ) * COS(abs(lat) * pi()/180)
       * POWER(SIN((-122 - long) * pi()/180 / 2), 2) )) as  distance
FROM myTable
HAVING distance < 50
ORDER BY distance

Features

• Fast
• More robust to floating point errors

Note that 3959 is the Earth radius in miles. Earth radius in kilometres (km): 6371

You can find more information here

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

Determine if one coordinate is in radius of another

This post shows how to do this in SQL Server.

And here is how to do it in MySQL:

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + 
         COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance 
FROM members 
HAVING distance<='10' ORDER BY distance ASC

---

Related Topics