Mysql8存储GIS数据,QGIS中显示

1 MySQL对GIS数据的支持

MySQL的GIS功能遵守OGC的OpenGIS Geometry Model,支持其定义的空间数据类型的一个子集,包括以下空间数据类型:

  • GEOMETRY:不可实例化的数据类型,但是可以作为一个列的类型,存储任何一种其他类型的数据
  • POINT:点
  • LINESTRING:线
  • POLYGON:多边形,由多条闭合的线构成的图形
  • MULTIPOINT:点集合
  • MULTILINESTRING:线集合
  • MULTIPOLYGON:多边形集合
  • GEOMCOLLECTION:空间对象集合

2 创建数据库表,存储GIS中常见数据格式

新建一个数据库test_geom

CREATE DATABASE test_geom;
2.1 存储点

建表

CREATE TABLE `t_pot` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 255 ) DEFAULT NULL,
	`geom` point SRID 4326,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

添加测试数据

-- 方式一:使用空间对象构造函数 POINT()
INSERT INTO `t_pot` VALUES ('1', 'P_A', ST_SRID(POINT(113.79999160766602,34.79329457793592), 4326));
INSERT INTO `t_pot` VALUES ('2', 'P_B', ST_SRID(POINT(113.78788948059082,34.793647004854), 4326));
INSERT INTO `t_pot` VALUES ('3', 'P_C', ST_SRID(POINT(113.81020545959473,34.79442233877167), 4326));

-- 方式二:使用wkt格式 ST_GeometryFromText(wkt文本)
INSERT INTO `t_pot` VALUES ('4', 'C', ST_SRID(ST_GeometryFromText('POINT(113.81020545959473 34.79442233877167)'), 4326));
2.2 存储线

建表

CREATE TABLE `t_line` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 255 ) DEFAULT NULL,
	`geom` linestring SRID 4326,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

添加测试数据

INSERT INTO `t_line` VALUES ('1', 'L_A', ST_SRID(ST_GeometryFromText('LineString(113.79999160766602 34.79329457793592, 113.80290985107422 34.79361176222998)'), 4326));
INSERT INTO `t_line` VALUES ('2', 'L_B', ST_SRID(ST_GeometryFromText('LineString(113.80471229553221 34.801470494524686, 113.8043689727783 34.79378797519947, 113.8044548034668 34.78568178886447)'), 4326));
INSERT INTO `t_line` VALUES ('3', 'L_C', ST_SRID(ST_GeometryFromText('LineString(113.79192352294922 34.79822841771478, 113.79467010498047 34.79343554888391, 113.79441261291504 34.78575228087539)'), 4326));
2.3 存储面

建表

CREATE TABLE `t_polygon` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 255 ) DEFAULT NULL,
	`geom` polygon SRID 4326,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

添加测试数据

INSERT INTO `t_polygon` VALUES ('1', 'A_A', ST_SRID(ST_GeometryFromText('polygon((113.80080699920654 34.796836399961705, 113.79735231399536 34.79163815124442, 113.80486249923706 34.79211393678524, 113.80080699920654 34.796836399961705))'), 4326));
INSERT INTO `t_polygon` VALUES ('2', 'A_B', ST_SRID(ST_GeometryFromText('polygon((113.79990577697754 34.794457581049265, 113.79887580871582 34.79271307022796, 113.80095720291138 34.79287166364592, 113.79990577697754 34.794457581049265))'), 4326));
2.4 测试查询
SELECT id, name, geom FROM t_pot
SELECT id, name, ST_AsText(geom) as geom FROM t_pot
SELECT id, name, ST_AsGeoJSON(geom) as geom FROM t_pot

小结:

空间索引: 以上示例建表时未创建空间索引。

SRID: 即坐标系。在创建表时,若不指定坐标系,默认使用SRID 0(表示无限的直角笛卡尔平面,其xy轴未分配任何单位);未指定坐标系时,无法在QGIS中加载

2 QGIS中添加Mysql图层

  1. 打开“数据源管理器”
    在这里插入图片描述
  2. 连接Mysql数据库,添加图层
    在这里插入图片描述
    在这里插入图片描述
  3. 数据添加成功
    在这里插入图片描述

3 空间查询、空间分析函数

查询:

-- 内部格式转换为WKT
SELECT ST_ASTEXT(pot) FROM t_pot

-- 内部格式转换为WKB
SELECT ST_ASBINARY(pot) FROM t_pot

参考:

MySQL GIS功能介绍_爱是与世界平行的博客-CSDN博客_mysql opengis

mysql 8.x 中的 geometry 变更/缓存命中/SRID/EPSG/WKT等问题 - 知乎 (zhihu.com)

MySql GIS 空间数据库使用体验_Barfi_的博客-CSDN博客

Mysql gis 空间数据库功能详解学习_chaiqi的博客-CSDN博客_gis数据库 mysql

MySql空间数据,多边形区域是否包含点_sprouting_new的博客-CSDN博客