How to apply filters on the basis of latitude and longitude?

user_10791828 0
Viewed 410

I want to apply filters on the basis of latitude and longitude. There is a table having column id, title, latitude, longitude (MySql) & I want to get the results in the range of 4 km as I am providing latitude and longitude of my current location. 

SELECT * FROM ads WHERE (latitude = 26.85005 AND longitude = 26.85393) AND (longitude = 80.94964 AND longitude = 80.96595) HAVING acos(sin(1.3963) * sin(latitude) + cos(1.3963) * cos(latitude) * cos(longitude - (-0.6981))) <= 0.1570
mysql php
3 Answers

yes, that is possible.

SELECT a.title,
   111.111 *
    DEGREES(ACOS(LEAST(COS(RADIANS(a.Latitude))
         * COS(RADIANS(b.Latitude))
         * COS(RADIANS(a.Longitude - b.Longitude))
         + SIN(RADIANS(a.Latitude))
         * SIN(RADIANS(b.Latitude)), 1.0))) AS distance_in_km
  FROM table AS a
  JOIN table AS b ON a.id <> b.id
  HAVING distance_in_km <= 4

Here, HAVING distance_in_km <= 4 4 is the KM range.

user_25425250

Finding locations nearby with MySQL

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

Table Structure :

id,title,lat,lng

NOTE - Here latitude = 37 & longitude = -122. So you just pass your own.

 SELECT id, 
 ( 3959 * acos( cos( radians(37) ) * 
 cos( radians( lat ) ) * 
 cos( radians( lng ) - radians(-122) ) +
 sin( radians(37) ) * 
 sin( radians( lat ) ) ) )
 AS distance FROM your_table_name HAVING
 distance < 25 ORDER BY distance LIMIT 0 , 20;

more details here: https://en.wikipedia.org/wiki/Haversine_formula

3959 - Miles = 6371 - Kilometers

user_80768310 
$latitude='28.5410297';//yours latitude
$longitude='-81.37857600000001';//yours longitude
SELECT *,(((acos(sin((" . $latitude . "*pi()/180)) * sin((`Latitude`*pi()/180))+cos((" . $latitude . "*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((" . $longitude . "- `Longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance from table_name having distance < 4 ORDER BY distance ASC"
user_101652840
Related