目的:
将空间数据,存入到mysql中,然后其他GIS服务,通过odbc或者jdbc方式读取空间数据,实现图层展示和计算。
空间数据主要涉及:
点 - POINT(X Y)
线 - LINESTRING(X1 Y1,X2 Y2,X3 Y3,X4 Y4)
面 - POLYGON(X1 Y1,X2 Y2,X3 Y3,X4 Y4)
点集合 -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))
例子:
建表语句
-- auto-generated definition
CREATE TABLE points
(
id INT(11) UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(20) DEFAULT '' NOT NULL,
location POINT NOT NULL
)
ENGINE = InnoDB
CHARSET = utf8;
-- auto-generated definition
CREATE TABLE `lines`
(
id INT(11) UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(20) DEFAULT '' NOT NULL,
location LINESTRING NOT NULL
)
ENGINE = InnoDB
CHARSET = utf8;
-- auto-generated definition
CREATE TABLE multi_point
(
id INT(11) UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(20) DEFAULT '' NOT NULL,
location MULTIPOINT NOT NULL
)
ENGINE = InnoDB
CHARSET = utf8;
-- auto-generated definition
CREATE TABLE multi_line
(
id INT(11) UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(20) DEFAULT '' NOT NULL,
location MULTILINESTRING NOT NULL
)
ENGINE = InnoDB
CHARSET = utf8;
插入部分数据
插入点数据
INSERT INTO `points` VALUES (1, '圆明园', pointfromtext('POINT(116.311 40.014)'));
INSERT INTO `points` VALUES (2, '清华大学', pointfromtext('POINT(116.335 40.0105)'));
INSERT INTO `points` VALUES (3, '颐和园', pointfromtext('POINT(116.278 40.0065)'));
INSERT INTO `points` VALUES (4, '天安门', pointfromtext('POINT(116.405 39.9154)'));
INSERT INTO `points` VALUES (5, '北京站', pointfromtext('POINT(116.434 39.9101)'));
INSERT INTO `points` VALUES (6, '卢沟桥', pointfromtext('POINT(116.225 39.8565)'));
INSERT INTO `points` VALUES (7, '北京西站', pointfromtext('POINT(116.327 39.9025)'));
INSERT INTO `points` VALUES (8, '北京首都国际机场', pointfromtext('POINT(116.612 39.9025)'));
插入线数据
insert into multi_line values (4,"test",multilinestringfromtext('linestring(0 0,12 3), linestring(2 23,22 13)'));
发现保存之后,只有第一个线,并没有第二条线,这是为什么呢?写法不对?于是修改写法
insert into multi_line values (4,"test",multilinestringfromtext('(0 0,12 3), (2 23,22 13)'));
--结果
sql> insert into multi_line values (4,"test",multilinestringfromtext('(0 0,12 3), (2 23,22 13)'))
[2020-10-15 17:48:29] [23000][1048] Column 'location' cannot be null
竟然报错了,这有是咋回事儿?难道写法还是不对?再改!
insert into multi_line values (4,"test",multilinestring('(0 0,12 3), (2 23,22 13)'));
--结果
sql> insert into multi_line values (4,"test",multilinestring('(0 0,12 3), (2 23,22 13)'))
[2020-10-15 17:49:32] [22007][1367] Illegal non geometric ''(0 0,12 3), (2 23,22 13)'' value found during parsing
又报错了,冷静分析一下,看错误提示,应该是字符串转义过程中出错了,再改!
insert into multi_line values (4,"test",multilinestring((0 0,12 3), (2 23,22 13)));
--结果
sql> insert into multi_line values (4,"test",multilinestring((0 0,12 3), (2 23,22 13)))
[2020-10-15 17:50:47] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0,12 3), (2 23,22 13)))' at line 1
整个人都不好了,先上个厕所再说。
从测试回来后,感觉无事一身轻,突然想到了解决方法
insert into multi_line values (4,"aoligei",ST_GeomFromText('multilinestring((3 4,12 3), (2 2,31 13))'));
--结果
sql> insert into multi_line values (4,"aoligei",ST_GeomFromText('multilinestring((3 4,12 3), (2 2,31 13))'))
[2020-10-15 17:52:52] 1 row affected in 5ms
成功了!成功了!
分析一下原因,我人员是这样的,需要构造一个字符串,里面是标准的空间数据格式,然后通过FromText的方式,转换成标准格式插入数据库。
主要就是这里需要记录一下,其他的就比较简单了,因为我并不需要进行空间数据的比较运算,只是用于存储,所以不进行过多的赘述,目前为止到这儿已经可以了。