Select Within 20 Kilometers Based on Latitude/Longitude

select within 20 kilometers based on latitude/longitude

SELECT  *
FROM    mytable m
JOIN    mytable mn
ON      ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE   m.name = 'grey river'

If your table is MyISAM you may want to store your points in a native geometry format and create a SPATIAL index on it:

ALTER TABLE mytable ADD position POINT;

UPDATE  mytable
SET     position = POINT(latitude, longitude);

ALTER TABLE mytable MODIFY position NOT NULL;

CREATE SPATIAL INDEX sx_mytable_position ON mytable (position);

SELECT  *
FROM    mytable m
JOIN    mytable mn
ON      MBRContains
                (
                LineString
                        (
                        Point
                                (
                                X(m.position) - 0.009 * 20,
                                Y(m.position) - 0.009 * 20 / COS(RADIANS(X(m.position)))
                                ),
                        Point
                                (
                                X(m.position) + 0.009 * 20,
                                Y(m.position) + 0.009 * 20 / COS(RADIANS(X(m.position))
                                )
                        ),
                mn.position
                )
        AND ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE   m.name = 'grey river'

Find distance between two points using latitude and longitude in mysql

I think your question says you have the city values for the two cities between which you wish to compute the distance.

This query will do the job for you, yielding the distance in km. It uses the spherical cosine law formula.

Notice that you join the table to itself so you can retrieve two coordinate pairs for the computation.

SELECT a.city AS from_city, b.city AS to_city, 
   111.111 *
    DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
         * COS(RADIANS(b.Latitude))
         * COS(RADIANS(a.Longitude - b.Longitude))
         + SIN(RADIANS(a.Latitude))
         * SIN(RADIANS(b.Latitude))))) AS distance_in_km
  FROM city AS a
  JOIN city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7

Notice that the constant 111.1111 is the number of kilometres per degree of latitude, based on the old Napoleonic definition of the metre as one ten-thousandth of the distance from the equator to the pole. That definition is close enough for location-finder work.

If you want statute miles instead of kilometres, use 69.0 instead.

http://sqlfiddle.com/#!9/21e06/412/0

If you're looking for nearby points you may be tempted to use a clause something like this:

   HAVING distance_in_km < 10.0    /* slow ! */
    ORDER BY distance_in_km DESC

That is (as we say near Boston MA USA) wicked slow.

In that case you need to use a bounding box computation. See this writeup about how to do that. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

The formula contains a LEAST() function. Why? Because the ACOS() function throws an error if its argument is even slightly greater than 1. When the two points in question are very close together, the expression with the COS() and SIN() computations can sometimes yield a value slightly greater than 1 due to floating-point epsilon (inaccuracy). The LEAST(1.0, dirty-great-expression) call copes with that problem.

There's a better way, a formula by Thaddeus Vincenty. It uses ATAN2() rather than ACOS() so it's less susceptible to epsilon problems.

---

Edit 2022 (by Alexio Vay):
As of today the modern solution should be the following short code:

   select ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)) 

Please check out the answer of Naresh Kumar.

20 km/miles around a lat, long

I think this is an answer to what you are asking. Latitude and longitude degrees are approximately 111km apart, so you can figure that into your math. I hope this is what you are asking.

private function findServicesNearLocation($filter = array(), $lat, $lng, $distanceKilometers=0) {
    // set radius
    $difference = 0.07;
    $distance=$distanceKilometers!=0?ceil($distanceKilometers/111):0;
    // build filter
    $latitude_from = floatval($lat) - $difference - $distance;
    $latitude_to = floatval($lat) + $difference + $distance;
    $longitude_from = floatval($lng) - $difference - $distance;
    $longitude_to = floatval($lng) + $difference + $distance;

    $filter[] = "a.gps_latitude >= {$latitude_from}";
    $filter[] = "a.gps_latitude <= {$latitude_to}";
    $filter[] = "a.gps_longitude >= {$longitude_from}";
    $filter[] = "a.gps_longitude <= {$longitude_to}";

    // return services
    return $this->vendors->getVendors(implode(' AND ', $filter));
}

How to select longitude, latitude around specifc location from mysql?

SELECT *,3956*2*ASIN(SQRT(POWER(SIN((19.286558 - latitude)*pi()/180/2),2)+COS(19.286558 * pi()/180)
*COS(latitude * pi()/180)*POWER(SIN((-99.612494 -longitude)* pi()/180/2),2)))
as distance FROM table  having distance < 10 ORDER BY distance;

This will give you records within 10 Km range.
modify query for 500 meters in having clause.

Select Records which have distance of 5km or greater between them

No stored procedure, just pure unbridled sql glory:

SET @prevLong=-1.0000;
SET @prevLat=-1.0000;
SET @currDist=1.0000;
select id, diff from (
select id,
@prevLat prev_lat,
@currDist:= 6371 * 2 * (atan2(sqrt(sin(radians(@prevLat - lat)/2)
                       * sin(radians(@prevLat - lat)/2)
                       + cos(radians(lat))
                       * cos(radians(@prevLat))
                       * sin(radians(@prevLong - longi)/2)
                       * sin(radians(@prevLong - longi)/2))
                  ,sqrt(1-(sin(radians(@prevLat - lat)/2)
                           * sin(radians(@prevLat - lat)/2)
                           + cos(radians(lat))
                           * cos(radians(@prevLat))
                           * sin(radians(@prevLong
                                         - longi)/2)
                           * sin(radians(@prevLong - longi)/2))))) diff,
@prevLong prevLong,
case when @currdist > 5 then @prevLat:=lat  else null end  curr_lat,
case when @currDist > 5 then @prevLong:= longi  else null end  curr_long

  from latLong
order by id asc
) a where diff > 5

SQLFiddle to prove that magic is real:
http://sqlfiddle.com/#!9/7e4fe/19

Edit
In Codeigniter you can use variables like the following:

$this->db->query("SET @prevLong=-1.0000");
$this->db->query("SET @prevLat=-1.0000");
$this->db->query("SET @prevDist=-1.0000");

Then issue your query as normal

$query= $this->db->query("SELECT ...");

Finding Cities within 'X' Kilometers (or Miles)

This is a MySQL query that will do exactly what you want. Keep in mind things like this are approximations generally, as the earth is not perfectly spherical nor does this take into account mountains, hills, valleys, etc.. We use this code on AcademicHomes.com with PHP and MySQL, it returns records within $radius miles of $latitude, $longitude.

$res = mysql_query("SELECT
    * 
FROM
    your_table
WHERE
    (
        (69.1 * (latitude - " . $latitude . ")) * 
        (69.1 * (latitude - " . $latitude . "))
    ) + ( 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3)) * 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3))
    ) < " . pow($radius, 2) . " 
ORDER BY 
    (
        (69.1 * (latitude - " . $latitude . ")) * 
        (69.1 * (latitude - " . $latitude . "))
    ) + ( 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3)) * 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3))
    ) ASC");

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

---

Related Topics