解析过程中发现的非法非几何'aswkb(...)'值

Question

我使用了http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html所以我创建了两个表，插入了一些数据，当我试图使用这样的脚本获得一些数据时

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;

我得到一个错误："错误代码：1367在分析时发现非法的非几何"aswkb（c.cab_loc）"值

有什么建议吗？

我在这个问题上取得了一些进展，我试着运行

SELECT 
    asbinary(c.cab_loc)
FROM usercoordinates.cab c;

我在每一行中都得到NULL，但如果我使用astext，我在每行中都得到POINT（…）

最后我得到了它，也许不是最好的解决方案，但

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;

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

Answer 2

非常难看的解决方案，但我也找不到其他解决方案。

顺便说一句，我们可以通过使用额外的包装功能来稍微缩短查询时间：

DELIMITER $$
DROP FUNCTION IF EXISTS pointIt $$
CREATE function pointIt (src POINT)
RETURNS POINT
BEGIN
RETURN GeomFromText(astext(src));
END $$
DELIMITER ;

Answer 3

我不得不对Hituptony的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;