shp导入mysql

1.解压软件

将FWTools2.4.7.rar解压到D:\FWTools2.4.7 （无需安装）

2.将shp导入mysql

a 进入dos界面
b cd D:\FWTools2.4.7\bin
c 执行命令：
ogr2ogr -f MySQL MYSQL:数据库名,host=IP地址,user=用户,password=密码,port=端口 -nln 导入后表名 shp文件地址
示例:
ogr2ogr -f MySQL MYSQL:pms,host=localhost,user=root,password=1,port=3306 -nln beacon E:\点位.shp

3.mysql中执行

a 修改double的小数点位

b beacon点表

select * from point_info 
-- shp文件导入后的表
select * from beacon
-- 批量替换
delete from point_info where layer_id ='B1'；
set @r:=(select max(id) from point_info);
insert into point_info (id,name,layer_id,longitude,latitude,beacon_id,mac,location_type,status,error_range)
select @r:=@r+1, concat(major,minor),'B1', x,y,concat(uuid,major,minor),`设备id`,1,0,10
from  beacon
-- 根据唯一id，更新
update  point_info a set longitude=(select x from beacon b where  a.beacon_id=concat(b.uuid,b.major,b.minor) ) where a.layer_id='B1';
update  point_info a set latitude=(select y from beacon b where  a.beacon_id=concat(b.uuid,b.major,b.minor) ) where a.layer_id='B1'
update  point_info a set mac=(select `设备id` from beacon b where  a.beacon_id=concat(b.uuid,b.major,b.minor) ) where a.layer_id='B1'

注意：
报错：

处理：