Hey, MySQL is still missing the 'distance' function (distance between two points) so I wrote a little one for my convenience, hope it helps others, here it is:
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`distance` $$
CREATE FUNCTION `distance`(a POINT, b POINT) RETURNS double
DETERMINISTIC
COMMENT 'distance function'
BEGIN
RETURN round(glength(linestringfromwkb(linestring(asbinary(a), asbinary(b)))));
END $$
DELIMITER ;
If you think that this kind of utility functions should ship with MySQL add your voice to this feature request
Subscribe to:
Post Comments (Atom)
6 comments:
What is the metric of the return value of this function? Is it in miles, kms or some other unit?
it has NO metrics. if you store metric-measured points, it returns metric distance and so on.
I like the distance formula, Thanks.
I'm having a problem.
1) How can I force the distance to be in miles? I'm storing the point as long/lat (xxx.xxxxx)
2) As shown below, I have 2 points which have different lat/long, but the distance between them is 0, why is this? I'm thinking I don't have the precision I need in the long/lat. (They are truly about 8 miles apart) the 3rd record is about 1600 miles apart, but it's showing 15. so my scale is way off.
Thanks,
mysql> SELECT dest.id, dest.display_name, dest.postal_code, dest.lat, dest.lng, AsText(dest.loc), distance(orig.loc, dest.loc) as sdistance FROM friends orig, friends dest where orig.postal_code = '90278' order by sdistance;
+----+--------------+-------------+---------+----------+------------------------------+-----------+
| id | display_name | postal_code | lat | lng | AsText(dest.loc) | sdistance |
+----+--------------+-------------+---------+----------+------------------------------+-----------+
| 2 | Carson | 90278 | 33.8745 | -118.372 | POINT(-118.372031 33.874488) | 0 |
| 10 | freddy3 | 90275 | 33.7589 | -118.36 | POINT(-118.35986 33.758866) | 0 |
| 1 | Redondo Guy | 80503 | 40.1797 | -105.205 | POINT(-105.20545 40.179731) | 15 |
Dont use this, its just a trig function, see proof
SET @lat1 =40.756054, @lon1 = -73.986951, /** NEW YORK */
@lat2 =51.5001524, @lon2 = 0.1262362; /** London */
SELECT GLength(LineStringFromWKB(LineString(ASBINARY(GeomFromText(CONCAT('POINT(',@lat1,' ', @lon1,')'))),
ASBINARY(GeomFromText(CONCAT('POINT(',@lat2,' ', @lon2,')')))))) AS distance,
SQRT(POW(@lat1-@lat2,2) + POW(@lon1-@lon2,2)) AS trig;
Is the distance calculated is the distance b/w two points on the surface of earth or simply the distance b/w points in a plane ???
Drew's statement is in my opinion of high value.
As far as I can see no transformation of the spherical coordinates to planar coordinates is performed, the numerical values (lat, long) are just treated as if they would be euclidian, planar coordinates.
Therefore the calculated value has almost no relationship to distance on the globe.
Post a Comment