MySQL Forums
Forum List  »  Optimizer & Parser

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
Re: improving speed of mysql distance filter
5008
October 05, 2006 09:56AM


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.