下面的例子插入了更加复杂的几何数据到表中
SET @g = 'LINESTRING(0 0,1 1,2 2)'; INSERT INTO geom VALUES (ST_GeomFromText(@g));
SET @g = ‘POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))’;
INSERT INTO geom VALUES (ST_GeomFromText(@g));
SET @g =‘GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))’;
INSERT INTO geom VALUES (ST_GeomFromText(@g));
前面的语句使用了ST_GeomFromText() ST_GeomFromText()
SET @g = 'POINT(1 1)'; INSERT INTO geom VALUES (ST_PointFromText(@g));
SET @g = ‘LINESTRING(0 0,1 1,2 2)’;
INSERT INTO geom VALUES (ST_LineStringFromText(@g));
SET @g = ‘POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))’;
INSERT INTO geom VALUES (ST_PolygonFromText(@g));
SET @g =
‘GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))’;
INSERT INTO geom VALUES (ST_GeomCollFromText(@g));
一个应用程序想要在查询服务器的时候使用WKB 表示几何数值.下面有几个方法实现这个需求。例如
-
插入一个
POINT(1 1)使用二进制语法:mysql> INSERT INTO geom VALUES -> (ST_GeomFromWKB(0x0101000000000000000000F03F000000000000F03F)); -
一个ODBC应用可以发送一个WKB表达式，并把他绑定到一个占位符上，使用BLOB
类型: INSERT INTO geom VALUES (ST_GeomFromWKB(?))
其他程序接口可能支持类似的占位符机制
-
C 语言中，你可以使用
mysql_real_escape_string() 跳过一个二进制数值，包含结果到一个查询字符串中
12.5.3.4 读取Fetching Spatial Data
Geometry数值存储在表格中可以读取到内部格式，你可以转换成WKT或者WKB格式
-
读取空间数据到内部格式：
读出来的内部格式在表和表之间传递很有用
CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;
-
读取到WKT格式：
ST_AsText()方法将内部格式转换成WKT字符串 SELECT ST_AsText(g) FROM geom;
-
读取到WKB格式：
ST_AsBinary()方法将内部格式转换到一个 包含WKB值得 BLOB SELECT ST_AsBinary(g) FROM geom;
12.5.3.5 优化空间分析Optimizing Spatial Analysis
对于MyISAM InnoDB SPATIAL
-
Point 搜索：搜索包含一个给定的点的所有对象
-
地区搜索：搜索与给定区域重叠的所有对象
MySQL使用
同样也可能创建普通索引到空间列上，在一个非空间索引, you must declare a prefix for any spatial column except for POINT
MyISAM InnoDB SPATIAL SPATIAL
12.5.3.6 Creating Spatial Indexes
For MyISAM and (as of MySQL 5.7.5) InnoDB tables, MySQL可以使用类似普通索引的语法创建空间索引， 但是需要使用
SPATIAL INDEX 创建了一个R-tree 索引。对于支持非空间索引的存储引擎，引擎创建一个B-tree索引. B-tree 索引在准确值查找上是很有用的，但在范围扫描就不一样了。
删除spatial indexes, use ALTER TABLE or DROP INDEX:
Example: 假设一个 table geom包含超过32,000 geometries,存放在类型为GEOMETRY 的
mysql> DESCRIBE geom;
+-------+----------+------+-----+---------+----------------+
+-------+----------+------+-----+---------+----------------+
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM geom;
+----------+
| count(*)
+----------+
|
+----------+
1 row in set (0.00 sec)
添加一个空间索引到g。
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g) ENGINE=MyISAM;
Query OK, 32376 rows affected (4.05 sec)
Records: 32376
12.5.3.7 Using Spatial Indexes
优化器调查是否可用空间索引参与搜索查询，这个查询在WHERE子句中使用函数如MBRContains()或MBRWithin()。以下查询找到所有对象在给定的矩形:
mysql> SET @poly =
mysql> SELECT fid,ST_AsText(g) FROM geom WHERE
+-----+---------------------------------------------------------------+
| fid | ST_AsText(g)
+-----+---------------------------------------------------------------+
|
|
|
|
|
|
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
|
|
|
|
|
|
|
|
|
|
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.00 sec)
使用EXPLAIN 检查这个查询被执行的方式
mysql> SET @poly =
mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM geom WHERE
*************************** 1. row ***************************
possible_keys: g
1 row in set (0.00 sec)
如果不使用空间索引的情况:
mysql> SET @poly =
mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM g IGNORE INDEX (g) WHERE
*************************** 1. row ***************************
possible_keys: NULL
1 row in set (0.00 sec)
mysql> SET @poly =
mysql> SELECT fid,ST_AsText(g) FROM geom IGNORE INDEX (g) WHERE
+-----+---------------------------------------------------------------+
| fid | ST_AsText(g)
+-----+---------------------------------------------------------------+
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.46 sec)
13.15
MySQL提供了在空间数据上执行各种操作的函数。根据操作的类型这些函数可以被分成几个大类
-
创建各种格式几何图形函数
(WKT, WKB, internal) -
几何图形格式之间的转换函数
-
几何的定性或定量属性的访问函数
-
描述两个图形之间的关系函数
-
从现有的创建新的几何图形函数
13.15.1
The following table lists each spatial function and provides a short description of each one.
Table
| Name | Description |
|---|---|
Area() |
Return Polygon or MultiPolygon area |
AsBinary(), AsWKB() |
Convert from internal geometry format to WKB |
AsText(), AsWKT() |
Convert from internal geometry format to WKT |
Buffer() |
Return geometry of points within given distance from geometry |
Centroid() |
Return centroid as a point |
Contains() |
Whether MBR of one geometry contains MBR of another |
ConvexHull() |
Return convex hull of geometry |
Crosses() |
Whether one geometry crosses another |
Dimension() |
Dimension of geometry |
Disjoint() |
Whether MBRs of two geometries are disjoint |
Distance() |
The distance of one geometry from another |
EndPoint() |
End Point of LineString |
Envelope() |
Return MBR of geometry |
Equals() |
Whether MBRs of two geometries are equal |
ExteriorRing() |
Return exterior ring of Polygon |
GeomCollFromText(), GeometryCollectionFromTe |
Return geometry collection from WKT |
GeomCollFromWKB(), GeometryCollectionFromWK |
Return geometry collection from WKB |
GeometryCollection()
|
从几何图形构造几何图形集合
|
GeometryN() |
Return N-th geometry from geometry collection |
GeometryType() |
Return name of geometry type |
GeomFromText(), GeometryFromText() |
Return geometry from WKT |
GeomFromWKB(), GeometryFromWKB() |
Return geometry from WKB |
GLength() |
Return length of LineString |
InteriorRingN() |
Return N-th interior ring of Polygon |
Intersects() |
Whether MBRs of two geometries intersect |
IsClosed() |
Whether a geometry is closed and simple |
IsEmpty() |
Placeholder function |
IsSimple() |
Whether a geometry is simple |
LineFromText(), LineStringFromText() |
Construct LineString from WKT |
LineFromWKB(), LineStringFromWKB() |
Construct LineString from WKB |
LineString()
|
构造 |
MBRContains()
|
一个几何的MBR包含了另一个的MBR |
MBRCoveredBy()
|
一个MBR是否被另一个覆盖 |
MBRCovers()
|
一个MBR是否覆盖了另一个 |
MBRDisjoint()
|
两个几何体的MBR是否分离 |
MBREqual() |
Whether MBRs of two geometries are equal |
MBREquals()
|
两个几何体的MBR是否相同 |
MBRIntersects()
|
两个几何体的MBR是否相交 |
MBROverlaps()
|
两个几何体的MBR是否重叠 |
MBRTouches()
|
两个几何体的MBR是否触碰 |
MBRWithin()
|
一个几何体的MBR是否在另一个的MBR里面 |
MLineFromText(), MultiLineStringFromText() |
Construct MultiLineString from WKT |
MLineFromWKB(), MultiLineStringFromWKB() |
Construct MultiLineString from WKB |
MPointFromText(), MultiPointFromText() |
Construct MultiPoint from WKT |
MPointFromWKB(), MultiPointFromWKB() |
Construct MultiPoint from WKB |
MPolyFromText(), MultiPolygonFromText() |
Construct MultiPolygon from WKT |
MPolyFromWKB(), MultiPolygonFromWKB() |
Construct MultiPolygon from WKB |
MultiLineString()
|
Contruct MultiLineString from LineString values |
MultiPoint()
|
Construct MultiPoint from Point values |
MultiPolygon()
|
Construct MultiPolygon from Polygon values |
NumGeometries() |
Return number of geometries in geometry collection |
NumInteriorRings() |
Return number of interior rings in Polygon |
NumPoints() |
Return number of points in LineString |
Overlaps() |
Whether MBRs of two geometries overlap |
Point()
|
Construct Point from coordinates |
PointFromText() |
Construct Point from WKT |
PointFromWKB() |
Construct Point from WKB |
PointN() |
Return N-th point from LineString |
PolyFromText(), PolygonFromText() |
Construct Polygon from WKT |
PolyFromWKB(), PolygonFromWKB() |
Construct Polygon from WKB |
Polygon()
|
Construct Polygon from LineString arguments |
SRID() |
Return spatial reference system ID for geometry |
ST_Area()
|
返回 Polygon or MultiPolygon 范围 |
ST_AsBinary(), ST_AsWKB()
|
将内部格式转换成WKB |
ST_AsGeoJSON()
|
从几何体中生成GeoJSON |
ST_AsText(), ST_AsWKT()
|
将内部格式转换成WKT |
ST_Buffer()
|
返回给定几何体给定距离内的几何体的点 |
ST_Buffer_Strategy()
|
ST_Buffer()生成策略选项 |
ST_Centroid()
|
返回几何中心点 |
ST_Contains()
|
是否一个几何体包含另外一个 |
ST_ConvexHull()
|
Return convex hull of geometry |
ST_Crosses()
|
是否一个几何体和其他的交叉 |
ST_Difference()
|
返回两个几何体不同的点集合 |
ST_Dimension()
|
几何体维度 |
ST_Disjoint()
|
一个几何体是否和另一个分离 |
ST_Distance()
|
两个几何体的距离 |
ST_Distance_Sphere()
|
两个几何体在地球上的最小距离 |
ST_EndPoint()
|
返回LineString的结束点 |
ST_Envelope()
|
返回几何体MBR |
ST_Equals()
|
两个几何体是否相等 |
ST_ExteriorRing()
|
返回Polygon的外部圈 |
ST_GeoHash()
|
生成geohash值 |
ST_GeomCollFromText(), ST_GeometryCollectionFromTe, ST_GeomCollFromTxt()
|
返回从 WKT生成的几何体集合 |
ST_GeomCollFromWKB(), ST_GeometryCollectionFromWK
|
返回从 WKB生成的几何体集合 |
ST_GeometryN()
|
返回集合中第N个几何体 |
ST_GeometryType()
|
返回几何体类型 |
ST_GeomFromGeoJSON()
|
从GeoJSON对象生成几何体 |
ST_GeomFromText(), ST_GeometryFromText()
|
从 WKT返回几何体 |
ST_GeomFromWKB(), ST_GeometryFromWKB()
|
从WKB返回几何体 |
ST_InteriorRingN()
|
返回Polygon的第N个内部环 |
ST_Intersection()
|
返回两个几何体的交叉点集合 |
ST_Intersects()
|
一个几何体是否和另一个交叉 |
ST_IsClosed()
|
一个几何体是否是simple且closed |
ST_IsEmpty()
|
Placeholder function |
ST_IsSimple()
|
Whether a geometry is simple |
ST_IsValid()
|
Whether a geometry is valid |
ST_LatFromGeoHash()
|
从geohash返回纬度 |
ST_Length()
|
Return length of LineString |
ST_LineFromText(), ST_LineStringFromText()
|
Construct LineString from WKT |
ST_LineFromWKB(), ST_LineStringFromWKB()
|
Construct LineString from WKB |
ST_LongFromGeoHash()
|
从geohash返回经度 |
ST_MakeEnvelope()
|
两点之间的矩形 |
ST_MLineFromText(), ST_MultiLineStringFromText()
|
Construct MultiLineString from WKT |
ST_MLineFromWKB(), ST_MultiLineStringFromWKB()
|
Construct MultiLineString from WKB |
ST_MPointFromText(), ST_MultiPointFromText()
|
Construct MultiPoint from WKT |
ST_MPointFromWKB(), ST_MultiPointFromWKB()
|
Construct MultiPoint from WKB |
ST_MPolyFromText(), ST_MultiPolygonFromText()
|
Construct MultiPolygon from WKT |
ST_MPolyFromWKB(), ST_MultiPolygonFromWKB()
|
Construct MultiPolygon from WKB |
ST_NumGeometries()
|
返回集合中的几何体个数 |
ST_NumInteriorRing(), ST_NumInteriorRings()
|
|
ST_NumPoints()
|
返回LineString中点的个数 |
ST_Overlaps()
|
是否一个几何体和另一个重叠 |
ST_PointFromGeoHash()
|
从geohash转换成POINT值 |
ST_PointFromText()
|
从WKT生成POINT |
ST_PointFromWKB()
|
从WKB生成POINT |
ST_PointN()
|
返回LineString的第N点 |
ST_PolyFromText(), ST_PolygonFromText()
|
Construct Polygon from WKT |
ST_PolyFromWKB(), ST_PolygonFromWKB()
|
Construct Polygon from WKB |
ST_Simplify()
|
返回简化的几何体 |
ST_SRID()
|
返回几何体的空间关系系统ID |
ST_StartPoint()
|
LineString的开始点 |
ST_SymDifference()
|
Return point set symmetric difference of two geometries |
ST_Touches()
|
一个几何体是否触碰到另一个 |
ST_Union()
|
返回两个几何体所有点的联合集合 |
ST_Validate()
|
Return validated geometry |
ST_Within()
|
一个几何体是否在另一个中 |
ST_X()
|
返回点的X坐标 |
ST_Y()
|
返回点的Y坐标 |
StartPoint() |
Start Point of LineString |
Touches() |
Whether one geometry touches another |
Within() |
Whether MBR of one geometry is within MBR of another |
X() |
Return X coordinate of Point |
Y() |
Return Y coordinate of Point |