Skip navigation links

MySQL Forums :: Optimizer & Parser :: improving speed of mysql distance filter


Advanced Search

Re: improving speed of mysql distance filter
Posted by: John Campbell ()
Date: October 05, 2006 09:56AM

I assume you don't want to calculate the distance from one point to every other point, becuase if you do, there is no way to avoid having the database calculate the distance to each point. Some distance formula optimizations would be possible but you can't avoid a calculation for each row.

However, if you want to find all points within say 1 mi, then you need to first filter the set of points by lat and lng. Below is an example

Slow:
SELECT DistanceFormula(fld.lat1,fld.lng1, lat2, lng2) AS Dist WHERE Dist < 1mi

Fast (Assuming lat and lng fields are indexed)
SELECT DistanceForumla(fld.lat1,fld.lng1, lat2, lng2) AS Dist WHERE fld.lat1 < lat2 + offset AND fld.lat1 > lat2 - offset AND fld.lng1 > lng2 - offset AND fld.lng1 < lng2 + offset AND Dist < 1 km

The offset should be a degree representation that is always more than 1 km. (e.g. (1mi * 360 / circumference of the earch) ) The second method allows the database to filter the set of points to a smaller set, and then calculate the distance from a point to a much smaller set.

Method 2 essentially draws a box around the start point, gets all the points inside the box, then calculates the distance from the start point to each point in the box. It is important to make sure the box is bigger than the filtering distance.

Hope this helps.

Options: ReplyQuote


Subject Views Written By Posted
improving speed of mysql distance filter 8090 aubrey falconer 05/19/2006 05:27PM
Re: improving speed of mysql distance filter 2914 aubrey falconer 06/17/2006 02:52PM
Re: improving speed of mysql distance filter 2818 Bob Field 06/17/2006 03:12PM
Re: improving speed of mysql distance filter 3816 Joost 06/28/2006 01:40AM
Re: improving speed of mysql distance filter 4663 John Campbell 10/05/2006 09:56AM
Re: improving speed of mysql distance filter 2650 Joost 06/28/2006 11:27AM
Re: improving speed of mysql distance filter 2951 Peter Brawley 06/29/2006 07:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.