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
