Chapter聽19.聽Spatial Extensions in MySQL - MySQL 5.1参考手册中文版

GeomCollFromText(wkt[,srid]) , GeometryCollectionFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��GEOMETRYCOLLECTION鍊笺��

GeomFromText(wkt[,srid]) , GeometryFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��浠讳綍绫诲瀷鐨勫嚑浣曞�笺��

LineFromText(wkt[,srid]) , LineStringFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��LINESTRING鍊笺��

MLineFromText(wkt[,srid]) , MultiLineStringFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��MULTILINESTRING鍊笺��

MPointFromText(wkt[,srid]) , MultiPointFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��MULTIPOINT鍊笺��

MPolyFromText(wkt[,srid]) , MultiPolygonFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��MULTIPOLYGON鍊笺��

PointFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��POINT鍊笺��

PolyFromText(wkt[,srid]) , PolygonFromText(wkt[,srid])

浣跨敤鍏�WKT琛ㄧず鍜�SRID鏋勯��POLYGON鍊笺��

BdMPolyFromText(wkt,srid)

浠ュ寘鍚�宸插叧闂�LineString鍊间换鎰忛泦鍚堢殑WKT鏍煎紡锛屼粠MultiLineString鍊兼瀯閫�MultiPolygon鍊笺��

BdPolyFromText(wkt,srid)

浠ュ寘鍚�宸插叧闂�LineString鍊间换鎰忛泦鍚堢殑WKT鏍煎紡锛屼粠MultiLineString鍊兼瀯閫�Polygon鍊笺��

GeomCollFromWKB(wkb[,srid]) , GeometryCollectionFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��GEOMETRYCOLLECTION鍊笺��

GeomFromWKB(wkb[,srid]) , GeometryFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��浠绘剰绫诲瀷鐨勫嚑浣曞�笺��

LineFromWKB(wkb[,srid]) , LineStringFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��LINESTRING鍊笺��

MLineFromWKB(wkb[,srid]) , MultiLineStringFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��MULTILINESTRING鍊笺��

MPointFromWKB(wkb[,srid]) , MultiPointFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��MULTIPOINT鍊笺��

MPolyFromWKB(wkb[,srid]) , MultiPolygonFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��MULTIPOLYGON鍊笺��

PointFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��POINT鍊笺��

PolyFromWKB(wkb[,srid]) , PolygonFromWKB(wkb[,srid])

浣跨敤鍏�WKB琛ㄧず鍜�SRID鏋勯��POLYGON鍊笺��

BdMPolyFromWKB(wkb,srid)

浠ュ寘鍚�宸插叧闂�LineString鍊间换鎰忛泦鍚堢殑WKB鏍煎紡锛屼粠MultiLineString鍊兼瀯閫�MultiPolygon鍊笺��

BdPolyFromWKB(wkb,srid)

浠ュ寘鍚�宸插叧闂�LineString鍊间换鎰忛泦鍚堢殑WKB鏍煎紡锛屼粠MultiLineString鍊兼瀯閫�Polygon鍊笺��

GeometryCollection(g1,g2,...)

鏋勯��WKB GeometryCollection銆傚�傛灉浠讳綍鍙傞噺涓嶆槸鏋勯�犺壇濂界殑鍑犱綍瀵硅薄WKB琛ㄨ揪寮忥紝杩斿洖鍊间负NULL銆�

LineString(pt1,pt2,...)

浠庡�氫��WKB Point鍙傞噺鏋勯��WKB LineString鍊�銆傚�傛灉浠讳綍鍙傞噺涓嶆��WKB Point锛岃繑鍥炲�间负NULL銆傚�傛��Point鍙傞噺鐨勬暟鐩�灏忎��2锛岃繑鍥炲�间负NULL銆�

MultiLineString(ls1,ls2,...)

浣跨敤WKB LineString鍙傞噺鏋勯��WKB MultiLineString鍊�銆傚�傛灉浠讳綍鍙傞噺涓嶆��WKB LineString锛岃繑鍥炲�间负NULL銆�

MultiPoint(pt1,pt2,...)

浣跨敤WKB Point鍙傞噺鏋勯��WKB MultiPoint鍊笺�傚�傛灉浠讳綍鍙傞噺涓嶆��WKB Point锛岃繑鍥炲�间负NULL銆�

MultiPolygon(poly1,poly2,...)

浠庝竴缁�WKB Polygon鍙傞噺鏋勯��WKB MultiPolygon鍊笺�傚�傛灉浠讳綍鍙傞噺涓嶆��WKB Polygon锛岃繑鍥炲�间负NULL銆�

Point(x,y)

浣跨敤鍏跺潗鏍囨瀯閫�WKB Point銆�

Polygon(ls1,ls2,...)

浠庡�氫��WKB LineString鍙傞噺鏋勯��WKB Polygon鍊笺�傚�傛灉浠讳綍鍙傞噺鏈�琛ㄧず涓�LinearRing鐨�WKB褰㈠紡锛堝嵆锛岄潪灏侀棴鍜岀畝鍗�LineString锛�锛岃繑鍥炲�间负NULL銆�

Dimension(g)

杩斿洖鍑犱綍鍊�g鐨勫浐鏈夌淮鏁般�傜粨鏋滃彲浠ユ槸-1銆�0銆�1鎴�2銆傦紙鍏充簬杩欎簺鍊肩殑鍚�涔夛紝璇峰弬瑙�19.2.2鑺傦紝鈥滅被Geometry鈥�锛夈��

mysql> SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));
+------------------------------------------------+
| Dimension(GeomFromText('LineString(1 1,2 2)')) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

Envelope(g)

杩斿洖鍑犱綍鍊�g鐨勬渶灏忚竟鐣岀煩褰�锛圡BR锛夈�傜粨鏋滀互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))                        |
+-------------------------------------------------------+

GeometryType(g)

浠ュ瓧绗︿覆褰㈠紡杩斿洖鍑犱綍绫诲瀷鐨勫悕绉帮紝鍑犱綍瀹炰緥g鏄�鍑犱綍绫诲瀷鐨勬垚鍛樸�傝�ュ悕绉颁笌鍙�瀹炰緥鍖栧嚑浣曞瓙绫讳箣涓�瀵瑰簲銆�

mysql> SELECT GeometryType(GeomFromText('POINT(1 1)'));
+------------------------------------------+
| GeometryType(GeomFromText('POINT(1 1)')) |
+------------------------------------------+
| POINT                                    |
+------------------------------------------+

SRID(g)

杩斿洖鎸囨槑浜嗗嚑浣曞��g鐨勭┖闂村弬鑰冪郴缁�ID鐨勬暣鏁般��

鍦�MySQL涓�锛�SRID鍊间粎鏄�涓庡嚑浣曞�肩浉鍏崇殑鏁存暟銆傛墍鏈夎�＄畻鍧囨槸鍦ㄦ�у嚑閲屽緱锛堝钩闈�锛夊嚑浣曚腑杩涜�岀殑銆�

mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
+-----------------------------------------------+
| SRID(GeomFromText('LineString(1 1,2 2)',101)) |
+-----------------------------------------------+
|                                           101 |
+-----------------------------------------------+

Boundary(g)

杩斿洖鍑犱綍鍊�g鐨勭粍鍚堣竟鐣岀殑闂�鍖呯殑鍑犱綍瀵硅薄銆�

IsEmpty(g)

濡傛灉鍑犱綍鍊�g涓虹┖鐨勫嚑浣曞�硅薄锛岃繑鍥�1锛屽�傛灉闈炵┖锛岃繑鍥�0锛屽�傛灉鍙傞噺涓�NULL锛岃繑鍥�-1銆傚�傛灉鍑犱綍瀵硅薄鏄�绌虹殑锛屽畠琛ㄧず绌虹殑鐐归泦鍚堛��

IsSimple(g)

鐩�鍓嶈�ュ嚱鏁版槸鍗犱綅绗︼紝涓嶅簲浣跨敤瀹冦�傚�傛灉瀹炴柦浜嗗畠锛屽叾琛屼负涓庝笅娈垫墍缁欏嚭鐨勬弿杩扮被浼笺��

濡傛灉鍑犱綍鍊糶娌℃湁寮傚父鐨勫嚑浣曠偣锛堝�傝嚜鐩镐氦鎴栬嚜鐩稿垏锛夛紝杩斿��1銆傚�傛灉鍙傞噺涓嶆槸绠�鍗曞弬閲忥紝IsSimple()杩斿洖0锛�濡傛灉鍙傞噺鏄疦ULL锛岃繑鍥�-1銆�

X(p)

浠ュ弻绮惧害鏁板�艰繑鍥炵偣p鐨�X鍧愭爣鍊笺��

mysql> SELECT X(GeomFromText('Point(56.7 53.34)'));
+--------------------------------------+
| X(GeomFromText('Point(56.7 53.34)')) |
+--------------------------------------+
|                                 56.7 |
+--------------------------------------+

Y(p)

浠ュ弻绮惧害鏁板�艰繑鍥炵偣p鐨�Y鍧愭爣鍊笺��

mysql> SELECT Y(GeomFromText('Point(56.7 53.34)'));
+--------------------------------------+
| Y(GeomFromText('Point(56.7 53.34)')) |
+--------------------------------------+
|                                53.34 |
+--------------------------------------+

EndPoint(ls)

杩斿洖LineString鍊�1s鐨勬渶鍚庝竴涓�鐐圭��Point銆�

mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3)                          |
+-------------------------------------+

GLength(ls)

浠ュ弻绮惧害鏁板�艰繑鍥�LineString鍊�1s鍦ㄧ浉鍏崇殑绌洪棿鍙傝�冪郴涓�鐨勯暱搴︺��

mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT GLength(GeomFromText(@ls));
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
|            2.8284271247462 |
+----------------------------+

NumPoints(ls)

杩斿洖LineString鍊�1s涓�鐨勭偣鏁般��

mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT NumPoints(GeomFromText(@ls));
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
|                            3 |
+------------------------------+

PointN(ls,n)

杩斿洖LineString鍊�1s涓�鐨勭��n涓�鐐广�傜偣缂栧彿浠�1寮�濮嬨��

mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(PointN(GeomFromText(@ls),2));
+-------------------------------------+
| AsText(PointN(GeomFromText(@ls),2)) |
+-------------------------------------+
| POINT(2 2)                          |
+-------------------------------------+

StartPoint(ls)

杩斿洖LineString鍊�1s鐨勭��涓�涓�鐐圭��Point銆�

mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(StartPoint(GeomFromText(@ls)));
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1)                            |
+---------------------------------------+

IsRing(ls)

濡傛灉LineString鍊�ls鏄�灏侀棴鐨勶紙鍗冲��StartPoint()鍜�EndPoint()鍊肩浉鍚�锛夊拰绠�鍗曠殑锛堟湭閫氳繃鐩稿悓鐨勭偣1娆′互涓婏級杩斿洖1銆傚�傛��ls涓嶆槸鐜�锛岃繑鍥�0锛屽�傛灉瀹冩��NULL锛岃繑鍥�-1銆�

GLength(mls)

浠ュ弻绮惧害鏁板�煎舰寮忚繑鍥�MultiLineString鍊�m1s鐨勯暱搴︺��mls鐨勯暱搴︾瓑浜庡叾鍏冪礌鐨勯暱搴︿箣鍜屻��

mysql> SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';
mysql> SELECT GLength(GeomFromText(@mls));
+-----------------------------+
| GLength(GeomFromText(@mls)) |
+-----------------------------+
|             4.2426406871193 |
+-----------------------------+

IsClosed(mls)

濡傛灉MultiLineString鍊�m1s鏄�灏侀棴鐨勶紙鍗�StartPoint()鍜�EndPoint()鍊煎��m1s涓�鐨勬瘡涓�LineString鏄�鐩稿悓鐨�锛夎繑鍥�1銆傚�傛��mls鏄�闈炲皝闂�鐨勶紝杩斿洖0锛屽�傛灉瀹冩��NULL锛岃繑鍥�-1銆�

mysql> SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';
mysql> SELECT IsClosed(GeomFromText(@mls));
+------------------------------+
| IsClosed(GeomFromText(@mls)) |
+------------------------------+
|                            0 |
+------------------------------+

Area(poly)

浠ュ弻绮惧害鏁板�煎舰寮忚繑鍥�Polygon鍊�poly鐨勯潰绉�锛屾牴鎹�鍦ㄥ叾绌洪棿鍙傝�冪郴涓�鐨勬祴閲忓�笺��

mysql> SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';
mysql> SELECT Area(GeomFromText(@poly));
+---------------------------+
| Area(GeomFromText(@poly)) |
+---------------------------+
|                         4 |
+---------------------------+

ExteriorRing(poly)

浠�LineString褰㈠紡杩斿洖Polygon鍊�poly鐨勫�栫幆銆�

mysql> SET @poly =
    -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
mysql> SELECT AsText(ExteriorRing(GeomFromText(@poly)));
+-------------------------------------------+
| AsText(ExteriorRing(GeomFromText(@poly))) |
+-------------------------------------------+
| LINESTRING(0 0,0 3,3 3,3 0,0 0)           |
+-------------------------------------------+

InteriorRingN(poly,n)

浠�LineString褰㈠紡杩斿洖Polygon鍊�poly鐨勭��n涓�鍐呯幆銆傜幆缂栧彿浠�1寮�濮嬨��

mysql> SET @poly =
    -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
mysql> SELECT AsText(InteriorRingN(GeomFromText(@poly),1));
+----------------------------------------------+
| AsText(InteriorRingN(GeomFromText(@poly),1)) |
+----------------------------------------------+
| LINESTRING(1 1,1 2,2 2,2 1,1 1)              |
+----------------------------------------------+

NumInteriorRings(poly)

杩斿洖Polygon鍊�poly鐨勫唴鐜�鐨勬暟鐩�銆�

mysql> SET @poly =
    -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
mysql> SELECT NumInteriorRings(GeomFromText(@poly));
+---------------------------------------+
| NumInteriorRings(GeomFromText(@poly)) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+

Area(mpoly)

浠ュ弻绮惧害鏁板�煎舰寮忚繑鍥�MultiPolygon鍊�mpoly鐨勯潰绉�锛屾牴鎹�鍦ㄥ叾绌洪棿鍙傝�冪郴涓�鐨勬祴閲忕粨鏋溿��

mysql> SET @mpoly =
    -> 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';
mysql> SELECT Area(GeomFromText(@mpoly));
+----------------------------+
| Area(GeomFromText(@mpoly)) |
+----------------------------+
|                          8 |
+----------------------------+

Centroid(mpoly)

浠�Point褰㈠紡杩斿洖鐢ㄤ簬MultiPolygon鍊�mpoly鐨勬暟瀛﹁川蹇冦�備笉淇濊瘉缁撴灉浣嶄簬MultiPolygon涓娿��

PointOnSurface(mpoly)

杩斿洖Point鍊硷紝淇濊瘉璇ュ�间綅浜�MultiPolygon鍊�mpoly涓娿��

GeometryN(gc,n)

杩斿洖GeometryCollection鍊�gc涓�绗�n涓�鍑犱綍瀵硅薄銆傚嚑浣曞�硅薄鐨勭紪鍙蜂��1寮�濮嬨��

mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
mysql> SELECT AsText(GeometryN(GeomFromText(@gc),1));
+----------------------------------------+
| AsText(GeometryN(GeomFromText(@gc),1)) |
+----------------------------------------+
| POINT(1 1)                             |
+----------------------------------------+

NumGeometries(gc)

杩斿洖GeometryCollection鍊�gc涓�鍑犱綍瀵硅薄鐨勬暟鐩�銆�

mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
mysql> SELECT NumGeometries(GeomFromText(@gc));
+----------------------------------+
| NumGeometries(GeomFromText(@gc)) |
+----------------------------------+
|                                2 |
+----------------------------------+

Envelope(g)

StartPoint(ls)

EndPoint(ls)

PointN(ls,n)

ExteriorRing(poly)

InteriorRingN(poly,n)

GeometryN(gc,n)

Buffer(g,d)

杩斿洖鍑犱綍瀵硅薄锛岃�ュ�硅薄浠ｈ〃鎵�鏈夎窛鍑犱綍鍊�g鐨勮窛绂诲皬浜庢垨绛変簬d鐨勬墍鏈夌偣銆�

ConvexHull(g)

杩斿洖鍑犱綍瀵硅薄锛岃�ュ�硅薄浠ｈ〃鍑犱綍鍊�g鐨勫嚫鍖呫��

Difference(g1,g2)

杩斿洖鍑犱綍瀵硅薄锛岃�ュ�硅薄琛ㄧず浜嗗嚑浣曞��g1涓�g2鐨勭偣闆嗗悎宸�寮傘��

Intersection(g1,g2)

杩斿洖鍑犱綍瀵硅薄锛岃�ュ�硅薄琛ㄧず浜嗗嚑浣曞��g1涓�g2鐨勭偣闆嗗悎浜ら泦銆�

SymDifference(g1,g2)

杩斿洖鍑犱綍瀵硅薄锛岃�ュ�硅薄琛ㄧず浜嗗嚑浣曞��g1涓�g2鐨勭偣闆嗗悎瀵圭О宸�銆�

Union(g1,g2)

杩斿洖鍑犱綍瀵硅薄锛岃�ュ�硅薄琛ㄧず浜嗗嚑浣曞��g1涓�g2鐨勭偣闆嗗悎鑱斿悎銆�