Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have a database populated with roughly 10k rows currently. All of them have a longitude/latitude based on the center of a zipcode. I found a query that I am now attempting to expand on, but all in all it works well to a point. However, in my example below I am trying to find things within a 25 mile radius, which in all seems to work for the most part. Most of my results do yield within the 25 mile criteria, however I am getting a handful that are way off anything from 86 miles to 800 miles off the mark.

Example: This is my center lat/lon: 37.2790669,-121.874722 = San Jose, CA Im getting results like: 33.016928,-116.846046 = San Diego, CA which is about 355 miles from San Jose.

my current query looks like:

SELECT *,(((acos(sin(($lat*pi()/180)) * sin((`latitude`*pi()/180))+cos(($lat*pi()/180)) * cos((`latitude`*pi()/180)) * cos((($lon - `longitude`)*pi()/180))))*180/pi())*60*1.1515) AS `distance` FROM `geo_locations` HAVING `distance` <= 25 ORDER BY `distance` ASC" 
like image 676
chris Avatar asked Jan 24 '12 21:01

chris


People also ask

How do you find the distance between two latitude longitude points in MySQL query?

Heres is MySQL query and function which use to get distance between two latitude and longitude and distance will return in KM. SELECT getDistance($lat1,$lng1,$lat2,$lng2) as distance FROM your_table. Almost a decade later, this function gives THE SAME results as Google Maps distance measurement.

How do you represent latitude and longitude in a database?

Latitude & longitude values can be represented & stored in a SQL database using decimal points (Decimal degrees) rather than degrees (or Degrees Minutes Seconds). Depending on the accuracy you may require, you can decide how many decimal points you need to keep latitude & longitude values.

What is the datatype of latitude and longitude in SQL?

Lat/Long is a position format (equivalent to UTM, MGRS, NZTM etc), whereas WGS84 is a datum - a reference system that position coordinates are applied to. You need to know both your coordinates and which datum you are using, although for online use it is almost always WGS84.


1 Answers

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.

like image 121
Gordnfreeman Avatar answered Oct 02 '22 13:10

Gordnfreeman