mysql存儲字段類型 :

• Geometry
  Geometry是幾何對象的基類, 也就是說Point, LineString, Polygon都是Geometry的子類,

• Point
  點對象, 有一個坐標(biāo)值碎乃，沒有長度、面積惠奸、邊界梅誓。
  數(shù)據(jù)格式為『經(jīng)度(longitude)在前，維度(latitude)在后，用空格分隔』 例: POINT(121.213342 31.234532)

• LineString
  線對象, 由一系列點連接而成梗掰。
  如果線從頭至尾沒有交叉嵌言，那就是簡單的（simple）
  如果起點和終點重疊，那就是封閉的（closed）
  數(shù)據(jù)格式為『點與點之間用逗號分隔及穗；一個點中的經(jīng)緯度用空格分隔摧茴，與POINT格式一致』例：LINESTRING(121.342423 31.542423,121.345664 31.246790,121.453178 31.456862)

• Polygon
  多邊形對象」÷剑可以是一個實心平面形苛白，即沒有內(nèi)部邊界，也可以有空洞焚虱，類似紐扣
  數(shù)據(jù)格式為
  『實心型: 一個表示外部邊界的LineString和0個表示內(nèi)部邊界的LineString組成』例:
  POLYGON((121.342423 31.542423,121.345664 31.246790,121.453178 31.456862),(121.563633 31.566652,121.233565 31.234565,121.568756 31.454367))
  『紐扣型: 一個表示外部邊界的LineString和多個表示內(nèi)部邊界的LineString組成』例: POLYGON((0 0,10 0, 10 10, 0 10))

• MultiPoint, MultiLineString, MultiPolygon, GeometryCollection
  為以上對象的集合购裙。
  數(shù)據(jù)格式為下例
  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))

常用函數(shù) :

一、幾何對象屬性查詢函數(shù):

Geometry(為基類函數(shù), 點線面都可用)

1. Dimension(g) : 返回幾何對象g的維數(shù), 點為0, 線為1, 多邊形為2
2. Envelope(g): 返回幾何對象g的最小邊界矩形(xy的極值點)著摔。如果對象為點則返回該點對象缓窜，如果對象為線和多邊形則返回極值xy坐標(biāo)構(gòu)造成的矩形Polygon
3. GeometryType(g): 返回幾何對象g的類型名稱, 點為POINT, 線為LINEPOINT, 多邊形為POLYGON
4. IsClosed(g): 返回幾何對象g是否封閉 ,條件為該線對象首尾point重合則為封閉, 封閉為1, 不封閉為0, 如果幾何對象不為線對象的話, 返回為null
5. IsSimple(g): 返回幾何對象g是否簡單, 條件為該線對象路徑?jīng)]有交叉則為簡單, 簡單為1, 不簡單為0, 如果幾何對象不為線對象的話, 返回為null

Point

1. X(p): 返回該點X坐標(biāo)
2. Y(p): 返回改點Y坐標(biāo)

LineString

1. EndPoint(line): 返回對象line的最后一個點Point
2. StartPoint(line): 返回對象line的第一個點Point
3. PointN(line, N): 返回對象line中第N個點定续，N從1開始

Polygon

1. ExteriorRing(poly): 返回多邊形對象poly的外輪廓線，類型為LineString
2. InteriorRingN(poly, N): 返回對象poly的第N個空洞輪廓線摹察，N從1開始
3. NumInteriorRings(poly): 返回對象poly的空洞個數(shù)

二倡鲸、返回新的幾何對象

1. st_union(g1, g2): 返回 面1和面2的并集
2. st_difference(g1, g2): 返回 面1 - (面1和面2的交集)
3. st_intersection(g1, g2): 返回 面1和面2的交集

三峭状、查詢幾何對象關(guān)系

1. ST_Contains(a,b): 如果幾何對象a完全包含幾何對象b, 則返回1, 否則0
2. ST_Crosses(a,b): 如果a橫跨b,則返回1,否則返回0
3. ST_Disjoint(a,b): 如果a和b不相交,則返回1.否則返回0
4. ST_Equals(a,b): 如果a和b有相同的幾何描述,則返回1, 否則返回0; 例如一棟樓的兩層xy坐標(biāo)描述一致,所以返回為1
5. ST_Intersects(a,b): 與ST_Disjoint結(jié)果完全相反
6. ST_Overlaps(a,b): 兩個維度相同的幾何對象相交的交集是一樣維度的幾何對象時, 返回1 , 否則返回0
7. ST_Touches(a,b): 幾何對象a交且只交于b的邊界時, 返回1, 否則0
8. ST_Within(a,b): 與ST_Contains(a,b)結(jié)果完全相反

四、數(shù)值計算

1. ST_Distance_Sphere(POINT(30 40), POINT(20 30)):計算兩點之間距離(千米)

五胆敞、描述語言轉(zhuǎn)化成幾何對象

1. geomfromtext(''): 空間函數(shù)中, 參數(shù)不可直接寫空間描述格式, 需要用geomfromtext('')來將描述語言轉(zhuǎn)化成函數(shù)的對象,例如, 要查找test表中, 所有和 POLYGON((4 4, 4 6, 6 6, 6 4,4 4)) 相交的多邊形, 則sql寫為select polygon1 from test where st_disjoint(geomfromtext('POLYGON((4 4, 4 6, 6 6, 6 4,4 4))'),polygon1) = 0

六移层、附：MySQL空間相關(guān)函數(shù)一覽表

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(),GeometryCollectionFromText()Return geometry collection from WKT
15. GeomCollFromWKB(),GeometryCollectionFromWKB()Return geometry collection from WKB
16. GeometryCollection() Construct geometry collection from geometries
17. GeometryN() Return N-th geometry from geometry collection
18. GeometryType() Return name of geometry type
19. GeomFromText(),GeometryFromText()Return geometry from WKT
20. GeomFromWKB(),GeometryFromWKB()Return geometry from WKB
21. GLength() Return length of LineString
22. InteriorRingN() Return N-th interior ring of Polygon
23. Intersects() Whether MBRs of two geometries intersect
24. IsClosed() Whether a geometry is closed and simple
25. IsEmpty() Placeholder_function
26. IsSimple() Whether a geometry is simple
27. LineFromText(),LineStringFromText()Construct LineString from WKT
28. LineFromWKB(),LineStringFromWKB()Construct LineString from WKB
29. LineString() Construct LineString from Point values
30. MBRContains() Whether MBR of one geometry contains MBR of another
31. MBRDisjoint() Whether MBRs of two geometries are disjoint
32. MBREqual() Whether MBRs of two geometries are equal
33. MBRIntersects() Whether MBRs of two geometries intersect
34. MBROverlaps() Whether MBRs of two geometries overlap
35. MBRTouches() Whether MBRs of two geometries touch
36. MBRWithin() Whether MBR of one geometry is within MBR of another
37. MLineFromText(),MultiLineStringFromText()Construct MultiLineString from WKT
38. MLineFromWKB(),MultiLineStringFromWKB()Construct MultiLineString from WKB
39. MPointFromText(),MultiPointFromText()Construct MultiPoint from WKT
40. MPointFromWKB(),MultiPointFromWKB()Construct MultiPoint from WKB
41. MPolyFromText(),MultiPolygonFromText()Construct MultiPolygon from WKT
42. MPolyFromWKB(),MultiPolygonFromWKB()Construct MultiPolygon from WKB
43. MultiLineString() Contruct MultiLineString from LineString values
44. MultiPoint() Construct MultiPoint from Point values
45. MultiPolygon() Construct MultiPolygon from Polygon values
46. NumGeometries() Return number of geometries in geometry collection
47. NumInteriorRings() Return number of interior rings in Polygon
48. NumPoints() Return number of points in LineString
49. Overlaps() Whether MBRs of two geometries overlap
50. Point() Construct Point from coordinates
51. PointFromText() Construct Point from WKT
52. PointFromWKB() Construct Point from WKB
53. PointN() Return N-th point from LineString
54. PolyFromText(),PolygonFromText()Construct Polygon from WKT
55. PolyFromWKB(), PolygonFromWKB() Construct Polygon from WKB
56. Polygon() Construct Polygon from LineString arguments
57. SRID() Return spatial reference system ID for geometry
58. ST_Area() Return Polygon or MultiPolygon area
59. ST_AsBinary(), ST_AsWKB() Convert from internal geometry format to WKB
60. ST_AsText(), ST_AsWKT() Convert from internal geometry format to WKT
61. ST_Buffer() Return geometry of points within given distance from geometry
62. ST_Centroid() Return centroid as a point
63. ST_Contains() Whether one geometry contains another
64. ST_Crosses() Whether one geometry crosses another
65. ST_Difference() Return point set difference of two geometries
66. ST_Dimension() Dimension of geometry
67. ST_Disjoint() Whether one geometry is disjoint from another
68. ST_Distance() The distance of one geometry from another
69. ST_EndPoint() End Point of LineString
70. ST_Envelope() Return MBR of geometry
71. ST_Equals() Whether one geometry is equal to another
72. ST_ExteriorRing() Return exterior ring of Polygon
73. ST_GeomCollFromText(),ST_GeometryCollectionFromText(),ST_GeomCollFromTxt()Return geometry collection from WKT
74. ST_GeomCollFromWKB(),ST_GeometryCollectionFromWKB()Return geometry collection from WKB
75. ST_GeometryN() Return N-th geometry from geometry collection
76. ST_GeometryType() Return name of geometry type
77. ST_GeomFromText(),ST_GeometryFromText()Return geometry from WKT
78. ST_GeomFromWKB(),ST_GeometryFromWKB()Return geometry from WKB
79. ST_InteriorRingN() Return N-th interior ring of Polygon
80. ST_Intersection() Return point set intersection of two geometries
81. ST_Intersects() Whether one geometry intersects another
82. ST_IsClosed() Whether a geometry is closed and simple
83. ST_IsEmpty() Placeholder_function
84. ST_IsSimple() Whether a geometry is simple
85. ST_LineFromText(),ST_LineStringFromText()Construct LineString from WKT
86. ST_LineFromWKB(),ST_LineStringFromWKB()Construct LineString from WKB
87. ST_NumGeometries() Return number of geometries in geometry collection
88. ST_NumInteriorRing(),ST_NumInteriorRings()Return number of interior rings in Polygon
89. ST_NumPoints() Return number of points in LineString
90. ST_Overlaps() Whether one geometry overlaps another
91. ST_PointFromText() Construct Point from WKT
92. ST_PointFromWKB() Construct Point from WKB
93. ST_PointN() Return N-th point from LineString
94. ST_PolyFromText(),ST_PolygonFromText()Construct Polygon from WKT
95. ST_PolyFromWKB(),ST_PolygonFromWKB()Construct Polygon from WKB
96. ST_SRID() Return spatial reference system ID for geometry
97. ST_StartPoint() Start Point of LineString
98. ST_SymDifference() Return point set symmetric difference of two geometries
99. ST_Touches() Whether one geometry touches another
100. ST_Union() Return point set union of two geometries
101. ST_Within() Whether one geometry is within another
102. ST_X() Return X coordinate of Point
103. ST_Y() Return Y coordinate of Point
104. StartPoint() Start Point of LineString
105. Touches() Whether one geometry touches another
106. Within() Whether MBR of one geometry is within MBR of another
107. X() Return X coordinate of Point
108. Y() Return Y coordinate of Point