建立mysql数据库
登录mysql后，创建一个新库。我的库名是geometry。
通过osm获得的poi信息切出上海杨浦区部分输出为geojson。
通过如下代码将geojson数据存入
/*
* @Author: citrusrlia@foxmail.com
* @FilePath: \shy-node-back\writeJsonInsert.js
* @Date: 2021-05-24 11:21:32
* @Description: geojson数据入库
*/
const mysql = require("mysql");
const fs = require("fs");
let result = fs.readFileSync("./statics/yangpu_poi.json"); //osm数据
let data = JSON.parse(result.toString());
let ready = data.features;
const connection = mysql.createConnection({
host: "localhost",
user: "************",
password: "*************",
database: "************",
port: "6326",
});
connection.connect();
let AddParam = [];
for (let i = 0, l = ready.length; i < l; i++) {
let osm_id = ready[i].properties.osm_id,
type = ready[i].geometry.type,
coordinateStr = ready[i].geometry.coordinates.toString(),
code = ready[i].properties.code,
fclass = ready[i].properties.fclass,
name = ready[i].properties.name;
AddParam = [osm_id, type, coordinateStr, code, fclass, name];
connection.query(
"INSERT INTO geometry(osm_id,type,coordinates,code,fclass,name) VALUES(?,?,?,?,?,?)",
AddParam,
function (error, results) {
if (error) {
console.log("INSERT ERROR - ", error.message);
return;
}
console.log("INSERT ID:", results);
}
);
}
connection.end();
这里将经纬度数据转化为字符串存储，部分长度会超过255字节限制，字段类型需要改为长文本类型。
image.png
现在获得了储存了geojson数据的表。
使用express框架搭建服务
首先安装express和express脚手架
npm i express
npm i express-generator
运行 建立名叫app的项目 会得到如下图的目录结构
express app
image.png
先在public/javascripts中新建sqls.js 用module.exports输出各增删改查方法
/*
* @Author: citrusrlia@foxmail.com
* @FilePath: \app\public\javascripts\sqls.js
* @Date: 2021-05-24 15:01:08
* @Description: 对数据库的操作
*/
const mysql = require("mysql");
const connection = mysql.createConnection({
host: "localhost",
user: "*******",
password: "***********",
database: "*************",
port: "6326",
});
function addItem(params, callback) {
let sql = `INSERT INTO geometry(osm_id,type,coordinates,code,fclass,name,id) VALUES(?,?,?,?,?,?,?)`;
let AddParam = [
params.osm_id,
params.type,
params.coordinates,
params.code,
params.fclass,
params.name,
params.id,
];
connection.query(sql, AddParam, function (err, result) {
if (err) {
console.log("Add Geometry ERROR - ", err.message);
return;
}
callback(result);
});
}
function deleteByName(name, callback) {
let sql = `DELETE FROM geometry where name=?`;
connection.query(sql, [name], function (err, result) {
if (err) {
console.log("Delete Geometry ERROR - ", err.message);
return;
}
callback(result);
});
}
function editById(params, id, callback) {
let sql = `UPDATE geometry SET osm_id=?,type=?,coordinates=?,code=?,fclass=?,name=?,id=? where osm_id=? or id=?`;
let UpdateParam = [
params.osm_id,
params.type,
params.coordinates,
params.code,
params.fclass,
params.name,
params.id,
];
connection.query(sql, [...UpdateParam, id, id], function (err, result) {
if (err) {
console.log("UPDATE Geometry ERROR - ", err.message);
return;
}
callback(result);
});
}
function pagination(thispage, pages, callback) {
let sql = `SELECT * from geometry limit ${(thispage - 1) * pages},${pages}`;
connection.query(sql, function (err, result) {
if (err) {
console.log("pagination ERROR - ", err.message);
return;
}
callback(result);
});
}
function getByClass(fclass, callback) {
let sql = `SELECT * FROM geometry WHERE fclass = ?`;
connection.query(sql, [fclass], function (err, result) {
if (err) {
console.log("GET By class ERROR - ", err.message);
return;
}
callback(result);
});
}
function getById(id, callback) {
let sql = `SELECT * FROM geometry WHERE id = ? or osm_id=?`;
connection.query(sql, [id, id], function (err, result) {
if (err) {
console.log("GET By ID ERROR - ", err.message);
return;
}
callback(result);
});
}
function getByName(name, callback) {
let sql = `SELECT * FROM geometry WHERE name = ?`;
connection.query(sql, [name], function (err, result) {
if (err) {
console.log("GET By Name ERROR - ", err.message);
return;
}
callback(result);
});
}
function getAllFclass(callback) {
let sql = `SELECT fclass FROM geometry`;
connection.query(sql, function (err, result) {
if (err) {
console.log("GET ALL CLASS ERROR - ", err.message);
return;
}
let arr = [];
for (let i = 0, l = result.length; i < l; i++) {
if (!arr.includes(result[i].fclass)) {
arr.push(result[i].fclass);
}
}
callback(arr);
});
}
function getByPosition(initPoint, range, jsonUrl) {
try {
initPoint = eval(initPoint + "//@ sourceURL=initpoint"); //便于调试eval代码
let result = fs.readFileSync(jsonUrl);
let data = JSON.parse(result.toString());
let Arr = [];
for (let i = 0, l = data.features.length; i < l; i++) {
let center = data.features[i].geometry.coordinates[0].reduce(
(acc, cur, index, array) => {
let lng = acc[0] + cur[0],
lat = acc[1] + cur[1];
if (index === array.length - 1) {
return [lng / array.length, lat / array.length];
}
return [lng, lat];
},
[0, 0]
);
let distance = caculateLL(center, initPoint);
if (distance < range) {
Arr.push(data.features[i]);
}
}
return Arr;
} catch (err) {
console.log(err);
}
}
/**
* @author: citrusrlia@foxmail.com
* @description: 计算两点间距离
* @param {p1,p2 : Arr}
* @return {number}
*/
function caculateLL(p1, p2) {
let lat1 = p1[0],
lng1 = p1[1],
lat2 = p2[0],
lng2 = p2[1];
var radLat1 = (lat1 * Math.PI) / 180.0;
var radLat2 = (lat2 * Math.PI) / 180.0;
var a = radLat1 - radLat2;
var b = (lng1 * Math.PI) / 180.0 - (lng2 * Math.PI) / 180.0;
var s =
2 *
Math.asin(
Math.sqrt(
Math.pow(Math.sin(a / 2), 2) +
Math.cos(radLat1) * Math.cos(radLat2) * Math.pow(Math.sin(b / 2), 2)
)
);
s = s * 6378.137;
s = Math.round(s * 10000) / 10;
return s;
}
function getAll(callback) {
// connection.connect();
let sql = "SELECT * FROM geometry";
connection.query(sql, function (err, result) {
if (err) {
console.log("GETALL ERROR - ", err.message);
return;
}
callback(result);
});
// connection.end();
}
module.exports = {
addItem,
deleteByName,
editById,
pagination,
getByClass,
getById,
getByName,
getAllFclass,
getByPosition,
getAll,
};
接着修改路由index.js，在请求对应地址时返回对应sql结果。node-mysql查询结果无法通过return返回，需要传入回调函数在回调中做res.send()，返回结果。views中页面使用pug编写，可以通过修改app.set("view engine","html")改成html语言或其他语言。使用require引入sqls.js时，项目未编译，如下设置不会使index.js中 ./ 路径映射到根目录下。
app.use(express.static(path.join(__dirname, "public")));
var express = require("express");
var router = express.Router();
var sqls = require("../public/javascripts/sqls");
var url = require("url")
/* 展示views中地址 */
router.get("/", function (req, res, next) {
res.render("index", { title: "Hello,简书的朋友!" });
});
router.get("/getByClass", function (req, res) {
/**
* @description: 根据类别查询对应条目
* @param {类别:fclass}
* @return {Array}
*/
let params = url.parse(req.url, true).query;
sqls.getByClass(params.fclass, (searchResult) => {
res.send(searchResult);
});
});
router.get("/getById", function (req, res) {
/**
* @author: citrusrlia@foxmail.com
* @description: 根据osm_id查找对应条目
* @param {String:osm_id}
* @return {Object}
*/
let params = url.parse(req.url, true).query;
sqls.getById(params.id, (searchResult) => {
res.send(searchResult);
});
});
router.get("/getByName", function (req, res) {
/**
* @author: citrusrlia@foxmail.com
* @description: 根据名称查找对应条目
* @param {String:name}
* @return {Arr}
*/
let params = url.parse(req.url, true).query;
sqls.getByName(params.name, (result) => {
res.send(result);
});
});
router.get("/pagination", function (req, res) {
/**
* @description: 分页查询对应条目
* @param {查询页标page,每页条目数perpage}
* @return {Array}
*/
let params = url.parse(req.url, true).query;
sqls.pagination(params.page, params.perpage, (searchResult) => {
res.send(searchResult);
});
});
router.post("/addItem", function (req, res) {
/**
* @description: 添加条目
* @param {addData:Object}
* @return {nothing}
*/
let params = url.parse(req.url, true).query;
sqls.addItem(params, (searchResult) => {
if (searchResult) res.send("添加成功");
});
});
router.post("/delectByName", function (req, res) {
/**
* @description: 删除条目
* @param {条目名称:name}
* @return {nothing}
*/
let params = url.parse(req.url, true).query;
sqls.delectByName(params.name, (searchResult) => {
if (searchResult) res.send("删除成功");
});
});
router.post("/editById", function (req, res) {
/**
* @description: 修改条目
* @param {params:Object,条目名称:searchId}
* @return {Array}
*/
let params = url.parse(req.url, true).query;
sqls.editById(params, params.searchId, (searchResult) => {
if (searchResult) res.send("修改成功");
});
});
router.get("/getAllFclass", function (req, res) {
/**
* @author: citrusrlia@foxmail.com
* @description:获取所有fclass
* @param {nothing}
* @return {Arr}
*/
let params = url.parse(req.url, true).query;
sqls.getAllFclass((searchResult) => {
res.send(searchResult);
});
});
router.get("/getByPosition", function (req, res) {
/**
* @author: citrusrlia@foxmail.com
* @description:获取点附近features
* @param {initPoint:[lng,lat] ,range:number}
* @return {Arr}
*/
let params = url.parse(req.url, true).query;
sqls.getByPosition(params.initPoint, params.range, (searchResult) => {
res.send(searchResult);
});
});
router.get("/getAll", function (req, res) {
/**
* @author: citrusrlia@foxmail.com
* @description: 获取所有features
* @param {}
* @return {Arr}
*/
let params = url.parse(req.url, true).query;
sqls.getAll((searchResult) => {
res.send(searchResult);
});
});
module.exports = router;
调用
image.png
访问对应端口，成功拿到数据库中数据。
总结
由于本人系gis前端工程师，第一次自己做后端服务，文中不足之处希望在看本文的朋友多多留言指正，万分感谢！
