Hi Anthony,
'As Miles' just names that column in the output, and if you want to retrieve
locations within a radius, you need that name, eg
SELECT loc1.name, loc1.lat, loc1.lon, loc2.name, loc2.lat, loc2.lon,
3963 * acos(cos(radians(90-loc1.lat)) * cos(radians(90-loc2.lat)) +
sin(radians(90-loc1.lat)) * sin(radians(90-loc2.lat))
* cos(radians(loc1.lon-loc2.lon))) AS Miles
FROM locations AS loc1
INNER JOIN locations AS loc2 ON loc1.name = 'New York' AND loc2.name <>
'New York'
HAVING MILES < 200;
'Havng' is slow, so if such queries are frequent, for speed you probably
will want to pre-populate something like
CREATE TABLE distances (
int from_id NOT NULL,
int to_id NOT NULL;
double distance NOT NULL )
with results of the above query for all combinations of locations taken two
at a time. If there are 10,000 rows in locations, distances will have about
50 million pre-computed distances.
HTH
PB
-----
----- Original Message -----
From: Anthony Ward
To: Peter Brawley ; [EMAIL PROTECTED]
Sent: Wednesday, June 04, 2003 5:02 AM
Subject: Re: efficient query or not?
Hi,
You mention this
SELECT loc1.name, loc1.lat, loc1.lon, loc2.name, loc2.lat, loc2.lon,
3963 * acos(cos(radians(90-loc1.lat)) * cos(radians(90-loc2.lat)) +
sin(radians(90-loc1.lat)) * sin(radians(90-loc2.lat))
* cos(radians(loc1.lon-loc2.lon))) AS Miles
FROM locations AS loc1
INNER JOIN locations AS loc2 ON loc1.id = 1 AND loc2.id = 2
the only problems is that I have tremendous problem understanding AS,
I think it is creating a "virtual" column name called Miles.
Also, if i search people that are about 100miles or XXmiles away from me,
how do i check that because
I don't understand the use of loc1.id=1 and loc2.id=2
Thanx for the help.
Anthony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
