Anthony,
As Bruce noted, you can greatly improve efficiency by using a WHERE clause,
but as you may be quoting a first formula I posted 'from first principles',
but which gives inaccurate results, I ought to correct it. Here is a much
more accurate formula for distance in miles...
3963 * acos(cos(radians(90-lat1)) * cos(radians(90-lat2)) +
sin(radians(90-lat1)) * sin(radians(90-lat2)) * cos(radians(lon1-lon2)))
(note: you can also find estimates of the earth's radius of 3959 and 3955)
Given a table named locations with a bit of data ...
CREATE TABLE locations (
id int(10) unsigned NOT NULL auto_increment,
name char(20) NOT NULL default '',
lat double NOT NULL default '0',
lon double NOT NULL default '0',
PRIMARY KEY (`id`)
);
INSERT INTO locations VALUES (0,'New York', 40.7695, -73.9512),
(0,'Boston', 42.3512, -71.0536),
(0,'San Francisco', 37.775, -122.417),
(0,'San Diego', 32.815, -117.136);
(note: lat and lon are in decimal degrees)
then this query, using the above formula
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
gives pretty good results.
HTH
PB
-----
Hi,
I need to calculate distance from a point and for the select statment I
have
this
SELECT userid FROM place WHERE acos( cos($longitude) * cos($latitude) *
cos(place.latitude) *cos(place.longitude) +cos($longitude) *
sin($latitude)
* cos(place.latitude) * sin(place.longitude) +sin($longitude) *
sin($latitude) ) * 3963 <= 1000);
Would you consider this HORRIBLY inefficient or GOOD.
if it is horrible how can i make it efficient?
Anthony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
