公司数据库迁移,从oracle迁移到mysql,现在遇到这个问题,计算两点距离的时候
SELECT ST_Distance(SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128914 , SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128915);报了语法错误
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT astext(GEOMETRY) FROM GIS_POI WHERE ID=128914), @g2 = GeomFromText(SELECT' at line 1SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128914
SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128915都有正常的点数据返回
SELECT ST_Distance(SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128914 , SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128915);报了语法错误
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT astext(GEOMETRY) FROM GIS_POI WHERE ID=128914), @g2 = GeomFromText(SELECT' at line 1SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128914
SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128915都有正常的点数据返回
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
| 1.4142135623730951 |
+-----------------------+
ST_Distance() was added in MySQL 5.6.1.
所以, 检查下你mysql 版本, 必需在 5.6.1 以上.
SET @id2 = (SELECT GO.GEOMETRY FROM GIS_POI GO WHERE GO.ID=128915 LIMIT 0, 1);
SET @g1 = POINT(@id1, @id1), @g2 = POINT(@id2, @id2);
SELECT ST_Distance(@g1, @g2);
不知道我这么写对不对, 沒试过....
可以正常执行,看来还是和oracle语法上有差别。多谢各位