List:General Discussion« Previous MessageNext Message »
From: douglass_davis Date:January 14 2006 1:36am
Subject:Re: Geographical advice
View as plain text  

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


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