用js将geojson转换成WKT格式并插入到数据库

用js将geojson转换成WKT格式并插入到数据库

var fs = require("fs");
var mysql = require("mysql");
var Geojson2wkt = require("Geojson2Wkt");

var connection = mysql.createConnection({
    host: "172.16.2.252",
    port: 3316,
    user: "root",
    password: "Wang4995",
    database: "point"
});

connection.connect();

// 定义表名
var tableName = "zy_plant"; // 替换为你想要的表名

// Read JSON file
fs.readFile("./zy20240110.json", { encoding: "utf-8" }, (err, data) => {
    if (err) {
        console.error(err);
        return;
    }

    let jsonobj = JSON.parse(data);
    var features = jsonobj.features;

    // Extract properties from the first feature
    var properties = features[0].properties;

    // Create table SQL statement
    var createTableSQL = "CREATE TABLE IF NOT EXISTS " + tableName + " (id INT AUTO_INCREMENT PRIMARY KEY, ";

    // Iterate through properties and add columns to the SQL statement
    for (var key in properties) {
        if (properties.hasOwnProperty(key)) {
            createTableSQL += key + " VARCHAR(255), ";
        }
    }

    createTableSQL += "geom GEOMETRY);";

    // Execute create table SQL statement
    connection.query(createTableSQL, (err, results) => {
        if (err) {
            console.error(err);
            return;
        }
        console.log("Table " + tableName + " created or already exists.");

        var insertedCount = 0;

        // Iterate through features and insert data into the table
        for (var i = 0; i < features.length; i++) {
            var feature = features[i];
            var insertData = [];
            var insertedFields = [];

            // Iterate through properties and add values to insertData
            for (var key in properties) {
                if (feature.properties.hasOwnProperty(key)) {
                    insertData.push("'" + feature.properties[key] + "'");
                    insertedFields.push(key);
                }
            }

            // Add geometry data
            var geometryWKT = Geojson2wkt.convert(feature.geometry);
            insertData.push("ST_GeomFromText('" + geometryWKT + "')");
            insertedFields.push("geom");

            // Construct insert SQL statement
            var insertSQL = "INSERT INTO " + tableName + " (" + insertedFields.join(", ") + ") VALUES (" + insertData.join(", ") + ")";

            // Execute insert SQL statement
            connection.query(insertSQL, (err, results) => {
                if (err) {
                    console.error(err);
                } else {
                    insertedCount++;
                    console.log(`Inserted data for fields: ${insertedFields.join(", ")}`);
                    console.log(`Total inserted records: ${insertedCount}`);
                }
            });
        }

        // Close database connection
        connection.end();
    });
});
``