mysql空间_MySQL5.6空间扩展（原创）

MySQL空间扩展

MySQL空间扩展支持几何数据的存储，生成，分析，优化。

1、空间数据类型(存储)

MySQL支持以下数据类型：

Geometry:可以存储所有的几何类型

Point:简单点

LINESTRING:简单线

POLYGON:简单面

MULITIPOINT:多点

MULITILINESTRING:多线

MUILITIPOLYGON:多面

GEOMETRYCOLLECTION:任何几何集合

在创建表的时候可以根据需求选择合适的几何类型存储你的空间数据。

2、空间数据类型的生成

MySQ L支持WKB,WKT数据生成空间数据类型，提供如下函数：

GeomFromText(wtk [,srid)   PointFromText    LINESTRINGFROMTEXT ......

GeomFromWKB(wtk [,srid)   GeomFromWKB  GeomFromWKB ......

3、空间运算分析

MYSQL以函数的形式提供对空间运算的支持：

ST_Buffer    ST_Contains   ST_Distance  ST_Intersects   MBRIntersects ........

4、优化

为了优化查询，MySQL提供空间索引支持，目前仅在MyISAM数据引擎下提供空间索引支持,要求几何字段非空

创建空间索引格式如下：

CREATE SPATIAL INDEX t_geo_test_sidx ON t_geo_test(SHAPE);

可以看到比普通索引多了一个关键字 Spatial，删除空间索引的语句与删除普通索引的语句是一样的。

5、测试

建表：

CREATE TABLE t_geo_test  (

`ID`  int(11) NOT NULL ,

`SHAPE`  geometry NOT NULL ,

PRIMARY KEY (`ID`)

)

ENGINE=MyISAM

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

CHECKSUM=0

ROW_FORMAT=DYNAMIC

DELAY_KEY_WRITE=0

;

插入数据：

insert into t_geo_test select fid,geomfromtext(shape) from zjgis.g_bd_substation_p;

查询：

通过SQL解释可以看到TYPE:ALL KEY:NULL 代表全表扫描

建索引：

CREATE SPATIAL INDEX t_geo_test_sidx ON t_geo_test(SHAPE);

可以看到TYPE:range KEY:t_geo_tesx_sidx 表示使用了空间索引t_geo_tesx_sidx

在数据量比较大的时候，空间索引至关重要。

建索引前查询时间：

建索引后查询时间：

虽然数据不多(3500左右)，查询耗时还是有所差距的。

6、附：MySQL空间相关函数一览表

The following table lists each spatial function and provides a short description of each one.

Name    Description

1、Area() Return Polygon or MultiPolygon area

2、AsBinary(), AsWKB() Convert from internal geometry format to WKB

3、AsText(), AsWKT() Convert from internal geometry format to WKT

4、Buffer() Return geometry of points within given distance from geometry

5、Centroid() Return centroid as a point

6、Contains() Whether MBR of one geometry contains MBR of another

7、Crosses() Whether one geometry crosses another

8、Dimension() Dimension of geometry

9、Disjoint() Whether MBRs of two geometries are disjoint

10、EndPoint() End Point of LineString

11、Envelope() Return MBR of geometry

12、Equals() Whether MBRs of two geometries are equal

13、ExteriorRing() Return exterior ring of Polygon

14、GeomCollFromText(),

15、GeometryCollectionFromText()

16、Return geometry collection from WKT

17、GeomCollFromWKB(),

18、GeometryCollectionFromWKB()

19、Return geometry collection from WKB

20、GeometryCollection() Construct geometry collection from geometries

21、GeometryN() Return N-th geometry from geometry collection

22、GeometryType() Return name of geometry type

23、GeomFromText(),

24、GeometryFromText()

25、Return geometry from WKT

26、GeomFromWKB(),

27、GeometryFromWKB()

28、Return geometry from WKB

29、GLength() Return length of LineString

30、InteriorRingN() Return N-th interior ring of Polygon

31、Intersects() Whether MBRs of two geometries intersect

32、IsClosed() Whether a geometry is closed and simple

33、IsEmpty() Placeholder function

34、IsSimple() Whether a geometry is simple

35、LineFromText(),

36、LineStringFromText()

37、Construct LineString from WKT

38、LineFromWKB(),

39、LineStringFromWKB()

40、Construct LineString from WKB

41、LineString() Construct LineString from Point values

42、MBRContains() Whether MBR of one geometry contains MBR of another

43、MBRDisjoint() Whether MBRs of two geometries are disjoint

44、MBREqual() Whether MBRs of two geometries are equal

45、MBRIntersects() Whether MBRs of two geometries intersect

46、MBROverlaps() Whether MBRs of two geometries overlap

47、MBRTouches() Whether MBRs of two geometries touch

48、MBRWithin() Whether MBR of one geometry is within MBR of another

49、MLineFromText(),

50、MultiLineStringFromText()

51、Construct MultiLineString from WKT

52、MLineFromWKB(),

53、MultiLineStringFromWKB()

54、Construct MultiLineString from WKB

55、MPointFromText(),

56、MultiPointFromText()

57、Construct MultiPoint from WKT

58、MPointFromWKB(),

59、MultiPointFromWKB()

60、Construct MultiPoint from WKB

61、MPolyFromText(),

62、MultiPolygonFromText()

63、Construct MultiPolygon from WKT

64、MPolyFromWKB(),

65、MultiPolygonFromWKB()

66、Construct MultiPolygon from WKB

67、MultiLineString() Contruct MultiLineString from LineString values

68、MultiPoint() Construct MultiPoint from Point values

69、MultiPolygon() Construct MultiPolygon from Polygon values

70、NumGeometries() Return number of geometries in geometry collection

71、NumInteriorRings() Return number of interior rings in Polygon

72、NumPoints() Return number of points in LineString

73、Overlaps() Whether MBRs of two geometries overlap

74、Point() Construct Point from coordinates

75、PointFromText() Construct Point from WKT

76、PointFromWKB() Construct Point from WKB

77、PointN() Return N-th point from LineString

78、PolyFromText(),

79、PolygonFromText()

80、Construct Polygon from WKT

81、PolyFromWKB(), PolygonFromWKB() Construct Polygon from WKB

82、Polygon() Construct Polygon from LineString arguments

83、SRID() Return spatial reference system ID for geometry

84、ST_Area() Return Polygon or MultiPolygon area

85、ST_AsBinary(), ST_AsWKB() Convert from internal geometry format to WKB

86、ST_AsText(), ST_AsWKT() Convert from internal geometry format to WKT

87、ST_Buffer() Return geometry of points within given distance from geometry

88、ST_Centroid() Return centroid as a point

89、ST_Contains() Whether one geometry contains another

90、ST_Crosses() Whether one geometry crosses another

91、ST_Difference() Return point set difference of two geometries

92、ST_Dimension() Dimension of geometry

93、ST_Disjoint() Whether one geometry is disjoint from another

94、ST_Distance() The distance of one geometry from another

95、ST_EndPoint() End Point of LineString

96、ST_Envelope() Return MBR of geometry

97、ST_Equals() Whether one geometry is equal to another

98、ST_ExteriorRing() Return exterior ring of Polygon

99、ST_GeomCollFromText(),

100、ST_GeometryCollectionFromText(),

101、ST_GeomCollFromTxt()

102、Return geometry collection from WKT

103、ST_GeomCollFromWKB(),

104、ST_GeometryCollectionFromWKB()

105、Return geometry collection from WKB

106、ST_GeometryN() Return N-th geometry from geometry collection

107、ST_GeometryType() Return name of geometry type

108、ST_GeomFromText(),

109、ST_GeometryFromText()

110、Return geometry from WKT

111、ST_GeomFromWKB(),

112、ST_GeometryFromWKB()

113、Return geometry from WKB

114、ST_InteriorRingN() Return N-th interior ring of Polygon

115、ST_Intersection() Return point set intersection of two geometries

116、ST_Intersects() Whether one geometry intersects another

117、ST_IsClosed() Whether a geometry is closed and simple

118、ST_IsEmpty() Placeholder function

119、ST_IsSimple() Whether a geometry is simple

120、ST_LineFromText(),

121、ST_LineStringFromText()

122、Construct LineString from WKT

123、ST_LineFromWKB(),

124、ST_LineStringFromWKB()

125、Construct LineString from WKB

126、ST_NumGeometries() Return number of geometries in geometry collection

127、ST_NumInteriorRing(),

128、ST_NumInteriorRings()

129、Return number of interior rings in Polygon

130、ST_NumPoints() Return number of points in LineString

131、ST_Overlaps() Whether one geometry overlaps another

132、ST_PointFromText() Construct Point from WKT

133、ST_PointFromWKB() Construct Point from WKB

134、ST_PointN() Return N-th point from LineString

135、ST_PolyFromText(),

136、ST_PolygonFromText()

137、Construct Polygon from WKT

138、ST_PolyFromWKB(),

139、ST_PolygonFromWKB()

140、Construct Polygon from WKB

141、ST_SRID() Return spatial reference system ID for geometry

142、ST_StartPoint() Start Point of LineString

143、ST_SymDifference() Return point set symmetric difference of two geometries

144、ST_Touches() Whether one geometry touches another

145、ST_Union() Return point set union of two geometries

146、ST_Within() Whether one geometry is within another

147、ST_X() Return X coordinate of Point

148、ST_Y() Return Y coordinate of Point

149、StartPoint() Start Point of LineString

150、Touches() Whether one geometry touches another

151、Within() Whether MBR of one geometry is within MBR of another

152、X() Return X coordinate of Point

153、Y() Return Y coordinate of Point