MySQL中地理位置数据扩展geometry的使用心得

最近学习了些mysql geometry数据存储和计算，在这里记录下。

1. 环境

　　geometry推荐在5.6版本以上使用，尽管大部分功能在5.5已经可用，除了距离计算函数st_distance等新增函数。

2. geometry主要相关类

2.1 geometry

　　geometry是所有此扩展中类型得基类，其他类型如point,linestring,polygon都是geometry的子类。geometry有一些属性，这些属性是所有其他几何类的共有属性：

　　type: 类型（point, linestring,...）

　　srid: 该值确定了用于描述定义几何对象的坐标空间的空间坐标系统，参考链接：https://www.cnblogs.com/joetao/articles/2086846.html

　　coordinates: 坐标值

　　interior, boundary, exterior: interior是几何对象所展空间的部分，boundary是几何对象的边界，exterior是几何对象未占有的空间。

　　mbr: 能够覆盖几何对象的最小矩形，可以想象成信封，它由几何对象中最大最小的坐标值组合而成：

　　　　((minx miny, maxx miny, maxx maxy, minx maxy, minx miny))

　　simple/nonsimple: 几何对象是否简单

　　closed/not closed: 几何对象是否封闭

　　dimension: 维度数（point: 0, linestring: 1, polygon: 2）

2.2 point

　　顾名思义就是点，有一个坐标值，没有长度、面积、边界。

2.3 linestring

　　顾名思义就是线，由一系列点连接而成。

　　如果线从头至尾没有交叉，那就是简单的（simple）

　　如果起点和终点重叠，那就是封闭的（closed）

2.4 polygon

　　多边形。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。

2.5 multipoint, multilinestring, multipolygon, geometrycollection

　　这4种类型都是集合类，是多个point、linestring或polygon组合在一起而成。

3. 几何对象在mysql中的数据格式

　　在mysql中有3种表达几何对象的格式：

　　-->wkt（文本格式）

　　-->wkb（二进制格式）

　　-->mysql内部存储格式

　　其中wkt格式简单易读，在这里着重介绍：

3.1 wkt

3.1.1 point

　　point(121.213342 31.234532)

　　经度(longitude)在前，维度(latitude)在后，用空格分隔

3.1.2 linestring

　　linestring(121.342423 31.542423,121.345664 31.246790,121.453178 31.456862)

　　点与点之间用逗号分隔；一个点中的经纬度用空格分隔，与point格式一致

3.1.3 polygon

　　polygon((121.342423 31.542423,121.345664 31.246790,121.453178 31.456862),(121.563633 31.566652,121.233565 31.234565,121.568756 31.454367))

　　由一个表示外部边界的linestring和0个或多个表示内部边界的linestring组成，最简单的就是只有一个外边界的情况：polygon((0 0,10,0 10 10, 0 10))

3.1.4 集合类格式

　　multipoint(0 0, 20 20, 60 60)

　　multilinestring((10 10, 20 20), (15 15, 30 15))

　　multipolygon(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

　　geometrycollection(point(10 10), point(30 30), linestring(15 15, 20 20))

4. 几何对象创建函数

　　mysql表中的几何对象有它自己的内部格式，我们需要将几何对象从方便输入的wkt格式转换为其内部格式，才能进行进一步的存储，计算等。

　　这里主要讲解使用wkt格式的函数，对于集合类对象的创建函数由于较少使用也不再列举

　　geomfromtext(wkt): 创建一个任何类型的几何对象geometry

　　pointfromtext(wkt): 创建一个point对象

　　linestringfromtext(wkt): 创建一个linestring对象

　　polygonfromtext(wkt): 创建一个polygon对象

5. 创建支持空间几何对象的表

5.1 创建表

　　以下是我创建的一个样例：

　　create table `t_geo_test` (
　　　　`id` int(11) not null auto_increment,
　　　　`name` varchar(64) not null,
　　　　`shape` geometry not null,
　　　　primary key (`id`)
　　) engine=myisam auto_increment=10 default charset=utf8 row_format=dynamic

　　这里的字段shape就是存储几何对象的，类型为geometry，可以支持point,linestring,polygon等任意几何对象。

　　引擎需要使用myisam。

　　表结构：

　　

5.2 插入数据

5.2.1 插入点数据

　　insert into `t_geo_test` (id,name,shape) values (1, 'p1', geomfromtext('point(121.474103 31.232862)'));

5.2.2 插入线数据

　　insert into `t_geo_test` (id,name,shape) values (2, 'l1', geomfromtext('linestring(121.474103 31.232862,121.472462 31.231339,121.471984 31.232821)'));

5.2.3 插入多边形数据

　　insert into `t_geo_test` (id,name,shape) values (5, 'polygon_1', geomfromtext('polygon((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))'));

5.3 获取数据

　　astext(): 此函数能将几何对象的内部存储格式转换为wkt格式

6. 常用函数

6.1 获取几何对象属性的函数

　　6.1.1 geometry

　　　　-->dimension(g)

　　　　　　返回对象g的维数

　　　　-->envelope(g)

　　　　　　返回对象g的最小边界矩形（mbr）。结类型为polygon值。

　　　　-->geometrytype(g)

　　　　　　以字符串形式返回几何类型的名称，如point,linestring

　　　　-->isclosed(g)

　　　　　　返回对象g是否封闭

　　　　-->issimple(g)

　　　　　　返回对象g是否简单

　　6.1.2 point

　　　　-->x(p)

　　　　　　以双精度数值返回点p的x坐标值(经度)。

　　　　-->y(p)

　　　　　　以双精度数值返回点p的y坐标值(纬度)。

　　6.1.3 linestring

　　　　-->endpoint(line)

　　　　　　返回对象line的最后一个点point

　　　　-->startpoint(line)

　　　　　　返回对象line的第一个点point

　　　　-->pointn(line, n)

　　　　　　返回对象line中第n个点，n从1开始

　　6.1.4 polygon

　　　　-->exteriorring(poly)

　　　　　　返回对象poly的外环，类型为linestring

　　　　-->interiorringn(poly, n)

　　　　　　返回对象poly的第n个内环，n从1开始

　　　　-->numinteriorrings(poly)

　　　　　　返回对象poly的neihuan个数

6.2 从现成几何对象创建新的对象

6.2.1 st_union(g1, g2)

　　将g1和g2合并为一个集合类对象

　　set @g1 = geomfromtext('polygon((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))');
　　set @g2 = geomfromtext('polygon((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))');

　　select st_union(@g1, @g2);

　　结果：

　　polygon((121.472655 31.230157, 121.471903 31.230229, 121.471898134093 31.2302649098516, 121.471603 31.230229, 121.470724 31.23155, 121.471761757556 31.2333253454665, 121.471775 31.233948, 121.474243 31.234804, 121.474597 31.2339365384615, 121.475777 31.232045, 121.475442678789 31.2318642395248, 121.475777 31.231045, 121.472655 31.230157))

6.2.2 st_difference(g1, g2)

　　返回几何对象，该对象表示了几何值g1与g2的点集合差异

　　set @g1 = geomfromtext('polygon((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))');
　　set @g2 = geomfromtext('polygon((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))');
　　select st_difference(@g1,@g2);

　　结果：

　　multipolygon(((121.471603 31.230229, 121.470724 31.23155, 121.471761757556 31.2333253454665, 121.471724 31.23155, 121.471898134093 31.2302649098516, 121.471603 31.230229)), ((121.475442678789 31.2318642395248, 121.474597 31.2339365384615, 121.475777 31.232045, 121.475442678789 31.2318642395248)))

 

6.2.3 st_intersection(g1,g2)

　　返回几何对象，该对象表示了几何值g1与g2的点集合交集

　　set @g1 = geomfromtext('polygon((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))');
　　set @g2 = geomfromtext('polygon((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))');

　　select st_intersection(@g1,@g2);

　　结果：

　　polygon((121.471898134093 31.2302649098516, 121.471724 31.23155, 121.471761757556 31.2333253454665, 121.471775 31.233348, 121.474243 31.234504, 121.474597 31.2339365384615, 121.475442678789 31.2318642395248, 121.472655 31.230357, 121.471898134093 31.2302649098516))

6.3 几何对象之间空间关系的函数

6.3.1 st_contains(g1, g2)

　　返回1: g1完全包含g2；返回0: g1未包含g2

6.3.2 st_crosses(g1, g2), st_intersects(g1, g2)

　　返回1: g1与g2相交；返回0:g1与g2未相交

6.3.3 st_disjoint(g1, g2)

　　是st_crosses的反函数

6.3.4 st_within(g1, g2)

　　g1在g2内则返回1，否则返回0

7. 空间索引

对表中的geometry类型的字段进行索引可以优化搜索，mysql中通过对geometry对象的mbr创建索引

创建：

create spatial index i_shape on `t_geo_test`(shape);

删除：

drop index i_shape on `t_geo_test`;

8.一些注意事项

8.1 目前mysql中支持的空间坐标系统没有gcj02,bd09等国内坐标系，默认使用wgs84地球坐标系，所以在创建几何对象时输入的坐标值尽量使用wgs84坐标，以避免误差。

8.2 mysql中的计算距离，长度，面积等绝对数值的空间计算函数（area(), glength(), st_distance()）存在一定的误差，尽量不要使用。