Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the space function in MySQL to achieve location clocking in. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Drawing acquires area coordinates

Because the front end of the project uses WeChat Mini Programs's wx.getLocation to obtain the geographical location, for the sake of coordinate consistency, Tencent Map's geographic location service is used in the background selection area. In the application tool-> drawing geometry, points, lines, polygons and circles can be easily selected here.

The selected location coordinates can be obtained by making minor changes to the official example.

Storage location

After you get the coordinates, how do you store them?

The Open Geospatial Alliance (OGC) is an international alliance of more than 250 companies, institutions and universities involved in the development of publicly available spatial solutions that can be used to manage a variety of applications for spatial data. OGC publishes the OpenGIS ®Implementation standard for geographic information, which is available from the OGC website http://www.opengeospatial.org/standards/sfs. In order to follow the OGC specification, MySQL implements spatial extensions as a subset of SQL with Geometry Types environment, which provides the functions of generating, storing and analyzing space. In short, MySQL can meet our needs.

MySQL provides a single storage type POINT, LINESTRING, POLYGON corresponding to geometry points, lines, polygons, GEOMETRY can store any of the three. At the same time, it has the ability to store multiple types, and MULTIPOINT, MULTILINESTRING, MULTIPOLYGON and GEOMETRYCOLLECTION correspond to the plural of a single figure in turn.

Back in the project, we used POLYGON.

The statement of the table 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

Insert data

MySQL supports the conversion of Well-Known text (WKT) format and Well-Known binary (WKB) format to object type storage, and we use the easier-to-understand WKT format. Those who are interested in WKB can see here.

The insert statement is as follows:

INSERT INTO `polygon` VALUES ('1Qing,' Tsinghua University', GeomFromText ('POLYGON ((40.01169924229143 116.31565081888039)); INSERT INTO `polygon` VALUES (' 2Zhe, 'Peking University', GeomFromText') (POLYGON ((39.997114525893) 116.337565023167, 40.00237067000859 116.33735502275); INSERT INTO `polygon`polygon` (316.316992429143 116.31565081888039)); INSERT INTO `polygon`05905 ('Peking University', 'Peking University', 'POLYGON')

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

If geometry satisfies the conditions in such a (not exhaustive) list, it is syntactically well-formed:

The string has at least two points.

A polygon has at least one ring

Polygonal ring closed (the first and last points are the same)

The polygon ring has at least 4 points (the smallest polygon is a triangle, the first one is the same as the last point)

Collection is not empty (except for GeometryCollection)

Query and judge SELECT * FROM polygon WHERE MBRWithin (ST_GeomFromText ('POINT (39.991333490218544 116.309647487895), polygon); # at Peking University SELECT * FROM polygon WHERE MBRWithin (' POINT (39.988967560246685 116.3286905102832), polygon); # not at Peking University

Careful students may find that functions are used in the query statements here. In the past, using functions on query fields in SQL will inevitably lead to index failure and full table scanning, but not in spatial data. First, look at EXPLAIN statements and results:

Data of visible MySQL space type can also be indexed. The keyword used is SPATIAL usage as follows:

CREATE TABLE geom (g GEOMETRY NOT NULL); CREATE SPATIAL INDEX g ON geom (g); commonly used spatial calculation functions

1. Judge the distance between two points

ST_Distance (G1 and G2), which returns the distance between G1 and G2. If either parameter is NULL or empty geometry, return value is NULL.

2. Whether figure 1 fully contains figure 2

ST_Contains, which returns 1 or 0 to indicate whether G1 fully contains G2. The same effect can also be achieved with ST_Within (g2Powerg1).

3. Disjoint

ST_Disjoint (G2), returns 1 or 0 to indicate whether G1 is spatially disjoint with (disjoint) G2.

4. The situation about the intersection of graphics is quite complicated, including overlap, external intersection and so on. For details, please see here.

MySQL on how to use the space function to achieve location clocking in to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.