Geofence and distance¶
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;
The fence should contain only 1. The stored points and fence both use SRID 0, so their coordinates are Cartesian. The separate distance query uses SRID 4326 and should return about 111 km in metres. Do not interpret a Cartesian distance in SRID 0 as metres. See the source geodetic distance test.