- A+
mysql如何存储地理信息！
MySQL 存储地理信息通常使用 GEOMETRY 数据类型或其子类型（如 POINT, LINESTRING, POLYGON 等）。为了支持这些数据类型，MySQL 提供了 SPATIAL 索引，这允许我们执行高效的地理空间查询。
1. 创建支持地理信息的表
首先，我们需要一个包含 GEOMETRY 或其子类型列的表。以下是一个示例，展示如何创建一个包含 POINT 类型的表：
|
1
2
3
4
5
6
|
CREATE TABLE locations ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, position POINT NOT NULL, SPATIAL INDEX(position) -- 为位置列创建空间索引 ) ENGINE=InnoDB; |
2. 插入地理信息数据
我们可以使用 GeomFromText() 或 PointFromText() 函数插入地理数据。以下是如何插入一个点的示例：
|
1
2
3
4
5
|
INSERT INTO locations (name, position) VALUES ('Location A', GeomFromText('POINT(10 20)')); -- 或者使用 PointFromText INSERT INTO locations (name, position) VALUES ('Location B', PointFromText('POINT(30 40)')); |
3. 查询地理信息数据
我们可以使用 MBRContains(), Distance_Sphere(), ST_Distance_Sphere() 等函数来查询地理数据。以下是一些示例：
3.1查找指定矩形区域内的位置
|
1
2
3
4
5
6
|
-- 查找位置在 (0, 0) 到 (20, 20) 矩形区域内的所有位置 SELECT * FROM locations WHERE MBRContains( GeomFromText('POLYGON((0 0, 20 0, 20 20, 0 20, 0 0))'), position ); |
3.2查找距离特定点一定距离内的位置
注意：这里使用了 Distance_Sphere() 函数，它基于地球是完美球体的假设。对于更精确的计算，我们可以使用 ST_Distance_Sphere() 并指定地球半径。
|
1
2
3
4
5
6
7
8
9
|
-- 查找距离 (15, 15) 点 10 公里内的所有位置 -- 假设地球半径为 6371 公里（平均半径） SELECT *, (6371 * acos(cos(radians(15)) * cos(radians(X(position))) * cos(radians(Y(position)) - radians(15)) + sin(radians(15)) * sin(radians(X(position))))) AS distance_km FROM locations HAVING distance_km < 10; |
3.3使用 ST_Distance_Sphere() 查找距离
这是一个更精确的距离计算示例，它使用 ST_Distance_Sphere() 函数并指定地球的平均半径。
|
1
2
3
4
|
-- 查找距离 (15, 15) 点 10 公里内的所有位置 SELECT *, ST_Distance_Sphere(point(15, 15), position, 6371) AS distance_km FROM locations HAVING distance_km < 10; |
注意：上述查询中的距离计算是基于 Haversine 公式的简化版本，它假设地球是一个完美的球体。在实际应用中，我们可能需要使用更复杂的算法来考虑地球的不规则形状。
此外，我们还可以使用 MySQL 的其他地理空间函数和操作符来执行更复杂的地理空间查询和操作。
4.查询地理信息进阶示例
我们可以探讨一个更复杂的示例，该示例涉及POLYGON地理数据类型，并使用ST_Contains函数来检查一个点是否位于多边形内部。同时，我们也会使用ST_Distance_Sphere函数来计算点与多边形中心点的距离。
4.1创建表并插入数据
首先，我们创建一个包含POLYGON列的表，并插入一些多边形数据。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE polygons ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, shape POLYGON NOT NULL, SPATIAL INDEX(shape) ) ENGINE=InnoDB; INSERT INTO polygons (name, shape) VALUES ('Polygon A', GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')); INSERT INTO polygons (name, shape) VALUES ('Polygon B', GeomFromText('POLYGON((20 20, 30 20, 30 30, 20 30, 20 20))')); -- 创建一个包含点的表 CREATE TABLE points ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, position POINT NOT NULL, SPATIAL INDEX(position) ) ENGINE=InnoDB; INSERT INTO points (name, position) VALUES ('Point 1', GeomFromText('POINT(5 5)')); INSERT INTO points (name, position) VALUES ('Point 2', GeomFromText('POINT(25 25)')); |
4.2查询点是否在多边形内部，并计算距离
现在，我们可以编写一个查询来检查点是否位于多边形内部，并计算这些点与多边形中心点的距离。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
-- 假设我们想要检查'Point 1'和'Point 2'是否分别位于'Polygon A'和'Polygon B'内部 -- 并计算它们与各自多边形中心点的距离 -- 首先，我们需要计算每个多边形的中心点 SET @polygonA_center = ST_Centroid(GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')); SET @polygonB_center = ST_Centroid(GeomFromText('POLYGON((20 20, 30 20, 30 30, 20 30, 20 20))')); -- 然后，我们可以使用这些中心点与点表中的点进行比较和距离计算 SELECT p.name AS point_name, p.position, CASE WHEN ST_Contains(pg.shape, p.position) THEN 'Inside' ELSE 'Outside' END AS location_status, ST_Distance_Sphere(p.position, CASE pg.name WHEN 'Polygon A' THEN @polygonA_center ELSE @polygonB_center END, 6371) AS distance_km FROM points p JOIN polygons pg ON ( (p.name = 'Point 1' AND pg.name = 'Polygon A') OR (p.name = 'Point 2' AND pg.name = 'Polygon B') ); |
这个查询首先计算了两个多边形的中心点，并使用JOIN语句将点表与多边形表连接起来。它使用ST_Contains函数来检查点是否位于多边形内部，并使用ST_Distance_Sphere函数来计算点与对应多边形中心点的距离（以公里为单位）。注意，我们使用了CASE语句来根据点的名称选择正确的多边形中心点进行计算。
这个查询将返回每个点的名称、位置、是否在多边形内部的状态以及与对应多边形中心点的距离。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需99元，即可下载本站文章涉及的文件和软件。
- 我的微信
-
- 我的知识星球
-
