MySql GIS 空间数据库使用体验

本文时对MySql GIS中的部分函数做测试，便于理解和使用

1.理解下MySql GIS空间数据字段类型

1.一个点：POINT（15 20）

请注意，点坐标无指定分隔逗号。

2.一根线条，例如有四点组成的线条： LINESTRING（0 0，10 10，20 25，50 60）

请注意，点坐标对用逗号分隔。

3.一个多边形，例如一个外环和一个内环组成的多边形：POLYGON（（0 0,10 0,10 10 10 0 0），（5 5 7 5 7 7 5 7 5 5））

4.多点集合，例如三个点的值：MultIPOINT（0 0，20 20，60 60）

5.多线集合，例如两根线条的集合：MULTILINESTRING（（10 10，20 20），（15 15，30 15））

6.多边形集合，例如两个多边形值的集合：MULTIPOLYGON（（（0 0,10 0,10 10 10,0 0）），（（5 5 7 5 7 7 5 7 5 5）））

7.集合，例如两个点和一条线段的集合：GeometryCollection（POINT（10 10），POINT（30 30），LINESTRING（15 15，20 20））

2.添加测试数据

表t_pot 存储点信息，t_polygon存储几何信息

t_pot 创建语句（由于这里使用的是mysql 5.7的数据库，所以用InnoDB数据引擎，较低版本的数据库需要采用MyISAM ）

CREATE TABLE `t_pot` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `pot` point DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

往t_pot表中添加测试数据

 INSERT INTO `t_pot` VALUES ('1', 'A', GeomFromText('POINT(0 0)'));INSERT INTO `t_pot` VALUES ('2', 'B', GeomFromText('POINT(0 2)'));INSERT INTO `t_pot` VALUES ('3', 'C', GeomFromText('POINT(2 0)'));INSERT INTO `t_pot` VALUES ('4', 'D', GeomFromText('POINT(2 2)'));

 t_polygon创建语句

CREATE TABLE `t_polygon` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `pgn` polygon DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

往t_polygon表中添加测试数据

INSERT INTO `t_polygon` VALUES ('1', 'ABCD', GeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))'));INSERT INTO `t_polygon` VALUES ('2', 'AEGF', GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'));INSERT INTO `t_polygon` VALUES ('3', 'CEGFBD', GeomFromText('POLYGON((0 2, 0 4, 4 4, 4 0, 2 0, 2 2, 0 2))'));INSERT INTO `t_polygon` VALUES ('4', 'AHJIK', GeomFromText('POLYGON((0 0, 0 6, 6 6, 6 0, 0 0), (2 2, 4 4, 4 2, 2 2))'));INSERT INTO `t_polygon` VALUES ('5', 'DGK', GeomFromText('POLYGON((2 2, 4 4, 4 2, 2 2))'));INSERT INTO `t_polygon` VALUES ('6', 'GKJ', GeomFromText('POLYGON((4 4, 4 2, 6 6, 4 4))'));INSERT INTO `t_polygon` VALUES ('7', 'ADF', GeomFromText('POLYGON((0 0, 2 2, 4 0, 0 0))'));INSERT INTO `t_polygon` VALUES ('8', 'LDK', GeomFromText('POLYGON((1 1, 2 2, 4 2, 1 1))'));

t_pot表中的点坐标数据没有什么好解释的，下面对t_polygon数据进行说明

下面是表中对应的坐标点位置：

 

ABDC：POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))  面积为4的正方形

AEGF：POLYGON((0 0, 4 0, 4 4, 0 4, 0 0)) 面积为16的正方形

CEGFBD：POLYGON((0 2, 0 4, 4 4, 4 0, 2 0, 2 2, 0 2)) 面积为16的正方形 - 面积为4的正方形

DGK：POLYGON((2 2, 4 4, 4 2, 2 2))面积为2的直角等腰三角形

AHJIK：POLYGON((0 0, 0 6, 6 6, 6 0, 0 0), (2 2, 4 4, 4 2, 2 2))  面积为36的正方形 - 面积为2的三角形DGK

GKJ ：POLYGON((4 4, 4 2, 6 6, 4 4)) 面积为2的三角形

ADF ：POLYGON((0 0, 2 2, 4 0, 0 0))面积为4的等腰三角形

LDK：POLYGON((1 1, 2 2, 4 2, 1 1))面积为1的三角形

3.测试SQL语句

ST_GEOMFROMTEXT文本数据转成空间数据，所以下面看着就乱码了(Navicate中是不乱码)

mysql> SELECT ST_GEOMFROMTEXT("POINT(1 2)");+-------------------------------+| ST_GEOMFROMTEXT("POINT(1 2)") |+-------------------------------+|     

         ð?       @      |+-------------------------------+1 row in set (0.00 sec)

ST_ASTEXT空间数据转文本数据

mysql> SELECT ST_ASTEXT(ST_GEOMFROMTEXT("POINT(1 2)"));+------------------------------------------+| ST_ASTEXT(ST_GEOMFROMTEXT("POINT(1 2)")) |+------------------------------------------+| POINT(1 2)                               |+------------------------------------------+1 row in set (0.00 sec)

st_distance两点之间直线距离

mysql> select st_distance(ST_GEOMFROMTEXT("POINT(0 0)"),p.pot),p.`name` from t_pot p;+--------------------------------------------------+------+| st_distance(ST_GEOMFROMTEXT("POINT(0 0)"),p.pot) | name |+--------------------------------------------------+------+|                                                0 | A    ||                                                2 | B    ||                                                2 | C    ||                               2.8284271247461903 | D    |+--------------------------------------------------+------+4 rows in set (0.00 sec)

st_distance_sphere两点之间地理距离，单位米，例如记录店铺与用户间的距离

mysql> select st_distance(ST_GEOMFROMTEXT("POINT(0 0)"),p.pot),p.`name` from t_pot p;+--------------------------------------------------+------+| st_distance(ST_GEOMFROMTEXT("POINT(0 0)"),p.pot) | name |+--------------------------------------------------+------+|                                                0 | A    ||                                                2 | B    ||                                                2 | C    ||                               2.8284271247461903 | D    |+--------------------------------------------------+------+4 rows in set (0.00 sec)

ST_Intersects图形是否有交叉重叠

mysql> SELECT    -> ST_Intersects(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")),tp.`name`    -> FROM `t_polygon` tp;+-------------------------------------------------------------------------+--------+| ST_Intersects(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")) | name   |+-------------------------------------------------------------------------+--------+|                                                                       1 | ABCD   ||                                                                       1 | AEGF   ||                                                                       1 | CEGFBD ||                                                                       1 | AHJIK  ||                                                                       1 | DGK    ||                                                                       0 | GKJ    ||                                                                       1 | ADF    ||                                                                       1 | LDK    |+-------------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

ST_Equals图形是否有相等

mysql> SELECT    -> ST_Equals(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")),tp.`name`    -> FROM `t_polygon` tp;+---------------------------------------------------------------------+--------+| ST_Equals(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")) | name   |+---------------------------------------------------------------------+--------+|                                                                   1 | ABCD   ||                                                                   0 | AEGF   ||                                                                   0 | CEGFBD ||                                                                   0 | AHJIK  ||                                                                   0 | DGK    ||                                                                   0 | GKJ    ||                                                                   0 | ADF    ||                                                                   0 | LDK    |+---------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

ST_Touches相接触，但不包含

mysql> SELECT    -> ST_Touches(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")),tp.`name`    -> FROM `t_polygon` tp;+----------------------------------------------------------------------+--------+| ST_Touches(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")) | name   |+----------------------------------------------------------------------+--------+|                                                                    0 | ABCD   ||                                                                    0 | AEGF   ||                                                                    1 | CEGFBD ||                                                                    0 | AHJIK  ||                                                                    1 | DGK    ||                                                                    0 | GKJ    ||                                                                    0 | ADF    ||                                                                    0 | LDK    |+----------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

ST_Contains包含

mysql> SELECT    -> ST_Contains(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")),tp.`name`    -> FROM `t_polygon` tp;+-----------------------------------------------------------------------+--------+| ST_Contains(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))")) | name   |+-----------------------------------------------------------------------+--------+|                                                                     1 | ABCD   ||                                                                     1 | AEGF   ||                                                                     0 | CEGFBD ||                                                                     1 | AHJIK  ||                                                                     0 | DGK    ||                                                                     0 | GKJ    ||                                                                     0 | ADF    ||                                                                     0 | LDK    |+-----------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

ST_SymDifference返回表示设置的几何值的对称差的点的几何形状 g1和 g2，这语话加了ST_ASTEXT，把空间数据转换成文本，要不然看着乱码的(Navicate中是不乱码)

mysql> SELECT    -> ST_ASTEXT(ST_SymDifference(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,4 0,4 4,0 4,0 0))"))),tp.`name`    -> FROM `t_polygon` tp;+---------------------------------------------------------------------------------------+--------+| ST_ASTEXT(ST_SymDifference(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,4 0,4 4,0 4,0 0))"))) | name   |+---------------------------------------------------------------------------------------+--------+| POLYGON((0 2,2 2,2 0,4 0,4 4,0 4,0 2))                                                | ABCD   || GEOMETRYCOLLECTION()                                                                  | AEGF   || POLYGON((0 2,0 0,2 0,2 2,0 2))                                                        | CEGFBD || POLYGON((4 2,4 0,6 0,6 6,0 6,0 4,4 4,2 2,4 2))                                        | AHJIK  || POLYGON((4 4,0 4,0 0,4 0,4 2,2 2,4 4))                                                | DGK    || POLYGON((4 4,0 4,0 0,4 0,4 2,6 6,4 4))                                                | GKJ    || POLYGON((4 0,4 4,0 4,0 0,2 2,4 0))                                                    | ADF    || POLYGON((4 2,4 4,0 4,0 0,4 0,4 2),(4 2,1 1,2 2,4 2))                                  | LDK    |+---------------------------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

ST_Intersection在指定范围内交叉的集合

mysql> SELECT    -> ST_ASTEXT(ST_Intersection(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))"))),tp.`name`    -> FROM `t_polygon` tp;+--------------------------------------------------------------------------------------+--------+| ST_ASTEXT(ST_Intersection(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,2 0,2 2,0 2,0 0))"))) | name   |+--------------------------------------------------------------------------------------+--------+| POLYGON((0 2,0 0,2 0,2 2,0 2))                                                       | ABCD   || POLYGON((0 2,0 0,2 0,2 2,0 2))                                                       | AEGF   || LINESTRING(0 2,2 2,2 0)                                                              | CEGFBD || POLYGON((0 2,0 0,2 0,2 2,0 2))                                                       | AHJIK  || POINT(2 2)                                                                           | DGK    || GEOMETRYCOLLECTION()                                                                 | GKJ    || POLYGON((2 2,0 0,2 0,2 2))                                                           | ADF    || POLYGON((2 2,1 1,2 1.3333333333333335,2 2))                                          | LDK    |+--------------------------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

还是上面的语句，但是把范围调整了为了更清楚的分析数据

mysql> SELECT    -> ST_ASTEXT(ST_Intersection(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,4 0,4 4,0 4,0 0))"))),tp.`name`    -> FROM `t_polygon` tp;+---------------------------------------------------------------------------------------+--------+| ST_ASTEXT(ST_Intersection(tp.pgn,ST_GEOMFROMTEXT("POLYGON((0 0,4 0,4 4,0 4,0 0))")))  | name   |+---------------------------------------------------------------------------------------+--------+| POLYGON((0 2,0 0,2 0,2 2,0 2))                                                        | ABCD   || POLYGON((0 4,0 0,4 0,4 4,0 4))                                                        | AEGF   || POLYGON((0 4,0 2,2 2,2 0,4 0,4 4,0 4))                                                | CEGFBD || GEOMETRYCOLLECTION(MULTIPOLYGON(((0 4,0 0,4 0,4 2,2 2,4 4,0 4))),LINESTRING(4 4,4 2)) | AHJIK  || POLYGON((4 4,2 2,4 2,4 4))                                                            | DGK    || LINESTRING(4 4,4 2)                                                                   | GKJ    || POLYGON((4 0,2 2,0 0,4 0))                                                            | ADF    || POLYGON((4 2,2 2,1 1,4 2))                                                            | LDK    |+---------------------------------------------------------------------------------------+--------+8 rows in set (0.00 sec)

AREA计算面积

mysql> select AREA(tp.pgn),tp.`name`    -> FROM `t_polygon` tp;+--------------+--------+| AREA(tp.pgn) | name   |+--------------+--------+|            4 | ABCD   ||           16 | AEGF   ||           12 | CEGFBD ||           34 | AHJIK  ||            2 | DGK    ||            2 | GKJ    ||            4 | ADF    ||            1 | LDK    |+--------------+--------+8 rows in set, 1 warning (0.00 sec)

还有更多的函数的使用和说明可以参考：mysql 函数说明