-
Mysql空间数据&空间索引(spatial)(5)
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 |
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
SQL SERVER中递归
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
这是目前我见过最好的跨域解决方案！
减少回流与重绘
减少回流与重绘
如何使用KrpanoToolJS在浏览器切图
performance.now() 与 Date.now() 对比
一款纯 JS 实现的轻量化图片编辑器
关于开发 VS Code 插件遇到的 workbench.scm.
前端设计模式——观察者模式
前端设计模式——中介者模式
创建型-原型模式