Illegal non geometric 'aswkb(...)' value found during parsing
I used tutorial from http://dev.mysql.com/tech-resources/articles/4.1/gis开发者_运维问答-with-mysql.html so i created two tables, inserted some data and when i tried to get some data using such script
SELECT
c.cab_driver,
ROUND(GLength(LineStringFromWKB(LineString(AsBinary(c.cab_loc),
AsBinary(a.address_loc)))))
AS distance
FROM cab c, address a
WHERE a.address = 'Foobar street 110'
ORDER BY distance ASC LIMIT 1;
i got an error: "Error Code: 1367 Illegal non geometric 'aswkb(c
.cab_loc
)' value found during parsing"
Any suggestions?
I've got some progress in this question, i tried to run
SELECT
asbinary(c.cab_loc)
FROM usercoordinates.cab c;
and i am getting NULL in every row but if i use astext i get POINT(...) in every row
Finally i got it, maybe not the best solution but however
SELECT
c.cab_driver,
Round(glength(LineStringFromWKB(LineString(GeomFromText(astext(c.cab_loc)),GeomFromText(astext(a.address_loc)))))) AS distance
FROM cab c, address a
WHERE a.address = 'Foobar street 99'
ORDER BY distance ASC LIMIT 1;
SELECT c.cab_driver,
Round(glength(LineStringFromWKB(LineString(GeomFromText(astext(c.cab_loc)),
GeomFromText(astext(a.address_loc)))))) AS distance
FROM cab c, address a
WHERE a.address = 'Foobar street 99'
ORDER BY distance ASC LIMIT 1;
Very ugly solution, but I couldn't find another one too.
By the way, we can to shorten query a little by using additional wrap-function:
DELIMITER $$
DROP FUNCTION IF EXISTS pointIt $$
CREATE function pointIt (src POINT)
RETURNS POINT
BEGIN
RETURN GeomFromText(astext(src));
END $$
DELIMITER ;
I had to make some slight changes of Hituptony's solution for Mysql 5.6:
SELECT c.cab_driver,
Round(glength(LineStringFromWKB(LineString(c.cab_loc,
a.address_loc)))) AS distance
FROM cab c, address a
WHERE a.address = 'Foobar street 99'
ORDER BY distance ASC LIMIT 1;
精彩评论