Assign a point to a zone by spatial query, not by typing

On a mapping project, every point of interest had to belong to a zone of the site: a sector, a quay, a building. The first instinct is to put a dropdown in the admin form and ask whoever is typing to pick one.

That is the right instinct for shipping quickly, and it is a debt that accrues interest.

Why manual entry always ends up lying

The problem is not that people make mistakes. It is that there are then two sources of truth for the same fact: the position of the point on the map, and the zone written in the database. Nothing forces them to keep agreeing.

They diverge at the first of these events, and one of them will happen:

• someone moves a point on the map without reopening the dropdown
• a zone is redrawn, and the points it used to contain are never reviewed
• a data import fills in the position but not the zone
• two people administer in parallel and do not make the same correction

And the divergence is silent. No error, no alert: just a map showing a point in one place and a filter that files it somewhere else. You find out months later, through a user who cannot find what they are looking for.

The position already contains the answer

If zones are stored as polygons and points as points, “which zone is this point in?” is a question of geometry, not of data entry. The database can answer it, given the right column type.

-- Zones, as polygons. SRID 4326 is the usual longitude/latitude
-- coordinate system of a GeoJSON file.
CREATE TABLE zone (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  nom         VARCHAR(120) NOT NULL,
  contour     POLYGON SRID 4326 NOT NULL,
  SPATIAL INDEX (contour)
);

-- Points of interest. We do NOT store a zone_id column:
-- that would recreate the second source of truth.
CREATE TABLE poi (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  nom         VARCHAR(160) NOT NULL,
  position    POINT SRID 4326 NOT NULL,
  SPATIAL INDEX (position)
);

The query that answers the question:

SELECT z.id, z.nom
FROM   zone z
JOIN   poi  p ON ST_Contains(z.contour, p.position)
WHERE  p.id = ?;

ST_Contains tests whether the polygon contains the point. It is the classic point-in-polygon test, but run by the engine with a spatial index behind it, rather than by a loop in application code.

The coordinate order trap

This is the mistake that costs the most time, because it produces no error: it produces empty results.

A GeoJSON file writes its coordinates in [longitude, latitude] order. MySQL, with SRID 4326, expects (latitude, longitude) — the other way round. Copy the coordinates across as they are and the geometries are valid, the queries run, and no point is ever in any zone. You hunt for the bug in the query for hours when it is in the import.

Two ways out, whichever you prefer, but only one across the whole project:

-- 1. Swap explicitly on import
SET @g = ST_GeomFromGeoJSON(?, 2, 4326);

-- 2. Or ask for GeoJSON order when reading
SELECT ST_AsGeoJSON(contour, 8, 4) FROM zone;

The ten-second test that settles it: take a point whose zone you can see by eye on the map, then run the query. If it returns nothing, it is the coordinate order, not the logic.

The edge cases to decide, rather than suffer

A spatial query answers exactly what it is asked. The following are not bugs, they are business decisions:

A point exactly on the boundary. ST_Contains returns false for a point lying strictly on the edge. If zones abut and a point falls precisely on the line, it belongs to nothing. If that is a problem in your domain, ST_Intersects includes the boundary — but then a point on a shared edge belongs to two zones.

A point in no zone at all. A point outside the site, or a zone never drawn. The query returns zero rows. Plan an “unclassified” display rather than leaving the map stuck or the filter empty.

Overlapping zones. The query returns several rows. Either overlap is forbidden at entry, or a rule decides — smallest zone wins:

SELECT z.id, z.nom
FROM   zone z
JOIN   poi  p ON ST_Contains(z.contour, p.position)
WHERE  p.id = ?
ORDER BY ST_Area(z.contour) ASC
LIMIT 1;

The point that matters: each of those three situations must be chosen. A spatial query that has not answered these three questions will one day produce a result nobody can explain.

What about performance?

The spatial index does the heavy lifting: it first discards the polygons whose bounding box cannot contain the point, then tests precisely only the few remaining candidates. Across a few dozen zones and a few hundred points, it is instant.

The real performance question is not the query, it is how often it runs. Recomputing the assignment on every render of the map is waste. Two strategies, depending on the project:

• Compute on write: on every creation or move of a point, and every change to a zone, update a derived field. Fast to read, but the whole zone has to be recomputed when its outline changes — that is the classic omission.
• Compute on read, with a cache: the query stays the source of truth, and the cache is invalidated whenever a zone changes. Safer, slightly more code.

Either way, the geometry remains the reference. The derived field is only a disposable copy, and that is precisely what separates it from manual entry: it can be rebuilt at any moment with a single query.

What we take from it

Every time a user is asked to enter information the system can derive, an opportunity for divergence is created. This is not a question of trusting the user: it is a question of design. Two sources of truth for the same fact always end up contradicting each other, and it is always discovered at the worst moment.

The dropdown looked like a shortcut. It was a loan.