MySQL has some powerful, and perhaps underused spatial extensions, but the most interesting functions are still unimplemented: “Note: Currently, MySQL does not implement these functions…”
Among those as-yet unimplemented functions is DISTANCE()
. Alternatives can be found here and here, though neither is clean or simple. I wonder if a simple MBRContains()
is good enough, though…
There’s an example of distance calculation in the mysql manual – the trick is to make a line out of the two points and then get the length of the line:
ROUND(GLength(LineStringFromWKB(LineString(AsBinary(point1), AsBinary(point2)))))
http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html
Thanks for your work – saved me a bit of digging!
Excelent.
It should be noted that that GLength is only acurate when the coordinate system is a flat grid projection. The links provided in the orignal post are for calculating geodesic distance (Non Euclidean) which is needed if your geometry is defined by lon/lat.
regarding the issue with the flat grid i agree. However, does anyone know for which distances this effect is noteable? e.g. if i want to get all locations within 50 miles, does it really make a big difference?