假设我们的表中有一个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;