node_util/index/tb_cs/main.js

const fs = require('fs');
const turf = require('@turf/turf');
const wkx = require('wkx');
const Database = require('../db');
const objUtils = require('../../utils/objUtils');
const moment = require('moment');
const { console } = require('inspector');


const pool = new Database({
    host: '192.168.100.30',
    database: 'real3d',
    user: 'postgres',
    password: 'postgis',
    port: 5432,
});
const saveInfoTable = "tdgy_sj1";
const saveGeomTable = "TB_TDGY_SJ1";

async function updateTable() {
    // 连接到数据库，设置模式
    await pool.connect();
    await pool.setSchema('vector');

    let tbMap = new Map();
    tbMap.set("tdgy_sj_use", "TB_TDGY_SJ_use");
    tbMap.set("tdgy_sj_2022_cr", "TB_TDGY_SJ_use_2022cr");
    tbMap.set("tdgy_sj_2022_hb", "TB_TDGY_SJ_use_2022hb");
    tbMap.set("tdgy_sj_2023_cr", "TB_TDGY_SJ_use_2023cr");
    tbMap.set("tdgy_sj_2023_hb", "TB_TDGY_SJ_use_2023hb");

    for (const [infoTbName, geomTbName] of tbMap) {
        // 查询全部的属性表
        const sqlInfoQuery = `SELECT * FROM ${infoTbName}`;
        console.log(sqlInfoQuery);
        const infos = await pool.query(sqlInfoQuery);

        // 查询全部的几何表
        const sqlGeomQuery = `SELECT pid,xmmc,st_asewkt(geom) FROM "${geomTbName}"`;
        console.log(sqlGeomQuery);
        const geoms = await pool.query(sqlGeomQuery);

        // 生成pid对应的几何信息list
        let infoMap = new Map();
        for (let i = 0; i < geoms.rows.length; i++) {
            if (infoMap.has(geoms.rows[i].pid)) {
                let list = infoMap.get(geoms.rows[i].pid);
                list.push(geoms.rows[i]);
                infoMap.set(geoms.rows[i].pid, list);
            } else {
                infoMap.set(geoms.rows[i].pid, [geoms.rows[i]]);
            }
        }

        // 将infos插入到数据库
        for (let i = 0; i < infos.rows.length; i++) {
            let info = infos.rows[i];
            info = objUtils.setEmptyStrings(info)

            // 生成插入语句
            let sqlInsert = `INSERT INTO ${saveInfoTable}(xmmc,tdzl,crmj,tdyt,rjl,cjj,gylx,cjr,pzjg,pzwh,pzrq,xzqh,sulx,srf,crmj_m,tdsyq,htbh,jbr,zdyt,cqzh) VALUES ('${info.xmmc}','${info.tdzl}','${info.crmj}','${info.tdyt}','${info.rjl}','${info.cjj}','${info.gylx}','${info.cjr}','${info.pzjg}','${info.pzwh}','${info.pzrq}','${info.xzqh}','${info.sulx}','${info.srf}',${info.crmj_m},'${info.tdsyq}','${info.htbh}','${info.jbr}','${info.zdyt}','${info.cqzh}') RETURNING id`;

            console.log(sqlInsert);
            const insertInfo = await pool.query(sqlInsert);
            const insertedId = insertInfo.rows[0].id;
            console.log(insertedId);

            console.log("原id是什么:" + info.id);

            let geomList = infoMap.get(info.id);
            if(geomList==undefined){
                continue;
            }
            for (let j = 0; j < geomList.length; j++) {
                let geomItem = geomList[j];
                // TODO: 生成插入语句
                // 生成插入语句
                let sqlInsert = `INSERT INTO "${saveGeomTable}"(pid,xmmc,geom) VALUES ('${insertedId}','${geomItem.xmmc}',public.ST_GeomFromEWKT('${geomItem.st_asewkt}'))`;
                console.log(sqlInsert);
                const insertInfo = await pool.query(sqlInsert);
                console.log(insertInfo);
            }
        }

    }
}

updateTable()