mysql spatial 函数_MySQL中spatial基本操作

mysql的基本空间操作

具体参见 https://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference.html

新版函数中很多添加了ST_前缀 在将来的一个版本中会废除旧的函数，新函数参见上面url

1.1 创建点要素

Create table point(

name varchar(10),

geom point

);

1.2 插入要素 GeomFromText 可换成 PointFromText

insert into point values(‘xiaoming’,GeomFromText(‘POINT(1 1)’));

SET @g = ‘POINT(2 2)’;

insert into point values(‘daming’,GeomFromText(@g));

2.1 创建线要素

Create table line(

name varchar(10),

geom LineString

);

2.2 插入线要素

SET @l = ‘LINESTRING(0 0,1 1,2 2)’;

INSERT INTO line VALUES(‘xiaoming’,LineStringFromText(@l));

INSERT INTO line VALUES(‘daming’,GeomFromText(‘LINESTRING(0 0,1 1,2 2,3 3)’));

3.1 创建面要素

Create table polygon(

name varchar(10),

geom polygon

);

3.2 插入面要素 可用 PolygonFromText 函数

SET @g = ‘POLYGON((0 0,3 0,3 3,0 3,0 0))’;

INSERT INTO polygon VALUES(‘xiaoming’,GeomFromText(@g));

SET @g2 = ‘POLYGON((0 0,3 0,3 3,0 3,0 0),(1 1,2 0,2 2,0 2,1 1))’;

INSERT INTO polygon VALUES(‘xiaoming’,GeomFromText(@g2));

4.1 创建GEOMETRY 要素(点线面的混合)

Create table Geometry(

name varchar(10),

geom GEOMETRY

);

4.2 插入数据

insert into Geometry values(‘xiaoming’,GeomFromText(‘POINT(1 1)’));

INSERT INTO Geometry VALUES(‘daming’,GeomFromText(‘LINESTRING(0 0,1 1,2 2,3 3)’));

SET @g = ‘POLYGON((0 0,3 0,3 3,0 3,0 0))’;

INSERT INTO Geometry VALUES(‘xiaoming’,GeomFromText(@g));

5 获取空间数据

5.1 获取文本空间数据 WKT AsText 函数将其转换为字符串格式

select name,AsText(geom) from point;

select name,AsText(geom) from Geometry;

5.2 获取二进制空间数据 WKB

select name,AsBinary(geom) from point;

6 空间数据属性查看

6.1 Geometry 通用函数

6.1.1 维度函数 -1,0(点) 1(线) 2(面)

select Dimension(geom) from point;

select Dimension(geom) from Geometry;

6.1.2 最小边界函数

select AsText(Envelope(geom)) from line;

select AsText(Envelope(geom)) from point;

6.1.3 几何类型函数

select GeometryType(geom) from Geometry;

6.2 point 函数 X(),Y() 分别返回点的经纬度

select x(geom),Y(geom) from point;

6.3 lingString 函数

6.3.1 EndPoint 返回最后一个点 StarPoint 返回线段的第一个点

select AsText(EndPoint(geom)) from line;

6.3.2 Glength 返回线段的长度

select Glength(geom) from line;

6.3.3 NumPoints 返回线段的节点数

select NumPoints(geom) from line;

6.3.4 PointN 返回线段的第N个点

select AsText(PointN(geom,2)) from line;

6.4 POLYGON 函数

6.4.1 计算面积Area

select Area(geom) from polygon;

6.4.2 外环 ExteriorRing 返回linestring

select Astext(ExteriorRing(geom)) from polygon;

6.4.3 内环 InteriorRingN(geom,N) N从1开始

SELECT ASTEXT(InteriorRingN(geom,1)) from polygon;

6.4.3 环数 NumInteriorRings(geom);

select NumInteriorRings(geom) from polygon;

7 空间数据关系

7.1 基于几何最小边界

7.1.1 MBRContains(g1,g2) 返回1说明g1的最小边界矩形包含g2

select MBRContains(polygon.geom,point.geom) from point,polygon;

选择包含在多边形最小矩形边界中的点

select AsText(point.geom) from point,polygon where MBRContains(polygon.geom,point.geom);

7.1.2 还有各种最小边界相交 查看 http://tool.oschina.net/uploads/apidocs/mysql-5.1-zh/spatial-extensions-in-mysql.html#creating-a-spatially-enabled-mysql-database

7.2 包含在说明范围 已实现

select AsText(point.geom) from point,polygon where ST_Contains(polygon.geom,point.geom);