GIS geofences and regional analysis¶
This planar SRID-0 geofence uses application-defined units. Changing SRID to 4326 does not make it a geodetic geofence.
DROP DATABASE IF EXISTS gis_fence_402;
CREATE DATABASE gis_fence_402;
USE gis_fence_402;
CREATE TABLE regions(id INT PRIMARY KEY, area POLYGON);
CREATE TABLE events(id INT PRIMARY KEY, customer VARCHAR(20), location POINT);
INSERT INTO regions VALUES (1,ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0))'));
INSERT INTO events VALUES (1,'inside',ST_GeomFromText('POINT(2 2)')),(2,'boundary',ST_GeomFromText('POINT(0 2)')),(3,'outside',ST_GeomFromText('POINT(5 5)'));
SELECT e.id,ST_Contains(r.area,e.location) AS contained,ST_Covers(r.area,e.location) AS covered FROM events e CROSS JOIN regions r ORDER BY e.id;
SELECT r.id,COUNT(*) AS covered_events FROM regions r JOIN events e ON ST_Covers(r.area,e.location) GROUP BY r.id;
EXPLAIN SELECT e.id FROM events e JOIN regions r ON ST_Covers(r.area,e.location);
DROP DATABASE gis_fence_402;
inside has contained/covered=1; boundary has contained=0, covered=1; outside has both zero. The region count is 2. Decide whether business boundaries are included before choosing Contains/Covers.
JOIN orders/devices by keys and handle event grain/duplicates before aggregation. Test invalid polygons, NULL and mismatched SRIDs separately. MBR predicates compare envelopes, not exact geometry. Inspect plans/scans rather than inferring spatial-index support from ST functions.
Export fixed inputs/results for review. See basics and functions.