Re: :::: I need help for MYSQL and PHP QUERY COMPUTATION and sorting

[Peter Brawley]

1. To compute latitudes & longitudes you should store them as numerics, eg as floats, definitely NOT as character values.

2. It's a terrible idea to use misspelled common words as column names. Longitude, not 'longtitude'.

3. Your query has an unmatched parenthesis so it generates a syntax error.

4. I haven't checked your formula, but your query references just one latitude and one longitude, which can't yield a distance.

5. Here is a great circle distance function that is reasonable accurate (eg it gives the distance between NY and LA as 3941.18 km):

SET GLOBAL log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS GeoDistKM;
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
DECLARE pi, q1, q2, q3 FLOAT;
DECLARE rads FLOAT DEFAULT 0;
SET pi = PI();
SET lat1 = lat1 * pi / 180;
SET lon1 = lon1 * pi / 180;
SET lat2 = lat2 * pi / 180;
SET lon2 = lon2 * pi / 180;
SET q1 = COS(lon1-lon2);
SET q2 = COS(lat1-lat2);
SET q3 = COS(lat1+lat2);
SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
RETURN 6378.388 * rads;
END;
|
DELIMITER ;

Once your latitude & longitude cols are changed to floats, the above function gives the distance between your first two Abbotsfords as follows:

SELECT GeoDistKM( c1.latitude,c1.longitude,c2.latitude,c2.longitude) AS Dist
FROM tbl_city c1
INNER JOIN tbl_city c2 ON c1.id=2 AND c2.id=3;

+-----------------+
| Dist |
+-----------------+
| 3689.8120117188 |
+-----------------+

PB



Edited 1 time(s). Last edit at 04/26/2006 11:04AM by Peter Brawley.