Complete steps to implement location punch-in using MySQL spatial functions

Preface

The project requirement is to determine whether the user's current location is within a given geographical location range. Only if the location restrictions are met can the user check in. The location range is one or more irregular polygons. As shown in the figure below, determine whether the user is in Tsinghua University or Peking University.

Get the coordinates of the graphics area#

Because the front end of the project uses wx.getLocation of the WeChat applet to obtain the geographic location, in order to ensure the consistency of coordinates, the background selection area uses the geographic location service of Tencent Maps. In Application Tools->Drawing Geometric Figures, points, lines, polygons and circles are provided for easy selection. See here.

Slightly modify the official example to get the selected location coordinates.

Storage Location

After obtaining the coordinate position, the next step is how to store it?

The Open Geospatial Consortium (OGC) is an international alliance of more than 250 companies, institutions, and universities involved in developing publicly available spatial solutions that can be used in a wide range of applications that manage spatial data. OGC has published the OpenGIS® Implementation standard for geographic information, which is available from the OGC website at http://www.opengeospatial.org/standards/sfs. In order to comply with the OGC specification, MySQL implements spatial extensions as a subset of SQL with the Geometry Types environment, providing the functions of generating, storing, and analyzing space. In short, MySQL can meet our needs.
MySQL provides individual storage types POINT, LINESTRING, and POLYGON corresponding to geometric points, lines, and polygons. GEOMETRY can store any of the three. It also has the ability to store multiple types. MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION correspond to the plural number of a single graphic.

Back to the project, we used POLYGON.

The table creation statement is as follows:

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 converting Well-Known Text (WKT) and Well-Known Binary (WKB) formats into object types for storage. We use the WKT format, which is easier to understand. Those interested in WKB can see here.

The insert statement is as follows:

INSERT INTO `polygon` VALUES ('1', 'Tsinghua University', 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', 'Peking University', GeomFromText('POLYGON((39.99711457525893 116.30450117461078,39.98673259872773 116.30535884106575,39.98673259872773 116.31702308311287,39.99963848242885 116.31598375134854,39.99711457525893 116.30450117461078))'));

It should be noted that the points of the polygon returned by Tencent Maps are not closed, and the polygon function requires that the first point and the last point are the same in order to determine whether the polygon is closed. If the polygon is not closed, the result returned will be NULL and the insert statement will fail.

A geometry is syntactically well-formed if it satisfies conditions such as in this (non-exhaustive) list:

• Linestring has at least two points
• The polygon has at least one ring
• Polygon ring is closed (first and last points are the same)
• A polygon ring has at least 4 points (the smallest polygon is a triangle where the first and last points are the same)
• The collection is not empty (except GeometryCollection)
  

Query judgment

SELECT * FROM polygon WHERE
	MBRWithin (ST_GeomFromText('POINT(39.991333490218544 116.30964748487895)'), polygon);
# SELECT * FROM polygon WHERE in Peking University
	MBRWithin (ST_GeomFromText('POINT(39.988967560246685 116.3286905102832)'), polygon);
# Not in Peking University

Careful students may have discovered that the query statement here uses a function. In previous SQL, if a function is used on a query field, it will inevitably lead to index failure and full table scan, but this will not happen in spatial data. First look at the EXPLAIN statement and results:

It can be seen that MySQL spatial data can also be indexed, and the keyword used is SPATIAL

Usage is as follows:

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

Commonly used spatial calculation functions

1. Determine the distance between two points

ST_Distance(g1, g2) returns the distance between g1 and g2. If either argument is NULL or an empty geometry, the return value is NULL.

2. Does Graphic 1 completely contain Graphic 2?

ST_Contains(g1, g2) Returns 1 or 0 to indicate whether g1 completely contains g2. You can also use ST_Within(g2,g1) to achieve the same effect.

3. No intersection

ST_Disjoint(g1, g2) Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2.

4. The situation of graphic intersection is more complicated, including overlap, external intersection, etc. You can see the details here

Summarize

This article uses the built-in Polygon data type of MySQL to store spatial data through a geographic location check-in requirement. The ST_Contains(g1, g2) function is used to substitute the geographic area preset in the background and the user's geographic location obtained by the front end to determine whether the user is within the check-in range. It also involves that MySQL can still use indexes when using functions as query fields, and finally extends some other spatial processing functions.

This is the end of this article about using MySQL spatial functions to implement location punch-in. For more relevant MySQL spatial function location punch-in content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!