- 创建列表
DROP TABLE IF EXISTS `tb_point`; #判断如果存在同名表格则drop创建新表
CREATE TABLE `tb_point` (
`timestamp` date NOT NULL COMMENT '时间戳',
`point` point NOT NULL COMMENT '经纬度', #空间数据field,notnull设置是必须的
`type` char(20) character set utf8 default '' COMMENT '点类型',
`name` varchar(50) character set utf8 NOT NULL COMMENT '点名称',
`introduce` text character set utf8 COMMENT '介绍,包含文字、视频、目录,json格式',
`attention` int(5) NOT NULL default '0' COMMENT '关注人数',
`score` tinyint(2) NOT NULL default '0' COMMENT '评分,用于协同过滤推荐',
`status` tinyint(2) NOT NULL default '2' COMMENT '状态,用于管理员添加',
`uri` varchar(255) character set utf8 default NULL COMMENT '维基uri',
PRIMARY KEY (`timestamp`,`point`(25)), #geometry数据类型不能作为primary key
KEY `index_timestamp` USING BTREE (`timestamp`),
KEY `index_name` USING HASH (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; #选用MYISAM和INNODB都可以,但是MYISAM支持全文检索,如果检索调用较多就用MYISAM,写入频繁就用INNODB
2.Insert示例:
INSERT INTO `tb_point` VALUES ('1900-01-01', GeomFromText('POINT(123.462202 41.804471)'), '景点', '沈阳故宫', '{\"text\":\"1900年左右的沈阳故宫文字介绍\",\"picture\":\"\",\"video\":\"\"}', '1000', '0', '1', null); # GEOMFROMTEXT函数实现WKT到数据库内部几何格式的转换。而GEOMFROMWKB函数用于转换WKB。
3.select示例:
SELECT timestamp,AsText(point),type,name,introduce,attention,score,uri FROM tb_point order by AsText(point),`timestamp` DESC ;# 获取文本空间数据 WKT AsText 函数将其转换为字符串格式 ,AsBinary获取二进制空间数据 WKB
4. Envelope
Envelope(g)
返回几何值g的最小边界矩形(MBR)。结果以Polygon值的形式返回。
多边形(polygon)是由边界框的顶点定义的:
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
mysql> SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));
+-------------------------------------------------------+
| AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) |
+-------------------------------------------------------+
| POLYGON((1 1,2 1,2 2,1 2,1 1)) |
+-------------------------------------------------------+
5. 用以下SQL从数据表中获得空间数据
SELECT id,name,ASTEXT(pnt),ASTEXT(line),ASTEXT(pgn) from `test`;
ASTEXT函数的功能与GEOMFROMTEXT的功能恰好相反,就是将数据从内部格式转换为WKT;相应的ASBINARY可以转换为WKB。
6. 包含关系
- MBRContains(g1,g2)
返回1或0以指明g1的最小边界矩形是否包含g2的最小边界矩形。
- MBRWithin(g1,g2)
返回1或0以指明g1的最小边界矩形是否位于g2的最小边界矩形内。
7.Table 12.19 Spatial Functions
Name | Description |
Area() (deprecated 5.7.6) | Return Polygon or MultiPolygon area |
AsBinary(), AsWKB() (deprecated 5.7.6) | Convert from internal geometry format to WKB |
AsText(), AsWKT() (deprecated 5.7.6) | Convert from internal geometry format to WKT |
Buffer() (deprecated 5.7.6) | Return geometry of points within given distance from geometry |
Centroid() (deprecated 5.7.6) | Return centroid as a point |
Contains() (deprecated 5.7.6) | Whether MBR of one geometry contains MBR of another |
ConvexHull() (deprecated 5.7.6) | Return convex hull of geometry |
Crosses() (deprecated 5.7.6) | Whether one geometry crosses another |
Dimension() (deprecated 5.7.6) | Dimension of geometry |
Disjoint() (deprecated 5.7.6) | Whether MBRs of two geometries are disjoint |
Distance() (deprecated 5.7.6) | The distance of one geometry from another |
EndPoint() (deprecated 5.7.6) | End Point of LineString |
Envelope() (deprecated 5.7.6) | Return MBR of geometry |
Equals() (deprecated 5.7.6) | Whether MBRs of two geometries are equal |
ExteriorRing() (deprecated 5.7.6) | Return exterior ring of Polygon |
GeomCollFromText(), GeometryCollectionFromText()(deprecated 5.7.6) | Return geometry collection from WKT |
GeomCollFromWKB(), GeometryCollectionFromWKB() (deprecated 5.7.6) | Return geometry collection from WKB |
Construct geometry collection from geometries | |
GeometryN() (deprecated 5.7.6) | Return N-th geometry from geometry collection |
GeometryType() (deprecated 5.7.6) | Return name of geometry type |
GeomFromText(), GeometryFromText() (deprecated 5.7.6) | Return geometry from WKT |
GeomFromWKB(), GeometryFromWKB() (deprecated 5.7.6) | Return geometry from WKB |
GLength() (deprecated 5.7.6) | Return length of LineString |
InteriorRingN() (deprecated 5.7.6) | Return N-th interior ring of Polygon |
Intersects() (deprecated 5.7.6) | Whether MBRs of two geometries intersect |
IsClosed() (deprecated 5.7.6) | Whether a geometry is closed and simple |
IsEmpty() (deprecated 5.7.6) | Placeholder function |
IsSimple() (deprecated 5.7.6) | Whether a geometry is simple |
LineFromText(), LineStringFromText() (deprecated 5.7.6) | Construct LineString from WKT |
LineFromWKB(), LineStringFromWKB() (deprecated 5.7.6) | Construct LineString from WKB |
Construct LineString from Point values | |
Whether MBR of one geometry contains MBR of another | |
Whether one MBR is covered by another | |
Whether one MBR covers another | |
Whether MBRs of two geometries are disjoint | |
MBREqual() (deprecated 5.7.6) | Whether MBRs of two geometries are equal |
Whether MBRs of two geometries are equal | |
Whether MBRs of two geometries intersect | |
Whether MBRs of two geometries overlap | |
Whether MBRs of two geometries touch | |
Whether MBR of one geometry is within MBR of another | |
MLineFromText(), MultiLineStringFromText() (deprecated 5.7.6) | Construct MultiLineString from WKT |
MLineFromWKB(), MultiLineStringFromWKB() (deprecated 5.7.6) | Construct MultiLineString from WKB |
MPointFromText(), MultiPointFromText() (deprecated 5.7.6) | Construct MultiPoint from WKT |
MPointFromWKB(), MultiPointFromWKB() (deprecated 5.7.6) | Construct MultiPoint from WKB |
MPolyFromText(), MultiPolygonFromText() (deprecated 5.7.6) | Construct MultiPolygon from WKT |
MPolyFromWKB(), MultiPolygonFromWKB() (deprecated 5.7.6) | Construct MultiPolygon from WKB |
Contruct MultiLineString from LineString values | |
Construct MultiPoint from Point values | |
Construct MultiPolygon from Polygon values | |
NumGeometries() (deprecated 5.7.6) | Return number of geometries in geometry collection |
NumInteriorRings() (deprecated 5.7.6) | Return number of interior rings in Polygon |
NumPoints() (deprecated 5.7.6) | Return number of points in LineString |
Overlaps() (deprecated 5.7.6) | Whether MBRs of two geometries overlap |
Construct Point from coordinates | |
PointFromText() (deprecated 5.7.6) | Construct Point from WKT |
PointFromWKB() (deprecated 5.7.6) | Construct Point from WKB |
PointN() (deprecated 5.7.6) | Return N-th point from LineString |
PolyFromText(), PolygonFromText() (deprecated 5.7.6) | Construct Polygon from WKT |
PolyFromWKB(), PolygonFromWKB() (deprecated 5.7.6) | Construct Polygon from WKB |
Construct Polygon from LineString arguments | |
SRID() (deprecated 5.7.6) | Return spatial reference system ID for geometry |
Return Polygon or MultiPolygon area | |
Convert from internal geometry format to WKB | |
Generate GeoJSON object from geometry | |
Convert from internal geometry format to WKT | |
Return geometry of points within given distance from geometry | |
Produce strategy option for ST_Buffer() | |
Return centroid as a point | |
Whether one geometry contains another | |
Return convex hull of geometry | |
Whether one geometry crosses another | |
Return point set difference of two geometries | |
Dimension of geometry | |
Whether one geometry is disjoint from another | |
The distance of one geometry from another | |
Minimum distance on earth between two geometries | |
End Point of LineString | |
Return MBR of geometry | |
Whether one geometry is equal to another | |
Return exterior ring of Polygon | |
Produce a geohash value | |
ST_GeomCollFromText(), ST_GeometryCollectionFromText(), ST_GeomCollFromTxt() | Return geometry collection from WKT |
Return geometry collection from WKB | |
Return N-th geometry from geometry collection | |
Return name of geometry type | |
Generate geometry from GeoJSON object | |
Return geometry from WKT | |
Return geometry from WKB | |
Return N-th interior ring of Polygon | |
Return point set intersection of two geometries | |
Whether one geometry intersects another | |
Whether a geometry is closed and simple | |
Placeholder function | |
Whether a geometry is simple | |
Whether a geometry is valid | |
Return latitude from geohash value | |
Return length of LineString | |
Construct LineString from WKT | |
Construct LineString from WKB | |
Return longitude from geohash value | |
Rectangle around two points | |
Construct MultiLineString from WKT | |
Construct MultiLineString from WKB | |
Construct MultiPoint from WKT | |
Construct MultiPoint from WKB | |
Construct MultiPolygon from WKT | |
Construct MultiPolygon from WKB | |
Return number of geometries in geometry collection | |
Return number of interior rings in Polygon | |
Return number of points in LineString | |
Whether one geometry overlaps another | |
Convert geohash value to POINT value | |
Construct Point from WKT | |
Construct Point from WKB | |
Return N-th point from LineString | |
Construct Polygon from WKT | |
Construct Polygon from WKB | |
Return simplified geometry | |
Return spatial reference system ID for geometry | |
Start Point of LineString | |
Return point set symmetric difference of two geometries | |
Whether one geometry touches another | |
Return point set union of two geometries | |
Return validated geometry | |
Whether one geometry is within another | |
Return X coordinate of Point | |
Return Y coordinate of Point | |
StartPoint() (deprecated 5.7.6) | Start Point of LineString |
Touches() (deprecated 5.7.6) | Whether one geometry touches another |
Within() (deprecated 5.7.6) | Whether MBR of one geometry is within MBR of another |
X() (deprecated 5.7.6) | Return X coordinate of Point |
Y() (deprecated 5.7.6) | Return Y coordinate of Point |
最后求关注,求点赞,欢迎大家关注我的公众号
记录所学所用,包括但不限于遥感、地信、气象、生态环境,机器学习知识,相关文献阅读,编程代码实现。偶尔荒腔走板的聊聊其他。欢迎不同领域的朋友们加入进来,多多交流。