一、基本架构
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>