GIS开发：geojson数据导入mysql数据库

原创

GIS技术杂谈 2022-09-27 16:29:10 ©著作权

文章标签 json mysql sql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者GIS技术杂谈的原创作品，请联系作者获取转载授权，否则将追究法律责任

使用nodejs，实现将geojson空间图层数据，导入到mysql数据库中。
具体的实现代码：
//引用fs、mysql和Geojson2Wkt的类库
var fs = require("fs");
var mysql = require('mysql');
     //Geojson2Wkt是将geojson中的坐标转换成wkt的形式
var Geojson2wkt = require('Geojson2Wkt');
    //设置mysql的连接信息
var connection = mysql.createConnection({ 
  host: 'localhost', 

  user: 'root', 

  password: '123456', 

  database: 'databasename'
});
//打开数据库连接
connection.connect();
var sql = [];
fs.readFile('geojson图层文件', {
  encoding: "utf-8"
}, (err, data) => {
  let jsonobj = JSON.parse(data);
  var arr = jsonobj.features;
  var brr = [];
  var crr = [];
  var drr = [];
  var data = [];
  var k = 0;
  var sqla;
//将geojson坐标转换成wkt形式
  for (var i = 0; i < arr.length; i++) {
    brr[i] = arr[i].geometry;
    drr[i] = arr[i].properties; 

    crr[i] = Geojson2wkt.convert(brr[i]);
  }
  for (var j in drr[0]) {
    k++;
    data[k] = j;
  }
 //获得geojson数据的空间类型，mysql需要根据不同的空间类型设置字段
  var tdata = arr[0].geometry.type;
  tdata = tdata.toLowerCase();
 //创建mysql数据表的sql语句

  sqla = "CREATE TABLE IF NOT EXISTS `tablename`( `" + data[1] + "`  VARCHAR(100) NOT NULL, `" + data[2] + "` VARCHAR(100) NOT NULL, `" + data[3] + "` VARCHAR(100) NOT NULL, `" + data[4] + "` VARCHAR(100) NOT NULL, `" + data[5] + "` VARCHAR(100) NOT NULL,`geometry`  " + tdata + " NOT NULL)";
  connection.query(sqla);
//拼接sql语句，循环写入到图层中的数据
  for (var t = 0; t < arr.length; t++) {
    sql[t] =
 "insert into tablename(" + data[1] + "," + data[2] + "," + data[3] + "," + data[4] + "," + data[5] + ", geometry) values('" + drr[t][data[1]] + "','" + drr[t][data[2]] + "'," + "'" + drr[t][data[3]] + "','" + drr[t][data[4]] + "'," + "'" + drr[t][data[5]] + "'" + ",GeomFromText('" + crr[t] + "'))"; 
    connection.query(sql[t]);
  }
});