How to Use MySQL Polygon Types for Precise Location Check‑In

Getting Region Coordinates

The frontend uses the WeChat Mini Program wx.getLocation to obtain the user's location, while the backend selects region boundaries via Tencent Map's geographic service. In the tool's "Draw Geometry" section you can create points, lines, polygons, and circles, then export the selected coordinates.

Storing Locations

To store the coordinates, we follow the Open Geospatial Consortium (OGC) specifications. MySQL implements OGC spatial extensions, providing geometry types such as POINT, LINESTRING, POLYGON, and the generic GEOMETRY. For this project we use POLYGON to represent campus areas.

CREATE TABLE `polygon` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `polygon` polygon NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `d` (`polygon`)
) DEFAULT CHARSET=utf8;

Inserting Data

MySQL supports storing geometries in Well‑Known Text (WKT) format. The following statements insert two campus polygons (Tsinghua University and Peking University) using GeomFromText:

INSERT INTO `polygon` VALUES ('1','清华大学', GeomFromText('POLYGON((40.01169924229143 116.31565081888039,39.99304082299905 116.31616541796757,39.99343506780591 116.33297565023167,40.00237067000859 116.33743550702275,40.01340715321479 116.33057418815224,40.01169924229143 116.31565081888039))'));
INSERT INTO `polygon` VALUES ('2','北京大学', GeomFromText('POLYGON((39.99711457525893 116.30450117461078,39.98673259872773 116.30535884106575,39.98673259872773 116.31702308311287,39.99963848242885 116.31598375134854,39.99711457525893 116.30450117461078))'));

Note that the polygon returned by Tencent Map may not be closed; the first and last points must be identical for POLYGON. If not closed, the function returns NULL and the insert fails.

LineString must contain at least two points.

Polygon must have at least one ring.

Polygon ring must be closed (first and last point identical).

Polygon ring must contain at least four points (minimum triangle with repeated first/last point).

Geometry collections must not be empty (except GeometryCollection).

Querying and Determining Presence

SELECT * FROM polygon WHERE MBRWithin(ST_GeomFromText('POINT(39.991333490218544 116.30964748487895)'), polygon);  -- Inside Peking University
SELECT * FROM polygon WHERE MBRWithin(ST_GeomFromText('POINT(39.988967560246685 116.3286905102832)'), polygon);   -- Outside Peking University

Although using a function on a column often disables index usage in ordinary SQL, MySQL spatial indexes still work. An EXPLAIN shows that the query utilizes the SPATIAL index.

Creating a spatial index is done with the SPATIAL keyword:

CREATE TABLE geom (g GEOMETRY NOT NULL);
CREATE SPATIAL INDEX g ON geom (g);

Common Spatial Functions

Typical functions include: ST_Distance(g1, g2) – returns the distance between two geometries. ST_Contains(g1, g2) (or ST_Within(g2, g1)) – returns 1 if g1 completely contains g2. ST_Disjoint(g1, g2) – returns 1 if the geometries do not intersect.

Other intersection-related functions are documented in MySQL spatial relation references.

Conclusion

This article demonstrates a location‑check‑in scenario by storing campus areas as MySQL POLYGON types, inserting data in WKT format, and using spatial functions and SPATIAL indexes to efficiently determine whether a user's coordinates fall within the defined region, while also highlighting useful GIS functions.