开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜