MYSQL空间查询示例

假设我们的表中有一个geometry字段用于存贮空间信息，并且已经建立的空间索引，我这里用的是投影坐标，而不是经纬度，如果是经纬度，简单换一下坐标就可以了。简单查询示例如下

（1）空间矩形范围查询：

最简单：SELECT NAME,ASTEXT(GEOMETRY) AS GEOMETRY FROM fac_point t WHERE X(t.geometry) >0 AND X(t.geometry) <80000 AND Y(t.GEOMETRY) >0 AND Y(t.GEOMETRY) <80000;

通过空间函数获得：SELECT NAME,ASTEXT(GEOMETRY) AS GEOMETRY FROM fac_point t WHERE INTERSECTS(GEOMETRY,ST_Envelope(ST_GeomFromText('LineString(45000 45000,60000 60000)')));

（2）空间多边形范围内查询：

注意polygon的第一个坐标和最后一个坐标要一致。

SELECT NAME,ASTEXT(GEOMETRY) AS GEOMETRY FROM fac_point t WHERE  ST_Intersects(GEOMETRY,ST_GeomFromText('POLYGON((44270 58295,45185 60857,46687 59760,46659 57792,45332 57069,44270 58295))'));

SELECT NAME,ASTEXT(GEOMETRY) AS GEOMETRY FROM fac_point t WHERE  ST_Within(GEOMETRY,ST_GeomFromText('POLYGON((44270 58295,45185 60857,46687 59760,45659 57792,45332 57069,44270 58295))'));

（3）空间圆内查询：

SELECT NAME,ASTEXT(GEOMETRY) AS GEOMETRY FROM fac_point t WHERE  ST_Distance(GEOMETRY,ST_GeomFromText('POINT(44270 58295)'))<=500;