MySQL支持的空间数据格式有WKT,WKB,数据类型有:POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION。
例如WKT格式如下:
POINT(15, 20)
LINESTRING(0 0, 10 10, 20 25, 50 60)
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
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))
WKB格式则使用BLOB类型。
创建空间数据库:
createdatabasespatialtest;
创建可以存储点、线、面的空间数据表:
createtablet_geo(geo geometry);
创建存储点的空间数据表:
createtablet_pt(pt point);
创建存储线的空间数据表:
createtablet_line(line linestring);
创建存储面的空间数据表:
createtablet_polygon(poly polygon);
插入WKT数据测试:
insert into t_geo(geo) values(GeometryFromText('POINT(0 0)'));
insertintot_geo(geo)values(GeometryFromText('POLYGON((5 0.0,0.0 -5,-5 0,0.0 5,5 0.0))'));
insertintot_geo(geo)values(GeometryFromText('LINESTRING(10 0, 5 5, 9 8, -10 -10)'));
insertintot_pt(pt)values(GeometryFromText('POINT(0 2)'));
insertintot_line(line)values(GeometryFromText('LINESTRING(10 0, 5 5, 9 8, -10 -10)'));
insertintot_polygon(poly)values(GeometryFromText('POLYGON((5 0.0,0.0 -5,-5 0,0.0 5,5 0.0))'));
查询数据测试:
selectAsbinary(pt)fromt_pt;
selectAstext(pt)fromt_pt;
给表创建空间索引:
altertablet_ptaddspatialindex(pt);
返回错误:
The usedtabletype doesn't support SPATIAL indexes
查询文档发现:Before MySQL 5.0.16, these features are available forMyISAMtables only. As of MySQL 5.0.16,InnoDB,NDB,BDB, andARCHIVEalso support spatial features.
所以需要执行如下语句:
ALTERTABLE`spatialtest`.`t_geo` ENGINE = MyISAM ;
然后在执行创建空间索引则返回成功。