【GIS】postgres（postgis） --》nodejs+express --》geojson --》leaflet

一、基本架构

1、数据存储层：PostgreSQL-9.2.13 + postgis_2_0_pg92

2、业务处理层：Nodejs + Express + PG驱动

3、前端展示层：Leaflet

二、参考资料

1、POSTGIS 函数接口：

https://blog.****.net/pzysoft/article/details/76944160

2、POSTGIS官方文档：

http://www.postgres.cn/index.php/home

http://postgis.net/docs/manual-2.3/ST_AsGeoJSON.html

http://www.postgres.cn/

3、NODEJS+POSTGRES：

http://yijiebuyi.com/blog/d70c54b7de06d6151b3c68b1474e4bf8.html

https://blog.****.net/wan_yanyan528/article/details/49250717

https://blog.****.net/cheneypao/article/details/51378053

 

三、关键代码

1、geojson处理：GeoJsonUtil.js

function ToGeoJson(jsonObject) {
    //移除geometry
    //把其他属性变为properties
    var geoJson = {
        "type": "Feature"
    };
    // console.log(jsonObject)
    // console.log(jsonObject.geometry)
    geoJson.geometry = eval('(' + jsonObject.geometry + ')');
    delete jsonObject.geometry;
    // console.log(jsonObject)
    geoJson.properties = jsonObject;
    return geoJson;
}

exports.ToGeoJson = ToGeoJson;

2、PG访问：pg.js

var pg = require('pg');

var server = 'localhost'
var db = 'ecodb'
var user = 'postgres'
var pwd = '*******'
var port = '5432'
var conString = "tcp://" + user + ":" + pwd + "@" + server + "/" + db + "";

var pgConfig = {
    user: user,
    database: db,
    password: pwd,
    host: server,
    port: port,
    poolSize: 5,
    poolIdleTimeout: 30000,
    reapIntervalMillis: 10000
};

var pgPool = new pg.Pool(pgConfig);
var client = new pg.Client(conString);

var PG = function () {
    console.log("准备向****数据库连接...");
};

PG.prototype.getConnection = function () {
    client.connect(function (err) {
        if (err) {
            return console.error('could not connect to postgres', err);
        }
        client.query('set client_encoding to "utf8";SELECT NOW() AS "theTime"', function (err, result) {
            if (err) {
                return console.error('error running query', err);
            }
            console.log(db + "数据库连接成功...");
        });
    });
};

// 查询函数
//@param str 查询语句
//@param value 相关值
//@param cb 回调函数
var clientHelper = function (str, value, cb) {
    client.query(str, value, function (err, result) {
        if (err) {
            cb("err");
            console.log(err);
        } else {
            // console.log(result)
            if (result.rows != undefined)
                cb(result.rows);
            else
                cb();
        }
    });
}

PG.prototype.exec = function (strSql, cb) {
    client.query(strSql, "", function (err, result) {
        if (err) {
            cb("err");
            console.log(err);
        } else {
            // console.log(result)
            if (result.rows != undefined)
                cb(result.rows);
            else
                cb();
        }
    });
}

//增
//@param tablename 数据表名称
//@param fields 更新的字段和值，json格式
//@param cb 回调函数
PG.prototype.save = function (tablename, fields, cb) {
    if (!tablename) return;
    var str = "insert into " + tablename + "(";
    var field = [];
    var value = [];
    var num = [];
    var count = 0;
    for (var i in fields) {
        count++;
        field.push(i);
        value.push(fields[i]);
        num.push("$" + count);
    }
    str += field.join(",") + ") values(" + num.join(",") + ")";
    clientHelper(str, value, cb);
};

//删除
//@param tablename 数据表名称
//@param fields 条件字段和值，json格式
//@param cb 回调函数
PG.prototype.remove = function (tablename, fields, cb) {
    if (!tablename) return;
    var str = "delete from " + tablename + " where ";
    var field = [];
    var value = [];
    var count = 0;
    for (var i in fields) {
        count++;
        field.push(i + "=$" + count);
        value.push(fields[i]);
    }
    str += field.join(" and ");
    clientHelper(str, value, cb);
}

//修改
//@param tablename 数据表名称
//@param fields 更新的字段和值，json格式
//@param mainfields 条件字段和值，json格式
PG.prototype.update = function (tablename, mainfields, fields, cb) {
    if (!tablename) return;
    var str = "update " + tablename + " set ";
    var field = [];
    var value = [];
    var count = 0;
    for (var i in fields) {
        count++;
        field.push(i + "=$" + count);
        value.push(fields[i]);
    }
    str += field.join(",") + " where ";
    field = [];
    for (var j in mainfields) {
        count++;
        field.push(j + "=$" + count);
        value.push(mainfields[j]);
    }
    str += field.join(" and ");
    clientHelper(str, value, cb);
}

//查询
//@param tablename 数据表名称
//@param fields 条件字段和值，json格式
//@param returnfields 返回字段,字段数组
//@param cb 回调函数
PG.prototype.select = function (tablename, fields, returnfields, cb) {
    if (!tablename) return;
    var returnStr = "";
    console.log(returnfields)
    if (returnfields.length == 0)
        returnStr = '*';
    else
        returnStr = returnfields.join(",");
    var str = "select " + returnStr + " from " + tablename; // + " where ";
    console.log('select:' + str)
    var field = [];
    var value = [];
    var count = 0;
    if (fields != '') {
        for (var i in fields) {
            count++;
            field.push(i + "='" + fields[i] + "'");
            value.push(fields[i]);
        }
    }
    if (count != 0) {
        str += " where " + field.join(" and ");
    }
    console.log('select:' + str)
    clientHelper(str, value, cb);
};

module.exports = new PG();

3、geojson服务：pgclient.js

var pgclient = require('./PG')
var express = require('express');
var app = express();
var bodyParser = require("body-parser");
var geoJson = require('./GeoJsonUtil')

//访问端口
var port = 9999

//接受post数据
app.use(bodyParser.urlencoded({
    extended: false
}));

//初始化数据库连接
pgclient.getConnection();

//设置跨域
function setCross(res) {
    //设置允许跨域的域名，*代表允许任意域名跨域
    res.header("Access-Control-Allow-Origin", "*");
    //允许的header类型
    res.header("Access-Control-Allow-Headers", "content-type");
    //跨域允许的请求方式
    res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
}

//数据记录转换为GeoJson
function DataToGeoJson(ds) {
    var jsonDS = JSON.stringify(ds);
    var geojson = {
        "type": "FeatureCollection"
    }
    var features = [];
    jsonDS = eval('(' + jsonDS + ')')

    for (var p in jsonDS) {
        var PO = jsonDS[p]

        var pRet = geoJson.ToGeoJson(PO);

        features.push(pRet)
    }
    geojson.features = features;
    return geojson;
}
app.get('/ListSTSAreaJson2', function (req, res) {
    console.log(req.query)
    console.log(req.body)

    setCross(res)

    var sql = "select id,name,code,color,show_on_map,ST_AsGeoJson(shape,6)  as geometry from chinastsarea ";
    var strSql = req.query.code == undefined ? sql :
        sql + " where code='" + req.query.code + "'";

    pgclient.exec(strSql, function (ds) {
        // console.log(ds)
        res.writeHead(200, {
            'Content-Type': 'text/plain; charset=utf-8'
        });
        try {
            var geojson = DataToGeoJson(ds)

            res.end(JSON.stringify(geojson));
        } catch (error) {

        }
    })
})

5、Leaflet空间数据展示：leaflet.geojson.html

<html>

<head>
    <meta charset=utf-8 />
    <title>Leaflet Control.Layers</title>
    <meta name='viewport' content='initial-scale=1,maximum-scale=1,user-scalable=no' />

    <!-- Load Leaflet from CDN -->
    <link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.4/dist/leaflet.css" integrity="sha512-puBpdR0798OZvTTbP4A8Ix/l+A4dHDD0DGqYW6RQ+9jxkRFclaxxQb/SJAWZfWAkuyeQUytO7+7N4QKrDh+drA=="
        crossorigin="" />
    <script src="https://unpkg.com/leaflet@1.3.4/dist/leaflet.js" integrity="sha512-nMMmRyTVoLYqjP9hrbed9S+FzjZHW5gY1TWCHA5ckwXZBadntCNs8kEqAWdrb9O7rxbCaA4lKTIWjDXZxflOcA=="
        crossorigin=""></script>

    <!-- Load Esri Leaflet from CDN -->
    <script src="https://unpkg.com/esri-leaflet@2.2.3/dist/esri-leaflet.js" integrity="sha512-YZ6b5bXRVwipfqul5krehD9qlbJzc6KOGXYsDjU9HHXW2gK57xmWl2gU6nAegiErAqFXhygKIsWPKbjLPXVb2g=="
        crossorigin=""></script>

    <script src='../assets/libs/jquery/jquery-2.1.1.min.js'></script>

    <style>
        body {
            margin: 0;
            padding: 0;
        }

        #map {
            position: absolute;
            top: 0;
            bottom: 0;
            right: 0;
            left: 0;
        }
    </style>
    <style>
        #selectedFeatures {
            position: absolute;
            bottom: 180px;
            right: 10px;
            z-index: 1000;
            background: white;
            padding: 1em;
        }

        #selectedFeatures2 {
            position: absolute;
            bottom: 240px;
            right: 10px;
            z-index: 1000;
            background: white;
            padding: 1em;
        }

        .leaflet-bar.map-text a {
            color: #79BD8F;
            display: inline;
        }
    </style>
    <style>
        #time-ranges {
            position: absolute;
            bottom: 300px;
            right: 10px;
            z-index: 1000;
            padding: 1em;
            background: white;
        }

        #time-ranges input {
            display: inline-block;
            border: 1px solid #999;
            font-size: 14px;
            border-radius: 4px;
            height: 28px;
            line-height: 28px;
        }

        #time-ranges input[type='submit'] {
            box-sizing: content-box;
            padding: 0 1em;
            text-transform: uppercase;
            color: white;
            background: #5C7DB8;
            border-color: #5C7DB8;
        }
    </style>
</head>

<body>

    <div id="map"></div>

    <script>
        var gray = L.layerGroup();

        // more than one service can be grouped together and passed to the control together
        L.esri.basemapLayer("DarkGray").addTo(gray);
        L.esri.basemapLayer("GrayLabels").addTo(gray);

        var map = L.map('map', {
            zoom: 4,
            layers: [gray]
        });

        var stsAreaLayer, stsSTSChildAreaLayer, stsSTSDemoAreaLayer;

        $.ajax({
            dataType: "json",
            url: "http://192.168.198.21:9999/ListSTSAreaJson2",
            success: function (data) {

                console.log(data);

                stsAreaLayer = L.geoJSON(data).addTo(map);
            }
        }).error(function () {});

        $.ajax({
            dataType: "json",
            url: "http://192.168.198.21:9999/ListSTSChildAreaJson2",
            success: function (data) {

                console.log(data);

                stsSTSChildAreaLayer = L.geoJSON(data).addTo(map);
            }
        }).error(function () {});

        $.ajax({
            dataType: "json",
            url: "http://192.168.198.21:9999/ListSTSDemoAreaJson2",
            success: function (data) {

                console.log(data);

                stsSTSDemoAreaLayer = L.geoJSON(data).addTo(map);
            }
        }).error(function () {});

        map.setView([37.71, 109.88], 3);
    </script>

</body>

</html>