daocha_serve/Source/server/yanyilianxiServer/sqlUtil.js

// 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用
// getALLlist
function tablelist(req) {
    let sql = `select * from yanyilianxi order by id asc`;
    return sql;
}

// 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用
//增
function add(req) {
    let name = req.body.name;
    let age = req.body.age;
    let mark = req.body.mark;
    let hobby = req.body.hobby;
    let sql = `INSERT INTO yanyilianxi ("name","age","mark","hobby")
	VALUES ( '${name}',${age},${mark},'${hobby}')`;
    return sql;
}
// 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用
//删
function delet(req) {
    let name = req.body.name;
    let sql = `DELETE FROM yanyilianxi
       WHERE name = '${name}' ;`;
    return sql;
}

// 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用 通用
//查
function select(req) {
    let name = req.body.name;
    let sql = `SELECT * FROM yanyilianxi
         WHERE name = '${name}';`;
    return sql;
}



//save->WKT
function savewkt(req) {
    let tablename = req.body.tablename;
    let obj = req.body.saveobj

    let sql = `INSERT INTO "${tablename}"(
        "slhName", "slhContent", "slhPlaceBelong", "slhShapeBelong", "slhCategory", "slhCoors", remark)
        VALUES ('${obj.name}','${obj.entitycontent}','${obj.layername}','${obj.entityshape}','${obj.entitylayer}',st_geomfromtext('${obj.wktcoor}',4326),'${obj.remark}');`
    return sql;
}
//get->WKT


//查询所有(包括空间信息geo字段)
function getallwkt(req) {
    let tablename = req.body.tablename;
    let type = req.body.type;
    let sql = `SELECT *,st_astext("slhCoors") FROM "${tablename}" WHERE "slhPlaceBelong" = '${type}';`;
    return sql;
}
//通过id查所选实体
function getwktbyId(req) {
    let tablename = req.body.tablename;
    let id = req.body.id;
    let sql = `SELECT *,st_astext("slhCoors") FROM "${tablename}" WHERE "slhId" = ${id};`;
    return sql;
}
//通过id更新所选实体信息
function updatawktbyId(req) {
    let tablename = req.body.tablename;
    let saveobj = req.body.saveobj;
    let sql = `UPDATE  "${tablename}" SET "slhName"='${saveobj.name}',"slhContent"='${saveobj.entitycontent}' 
                        WHERE "slhId" = ${saveobj.id};`;
    return sql;
}
//通过id删除所选实体
function delwktbyId(req) {
    let tablename = req.body.tablename;
    let id = req.body.id;
    let sql = `DELETE FROM "${tablename}"
       WHERE "slhId" = ${id} ;`;
    console.log(sql);
    return sql;
}

//缓冲区查询(输入：表名、wkt格式、缓冲区半径)使用ST_DWithin()函数
function queryBuffer(req) {
    // select *,st_astext("slhCoors") from public.t_yanyilianxi_shilianghua where ST_DWithin("slhCoors"::Geography, ST_GeomFromText('LINESTRING(111.6555 39.3585,111.6844 39.6145)',4326)::Geography, 5000)=true order by "slhId" asc
    let tablename = req.body.tablename;
    let wktstr = req.body.obj.wktcoor;
    let range = req.body.obj.R;
    let sql = `select *,st_astext("slhCoors") from "${tablename}" where ST_DWithin("slhCoors"::Geography, 
                ST_GeomFromText('${wktstr}',4326)::Geography, ${range})=true;`;
    console.log(sql);
    return sql;
}

//线、面缓冲区查询，（就是看表内有没有和目标实体相交的数据，有就返回，用ST_Intersects()函数）(输入：表名、wkt格式)
function queryBufferXJ(req) {
    //select *,st_astext("slhCoors") from public.t_yanyilianxi_shilianghua where ST_Intersects("slhCoors", ST_GeomFromText('LINESTRING(111.6555 39.3585,111.6844 39.6145)',4326))
    let tablename = req.body.tablename;
    let wktstr = req.body.obj.wktcoor;
    let sql = `select *,st_astext("slhCoors") from "${tablename}" where ST_Intersects("slhCoors", ST_GeomFromText('${wktstr}',4326));`;
    console.log(sql);
    return sql;
}

module.exports = {
    tablelist,
    add,
    delet,
    select,
    savewkt,
    getallwkt,
    getwktbyId,
    updatawktbyId,
    delwktbyId,
    queryBuffer,
    queryBufferXJ
};