Ben Clewett wrote:
> Hi,
>
> 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.
>
>
> I have been reading the Geographic Spacial extensions to MySQL. Which
> enable me to store the coordinates in a far more useful form. But do
> not seem to offer me the type of index I need.
>
>
> This must be a common problem, is there any person who can help me?
>
> Thanks in advance,
>
> Ben Clewett.
>
>
I wish mysql had the ability to automatically convert points to
different coordinate systems then get the distance like PostGIS, but it
doesn't. Anyway, I believe this will get you distance in miles:
SELECT(
DEGREES(
ACOS(
SIN(RADIANS( latitude1 )) * SIN(RADIANS( latitude2 ))
+ COS(RADIANS( latitude1 )) * COS(RADIANS( latitude2 ))
* COS(RADIANS( longitude1 - longitude2 ))
) * 60 * 1.1515
)
) AS distance
where latitude1, latitude1, longitude2, longitude2 are columns
Just SORT that by distance.
You can also do HAVING distance < 20 for example, to cut down on the number of points
returned.
--
http://www.douglassdavis.com
