Mysql spatial distance using POINT - Doesn't work

Question

My goal is to use mysql POINT (lat, long) to find the closest objects in the database. I am trying to do something like the bottom of this tutorial http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL . Here is what I got:

Table:

CREATE TABLE mark ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) DEFAULT NULL, loc POINT NOT NULL, SPATIAL KEY loc (loc) ) ENGINE=MyISAM; 

Insert some test data:

  INSERT INTO mark (loc,name) VALUES (POINT(59.388433,10.415039), 'Somewhere 1'); INSERT INTO mark (loc,name) VALUES (POINT(63.41972,10.39856), 'Somewhere 2'); 

Distance function declaration:

  DELIMITER $$ CREATE FUNCTION `distance` (a POINT, b POINT) RETURNS double DETERMINISTIC BEGIN RETURN round(glength(linestringfromwkb(linestring(asbinary(a), asbinary(b))))); END $$ DELIMITER; 

Trying to use function to search ex .:

  SELECT name, distance(mark.loc, GeomFromText( ' POINT(31.5 42.2) ' )) AS cdist FROM mark ORDER BY cdist limit 10; 

or

  SELECT DISTINCT dest.name, distance(orig.loc, dest.loc) as sdistance FROM mark orig, mark dest having sdistance < 10 ORDER BY sdistance limit 10; 

The problem I get is: ERROR 1367 (22007): Invalid non-geometric value aswkb (a @ 0) found during parsing, or ERROR 1416 (22003): Unable to get the geometry object from the data sent to the GEOMETRY field.

I can’t figure out how to solve this. The important thing is that the distance function can be used dynamically.

I also tried this solution: Find the distance between two points in MYSQL. (using dot type)

This is my version of mysql mysql Ver 14.14. Distribution 5.5.23 for Linux (x86_64) using readline 5.1

Hope someone can help me. Hurrah!

Answer 1

So, I got this as a request to calculate the distance, for example:

  SELECT glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(63.424818,10.402457)))),GeomFromText(astext(PointFromWKB(POINT(663.422238,10.398996)))))))*100 AS distance; 

I multiply it by 100 to get an approximation in kilometers. The result is not accurate, but "good." If someone knows a better way, feel free to comment.

Answer 2

define custom function

 CREATE DEFINER=`test`@`%` FUNCTION `geoDistance`(`lon1` DOUBLE, `lat1` DOUBLE, `lon2` DOUBLE, `lat2` DOUBLE) RETURNS double LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE v DOUBLE; SELECT cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)) INTO v; RETURN IF(v > 1, 0, 6371000 * acos(v)); END 

then call

 SELECT geoDistance(X(point1), Y(point1), X(spoint2), Y(point2)) 

the result comes in meters