Sql Select Radius Search Based on Latitude Longitude

radius search by latitude / longitude

Since you're on SQL 2008, consider using the native geospatial capabilities. You can do fancy things like:

• Create a persisted computed column of geography type that represents your point.
• Create a spatial index on the computed column. This will make things like yourPoint.STDistance(@otherPoint) <= @distance efficient

Like so:

alter table [yourTable] add [p] as geography::Point(Latitude, Longitude, 4326) persisted;
create spatial index [yourSpatialIndex] on [yourTable] ([p])

declare @Latitude float = <somevalue>, @Longitude float = <somevalue>;
declare @point geography = geography::Point(@Latitude, @Longitude, 4326);
declare @distance int = <distance in meters>;

select * from [yourTable] where @point.STDistance([p]) <= @distance;

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.

SQL query, select nearest places by a given coordinates

here's the PHP formula for calculating the distance between two points:

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') 
{
   $theta = $longitude1 - $longitude2;
   $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))+
               (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
   $distance = acos($distance); $distance = rad2deg($distance); 
   $distance = $distance * 60 * 1.1515;

   switch($unit) 
   { 
     case 'Mi': break;
     case 'Km' : $distance = $distance * 1.609344; 
   } 
   return (round($distance,2)); 
}

then add a query to get all the records with distance less or equal to the one above:

$qry = "SELECT * 
        FROM (SELECT *, (((acos(sin((".$latitude."*pi()/180)) *
        sin((`geo_latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
        cos((`geo_latitude`*pi()/180)) * cos(((".$longitude."-
        `geo_longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) 
        as distance
        FROM `ci_geo`)myTable 
        WHERE distance <= ".$distance." 
        LIMIT 15";

and you can take a look here for similar computations.

and you can read more here

Update:

you have to take in mind that to calculate longitude2 and longitude2 you need to know that:

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles. This is convenient because each minute (1/60th of a degree) is approximately one mile.

A degree of longitude is widest at the equator at 69.172 miles (111.321) and gradually shrinks to zero at the poles. At 40° north or south the distance between a degree of longitude is 53 miles (85 km).

so to calculate $longitude2 $latitude2 according to 50km then approximately:

$longitude2 = $longitude1 + 0.449; //0.449 = 50km/111.321km
$latitude2 = $latitude1 + 0.450; // 0.450 = 50km/111km

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

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

---

Related Topics