地理围栏与距离

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 的平面距离当作米数。参见源码 地理距离测试。