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

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;

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();

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");}//数据记录转换为GeoJsonfunction 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) { ???????} ???})})

<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>