Skip navigation links
# MySQL Forums :: Optimizer & Parser :: improving speed of mysql distance filter

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.

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.

Subject | Views | Written By | Posted |
---|---|---|---|

improving speed of mysql distance filter | 8307 | aubrey falconer | 05/19/2006 05:27PM |

Re: improving speed of mysql distance filter | 2975 | aubrey falconer | 06/17/2006 02:52PM |

Re: improving speed of mysql distance filter | 2890 | Bob Field | 06/17/2006 03:12PM |

Re: improving speed of mysql distance filter | 3988 | Joost | 06/28/2006 01:40AM |

Re: improving speed of mysql distance filter |
4802 | John Campbell | 10/05/2006 09:56AM |

Re: improving speed of mysql distance filter | 2735 | Joost | 06/28/2006 11:27AM |

Re: improving speed of mysql distance filter | 3045 | 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.