List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:January 10 2006 2:27pm
Subject:Re: Geographical advice
View as plain text  
If you visualise your search area as a circle around your 'target' coordinates, then you
can eliminate many of the irrelevant rows by search for coordinates that fall within a
square surrounding that circle.

So, imagine a simple grid with target coordinates of 6,8 and a search radius of 3.
Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11.

I'm not certain but I think MySQL should be able to used a combined index of (x,y) for
that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an
index.

HTH,
James Harvard

At 12:01 pm +0000 10/1/06, Ben Clewett wrote:
>I have a need to locate (x,y) coordinates from mysql where they are close to another
> coordinate.  For instance, all pizza bars near my car.
>
>Example:  Searching for points closer than z to (i,j) using Pythagoras:
>
>SET i = 10;
>SET j = 10;
>SET z = 30;
>SELECT x, y
>  FROM coordinates
>  WHERE POW(x - @i, 2) + POW(y - @i, 2) < POW(@z, 2)
>
>Big problem!  Must searches every row.  Linear indexing not able to help here.
Thread
Geographical adviceBen Clewett10 Jan
  • Re: Geographical adviceJames Harvard10 Jan
    • RE: Geographical adviceAndy Eastham10 Jan
  • Re: Geographical advicedouglass_davis14 Jan
RE: Geographical adviceBen Clewett10 Jan