Chapter聽19.聽Spatial Extensions in MySQL - MySQL 5.1参考手册中文版
鐩綍
MySQL鏀寔绌洪棿鎵╁睍锛屽厑璁哥敓鎴愩佷繚瀛樺拰鍒嗘瀽鍦扮悊鐗瑰緛銆傝繖浜涚壒寰佸彲鐢ㄤ簬MyISAM銆InnoDB銆NDB銆BDB鍜ARCHIVE琛紙浣嗘槸锛ARCHIVE寮曟搸涓嶆敮鎸绱㈠紩鍔熻兘锛屽洜姝わ紝涓嶈兘涓ARCHIVE鍒椾腑鐨勭┖闂村垪缂栧埗绱㈠紩锛夈
鏈珷浠嬬粛浜嗕笅杩拌棰橈細
路 OpenGIS鍑犱綍妯″瀷涓繖浜涚┖闂存墿灞曠殑鍩虹銆
路 鐢ㄤ簬琛ㄧず绌洪棿鏁版嵁鐨勬暟鎹牸寮忋
路 濡備綍鍦MySQL涓娇鐢ㄧ┖闂存暟鎹
路 浣跨敤鍏充簬绌洪棿鏁版嵁鐨勭储寮曞姛鑳姐
路 MySQL涓OpenGIS瑙勮寖鐨勫樊寮傘
濡傛灉鍦MySQL绌洪棿鎵╁睍鐨勪娇鐢ㄦ柟闈㈡湁浠讳綍闂锛屽彲鍦ㄦ垜浠綉绔欑殑GIS璁哄潧涓璁恒
閬典粠OpenGIS鑱旂洘锛OGC锛夌殑瑙勮寖锛MySQL瀹炴柦浜嗙┖闂存墿灞曘OGC鏄竴涓敱250澶氬鍏徃銆佷唬鐞嗘満鏋勫拰澶у鍙備笌鐨勫浗闄呰仈鐩燂紝璐熻矗寮鍙戝叕寮鐨勬蹇佃В鍐虫柟妗堬紝杩欑被瑙ｅ喅鏂规瀵规墍鏈夌敤鏉ョ鐞嗙┖闂存暟鎹殑搴旂敤閮芥槸鏈夌敤鐨勩OGC鐨勭綉绔欐槸http://www.opengis.org/銆
1997骞达紝OpenGIS鑱旂洘锛OGC锛夊彂甯冧簡閽堝SQL鐨OpenGIS庐绠鍗曠壒寰佽鑼锛屽湪璇ユ枃妗ｄ腑锛屾彁鍑轰簡鎵╁睍SQL RDBMS浠ユ敮鎸佺┖闂存暟鎹殑涓浜涙蹇垫ф柟娉曘傝瑙勮寖鍙粠OpenGIS缃戠珯涓婅幏寰http://www.opengis.org/docs/99-049.pdf銆傚叾涓寘鍚笌鏈珷鏈夊叧鐨勯澶栦俊鎭
MySQL瀹炴柦浜OGC寤鸿鐨鍏锋湁Geometry绫诲瀷鐨SQL鐜鐨勪竴涓瓙闆嗐傝鏈鎸囩殑鏄敤涓缁勯泦鍚堢被鍨嬫墿灞曠殑鐜銆傚叿鏈夊嚑浣曞肩殑SQL鍒楁槸浣滀负鎷ユ湁闆嗗悎绫诲瀷鐨勫垪瀹炴柦鐨勩傝瑙勮寖鎻忚堪浜SQL鍑犱綍绫诲瀷闆嗗悎锛屼互鍙婁綔鐢ㄥ湪杩欎簺绫诲瀷涓婄敤浜庡垱寤哄拰鍒嗘瀽鍑犱綍鍊肩殑鍑芥暟銆
鍦扮悊鐗瑰緛鎸囩殑鏄笘鐣屼笂鍏锋湁鍦扮悊浣嶇疆鐨勪换浣曚簨鐗┿傚畠鍙互鏄細
路 瀹炰綋锛屽灞便佹睜婧忋佸煄甯傘
路 绌洪棿锛屽閭斂鍖哄煙銆佺儹甯︺
路 鍙畾涔夌殑浣嶇疆锛屽涓ゆ潯琛楅亾鐩镐氦鐨勫崄瀛楄矾鍙ｃ
鏈変簺鏂囦欢閲囩敤鏈鍦扮悊绌洪棿鐗瑰緛鏉ユ寚浠ｅ湴鐞嗙壒寰併
鍑犱綍鏄彟涓涓〃绀哄湴鐞嗙壒寰佺殑鏈銆傛渶鍒濓紝鍗曡瘝鍑犱綍琛ㄧず鐨勬槸瀵瑰ぇ鍦扮殑娴嬮噺銆傛潵鑷埗鍥惧鐨勫彟涓涓惈涔夋寚鐨勬槸鍒跺浘浜哄憳鐢ㄤ簬缁樺埗涓栫晫鍦板浘鐨勫湴鐞嗙壒寰併
鏈珷灏嗘墍鏈夎繖浜涙湳璇綋浣滃悓涔夎瘝瀵瑰緟锛鍦扮悊鐗瑰緛銆鍦扮悊绌洪棿鐗瑰緛銆鎴栧嚑浣銆傛渶甯镐娇鐢ㄧ殑鏈鏄鍑犱綍銆
鎴戜滑灏鍑犱綍瀹氫箟涓轰笘鐣屼笂鍏锋湁鍦扮悊浣嶇疆鐨勭偣鎴栫偣鐨勯泦鍚堛
OGC鍏锋湁鍑犱綍绫诲瀷鐨SQL鐜寤鸿鐨勫嚑浣曠被鍨嬮泦鍚堬紝鍩轰簬OpenGIS鍑犱綍妯″瀷銆傚湪鏈ā鍨嬩腑锛屾瘡涓嚑浣曞璞″潎鍏锋湁涓嬭堪涓鑸睘鎬э細
路 涓庣┖闂村弬鑰冪郴缁熺浉鍏筹紝鍏朵腑浠嬬粛浜嗗畾涔夊璞＄殑鍧愭爣绌洪棿銆
路 灞炰簬鏌愮鍑犱綍绫汇
鍑犱綍绫诲畾涔変簡涓嬭堪灞傛锛
路 Geometry锛堥潪瀹炰緥鍖栵級
o Point锛堝彲瀹炰緥鍖栫殑锛
o Curve锛堥潪瀹炰緥鍖栵級
搂 LineString锛堝彲瀹炰緥鍖栫殑锛
路 Line
路 LinearRing
o Surface锛堥潪瀹炰緥鍖栵級
搂 Polygon锛堝彲瀹炰緥鍖栫殑锛
o GeometryCollection锛堝彲瀹炰緥鍖栫殑锛
搂 MultiPoint锛堝彲瀹炰緥鍖栫殑锛
搂 MultiCurve锛堥潪瀹炰緥鍖栵級
路 MultiLineString锛堝彲瀹炰緥鍖栫殑锛
搂 MultiSurface锛堥潪瀹炰緥鍖栵級
路 MultiPolygon锛堝彲瀹炰緥鍖栫殑锛
涓嶈兘鍦ㄩ潪瀹炰緥鍖栫被涓垱寤哄璞°傝兘澶熷湪鍙疄渚嬪寲绫讳腑鍒涘缓瀵硅薄銆傛墍鏈夌被鍧囨湁灞炴э紝鍙疄渚嬪寲绫昏繕鍙互鍖呭惈澹版槑锛堝畾涔夋湁鏁堢被瀹炰緥鐨勮鍒欙級銆
Geometry鏄竴绉嶅熀鏈被銆瀹冩槸涓绉嶆娊璞＄被銆Geometry鐨勫彲瀹炰緥鍖栧瓙绫婚檺鍒朵负鍙湪2缁村潗鏍囩┖闂翠腑瀛樺湪鐨0銆1銆2缁村嚑浣曞璞°傛墍鏈夌殑鍙疄渚嬪寲鍑犱綍绫绘槸杩欐牱瀹氫箟鐨勶紝浠庤屼娇寰楀嚑浣曠被鐨勫疄渚嬩粠鎷撴墤鎰忎箟涓婅鏄棴鍚堢殑锛堜篃灏辨槸璇达紝鎵鏈夊畾涔夌殑鍑犱綍绫诲寘鍚叾杈圭晫锛夈
鍩烘湰Geometry绫诲叿鏈夊叧浜Point銆Curve銆Surface鍜GeometryCollection鐨勫瓙绫伙細
路 Point琛ㄧず0缁村璞°
路 Curve琛ㄧず1缁村璞锛屽叿鏈夊瓙绫LineString锛屼互鍙婃绾у瓙绫Line鍜LinearRing銆
路 Surface鏄负2缁村璞¤璁＄殑锛屽叿鏈夊瓙绫Polygon銆
路 GeometryCollection鍏锋湁鐗规畩鐨0缁淬1缁村拰2缁寸被闆嗗悎锛屽悕涓MultiPoint銆MultiLineString鍜MultiPolygon锛屽垎鍒敤浜庝负瀵瑰簲鐨Points銆LineStrings鍜Polygons闆嗗悎杩涜鍑犱綍寤烘ā銆MultiCurve鍜MultiSurface鏄綔涓烘娊璞¤秴绫诲紩鍏ョ殑锛屽畠浠綊绾充簡鐢ㄤ簬澶勭悊Curves鍜Surfaces鐨勯泦鍚堟帴鍙ｃ
Geometry銆Curve銆Surface銆MultiCurve鍜MultiSurface瀹氫箟涓洪潪瀹炰緥鍖栫被銆傚畠浠负鍏跺瓙绫诲畾涔変簡鍏敤鏂规硶闆嗗悎锛岃屼笖鏄负鎵╁睍鑰屽寘鍚湪鍐呯殑銆
Point銆LineString銆Polygon銆GeometryCollection銆MultiPoint銆MultiLineString鍜MultiPolygon瀹氫箟涓哄彲瀹炰緥鍖栫被銆
Geometry鏄眰娆＄粨鏋勭殑鏍圭被銆傚畠鏄竴绉嶉潪瀹炰緥鍖栫被锛屼絾鍏锋湁寰堝灞炴э紝杩欎簺灞炴у鐢变换浣Geometry瀛愮被鍒涘缓鐨勬墍鏈夊嚑浣曞兼潵璇存槸鍏卞悓鐨勩備笅闈粙缁嶄簡杩欎簺灞炴э紙灏ゅ叾鏄叿鏈夎嚜宸辩壒娈婂睘鎬х殑瀛愮被锛夈
Geometry灞炴
Geometry鍊煎叿鏈変笅杩板睘鎬э細
路 鍏type锛堢被鍨嬶級銆傛瘡涓geometry灞炰簬灞傛缁撴瀯涓彲瀹炰緥鍖栫被涔嬩竴銆
路 鍏SRID锛屾垨绌洪棿鍙傝ID銆傝鍊肩‘瀹氫簡鐢ㄤ簬鎻忚堪瀹氫箟鍑犱綍瀵硅薄鐨勫潗鏍囩┖闂寸殑绌洪棿鍧愭爣绯荤粺銆
鍦MySQL涓紝SRID鍊间粎鏄笌geometry鍊肩浉鍏崇殑鏁存暟鍊笺傛墍鏈夎绠楀潎鏄湪娆у嚑閲屽緱鍑犱綍绯伙紙骞抽潰锛変腑杩涜鐨勩
路 瀹冨湪鍏剁┖闂村潗鏍囩郴缁熶腑鐨coordinates锛堝潗鏍囷級锛岃〃绀轰负鍙岀簿搴︽暟鍊硷紙8瀛楄妭锛夈傛墍鏈夌殑闈炵┖鍑犱綍瀵硅薄鑷冲皯鍖呭惈涓瀵瑰潗鏍囷紙X銆Y锛夈傜┖鍑犱綍瀵硅薄涓嶅惈鍧愭爣銆
鍧愭爣涓SRID鐩稿叧銆備緥濡傦紝鍦ㄤ笉鍚岀殑鍧愭爣绯诲唴锛屼袱涓璞′箣闂寸殑璺濈浼氭湁鎵涓嶅悓锛屽嵆浣胯繖涓や釜瀵硅薄鍏锋湁鐩稿悓鐨勫潗鏍囦篃鍚屾牱銆傝繖鏄洜涓猴紝骞抽潰鍧愭爣绯讳腑鐨勮窛绂诲拰鍦板績鍧愭爣绯伙紙鍦扮悆琛ㄩ潰涓婄殑鍧愭爣锛変腑鐨勮窛绂绘槸涓嶅悓鐨勪簨椤广
路 鍏interior锛堝唴閮級銆boundary锛堣竟鐣岋級鍜exterior锛堝閮級銆
姣忎釜鍑犱綍瀵硅薄鍧囧崰鎹┖闂翠腑鐨勬煇涓浣嶇疆銆傚嚑浣曞璞＄殑exterior锛堝閮級鎸囩殑鏄湭琚瀵硅薄鍗犳嵁鐨勬墍鏈夌┖闂淬傚叾interior锛堝唴閮級鎸囩殑鏄璇ュ璞″崰鎹殑绌洪棿銆傚叾boundary锛堣竟鐣岋級鎸囩殑鏄嚑浣曞璞″唴閮ㄥ拰澶栭儴涔嬮棿鐨勭晫闈€
路 鍏MBR锛堟渶灏忚竟鐣岀煩褰級鎴栧寘缁滈潰銆傝繖鏄竴绉嶈竟鐣屽嚑浣曞硷紝鐢辨渶灏忓拰鏈澶у潗鏍囷紙X,Y锛夋瀯鎴愩
路 ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
路 鏃犺鍊兼槸绠鍗曠殑鎴闈炵畝鍗曠殑銆傜被鍨嬶紙LineString銆MultiPoint銆MultiLineString锛夌殑鍑犱綍鍊兼垨鏄畝鍗曠殑锛屾垨鏄潪绠鍗曠殑銆傛瘡涓被鍨嬪喅瀹氫簡鍏惰嚜宸辩殑绠鍗曟垨闈炵畝鍗曞０鏄庛
路 鏃犺鍊兼槸灏侀棴鐨鎴闈炲皝闂殑銆傜被鍨嬶紙LineString銆MultiPoint锛夌殑鍑犱綍鍊兼垨鏄皝闂殑锛屾垨鏄潪灏侀棴鐨勩傛瘡涓被鍨嬪喅瀹氫簡鍏惰嚜宸辩殑灏侀棴鎴栭潪灏侀棴澹版槑銆
路 鏃犺鍊兼槸绌虹殑鎴闈炵┖鐨銆傚鏋滄病鏈変换浣曠偣锛屽嚑浣曞璞℃槸绌虹殑銆傜┖鍑犱綍瀵硅薄鐨勫唴閮ㄣ佸閮ㄥ拰杈圭晫鏈畾涔夛紙涔熷氨鏄锛屽畠浠敱Null鍊艰〃绀猴級銆傜┖鐨勫嚑浣曞璞″畾涔変负鎬绘槸绠鍗曠殑锛岄潰绉负0銆
路 鍏dimension锛堢淮鏁帮級銆傚嚑浣曞璞＄殑缁存暟涓鈭1銆0銆1鎴2锛
o 鈭1鐢ㄤ簬绌哄嚑浣曞璞°
o 0鐢ㄤ簬鏃犻暱搴︺佹棤闈㈢Н鐨勫嚑浣曞璞°
o 1鐢ㄤ簬鍏锋湁闈0闀垮害鍜0闈㈢Н鐨勫嚑浣曞璞°
o 2鐢ㄤ簬鍏锋湁闈0闈㈢Н鐨勫嚑浣曞璞°
Point瀵硅薄鐨勭淮鏁颁负0銆LineString瀵硅薄鐨勭淮鏁颁负1銆Polygon瀵硅薄鐨勭淮鏁颁负2銆MultiPoint銆MultiLineString鍜MultiPolygon瀵硅薄鐨勭淮鏁颁笌鏋勬垚瀹冧滑鐨勫厓绱犵殑缁存暟鐩稿悓銆
Point锛堢偣锛夋寚鐨勬槸浠ｈ〃鍧愭爣绌洪棿涓崟涓綅缃殑鍑犱綍绫汇
Point绀轰緥
路 鎯冲儚涓寮犲叿鏈変紬澶氬煄甯傜殑澶т笘鐣屽湴鍥俱傛瘡涓Point瀵硅薄鍙唬琛1涓煄甯傘
路 鍦ㄥ煄甯傚湴鍥句笂锛Point瀵硅薄鍙唬琛1涓叕鍏辨苯杞︾珯銆
Point灞炴
路 X-鍧愭爣鍊笺
路 Y-鍧愭爣鍊笺
路 Point瀹氫箟涓0缁村嚑浣曞璞°
路 Point鐨勮竟鐣屼负绌洪泦鍚堛
Curve锛堟洸绾匡級鏄竴绉1缁鍑犱綍瀵硅薄锛岄氬父鐢变竴绯诲垪鐐硅〃绀恒Curve鐨勭壒娈婂瓙绫诲畾涔変簡鐐逛箣闂寸殑鍐呮彃绫诲瀷銆Curve鏄竴绉嶉潪瀹炰緥鍖栫被銆
Curve灞炴
路 Curve鍏锋湁鍏剁偣鐨勫潗鏍囥
路 Curve瀹氫箟涓1缁村嚑浣曞璞°
路 濡傛灉鏈氳繃鐩稿悓鐨勭偣涓ゆ锛Curve灏辨槸绠鍗曠殑銆
路 濡傛灉鍏惰捣鐐圭瓑浜庡叾缁堢偣锛Curve灏辨槸灏侀棴鐨勩
路 灏侀棴Curve鐨勮竟鐣屼负绌恒
路 闈炲皝闂Curve鐨勮竟鐣岀敱鍏朵袱涓鐐规瀯鎴愩
路 绠鍗曚笖灏侀棴鐨Curve鏄LinearRing銆
LineString鏄叿鏈夌偣涔嬮棿绾挎у唴鎻掔壒鎬х殑Curve銆
LineString绀轰緥
路 鍦ㄤ笘鐣屽湴鍥句笂锛LineString瀵硅薄鍙〃绀烘渤娴併
路 鍦ㄥ煄甯傚湴鍥句笂锛LineString瀵硅薄鍙〃绀鸿閬撱
LineString灞炴
路 LineString鍏锋湁绾挎鐨勫潗鏍囷紝鐢辨瘡涓繛缁殑鐐瑰锛堜袱鐐癸級瀹氫箟銆
路 濡傛灉浠呭寘鍚袱鐐癸紝LineString涓Line銆
路 濡傛灉瀹冩棦鏄畝鍗曠殑涔熸槸灏侀棴鐨勶紝LineString涓LinearRing銆
Surface鏄竴绉2缁村嚑浣曞璞銆傚畠鏄竴绉嶉潪瀹炰緥鍖栫被銆傚叾鍞竴鐨勫彲瀹炰緥鍖栧瓙绫绘槸Polygon.
Surface灞炴
路 Surface瀹氫箟涓2缁村嚑浣曞璞°
路 鍦OpenGIS瑙勮寖涓紝灏嗙畝鍗曠殑Surface瀹氫箟涓虹敱鍗曚竴鈥patch鈥濇瀯鎴愮殑鍑犱綍瀵硅薄锛屽畠涓庡崟涓閮ㄨ竟鐣屼互鍙0鎴栧涓唴閮ㄨ竟鐣屾湁鍏炽
路 绠鍗Surface鐨勮竟鐣屾槸涓缁勪笌鍏跺唴閮ㄥ拰澶栭儴杈圭晫瀵瑰簲鐨勫皝闂洸绾跨殑闆嗗悎銆
Polygon鏄唬琛ㄥ杈瑰嚑浣曞璞＄殑骞抽潰Surface銆傚畠鐢鍗曚釜澶栭儴杈圭晫浠ュ強0鎴栧涓唴閮ㄨ竟鐣屽畾涔夛紝鍏朵腑锛屾瘡涓唴閮ㄨ竟鐣屽畾涔変负Polygon涓殑1涓瓟銆
Polygon绀轰緥
路 鍦ㄥ湴鍖哄湴鍥句笂锛Polygon瀵硅薄鍙〃绀烘．鏋椼佸尯绛夈
Polygon澹版槑
路 Polygon鐨勮竟鐣鐢变竴缁勬瀯鎴愬叾澶栭儴杈圭晫鍜屾瘮鍐呴儴杈圭晫鐨LinearRing褰掑悜闆嗗悎鏋勬垚锛堝嵆锛岀畝鍗曚笖灏侀棴鐨LineString瀵硅薄锛夈
路 Polygon娌℃湁浜ゅ弶鐨勭幆銆Polygon杈圭晫涓殑鐜彲鑳戒細鍦Point澶勭浉浜わ紝浣嗕粎浠ュ垏绾挎柟寮忕浉浜ゃ
路 Polygon娌℃湁绾裤佸皷宄版垨绌垮瓟銆
路 Polygon鏈夌敱杩炴帴鐐归泦鍚堟瀯鎴愮殑鍐呴儴銆
路 Polygon鍙兘鍖呭惈瀛斻傚浜庡叿鏈夊瓟鐨Polygon锛屽叾澶栭儴涓嶈繛鎺ャ傛瘡涓瓟瀹氫箟浜嗚繛鎺ョ殑澶栭儴閮ㄤ欢銆
鍓嶈堪澹版槑浣垮緱Polygon鎴愪负绠鍗曠殑鍑犱綍瀵硅薄銆
MultiCurve灞炴
路 MultiCurve鏄1缁村嚑浣曞璞°
路 褰撲笖浠呭綋鍏舵墍鏈夊厓绱犲潎鏄畝鍗曠殑鏃讹紝MultiCurve鎵嶆槸绠鍗曠殑銆浠绘剰涓ゅ厓绱犵殑鍞竴浜ゅ弶浠呭嚭鐜板湪涓ゅ厓绱犺竟鐣岀殑鐐逛笂銆
路 MultiCurve杈圭晫鏄氳繃閲囩敤鈥滄ā2鑱斿悎瑙勫垯鈥濓紙涔熺О涓哄鍋惰鍒欙級鑾峰緱鐨勶細濡傛灉鏌愪竴鐐逛綅浜庡鏁扮紪鍙MultiCurve鍏冪礌鐨勮竟鐣屽唴锛屽畠灏嗕綅浜MultiCurve鐨勮竟鐣屽唴銆
路 濡傛灉鍏舵墍鏈夊厓绱犲潎鏄皝闂殑锛屽垯MultiCurve涓哄皝闂殑銆
路 灏侀棴MultiCurve鐨勮竟鐣屾讳负绌恒
19.2.11. 绫籑ultiLineString
MultiPolygon绀轰緥
路 鍦ㄥ湴鍖哄湴鍥句笂锛MultiPolygon鍙〃绀烘箹娉婄郴缁熴
MultiPolygon澹版槑
路 MultiPolygon娌℃湁鍐呴儴鐩镐氦鐨勭殑2涓Polygon鍏冪礌銆
路 MultiPolygon娌℃湁2涓氦鍙夌殑Polygon鍏冪礌锛堝墠杩板０鏄庝篃绂佹浜ゅ弶锛夛紝涔熸病鏈夊湪鏃犳暟鐐瑰鐩告帴瑙︾殑2涓Polugon鍏冪礌銆
路 MultiPolygon涓嶈兘鍚湁鏈夋柇寮鐨勭嚎銆佸皷宄版垨绌垮瓟銆MultiPolygon鏄竴绉嶆甯哥殑灏侀棴鐐归泦鍚堛
路 瀵逛簬鏈1涓互涓Polygon鍏冪礌鐨MultiPolygon锛鍏锋湁涓嶈繛鎺ョ殑鍐呴儴銆MultiPolygon鍐呴儴宸茶繛鎺ラ儴浠剁殑鏁扮洰绛変簬MultiPolygon涓Polygon鍊肩殑鏁扮洰銆
MultiPolygon灞炴
路 MultiPolygon鏄2缁村嚑浣曞璞°
路 MultiPolygon杈圭晫鏄笌鍏Polygon鍏冪礌鐨勮竟鐣屽搴旂殑灏侀棴鏇茬嚎闆嗗悎锛LineString鍊硷級銆
路 MultiPolygon杈圭晫涓殑姣忎釜Curve鍑嗙‘浣嶄簬1涓Polygon鍏冪礌鐨勮竟鐣屽唴銆
路 Polygon鍏冪礌杈圭晫涓殑姣忎釜Curve浣嶄簬MultiPolygon鐨勮竟鐣屼腑銆
19.3. 鏀寔鐨勭┖闂存暟鎹牸寮
鍑犱綍瀵硅薄WKT琛ㄧず鐨勭ず渚嬶細
路 Point锛
路 POINT(15 20)
娉ㄦ剰锛屾寚瀹氱偣鍧愭爣鏃朵笉浣跨敤鍒嗛殧鐢ㄩ楀彿銆
路 鍏锋湁4涓偣鐨LineString锛
路 LINESTRING(0 0, 10 10, 20 25, 50 60)
娉ㄦ剰锛岀偣鍧愭爣瀵归噰鐢ㄩ楀彿闅斿紑銆
路 鍏锋湁1涓閮ㄧ幆鍜1涓唴閮ㄧ幆鐨Polygon锛
路 POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
路 鍏锋湁涓変釜Point鍊肩殑MultiPoint锛
路 MULTIPOINT(0 0, 20 20, 60 60)
路 鍏锋湁2涓LineString鍊肩殑MultiLineString锛
路 MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
路 鍏锋湁2涓Polygon鍊肩殑MultiPolygon锛
路 MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
路 鐢2涓Point鍊煎拰1涓LineString鏋勬垚鐨GeometryCollection锛
路 GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
鍦ㄦ湰绔犲紑濮嬪缁欏嚭鐨OGC瑙勮寖鏂囨。涓紝鍙壘鍒扮浉搴旂殑Backus-Naur璇硶锛屽畠鎸囧畾浜嗙敤浜庣紪鍐WKT鍊肩殑姝ｅ紡鐢熶骇瑙勫垯銆
19.3.2. 钁楀悕鐨勪簩杩涘埗(WKB)鏍煎紡
WKB鐢ㄤ簬浠ヤ簩杩涘埗娴佺殑褰㈠紡浜ゆ崲鍑犱綍鏁版嵁锛屼簩杩涘埗娴佺敱鍚嚑浣WKB淇℃伅鐨BLOB鍊艰〃绀恒
WKB浣跨敤1瀛楄妭鏃犵鍙锋暣鏁般4瀛楄妭鏃犵鍙锋暣鏁般佷互鍙8瀛楄妭鍙岀簿搴︽暟锛IEEE 754鏍煎紡锛夈1瀛楄妭绛変簬8姣旂壒銆
渚嬪锛屼笌POINT(1 1)瀵瑰簲鐨WKB鍊肩敱涓嬭堪21瀛楄妭搴忓垪鏋勬垚锛堝湪姝わ紝姣忎釜瀛楄妭鐢2涓崄鍏繘鍒舵暟鍊艰〃绀猴級锛
0101000000000000000000F03F000000000000F03F
璇ュ簭鍒楀彲鍒嗕负涓嬭堪閮ㄥ垎锛
Byte order : 01
WKB type : 01000000
X : 000000000000F03F
Y : 000000000000F03F
琛ㄧず濡備笅锛
路 瀛楄妭椤哄簭锛Byte order锛夊彲浠ユ槸0鎴1锛屽垎鍒〃鏄little-endian鎴big-endian瀛樺偍銆little-endian鍜big-endian瀛楄妭椤哄簭涔熷垎鍒О涓虹綉缁滄暟鎹〃绀猴紙NDR锛夊拰澶栭儴鏁版嵁琛ㄧず锛XDR锛夈
路 鈥WKB type鈥濓紙WKB绫诲瀷锛夋槸鎸囨槑鍑犱綍绫诲瀷鐨勪唬鐮併傚彇鍊间粠1鍒7锛屽垎鍒〃绀Point銆LineString銆Polygon銆MultiPoint銆MultiLineString銆MultiPolygon銆佸拰GeometryCollection銆
路 Point鍊煎叿鏈X鍜Y鍧愭爣锛屾瘡涓煎潎鐢ㄥ弻绮惧害鍊艰〃绀恒
瀵逛簬鏇村鏉傚嚑浣曞肩殑WKB鍊硷紝瀹冧滑鏄敱鏇村鏉傜殑鏁版嵁缁撴瀯琛ㄧず鐨勶紝璇︽儏璇峰弬瑙OpenGIS瑙勮寖銆
19.4. 鍒涘缓鍏峰绌洪棿鍔熻兘鐨凪ySQL鏁版嵁搴
路 GEOMETRY
路 POINT
路 LINESTRING
路 POLYGON
GEOMETRY鑳藉淇濆瓨浠讳綍绫诲瀷鐨勫嚑浣曞笺傚叾浠栫殑鍗曞肩被鍨POINT銆LINESTRING浠ュ強POLYGON鍙兘淇濆瓨鐗瑰畾鍑犱綍绫诲瀷鐨鍊笺
鍏朵粬鏁版嵁绫诲瀷鑳戒繚瀛樺涓硷細
路 MULTIPOINT
路 MULTILINESTRING
路 MULTIPOLYGON
路 GEOMETRYCOLLECTION
GEOMETRYCOLLECTION鑳戒繚瀛樹换鎰忕被鍨嬬殑瀵硅薄闆嗗悎銆傚浜庡叾浠栭泦鍚堢被鍨嬶紝MULTIPOINT銆MULTILINESTRING銆MULTIPOLYGON鍜GEOMETRYCOLLECTION锛屼粎闄愪簬鍏锋湁鐗瑰畾鍑犱綍绫诲瀷鐨勯泦鍚堟垚鍛樸
19.4.2. 鍒涘缓绌洪棿鍊
MySQL鎻愪緵浜嗕紬澶氫互WKT琛ㄨ揪寮忋佷互鍙婂彲閫夌殑绌洪棿鍙傝冪郴ID锛SRID锛変负杈撳叆鍙傛暟鐨勫嚱鏁般傚畠浠皢杩斿洖瀵瑰簲鐨勫嚑浣曞璞°
GeomFromText()鎺ュ彈浠讳綍鍑犱綍绫诲瀷鐨WKT浣滀负鍏剁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鍊笺
浣跨敤鍏WKT琛ㄧず鍜SRID鏋勯POINT鍊笺
PolyFromText(wkt[,srid]) , PolygonFromText(wkt[,srid])
浣跨敤鍏WKT琛ㄧず鍜SRID鏋勯POLYGON鍊笺
OpenGIS瑙勮寖杩樹粙缁嶄簡鐢ㄤ簬鏋勯Polygon鎴MultiPolygon鍊肩殑鍙夊嚱鏁帮紝杩欎簺鍊煎熀浜庣幆鍜屽皝闂LineString鍊奸泦鍚堢殑WKT琛ㄨ揪寮忋傝繖浜涘煎彲浠ョ浉浜ゃMySQL鏈疄鏂戒笅杩板嚱鏁帮細
MySQL鎻愪緵浜嗕紬澶氬嚱鏁帮紝瀹冧滑灏嗗寘鍚WKT琛ㄨ揪寮忋佹垨鍙夌殑绌洪棿鍙傝冪郴缁ID锛SRID锛夌殑BLOB浣滀负杈撳叆鍙傛暟銆傚畠浠繑鍥炲搴旂殑鍑犱綍瀵硅薄銆
GeomFromWKB()鎺ュ彈浠讳綍鍑犱綍绫诲瀷鐨WKB浣滀负鍏剁1涓弬閲忋傚湪瀹炴柦鏂规涓篃鎻愪緵浜嗕笌绫诲瀷鐩稿叧鐨勬瀯閫犲嚱鏁帮紝鐢ㄤ簬鏋勯犳瘡涓绉嶅嚑浣曠被鍨嬬殑鍑犱綍鍊笺-
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鍊笺
浣跨敤鍏WKB琛ㄧず鍜SRID鏋勯POINT鍊笺
PolyFromWKB(wkb[,srid]) , PolygonFromWKB(wkb[,srid])
浣跨敤鍏WKB琛ㄧず鍜SRID鏋勯POLYGON鍊笺
OpenGIS瑙勮寖杩樹粙缁嶄簡鐢ㄤ簬鏋勯Polygon鎴MultiPolygon鍊肩殑鍙夊嚱鏁帮紝杩欎簺鍊煎熀浜庣幆鍜屽皝闂LineString鍊奸泦鍚堢殑WKB琛ㄨ揪寮忋傝繖浜涘煎彲浠ョ浉浜ゃMySQL鏈疄鏂戒笅杩板嚱鏁帮細
娉ㄩ噴锛MySQL鏈疄鏂芥湰鑺傛墍鍒楃殑鍑芥暟銆
MySQL涓哄垱寤哄嚑浣WKB琛ㄨ揪寮忔彁渚涗簡鏈夌敤鐨勫嚱鏁伴泦鍚堛傛湰鑺備粙缁嶇殑鍑芥暟鏄OpenGIS瑙勮寖鐨MySQL鎵╁睍銆傝繖浜涘嚱鏁扮殑缁撴灉鏄寘鍚嚑浣曞硷紙鏃SRID锛夌殑BLOB鍊笺傝繖浜涘嚱鏁扮殑缁撴灉鍙GeomFromWKB()鍑芥暟绯诲垪涓换鎰忓嚱鏁扮殑绗1涓弬閲忓彇浠ｃ-
鏋勯WKB GeometryCollection銆傚鏋滀换浣曞弬閲忎笉鏄瀯閫犺壇濂界殑鍑犱綍瀵硅薄WKB琛ㄨ揪寮忥紝杩斿洖鍊间负NULL銆
浠庡涓WKB Point鍙傞噺鏋勯WKB LineString鍊銆傚鏋滀换浣曞弬閲忎笉鏄WKB Point锛岃繑鍥炲间负NULL銆傚鏋Point鍙傞噺鐨勬暟鐩皬浜2锛岃繑鍥炲间负NULL銆
浣跨敤WKB LineString鍙傞噺鏋勯WKB MultiLineString鍊銆傚鏋滀换浣曞弬閲忎笉鏄WKB LineString锛岃繑鍥炲间负NULL銆
浣跨敤WKB Point鍙傞噺鏋勯WKB MultiPoint鍊笺傚鏋滀换浣曞弬閲忎笉鏄WKB Point锛岃繑鍥炲间负NULL銆
浠庝竴缁WKB Polygon鍙傞噺鏋勯WKB MultiPolygon鍊笺傚鏋滀换浣曞弬閲忎笉鏄WKB Polygon锛岃繑鍥炲间负NULL銆
浣跨敤鍏跺潗鏍囨瀯閫WKB Point銆
浠庡涓WKB LineString鍙傞噺鏋勯WKB Polygon鍊笺傚鏋滀换浣曞弬閲忔湭琛ㄧず涓LinearRing鐨WKB褰㈠紡锛堝嵆锛岄潪灏侀棴鍜岀畝鍗LineString锛锛岃繑鍥炲间负NULL銆
路 浣跨敤CREATE TABLE璇彞鍒涘缓鍏锋湁绌洪棿鍒楃殑琛細
路 mysql> CREATE TABLE geom (g GEOMETRY);
路 Query OK, 0 rows affected (0.02 sec)
路 浣跨敤ALTER TABLE璇彞鍦ㄥ凡鏈夎〃涓鍔犵┖闂村垪锛屾垨灏嗙┖闂村垪浠庡凡鏈夎〃涓垹闄わ細
路 mysql> ALTER TABLE geom ADD pt POINT;
路 Query OK, 0 rows affected (0.00 sec)
路 Records: 0 Duplicates: 0 Warnings: 0
路 mysql> ALTER TABLE geom DROP pt;
路 Query OK, 0 rows affected (0.00 sec)
路 Records: 0 Duplicates: 0 Warnings: 0
19.4.4. 濉厖绌洪棿鍒
鍒涘缓浜嗙┖闂村垪鍚庯紝鍙敤绌洪棿鏁版嵁濉厖瀹冧滑銆
鍊煎簲浠ュ唴閮ㄥ嚑浣曟牸寮忎繚瀛橈紝浣嗕綘涔熻兘灏嗗叾浠WKT鎴WKB鏍煎紡杞崲涓哄唴閮ㄥ嚑浣曟牸寮忋傚湪涓嬮潰鐨勭ず渚嬩腑锛屼粙缁嶄簡閫氳繃灏WKT鍊艰浆鎹负鍐呴儴鍑犱綍鏍煎紡浠ヤ究灏嗗嚑浣曞兼彃鍏ヨ〃涓殑鏂规硶銆
浣犲彲浠ュ湪INSERT璇彞涓墽琛岀洿鎺ヨ浆鎹㈡搷浣滐細
INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (GeomFromText(@g));
涔熷彲浠ュ湪INSERT涔嬪墠鎵ц杞崲鎿嶄綔锛
SET @g = GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);
鍦ㄤ笅闈㈢殑绀轰緥涓紝灏嗗涓鏉傜殑鍑犱綍鍊兼彃鍏ュ埌浜嗚〃涓細
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (GeomFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (GeomFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (GeomFromText(@g));
鍦ㄥ墠闈㈢殑鎵鏈夌ず渚嬩腑锛屽潎閲囩敤浜GeomFromText()鏉ュ垱寤哄嚑浣曞笺備綘涔熷彲浠ヤ娇鐢ㄤ笌绫诲瀷鐩稿叧鐨勫嚱鏁帮細
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (PointFromText(@g));
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (LineStringFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (PolygonFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (GeomCollFromText(@g));
娉ㄦ剰锛屽鏋滃鎴风搴旂敤绋嬪簭鎵撶畻浣跨敤鍑犱綍鍊肩殑WKB琛ㄧず锛屽畠闇瑕佸湪闃熷垪涓皢姝ｇ‘鏋勯犵殑WKB鍙戦佽嚦鏈嶅姟鍣ㄣ備絾鏄紝瀛樺湪鏁扮婊¤冻璇ヨ姹傜殑鏂规硶銆備緥濡傦細
路 鐢ㄥ崄鍏繘鍒舵枃瀛楄娉曟彃鍏POINT(1 1)鍊硷細
路 mysql> INSERT INTO geom VALUES
路 -> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));
路 ODBC搴旂敤绋嬪簭鑳藉鍙戦WKB琛ㄨ揪寮忥紝骞朵娇鐢ㄥ叿鏈BLOB绫诲瀷鐨勫弬閲忓皢鍏剁粦瀹氬埌鍗犱綅绗︼細
路 INSERT INTO geom VALUES (GeomFromWKB(?))
鍏朵粬缂栫▼鎺ュ彛鎴栬涔熸敮鎸佺被浼肩殑鍗犱綅绗︽満鍒躲
鍦C绋嬪簭涓紝鍙互浣跨敤mysql_real_escape_string()杞箟浜岃繘鍒跺硷紝骞跺皢缁撴灉鍖呭惈鍦ㄥ皢鍙戦佽嚦鏈嶅姟鍣ㄧ殑鏌ヨ瀛楃涓层傝鍙傝25.2.3.52鑺傦紝鈥渕ysql_real_escape_string()鈥路 鐢ㄤ簬鍦ㄥ悇绉嶆牸寮忛棿杞崲鍑犱綍鍊肩殑鍑芥暟銆
路 鐢ㄤ簬璁块棶鍑犱綍鍊煎畾鎬ф垨瀹氶噺灞炴х殑鍑芥暟銆
路 鎻忚堪涓ょ鍑犱綍鍊间箣闂村叧绯荤殑鍑芥暟銆
路 浠庡凡鏈Geometry鍒涘缓鏂Geometry鐨勫嚱鏁
绌洪棿鍒嗘瀽鍑芥暟鍙敤浜庡緢澶氬満鍚堜笅锛屽锛
路 浠讳綍浜や簰寮SQL绋嬪簭锛屽mysql鎴MySQLCC銆
路 浠ヤ换浣曡瑷缂栧啓鐨勩佹敮鎸MySQL瀹㈡埛绔API鐨勫簲鐢ㄧ▼搴忋
19.5.1. Geometry鏍煎紡杞崲鍑芥暟
MySQL鏀寔涓嬭堪鐢ㄤ簬鍦ㄥ唴閮ㄦ牸寮忓拰WKT鎴WKB鏍煎紡闂磋浆鎹㈠嚑浣曞肩殑鍑芥暟锛
灏嗛噰鐢ㄥ唴閮ㄥ嚑浣曟牸寮忕殑鍊艰浆鎹负鍏WKB琛ㄧず锛屽苟杩斿洖浜岃繘鍒剁粨鏋溿
SELECT AsBinary(g) FROM geom;
灏嗛噰鐢ㄥ唴閮ㄥ嚑浣曟牸寮忕殑鍊艰浆鎹负鍏WKT琛ㄧず锛屽苟杩斿洖瀛楃涓茬粨鏋溿
mysql> SET @g = 'LineString(1 1,2 2,3 3)'; mysql> SELECT AsText(GeomFromText(@g)); +--------------------------+ | AsText(GeomFromText(@g)) | +--------------------------+ | LINESTRING(1 1,2 2,3 3) | +--------------------------+
灏嗗瓧绗︿覆鍊间粠鍏WKT琛ㄧず杞崲涓哄唴閮ㄥ嚑浣曟牸寮忥紝骞惰繑鍥炵粨鏋溿備篃鏀寔澶氱涓庣被鍨嬬浉鍏崇殑鍑芥暟锛屽PointFromText()鍜LineFromText()锛璇峰弬瑙19.4.2.1鑺傦紝鈥滀娇鐢╓KT鍑芥暟鍒涘缓Geometry锛堝嚑浣曪級鍊尖銆
灏嗕簩杩涘埗鍊间粠鍏WKB琛ㄧず杞崲涓哄唴閮ㄥ嚑浣曟牸寮忥紝骞惰繑鍥炵粨鏋溿備篃鏀寔澶氱涓庣被鍨嬬浉鍏崇殑鍑芥暟锛屽PointFromWKB()鍜LineFromWKB()锛岃鍙傝19.4.2.2鑺傦紝鈥滀娇鐢╓KB鍑芥暟鍒涘缓Geometry锛堝嚑浣曪級鍊尖銆
鏈妭鍒楀嚭鐨勫嚱鏁颁笉闄愬埗鍏跺弬閲忥紝鍙帴鍙椾换浣曠被鍨嬬殑鍑犱綍鍊笺
杩斿洖鍑犱綍鍊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 | +------------------------------------------------+-
杩斿洖鍑犱綍鍊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)) | +-------------------------------------------------------+ 浠ュ瓧绗︿覆褰㈠紡杩斿洖鍑犱綍绫诲瀷鐨勫悕绉帮紝鍑犱綍瀹炰緥g鏄嚑浣曠被鍨嬬殑鎴愬憳銆傝鍚嶇О涓庡彲瀹炰緥鍖栧嚑浣曞瓙绫讳箣涓瀵瑰簲銆
mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')); +------------------------------------------+ | GeometryType(GeomFromText('POINT(1 1)')) | +------------------------------------------+ | POINT | +------------------------------------------+杩斿洖鎸囨槑浜嗗嚑浣曞g鐨勭┖闂村弬鑰冪郴缁ID鐨勬暣鏁般
鍦MySQL涓紝SRID鍊间粎鏄笌鍑犱綍鍊肩浉鍏崇殑鏁存暟銆傛墍鏈夎绠楀潎鏄湪娆у嚑閲屽緱锛堝钩闈級鍑犱綍涓繘琛岀殑銆
mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +-----------------------------------------------+ | SRID(GeomFromText('LineString(1 1,2 2)',101)) | +-----------------------------------------------+ | 101 | +-----------------------------------------------+
OpenGIS瑙勮寖杩樺畾涔変簡涓嬭堪鍑芥暟锛MySQL鏈疄鏂借繖绫诲嚱鏁帮細
杩斿洖鍑犱綍鍊g鐨勭粍鍚堣竟鐣岀殑闂寘鐨勫嚑浣曞璞°
濡傛灉鍑犱綍鍊g涓虹┖鐨勫嚑浣曞璞★紝杩斿洖1锛屽鏋滈潪绌猴紝杩斿洖0锛屽鏋滃弬閲忎负NULL锛岃繑鍥-1銆傚鏋滃嚑浣曞璞℃槸绌虹殑锛屽畠琛ㄧず绌虹殑鐐归泦鍚堛
-
鐩墠璇ュ嚱鏁版槸鍗犱綅绗︼紝涓嶅簲浣跨敤瀹冦傚鏋滃疄鏂戒簡瀹冿紝鍏惰涓轰笌涓嬫鎵缁欏嚭鐨勬弿杩扮被浼笺
濡傛灉鍑犱綍鍊糶娌℃湁寮傚父鐨勫嚑浣曠偣锛堝鑷浉浜ゆ垨鑷浉鍒囷級锛岃繑鍥1銆傚鏋滃弬閲忎笉鏄畝鍗曞弬閲忥紝IsSimple()杩斿洖0锛濡傛灉鍙傞噺鏄疦ULL锛岃繑鍥-1銆
瀵逛簬鏈珷鍓嶉潰浠嬬粛鐨勬瘡涓彲瀹炰緥鍖栧嚑浣曠被锛屽潎鍖呭惈鐗瑰畾鐨勬潯浠讹紝杩欑被鏉′欢浼氫娇绫诲疄渚嬭鍒嗙被涓洪潪绠鍗曠殑銆
Point鐢X鍜Y鍧愭爣鏋勬垚锛屽彲浣跨敤涓嬭堪鍑芥暟鑾峰緱瀹冧滑锛
浠ュ弻绮惧害鏁板艰繑鍥炵偣p鐨X鍧愭爣鍊笺
mysql> SELECT X(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | X(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 56.7 | +--------------------------------------+浠ュ弻绮惧害鏁板艰繑鍥炵偣p鐨Y鍧愭爣鍊笺
mysql> SELECT Y(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | Y(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 53.34 | +--------------------------------------+
LineString鐢Point鍊肩粍鎴愩備綘鍙互鎻愬彇LineString鐨勭壒瀹氱偣锛岃鏁板畠鎵鍖呭惈鐨勭偣鏁帮紝鎴栬幏鍙栧叾闀垮害銆
杩斿洖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) | +-------------------------------------+
浠ュ弻绮惧害鏁板艰繑鍥LineString鍊1s鍦ㄧ浉鍏崇殑绌洪棿鍙傝冪郴涓殑闀垮害銆
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT GLength(GeomFromText(@ls)); +----------------------------+ | GLength(GeomFromText(@ls)) | +----------------------------+ | 2.8284271247462 | +----------------------------+
杩斿洖LineString鍊1s涓殑鐐规暟銆
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT NumPoints(GeomFromText(@ls)); +------------------------------+ | NumPoints(GeomFromText(@ls)) | +------------------------------+ | 3 | +------------------------------+
杩斿洖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) | +-------------------------------------+
杩斿洖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) | +---------------------------------------+
OpenGIS瑙勮寖杩樺畾涔変簡涓嬭堪鍑芥暟锛MySQL灏氭湭瀹炴柦杩欎簺鍑芥暟锛
浠ュ弻绮惧害鏁板煎舰寮忚繑鍥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 | +-----------------------------+
濡傛灉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 | +------------------------------+
浠ュ弻绮惧害鏁板煎舰寮忚繑鍥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 | +---------------------------+
浠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) | +-------------------------------------------+浠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) | +----------------------------------------------+杩斿洖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 | +---------------------------------------+
浠ュ弻绮惧害鏁板煎舰寮忚繑鍥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 | +----------------------------+
OpenGIS瑙勮寖杩樺畾涔変簡涓嬭堪鍑芥暟锛MySQL鏈疄鏂借繖绫诲嚱鏁帮細
杩斿洖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) | +----------------------------------------+
杩斿洖GeometryCollection鍊gc涓嚑浣曞璞＄殑鏁扮洰銆
mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))'; mysql> SELECT NumGeometries(GeomFromText(@gc)); +----------------------------------+ | NumGeometries(GeomFromText(@gc)) | +----------------------------------+ | 2 | +----------------------------------+
鍦19.5.2鑺傦紝鈥淕eometry鍑芥暟鈥涓紝鎴戜滑璁ㄨ浜嗕竴浜涘彲浠庡凡鏈夊嚑浣曞璞℃瀯閫犳柊鍑犱綍瀵硅薄鐨勫嚱鏁帮細
Envelope(g)
StartPoint(ls)
EndPoint(ls)
PointN(ls,n)
ExteriorRing(poly)
InteriorRingN(poly,n)
GeometryN(gc,n)
OpenGIS寤鸿浜嗗緢澶氬彲鐢熸垚鍑犱綍瀵硅薄鐨勫叾浠栧嚱鏁般傚畠浠槸涓哄疄鏂界┖闂存搷浣滅鑰岃璁＄殑銆
鍦MySQL涓湭瀹炴柦杩欎簺鍑芥暟銆傚畠浠垨璁镐細鍦ㄦ湭鏉ョ殑鐗堟湰涓嚭鐜般
-
杩斿洖鍑犱綍瀵硅薄锛岃瀵硅薄浠ｈ〃鎵鏈夎窛鍑犱綍鍊g鐨勮窛绂诲皬浜庢垨绛変簬d鐨勬墍鏈夌偣銆
杩斿洖鍑犱綍瀵硅薄锛岃瀵硅薄浠ｈ〃鍑犱綍鍊g鐨勫嚫鍖呫
杩斿洖鍑犱綍瀵硅薄锛岃瀵硅薄琛ㄧず浜嗗嚑浣曞g1涓g2鐨勭偣闆嗗悎宸紓銆
杩斿洖鍑犱綍瀵硅薄锛岃瀵硅薄琛ㄧず浜嗗嚑浣曞g1涓g2鐨勭偣闆嗗悎浜ら泦銆
杩斿洖鍑犱綍瀵硅薄锛岃瀵硅薄琛ㄧず浜嗗嚑浣曞g1涓g2鐨勭偣闆嗗悎瀵圭О宸
杩斿洖鍑犱綍瀵硅薄锛岃瀵硅薄琛ㄧず浜嗗嚑浣曞g1涓g2鐨勭偣闆嗗悎鑱斿悎銆
MySQL鎻愪緵浜嗕竴浜涘彲娴嬭瘯涓や釜鍑犱綍瀵硅薄g1鍜g2鏈灏忚竟鐣岀煩褰箣闂村叧绯荤殑鍑芥暟銆傚畠浠寘鎷細
杩斿洖1鎴0浠ユ寚鏄g1鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚﹀寘鍚g2鐨勬渶灏忚竟鐣岀煩褰€
mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); mysql> SET @g2 = GeomFromText('Point(1 1)'); mysql> SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1); ----------------------+----------------------+ | MBRContains(@g1,@g2) | MBRContains(@g2,@g1) | +----------------------+----------------------+ | 1 | 0 | +----------------------+----------------------+杩斿洖1鎴0浠ユ寚鏄庝袱涓嚑浣曞彉閲g1鍜g2鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚︿笉鐩镐氦銆
杩斿洖1鎴0浠ユ寚鏄庝袱涓嚑浣曞彉閲g1鍜g2鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚︾浉鍚屻
杩斿洖1鎴0浠ユ寚鏄庝袱涓嚑浣曞彉閲g1鍜g2鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚︾浉浜ゃ
杩斿洖1鎴0浠ユ寚鏄庝袱涓嚑浣曞彉閲g1鍜g2鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚︿氦杩
杩斿洖1鎴0浠ユ寚鏄庝袱涓嚑浣曞彉閲g1鍜g2鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚︽帴瑙︺
杩斿洖1鎴0浠ユ寚鏄g1鐨勬渶灏忚竟鐣岀煩褰㈡槸鍚︿綅浜g2鐨勬渶灏忚竟鐣岀煩褰㈠唴銆
mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); mysql> SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'); mysql> SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1); +--------------------+--------------------+ | MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) | +--------------------+--------------------+ | 1 | 0 | +--------------------+--------------------+
OpenGIS瑙勮寖瀹氫箟浜嗕笅杩板嚱鏁般傜洰鍓嶅湪MySQL灏氭湭鎸夌収瑙勮寖瀹炴柦瀹冧滑銆傚浜庨偅浜涘凡瀹炴柦鐨勫嚱鏁帮紝瀹冧滑杩斿洖鐨勭粨鏋滀笌瀵瑰簲鐨勫熀浜MBR鐨勫嚱鏁拌繑鍥炵殑鐩稿悓銆傚寘鎷笅闈㈠垪鍑虹殑鍑芥暟锛屼絾Distance()鍜Related()闄ゅ銆
鍦ㄦ湭鏉ョ殑鐗堟湰涓紝鍙兘浼氬疄鏂借繖浜涘嚱鏁帮紝涓虹┖闂村垎鏋愭彁渚涘叏閮ㄦ敮鎸侊紝鑰屼笉浠呬粎鏄熀浜MBR鐨勬敮鎸併
杩欎簺鍑芥暟浣滅敤鍦2涓嚑浣曞g1鍜g2涓娿
杩斿洖1鎴0浠ユ寚鏄g1鏄惁瀹屽叏鍖呭惈g2銆
-
濡傛灉g1鍦ㄧ┖闂翠笂涓巊2鐩镐氦锛岃繑鍥1銆傚鏋g1涓篜olygon鎴MultiPolygon锛岃繑鍥濶ULL锛屾垨濡傛灉g2涓Point鎴MultiPoint杩斿洖NULL銆傚惁鍒欙紝杩斿洖0銆
鏈鈥滅┖闂翠笂浜ゅ弶鈥濇寚鐨勬槸2涓粰瀹氬嚑浣曞璞′箣闂寸殑绌洪棿鍏崇郴锛屽畠鍏锋湁涓嬭堪灞炴э細
o 2涓粨鍚堝璞′氦鍙夈
o 鍏朵氦鍙夌粨鏋滃皢瀵艰嚧鍏剁淮鏁板皬浜庝袱涓粰瀹氬嚑浣曞璞℃渶澶х淮鏁扮殑鍑犱綍瀵硅薄銆
o 鍏朵氦鍙変笉绛変簬涓や釜鍑犱綍瀵硅薄涓殑浠讳綍1涓
杩斿洖1鎴0浠ユ寚鏄g1鏄惁涓g2浠庣┖闂翠笂涓嶇浉浜ゃ
浠ュ弻绮惧害鏁板煎舰寮忚繑鍥2涓嚑浣曞璞′腑2鐐归棿鐨勬渶鐭窛绂汇
杩斿洖1鎴0浠ユ寚鏄g1鏄惁浠庣┖闂翠笂绛夊悓浜g2銆
杩斿洖1鎴0浠ユ寚鏄g1鏄惁浠庣┖闂翠笂涓g2鐩镐氦銆
杩斿洖1鎴0浠ユ寚鏄g1鏄惁浠庣┖闂翠笂涓g2浜よ凯銆傚鏋2涓嚑浣曞璞′氦鍙夎屼笖鍏朵氦鍙夊皢瀵艰嚧鍏锋湁鐩稿悓缁存暟浣嗗苟涓嶇瓑鍚屼簬浠讳竴鍑犱綍瀵硅薄鐨勫嚑浣曞璞★紝灏嗕娇鐢ㄦ湳璇滅┖闂翠氦杩濄
杩斿洖1鎴0浠ユ寚鏄庣敱pattern_matrix鎸囧畾鐨勭┖闂村叧绯绘槸鍚﹀湪g1鍜g2闂村瓨鍦ㄣ傚鏋滃弬閲忎负NULL杩斿洖-1銆傛ā寮忕煩褰负瀛楃涓层傚鏋滃疄鏂戒簡璇ュ嚱鏁帮紝鍏惰鑼冨皢鍦ㄦ缁欏嚭銆
杩斿洖1鎴0浠ユ寚鏄g1鏄惁浠庣┖闂翠笂涓g2鎺ヨЕ銆傚鏋滃嚑浣曞璞＄殑鍐呴儴涓嶄氦鍙夛紝浣1涓嚑浣曞璞＄殑杈圭晫涓庡彟涓涓殑杈圭晫鎴栧唴閮ㄤ氦鍙夛紝杩欎袱涓嚑浣曞璞℃槸浠庣┖闂翠笂鎺ヨЕ鐨勩
杩斿洖1鎴0浠ユ寚鏄g1鏄惁浠庣┖闂翠笂浣嶄簬g2鍐呫
路 鎼滅储鍖呭惈缁欏畾鐐圭殑鎵鏈夊璞＄殑Point鏌ヨ銆
路 鎼滅储涓庣粰瀹氬湴鍖轰氦杩殑鎵鏈夊璞＄殑鍦板尯鏌ヨ銆
MySQL閲囩敤浜鍏锋湁2娆″垎瑁傜壒鎬х殑R-Trees鏉ヤ负绌洪棿鍒楃紪鍒剁储寮曘備娇鐢ㄥ嚑浣曞璞＄殑MBR鏉ュ垱寤虹┖闂寸储寮曘傚浜庡ぇ澶氭暟鍑犱綍瀵硅薄锛MBR鏄寘鍥村嚑浣曞璞＄殑鏈灏忕煩褰€傚浜庢按骞虫垨鍨傜洿linestring锛MBR閫鍖栦负linestring鐨勭煩褰€傚浜庣偣锛MBR鏄鍖栦负鐐圭殑鐭╁舰銆
姝ゅ锛岃繕鑳藉湪绌洪棿鍒椾笂鍒涘缓姝ｅ父绱㈠紩銆傞渶瑕佷负闄POINT鍒椾箣澶栫殑绌洪棿鍒椾笂鐨勪换浣曠储寮曪紙闈炵┖闂达級澹版槑鍓嶇紑銆
19.6.1. 鍒涘缓绌洪棿绱㈠紩
MySQL鑳藉浣跨敤涓庡垱寤烘瑙勭储寮曠被浼肩殑璇硶鍒涘缓绌洪棿绱㈠紩锛屼絾浣跨敤浜SPATIAL鍏抽敭瀛楄繘琛屼簡鎵╁睍銆傚浜庣洰鍓嶇紪鍒朵簡绱㈠紩鐨勭┖闂村垪锛屽繀椤诲皢鍏跺０鏄庝负NOT NULL銆傚湪涓嬮潰鐨勭ず渚嬩腑锛屼粙缁嶄簡鍒涘缓绌洪棿绱㈠紩鐨勬柟娉曘
路 瀵逛簬CREATE TABLE锛
路 mysql> CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
路 瀵逛簬ALTER TABLE锛
路 mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
路 瀵逛簬CREATE INDEX锛
路 mysql> CREATE SPATIAL INDEX sp_index ON geom (g);
瀵逛簬MyISAM琛紝SPATIAL INDEX璐熻矗鍒涘缓R-tree绱㈠紩銆傚浜庢敮鎸佺┖闂寸储寮曠殑鍏朵粬瀛樺偍寮曟搸锛SPATIAL INDEX鑳藉鍒涘缓B-tree绱㈠紩銆傚浜庡噯纭殑鍊兼煡鎵捐屼笉鏄寖鍥存壂鎻忥紝浣滅敤鍦ㄧ┖闂村间笂鐨B-tree绱㈠紩寰堟湁鐢ㄣ
瑕佹兂鎾ら攢绌洪棿绱㈠紩锛屽彲浣跨敤ALTER TABLE鎴DROP INDEX锛
路 瀵逛簬ALTER TABLE锛
路 mysql> ALTER TABLE geom DROP INDEX g;
路 瀵逛簬DROP INDEX锛
路 mysql> DROP INDEX sp_index ON geom;
绀轰緥锛氬亣瀹氳〃geom鍖呭惈32000浠ヤ笂鐨勫嚑浣曞璞★紝瀹冧滑淇濆瓨鍦ㄧ被鍨嬩负GEOMETRY鐨勫垪g涓璇ヨ〃杩樻湁鐢ㄤ簬淇濆瓨瀵硅薄ID鍊肩殑AUTO_INCREMENT鍒椼
mysql> DESCRIBE geom; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | fid | int(11) | | PRI | NULL | auto_increment | | g | geometry | | | | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) FROM geom; +----------+ | count(*) | +----------+ | 32376 | +----------+ 1 row in set (0.00 sec)
瑕佹兂鍦ㄥ垪g涓婃坊鍔犵┖闂寸储寮曪紝鍙娇鐢ㄤ笅杩拌鍙ワ細
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g); Query OK, 32376 rows affected (4.05 sec) Records: 32376 Duplicates: 0 Warnings: 0
浼樺寲绋嬪簭灏嗚皟鏌ュ彲鐢ㄧ殑绌洪棿绱㈠紩鏄惁鑳藉寘鍚湪浣跨敤鏌愪簺鍑芥暟鐨勬煡璇㈡悳绱腑锛屽WHERE瀛愬彞涓殑MBRContains()鎴MBRWithin()鍑芥暟銆渚嬪锛屽亣瀹氭垜浠墦绠楁壘鍑轰綅浜庣粰瀹氱煩褰腑鐨勬墍鏈夊璞★細
mysql> SELECT fid,AsText(g) FROM geom WHERE
mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);
+-----+-----------------------------------------------------------------------------+
| fid | AsText(g) |
+-----+-----------------------------------------------------------------------------+
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8) |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4) |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2) |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823) |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2) |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) |
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2) |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121) |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113) |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6) |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2) |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077) |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4) |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019) |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8) |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8) |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134) |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4) |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001) |
+-----+-----------------------------------------------------------------------------+
20 rows in set (0.00 sec)
鎴戜滑浣跨敤EXPLAIN鏉ユ鏌ヨ鏌ヨ鐨勬墽琛屾柟寮忥紙ID鍒楀凡琚垹闄わ紝浠ヤ究杈撳嚭鑳芥洿濂藉湴涓庨〉鍖归厤锛锛
mysql> EXPLAIN SELECT fid,AsText(g) FROM geom WHERE
mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);
+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| SIMPLE | geom | range | g | g | 32 | NULL | 50 | Using where |
+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
璁╂垜浠鏌ヤ竴涓嬪湪娌℃湁绌洪棿绱㈠紩鐨勬儏鍐典笅浼氬嚭鐜颁粈涔堬細
mysql> EXPLAIN SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE
mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);
+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| SIMPLE | geom | ALL | NULL | NULL | NULL | NULL | 32376 | Using where |
+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
鎵цSELECT璇彞锛屽拷鐣绌洪棿閿細
mysql> SELECT fid,AsText(g) FROM geom IGNORE INDEX (g) WHERE
mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);
+-----+-----------------------------------------------------------------------------+
| fid | AsText(g) |
+-----+-----------------------------------------------------------------------------+
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2) |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121) |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113) |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6) |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2) |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077) |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4) |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019) |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8) |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8) |
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8) |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4) |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2) |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823) |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2) |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134) |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4) |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001) |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) |
+-----+-----------------------------------------------------------------------------+
20 rows in set (0.46 sec)
鏈娇鐢ㄧ储寮曟椂锛岃鏌ヨ鐨勬墽琛屾椂闂村皢浠0.00绉掍笂鍗囧埌0.46绉掋
鍦ㄦ湭鏉ョ殑鐗堟湰涓紝绌洪棿绱㈠紩涔熷彲鑳戒細鐢ㄤ簬浼樺寲鍏朵粬鍑芥暟銆傝鍙傝19.5.4鑺傦紝鈥滄祴璇曞嚑浣曞璞￠棿绌洪棿鍏崇郴鐨勫嚱鏁扳銆
棰濆鐨勫厓鏁版嵁瑙嗗浘
OpenGIS瑙勮寖寤鸿浜嗘暟绉嶉澶栫殑鍏冩暟鎹鍥俱備緥濡傦紝鍖呭惈鍑犱綍鍒楃殑鎻忚堪鐨勫悕涓GEOMETRY_COLUMNS鐨勭郴缁熻鍥撅紝瀵逛簬鏁版嵁搴撲腑鐨勬瘡鍒楁湁1琛岀浉鍏冲唴瀹广
浣滅敤鍦LineString鍜MultiLineString涓婄殑OpenGIS鍑芥暟Length()鐩墠搴斿湪MySQL涓互GLength()鐨勬柟寮忚皟鐢ㄣ
闂鍦ㄤ簬瀛樺湪1涓敤浜庤绠楀瓧绗︿覆鍊奸暱搴︾殑宸叉湁SQL鍑芥暟Length()锛岃屼笖鍦ㄦ煇浜涙儏鍐典笅鏃犳硶鍒ゆ柇鍑芥暟鏄湪鏂囨湰鍦烘櫙涓嬭繕鏄┖闂村満鏅笅璋冪敤鐨勩傛垜浠渶瑕佷互鏌愮鏂瑰紡瑙ｅ喅璇ラ棶棰橈紝鎴栫‘瀹氬彟涓涓嚱鏁板悕銆
杩欐槸MySQL鍙傝冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝冩墜鍐岋紝璇疯闂dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆