一, 基本架构
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>
来源: http://www.bubuko.com/infodetail-2771279.html