mysql latitude/longitude radius
note: this is more of a note to myself than anything
To do latitude/longitude radius from a point in mysql, first you need to have a table with latitude/longitude pairs for items to look up. You can find various databases online with geocoded data.
Now lets assume we are on a spot near Union Square in New York.
its coded location is: Latitude: 40.7383040 and Longitude: -73.99319
We assume the following distances in relation to our earth’s radius (R)
6378137 meters, 6378.137 km, 3963.191 miles, 3441.596 nautical miles
We will use these in our computation for distance from point if we want to use miles, kilo’s or meters from our starting point, if you really wanted to get crazy then 6378137 meters = 20925646.3 feet so you could literally search for something within several hundred feet of yourself.
We use these units in the following SQL
note:PI = 3.141592653589793, mysql’s pi() function returns 3.141593 so if you need finer grain granularity then use the above constant, latitude and longitude are the names of fields in my database in the query below
select asciiname,latitude,longitude, acos(SIN( PI()* 40.7383040 /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* 40.7383040 /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* -73.99319 /180)
)* 3963.191 AS distance
FROM allcountries
WHERE 1=1
AND 3963.191 * ACOS( (SIN(PI()* 40.7383040 /180)*SIN(PI() * latitude/180)) +
(COS(PI()* 40.7383040 /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* -73.99319 /180))
) < = 1.5
ORDER BY 3963.191 * ACOS(
(SIN(PI()* 40.7383040 /180)*SIN(PI()*latitude/180)) +
(COS(PI()* 40.7383040 /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* -73.99319 /180))
)
This can be used over the results of a sub-query as well, so if you have a huge dataset you can search for a squared area by just adding and subtracting from both longitude and latitude taking that result set and running this query over it. The query give back results from closest to furthest.
In tests over 6.2M records on my machine here, this took 21.3 seconds to complete without subquery first and 0.05 seconds after subquery over the 6.2M records. latitude and longitude being indexed.
Recently
August 19th, 2006 at 7:17 am
August 31st, 2006 at 1:47 am
I’ve searched the web to find a code like yours for use in a website I’m making right now. I have a table that looks like this:
CREATE TABLE `tbl_villes` (
`ville` varchar(100) collate utf8_bin NOT NULL,
`cp` int(5) NOT NULL,
`lat` decimal(12,7) default ‘0.0000000′,
`lon` decimal(12,7) default ‘0.0000000′
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
How should your code like when using my table and calculating kilometers? I see you use a where statement 1=1
Kind regards,
Robin Meeuwsen
August 31st, 2006 at 8:30 am
Hi Robin, change the statement to instead of using “ORDER BY 3963.191 * ACOS(” which is miles to use “ORDER BY 6378.137 * ACOS(”
The WHERE 1=1 is just a ‘where’ stub. For your table you will need to do a few things.
First you want to index your lat/lon fields, and then you will want to change the above query anywhere it says “latitude” to say “lat” and same with longitude, so that it matches your database. Then, in PHP you will just want to change origin points (which I did not set up as variables) and perhaps work it into a function and try it out.
For speed on large datasets you probably want to grab a square block around the origin point first by adding a mile or so to and from both lat/lon for origin and then using the above as a subselect to work from the middle out.
Cheers and let me know how you do.
September 2nd, 2006 at 10:05 pm
[…] In many countries, the latitude and longitude of cell towers is public data it is in the US as well, however when registering with FCC the companies do not need to register the broadcast cell-id which is transmitted to the phones, to make matters more complex different cell companies can broadcast different ID’s from the same towers. From this, many different projects have popped up to attempt to map cell-id to latitude and longitude and work, I assume, to refine the results for a tower. The good news is, is that the FCC being a public entity is bound by the freedom of information act and must publish (even in a nasty form) it’s data, so we have a large database of the exact location of towers, but we just don’t what those towers are called and who uses them. The idea is that we can make some educated guesses and refine the FCC data with what little information we are given by the tower itself. Basically we are going hack the cell companies data into the FCC data, which we assume will have missing towers and incomplete data as the requirement to register the tower only applies to certain tower types and heights. If you read my post about working with lat/long pairs in mysql and doing great circle searches, then you may find the following code snippets interesting as it is to work with doing computation on latitude and longitude. I worked these out by porting some and reading some math forums, they will come in handy for inferring my location in proximity of cell towers. […]
September 27th, 2006 at 2:45 pm
I must say… WOW! Thanks alot! This works great! (I had to tweak it a little, and change the constants to variables
)
Steve.
October 24th, 2006 at 6:41 am
I am having difficulty in understanding what “you probably want to grab a square block around the origin point first by adding a mile or so to and from both lat/lon for origin and then using the above as a subselect to work from the middle out.” means… could you write down a sample code to explain this. ITs confusing what to add and where to add
Thanks
Aditya
October 24th, 2006 at 11:23 am
hi
well, you need to convert your one or two miles to radians first so lets say two miles:
2*pi()/180
multiply by the r value 3963.1
(kilometers is 6378)
and you have the value to add and subtract to your latitude and longitude of your base point.
If you are familiar with MySQL subqueries (or subselects) it is not that difficult.
The idea being you want a sub group of points that are more than the latitude minus the value and less than the latitude plus the value, as well as being less that of the longitude plus the same value and greater than the longitude minus the value.
that is a quick query over a large group just addition and subtraction.. Running the complex radius query over a large group will be slower.
see:
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html
and
http://www.databasejournal.com/features/mysql/article.php/3469351
which covers “having” as an alternative
November 29th, 2006 at 9:02 pm
I have been trying to get this to work for some time now, but I get the wrong results, and my math is to faded to completely understand what’s going on…
Anyway, this is my query:
select name,lat,lng, acos(SIN( PI()* 52.373173/180 )*SIN( PI()*lat/180 )
)+(cos(PI()* 52.373173/180)*COS( PI()*lat/180) *COS(PI()*lng/180-PI()* 4.893431/180)
)* 6378.137 AS distance
FROM locator_POI
and these are my results, but they are obviously wrong (all the distances are around 2300m, while they should be between a few 100 and some 5000).
Any idea’s on what i’m doing wrong? Thnx!
+——+———+———+—————–+
| name | lat | lng | distance |
+——+———+———+—————–+
| 01 | 52.3652 | 4.88115 | 2378.6421968184 |
| 02 | 52.3629 | 4.88388 | 2378.7676859112 |
| 03 | 52.3648 | 4.88442 | 2378.6656320204 |
| 04 | 52.3818 | 4.87091 | 2377.7478699396 |
| 05 | 52.3595 | 4.87463 | 2378.9534233493 |
| 06 | 52.3531 | 4.89388 | 2379.2950088385 |
| 07 | 52.3653 | 4.89103 | 2378.6410172559 |
| 08 | 52.3728 | 4.89375 | 2378.2352251204 |
| 09 | 52.3625 | 4.91091 | 2378.7889640923 |
| 10 | 52.3652 | 4.8812 | 2378.6444561397 |
| 11 | 52.3128 | 4.94371 | 2381.4643244755 |
| 12 | 52.3633 | 4.88372 | 2378.748587718 |
| 13 | 52.3664 | 4.8933 | 2378.5798242727 |
| 14 | 52.3665 | 4.89507 | 2378.5751001532 |
| 15 | 52.3841 | 4.88472 | 2377.6286886647 |
+——+———+———+—————–+
November 30th, 2006 at 2:31 pm
here is what one of my ruby functions look like:
def self.get_count_all_by_radius_for(lat,long,radius, uid)
find_by_sql [”SELECT COUNT(*) AS count FROM view_user_notes WHERE user_id = ? AND 3963.191 * ACOS( (SIN(PI()* ? /180)*SIN(PI() * latitude/180)) + (COS(PI()* ? /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* ? /180)) ) < = ? ORDER BY updated_on, 3963.191 * ACOS( (SIN(PI()* ? /180)*SIN(PI()*latitude/180)) + (COS(PI()* ? /180)* cos(PI()* latitude/180)* COS(PI() * longitude/180-PI()* ? /180)) ) desc", uid, lat, lat, long, radius, lat, lat, long]
end
in ruby find_by_sql queries, the ‘?” (question marks) are replaced in order of appearance after the sql..
Note the ‘order by’ …
that distance is a little funky, but ordering by this way works.
December 11th, 2006 at 11:33 am
This is a very useful statement - thanks! However, I’m trying to determine the distance from center (in miles); the distance figure returned by your SQL statement is useful for ordering, but is not the distance from center. How can I adjust your sample statement to output the figure I am looking for?
December 11th, 2006 at 5:15 pm
see my distance function here: http://blog.peoplesdns.com/archives/34
you can modify the above, however you will need to compute radians as well.
over a lot of data, this will be rough.
I might write a post about this and working on getting distances back in more meaningful numbers.
December 15th, 2006 at 7:44 pm
Thanks for this great query. I do believe i found a typo, leading to some of the strange results PanMan was getting. The first time the distance is calculated in the select statement, there is a mismatch of parenthesis:
acos(SIN( PI()* 40.7383040 /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* 40.7383040 /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* -73.99319 /180)
)* 3963.191 AS distance
it should be:
acos((SIN( PI()* 40.7383040 /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* 40.7383040 /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* -73.99319 /180))
)* 3963.191 AS distance
I hope this helps.
December 15th, 2006 at 7:52 pm
I’m not sure if this is mysql specific, but i’m able to drop the last calculation in the order by clause and replace it with “order by distance” Am I overlooking something? Is my previous post incorrect?
January 12th, 2007 at 6:37 am
Hi,
you need to convert your one or two miles to radians first so lets say two miles:
2*pi()/180
multiply by the r value 3963.1
(kilometers is 6378)
This formula gives me a negligible value. Can you please explain the order of multiplicationa and confirm that this is correct?
Many Thanks
January 18th, 2007 at 5:14 am
Hi,
use this formula :
SELECT 3956 * 2 * atan2(sqrt(pow(sin(((latitude * 3.14159265359 /180.0)-(33.887306 * 3.14159265359 /180.0))/2.0),2) + cos((33.887306 * 3.14159265359 /180.0)) * cos((latitude * 3.14159265359 /180.0)) * pow(sin(((longitude * 3.14159265359 /180.0)-(-117.894628 * 3.14159265359 /180.0))/2.0),2)),sqrt(1-(pow(sin(((latitude * 3.14159265359 /180.0)-(33.887306 * 3.14159265359 /180.0))/2.0),2) + cos((33.887306 * 3.14159265359 /180.0)) * cos((latitude * 3.14159265359 /180.0)) * pow(sin(((longitude * 3.14159265359 /180.0)-(-117.894628 * 3.14159265359 /180.0))/2.0),2))))) as distance FROM TABLE_NAME
It will give proper results
I hope this will help
January 19th, 2007 at 5:42 am
I’m getting an error with this query, namely:
SELECT name, latitude, longitude, acos( (
SIN( PI( ) * 40.7383040 /180 ) * SIN( PI( ) * latitude /180 ) ) + ( cos( PI( ) * 40.7383040 /180 ) * COS( PI( ) * latitude /180 ) * COS( PI( ) * longitude /180 - PI( ) * - 73.99319 /180 ) )
) * 3963.191 AS distance
FROM geonamescities
WHERE 1 =1
AND 3963.191 * ACOS( (
SIN( PI( ) * 40.7383040 /180 ) * SIN( PI( ) * latitude /180 ) ) + ( COS( PI( ) * 40.7383040 /180 ) * cos( PI( ) * latitude /180 ) * COS( PI( ) * longitude /180 - PI( ) * - 73.99319 /180 ) )
)
February 14th, 2007 at 5:47 pm
Thanks for this formula joeldg! I have a slightly different problem though. I have a `cities` table with a `id` `latitude` and `longitude` fields. I am given a city_id and I want to write a query that selects the `id`s of all the cities that are in a certain radius of that city. Can you help?
March 30th, 2007 at 12:51 am
Thanks for doing/finding the math! Saved me a lot of time in research!
May 14th, 2007 at 2:37 am
May 20th, 2007 at 3:42 pm
“2*pi()/180
multiply by the r value 3963.1
(kilometers is 6378)”
what did i wrong:
(kilometers*pi/180)*6378
daniel
June 29th, 2007 at 9:45 pm
July 13th, 2007 at 2:43 am
Thanks for doing the tricky math Joel! I used your formula with a slight modification.
Instead of finding the closest zip codes from my geo-data table, i needed to find the nearest store locations. So instead of the criteria of 1.5 miles, i just used “top” for MS SQL or “limit” for MySQL and did an inner join with my locations table so that only zips that contain a store would be returned.
I was imaging as well that if this is tough on the database, you could pre-calculate all of the SIN and COS values for the static source Lat/Lon (the one that you are measuring from). I think it depends on the DB, but there’s a chance all those calculations are happening 3 times - once for the select, once for the criteria and finally for the sort.
Anyway, thanks again - it saved me a ton of time!
July 18th, 2007 at 9:34 pm
I don’t think the calculation described works - I was getting some very bizarre results.
I found this other source for calculations: http://www.geodatasource.com/developers.html
July 18th, 2007 at 10:27 pm
Some bizarre results were also experienced with some of the formulas used by geodatasource.com, but this one seems okay with the data I tested: http://www.geodatasource.com/samples/distance.plsql.txt
Adaptation for MS SQL Server:
CREATE FUNCTION [dbo].[yougodo_Geo_CalculateDistance]
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float,
@EarthRadius float
)
RETURNS float
AS
BEGIN
DECLARE @DegToRad decimal(10,8)
SET @DegToRad = 57.29577951
RETURN (ISNULL(@EarthRadius,0) * ACOS((sin(ISNULL(@Latitude1,0) / @DegToRad) * SIN(ISNULL(@Latitude2,0) / @DegToRad)) +
(COS(ISNULL(@Latitude1,0) / @DegToRad) * COS(ISNULL(@Latitude2,0) / @DegToRad) *
COS(ISNULL(@Longitude2,0) / @DegToRad - ISNULL(@Longitude1,0)/ @DegToRad))))
END
GO
Please note though, it’s faster to use the calculation inline rather than calling a function (as I’ve provided for convenience). I experience about a 50% increase in speed when calculation is done inline directly as part of the query.
July 24th, 2007 at 3:26 pm
Below is the query i am using
select Name,Lat,Long, acos(SIN( PI()* 59.778630/180 )*SIN( PI()*Lat/180 )
)+(cos(PI()* 59.778630/180)*COS( PI()*Lat/180) *COS(PI()*Long/180-PI()* -88.105803/180)
)* 6378.137 AS distance
FROM googledata
and why do i get
———————————————
[localhost] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Long, acos(SIN( PI()* 59.778630 /180 )*SIN( PI()*Lat/180 )
)+(cos(PI()* 59.77863′ at line 1
——————————————–
any idea and My MYSQL server is MySQL Server 4.1
Please help needed..
July 24th, 2007 at 3:38 pm
is there something like this in MSSQL….?
July 27th, 2007 at 12:08 pm
Ok i understand this statement but i don’t understand where the 40.7383040 and -73.99319 come from.. can anyone explain? Thanks!..
July 30th, 2007 at 2:47 pm
those are my example latitude and longitude, that is right in the middle of New York city, near my work.
you probably want to change those for your own stuff..
August 1st, 2007 at 7:24 am
so the 40.7383040 and -73.99319 are the lat and log you are converting? or are you converting the lat and long you are selecting from the table? The reason i’m asking this is because if i can get this to work i want to use it with our AVL (automatic vehicle location) and i would have to run the query every 2 min.. so would i just pick a lat and log in the middle of the area where the trucks would drive?
Thanks!..
Craig..
August 1st, 2007 at 4:00 pm
Craig, I am not really sure what you mean.
for what I am doing, the 40.7.. and -73.99 are the center point that I am trying to find things around.. Usually it is saying.. “Okay I am at this address and want to know what is around me from closest to a certain radius” so we geocode the start position and then show how far things are away.
For you, it sounds like you would just find the lat/long of your main office or wherever the “center” point needs to be and then set your radius to some large number..
Otherwise, you can use one of the distance formulas I posted in one of the more recent posts and then just do distances from your main office to the location in the database..
If that makes any sense…
August 1st, 2007 at 4:17 pm
This may also help
when ‘km’
return %Q[ 6378.7 * acos(sin(radians(#{mylat})) * sin(radians(locations.latitude)) + cos(radians(#{mylat})) * cos(radians(locations.latitude)) * cos(radians(locations.longitude) - radians(#{mylon})))]
when ‘miles’
return %Q[3963.0 * acos(sin(radians(#{mylat})) * sin(radians(locations.latitude)) + cos(radians(#{mylat})) * cos(radians(locations.latitude)) * cos(radians(locations.longitude) - radians(#{mylon})))]
end
August 9th, 2007 at 4:03 am
joeldg said “mysql’s pi() function returns 3.141593 so if you need finer grain granularity then use…”, but this is slightly misleading.
Mysql.com says “The default number of decimal places displayed is five, but MySQL uses the full double-precision value internally”, so MySQL actually uses 3.1415926535898 to do its calculations. Ref: http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html#function_pi
joeldg - radians function was a good find - i have been using your original sql until now!!
August 31st, 2007 at 2:53 pm
thank you so, so much for posting this!
Question: it appears to order the distance DESC, and takes far more time to do an order by (distance ASC) .. any suggestion as to how to get the 10 closest cities without doing an “order by”?
Thanks again!!
rob
August 31st, 2007 at 3:54 pm
even better might be to be able to select within a rang of coordinates .. say everything within a 50 mile radius?
thanks a lot.. very appreciative.
September 20th, 2007 at 10:40 am
SO GREAT!
you can use this combined with this great google maps example:
http://maps.forum.nu/gm_clickable_circle.html
just change a function.. and post the query to the following action..
function drawFilledCircle(){
oStatusDiv = document.getElementById(”statusDiv”);
var zoom = map.getZoom();
var centerPt = normalProj.fromLatLngToPixel(centerMarker.getPoint(), zoom);
var radiusPt = normalProj.fromLatLngToPixel(radiusMarker, zoom);
var circlePoints = Array();
with (Math) {
var radius = floor(sqrt(pow((centerPt.x-radiusPt.x),2) + pow((centerPt.y-radiusPt.y),2)));
var thickness = min(255,radius);
for (var n = 1 ; n \n’;
oStatusDiv.innerHTML += ‘Circumference on Earth: ‘ + (PI*radOnEarth*2).toFixed(3) + ‘ km\n’;
oStatusDiv.innerHTML += ‘Areal on Earth: ‘ + (PI*pow(radOnEarth,2)).toFixed(3) + ‘ km\n’;
oStatusDiv.innerHTML += ”;
oStatusDiv.innerHTML += ’select city,latitude,longitude, acos(SIN( PI()* ‘+ lat +’ /180 )*SIN( PI()*latitude/180 ))+(cos(PI()* ‘+ lat +’ /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* ‘+ lng +’ /180))* 6378.137 AS distance FROM geodatas WHERE 1=1 AND 6378.137 * ACOS( (SIN(PI()* ‘+ lat +’ /180)*SIN(PI() * latitude/180)) + (COS(PI()* ‘+ lat +’ /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* ‘+ lng +’ /180))) ‘;
map.removeOverlay(circleLine2);
circleLine2 = new GPolyline(circlePoints,’#96BDFE’,thickness,0.5);
map.addOverlay(circleLine2);
}
}
}
October 15th, 2007 at 4:11 pm
Joel,
Could you give a brief concrete example of how to work a bounding-box subquery into your code? I’ve got a piece of mysql that looks very similar to your code above. It works but runs a bit slow, since it’s joining against over 850,000 Canadian postal codes!
I’d like to filter out everything but say a 10-kilometre box around the postal code I’m interested in, but I’m new enough to SQL that I can’t figure out how to do the subquery or subselection. I’ve looked at the mysql docs but am still confused.
TIA,
Howard
January 17th, 2008 at 5:35 am
Is this correct solution for for finding closest locations latitude and longitude with given miles?
January 31st, 2008 at 2:26 am
Good Tutorial. Thnx!
Typo: In “ORDER BY 3963.191 … PI()* -73.99319 /180)))”.
You have ‘*’ and ‘-’ together, it might end up with some errors when used w/ variables e.g. *-$longitude. Just put “()” around longitude value:)
February 9th, 2008 at 10:41 am
Hi,
thanks for that calculation. i am finding it extremely accurate.
I am wondering what kind of index you are using on your tables?
I have a table with over 2 million rows, so its taking longer than I would like to bring the results back.
Has anyone optimised a table for this kind of thing before?
February 19th, 2008 at 6:52 am
Thanks for that, saved me some think time. Just wanted to point out that multiplying by 3963.191 in the ORDER part is superfluous.
March 18th, 2008 at 3:21 am
Just wanted to remark that Claude’s comment above is correct. I successfully found locations withing an 8 mile radius, and the distance in miles is accurate as verified by a similar search in another program. I also dropped the last calculation in the ORDER BY clause and just put: ORDER BY distance
Thanks to joeldg for the original post (INCREDIBLE, man) and Claude for the clarification.
March 19th, 2008 at 12:02 pm
Kev
How I originally set this is up to have a “points” table which is simply id, lat, long
Then, from there we set up a data table that has the info for the position and then uses a point_id for the GeoPoint field..
You use a indexed Double type for the lat/long and run the query against that table and then right join in the data table with the point_id’s matching.
For MySql machines, my suggestion is ReiserFS for the file system (assuming dedicated mysql machine) and make sure your my.cnf file is fully optimized for the machine.. I should write up my config stuff for determining the proper my.cnf options.For this, you want to watch your join/sort server variables to make sure things are well speedy.
MyISAM engine type is usually what I use, however for data that is queried a lot and not changed often you can use HEAP tables to increase performance a lot. HEAP tables can be flushed to disk via a cron as needed to make sure new data is saved.
Just some ideas to speed things up.
April 14th, 2008 at 2:07 pm
I have been doing radius searches for ages, but have always used php to order the results by distance, this is genius! I love it! thanks!