MySQL中空间数据和批量插入

墨蓝 2022-01-27 11:47 378阅读 0赞

**1.MySQL中的空间扩展**

基本Geometry类具有关于Point、Curve、Surface和GeometryCollection的子类：  
·Point表示0维对象。  
·Curve表示1维对象，具有子类LineString，以及次级子类Line和LinearRing。  
·Surface是为2维对象设计的，具有子类Polygon。

参考文章：  
[https://blog.csdn.net/zzq900503/article/details/17142621][https_blog.csdn.net_zzq900503_article_details_17142621]  
[https://blog.csdn.net/woshirsn/article/details/52670383][https_blog.csdn.net_woshirsn_article_details_52670383]  
[https://blog.csdn.net/bit\_sky/article/details/50056605][https_blog.csdn.net_bit_sky_article_details_50056605]  
[https://blog.csdn.net/qq\_27690839/article/details/90377891][https_blog.csdn.net_qq_27690839_article_details_90377891]  
[https://blog.csdn.net/woshirsn/article/details/52670383][https_blog.csdn.net_woshirsn_article_details_52670383]

MySQL支持以下数据类型：  
Geometry:可以存储所有的几何类型  
Point:简单点  
LINESTRING:简单线  
POLYGON:简单面  
MULITIPOINT:多点  
MULITILINESTRING:多线  
MUILITIPOLYGON:多面  
GEOMETRYCOLLECTION:任何几何集合

建表：

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
    ;

更多插入数据、查询、建索引、以及MySQL空间相关函数可以参考：  
[https://www.cnblogs.com/liuwt0911/p/5803679.html][https_www.cnblogs.com_liuwt0911_p_5803679.html]

MySQL中地理位置数据扩展geometry的使用心得参考文章：  
[https://www.linuxidc.com/Linux/2018-09/154191.htm][https_www.linuxidc.com_Linux_2018-09_154191.htm]

地理空间数据Geometry在MySQL中使用  
[https://blog.csdn.net/wiborgite/article/details/85069833][https_blog.csdn.net_wiborgite_article_details_85069833]  
[https://blog.csdn.net/wiborgite/article/details/85076270][https_blog.csdn.net_wiborgite_article_details_85076270]

MySQL GIS空间数据库功能详细学习

[https://blog.csdn.net/chaiqi/article/details/23099407/][https_blog.csdn.net_chaiqi_article_details_23099407]  
[https://blog.csdn.net/long535/article/details/75714781][https_blog.csdn.net_long535_article_details_75714781]  
[https://blog.csdn.net/devcopper/article/details/38088001][https_blog.csdn.net_devcopper_article_details_38088001]

**2.mysql空间扩展 VS PostGIS**

参考文章：  
[https://www.cnblogs.com/LBSer/p/3629149.html][https_www.cnblogs.com_LBSer_p_3629149.html]

**3.Navicat for MySQL执行SQL语句**

参考文章：  
[https://jingyan.baidu.com/article/a378c960d1b713b3282830fe.html][https_jingyan.baidu.com_article_a378c960d1b713b3282830fe.html]

**4.MySQL设置唯一标识，主键**

参考文章：  
[https://blog.csdn.net/qq\_29735775/article/details/81165647][https_blog.csdn.net_qq_29735775_article_details_81165647]  
[https://blog.csdn.net/heiqibaier/article/details/80055830][https_blog.csdn.net_heiqibaier_article_details_80055830]

**5.MySQL批量导入数据**

[https://jingyan.baidu.com/article/3ea514899dcf0352e61bba25.html][https_jingyan.baidu.com_article_3ea514899dcf0352e61bba25.html]  
[https://blog.csdn.net/YYstrong/article/details/76335893][https_blog.csdn.net_YYstrong_article_details_76335893]  
[https://blog.csdn.net/u013793732/article/details/52673697][https_blog.csdn.net_u013793732_article_details_52673697]  
[https://jingyan.baidu.com/album/95c9d20d61b01dec4f75615a.html?picindex=6][https_jingyan.baidu.com_album_95c9d20d61b01dec4f75615a.html_picindex_6]

更多参考文章：  
[https://yq.aliyun.com/articles/50625][https_yq.aliyun.com_articles_50625]  
[https://bbs.csdn.net/topics/390584295][https_bbs.csdn.net_topics_390584295]  
[https://cloud.tencent.com/developer/information/gis空间数据库][https_cloud.tencent.com_developer_information_gis]  
[https://classicning.iteye.com/blog/100614][https_classicning.iteye.com_blog_100614]  
[https://www.zhihu.com/question/27918946][https_www.zhihu.com_question_27918946]

[https_blog.csdn.net_zzq900503_article_details_17142621]: https://blog.csdn.net/zzq900503/article/details/17142621
[https_blog.csdn.net_woshirsn_article_details_52670383]: https://blog.csdn.net/woshirsn/article/details/52670383
[https_blog.csdn.net_bit_sky_article_details_50056605]: https://blog.csdn.net/bit_sky/article/details/50056605
[https_blog.csdn.net_qq_27690839_article_details_90377891]: https://blog.csdn.net/qq_27690839/article/details/90377891
[https_www.cnblogs.com_liuwt0911_p_5803679.html]: https://www.cnblogs.com/liuwt0911/p/5803679.html
[https_www.linuxidc.com_Linux_2018-09_154191.htm]: https://www.linuxidc.com/Linux/2018-09/154191.htm
[https_blog.csdn.net_wiborgite_article_details_85069833]: https://blog.csdn.net/wiborgite/article/details/85069833
[https_blog.csdn.net_wiborgite_article_details_85076270]: https://blog.csdn.net/wiborgite/article/details/85076270
[https_blog.csdn.net_chaiqi_article_details_23099407]: https://blog.csdn.net/chaiqi/article/details/23099407/
[https_blog.csdn.net_long535_article_details_75714781]: https://blog.csdn.net/long535/article/details/75714781
[https_blog.csdn.net_devcopper_article_details_38088001]: https://blog.csdn.net/devcopper/article/details/38088001
[https_www.cnblogs.com_LBSer_p_3629149.html]: https://www.cnblogs.com/LBSer/p/3629149.html
[https_jingyan.baidu.com_article_a378c960d1b713b3282830fe.html]: https://jingyan.baidu.com/article/a378c960d1b713b3282830fe.html
[https_blog.csdn.net_qq_29735775_article_details_81165647]: https://blog.csdn.net/qq_29735775/article/details/81165647
[https_blog.csdn.net_heiqibaier_article_details_80055830]: https://blog.csdn.net/heiqibaier/article/details/80055830
[https_jingyan.baidu.com_article_3ea514899dcf0352e61bba25.html]: https://jingyan.baidu.com/article/3ea514899dcf0352e61bba25.html
[https_blog.csdn.net_YYstrong_article_details_76335893]: https://blog.csdn.net/YYstrong/article/details/76335893
[https_blog.csdn.net_u013793732_article_details_52673697]: https://blog.csdn.net/u013793732/article/details/52673697
[https_jingyan.baidu.com_album_95c9d20d61b01dec4f75615a.html_picindex_6]: https://jingyan.baidu.com/album/95c9d20d61b01dec4f75615a.html?picindex=6
[https_yq.aliyun.com_articles_50625]: https://yq.aliyun.com/articles/50625
[https_bbs.csdn.net_topics_390584295]: https://bbs.csdn.net/topics/390584295
[https_cloud.tencent.com_developer_information_gis]: https://cloud.tencent.com/developer/information/gis%E7%A9%BA%E9%97%B4%E6%95%B0%E6%8D%AE%E5%BA%93
[https_classicning.iteye.com_blog_100614]: https://classicning.iteye.com/blog/100614
[https_www.zhihu.com_question_27918946]: https://www.zhihu.com/question/27918946