• 【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.csdn.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.csdn.net/wan_yanyan528/article/details/49250717

    https://blog.csdn.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>
  • 相关阅读:
    Codeforces Round #417 C. Sagheer and Nubian Market
    linux 终端抓包命令
    计算机网络体系结构分析
    排序算法-快速排序
    排序算法-堆排序
    排序算法-希尔排序
    排序算法-插入排序
    排序算法-冒泡排序
    排序算法-选择排序
    杂谈:终端小工具
  • 原文地址:https://www.cnblogs.com/defineconst/p/9663363.html
Copyright © 2020-2023  润新知