地理围栏与距离¶
CREATE DATABASE cookbook_geo;
CREATE TABLE cookbook_geo.points(id INT PRIMARY KEY, location GEOMETRY);
INSERT INTO cookbook_geo.points VALUES
(1,ST_GEOMFROMTEXT('POINT(0 0)',0)),
(2,ST_GEOMFROMTEXT('POINT(2 2)',0));
SELECT id FROM cookbook_geo.points
WHERE ST_CONTAINS(ST_GEOMFROMTEXT(
'POLYGON((-1 -1,1 -1,1 1,-1 1,-1 -1))',0),location) ORDER BY id;
SELECT ST_DISTANCE(ST_GEOMFROMTEXT('POINT(0 0)',4326),
ST_GEOMFROMTEXT('POINT(1 0)',4326)) AS metres;
DROP DATABASE cookbook_geo;
围栏应只包含 1。GEOMETRY 列中的点和围栏都使用 SRID 0,坐标按平面处理。第二条单独用 SRID 4326 计算测地距离,应约为 111 km,以米计;不要把 SRID 0 的平面距离当作米数。参见源码 地理距离测试。