Mysql spatial distance using POINT - Not working

Question

My goal is to use mysql POINT(lat,long) to find nearby entities in the database. I'm trying to do something like in the bottom of this tutorial http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL . Here is what I have 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;

Inserting 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');

Declaring the distance function:

 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 the 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 am getting is: ERROR 1367 (22007): Illegal non geometric 'aswkb(a@0)' value found during parsing, or ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

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

I have also tried this solution: Find the distance between two points in MYSQL. (using the Point Datatype)

This is my mysql version mysql Ver 14.14 Distrib 5.5.23, for Linux (x86_64) using readline 5.1

Hope someones expertise can help me. Cheers!

Hegarty

Try round(glength(LineStringFromWKB(LineString(GeomFromText(astext(a)),GeomFromText(astext(b)))))) – Gastrulation

@Gastrulation - Thanks for the suggestion, I ended up with almost the same result. – Hegarty

Answer 1

So I ended up with this as query for calculating distance, an 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 am multiplying it by 100 to get an approximation in kilometers. The result is not exact, but "ok". If someone would know a better way, feel free to comment.

Hegarty

Answer 2

define a 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))

result comes in meters

Maiduguri