mysql存储地理信息的方法

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL 支持 `GEOMETRY` 及其子类型(如 `POINT`, `LINESTRING`, `POLYGON`)存储地理信息,并提供 `SPATIAL` 索引来加速查询。创建带有 `SPATIAL INDEX` 的表,使用 `GeomFromText` 或 `PointFromText` 插入数据,通过 `MBRContains`, `Distance_Sphere`, `ST_Distance_Sphere` 等函数查询。例如,查找矩形区域内位置、一定距离内的点,以及判断点是否在多边形内并计算距离。

MySQL 存储地理信息通常使用 GEOMETRY 数据类型或其子类型(如 POINT, LINESTRING, POLYGON 等)。为了支持这些数据类型,MySQL 提供了 SPATIAL 索引,这允许我们执行高效的地理空间查询。

1. 创建支持地理信息的表

首先,我们需要一个包含 GEOMETRY 或其子类型列的表。以下是一个示例,展示如何创建一个包含 POINT 类型的表:

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() 函数插入地理数据。以下是如何插入一个点的示例:

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查找指定矩形区域内的位置

-- 查找位置在 (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() 并指定地球半径。

-- 查找距离 (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() 函数并指定地球的平均半径。

-- 查找距离 (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列的表,并插入一些多边形数据。

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查询点是否在多边形内部,并计算距离

现在,我们可以编写一个查询来检查点是否位于多边形内部,并计算这些点与多边形中心点的距离。

-- 假设我们想要检查'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语句来根据点的名称选择正确的多边形中心点进行计算。

这个查询将返回每个点的名称、位置、是否在多边形内部的状态以及与对应多边形中心点的距离。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
8天前
|
存储 SQL 关系型数据库
实时计算 Flink版产品使用问题之要配置MySQL集群存储节点,该如何配置
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
存储 SQL 关系型数据库
MySQL存储过程和存储函数的使用
MySQL的存储过程和存储函数在功能和用法上有明显的区别。存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,通过指定名称和参数(如果有)来调用执行,可以返回多个值或结果集,但不直接返回值。而存储函数则是一个有返回值的特殊存储过程,它返回一个值或表对象,可以直接嵌入SQL语句中使用,如SELECT语句中。两者都是为了提高SQL代码的重用性和性能,但使用场景和方式有所不同。
174 4
|
1月前
|
SQL 关系型数据库 MySQL
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
|
1月前
|
存储 安全 关系型数据库
MYSQL--存储函数
MYSQL--存储函数
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(3)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
22天前
|
关系型数据库 MySQL 数据库
MySQL索引的类型与优化方法
MySQL索引的类型与优化方法
|
23天前
|
存储 关系型数据库 MySQL
MySQL删除索引的方法与注意事项
MySQL删除索引的方法与注意事项
|
27天前
|
关系型数据库 MySQL 数据库
Python中使用MySQL模糊查询的方法
(1)同样需要将your_username、your_password、your_database替换为我们的MySQL数据库的实际用户名、密码和数据库名。 (2)在mysql.connector.connect()中,我们没有直接指定字符集和游标类型,因为mysql-connector-python的默认配置通常已经足够好。但是,如果需要,我们可以添加这些配置选项。 (3)使用cursor.close()和cnx.close()来确保游标和连接都被正确关闭。 (4)mysql-connector-python也支持使用上下文管理器(即with语句)来自动管理游标和连接的关闭,但这需要创建一个