建立mysql數(shù)據(jù)庫
登錄mysql后眨猎，創(chuàng)建一個(gè)新庫。我的庫名是geometry强经。
通過osm獲得的poi信息切出上海楊浦區(qū)部分輸出為geojson睡陪。
通過如下代碼將geojson數(shù)據(jù)存入
/*
* @Author: citrusrlia@foxmail.com
* @FilePath: \shy-node-back\writeJsonInsert.js
* @Date: 2021-05-24 11:21:32
* @Description: geojson數(shù)據(jù)入庫
*/
const mysql = require("mysql");
const fs = require("fs");
let result = fs.readFileSync("./statics/yangpu_poi.json"); //osm數(shù)據(jù)
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();
這里將經(jīng)緯度數(shù)據(jù)轉(zhuǎn)化為字符串存儲(chǔ)，部分長度會(huì)超過255字節(jié)限制匿情，字段類型需要改為長文本類型兰迫。
image.png
現(xiàn)在獲得了儲(chǔ)存了geojson數(shù)據(jù)的表。
使用express框架搭建服務(wù)
首先安裝express和express腳手架
npm i express
npm i express-generator
運(yùn)行 建立名叫app的項(xiàng)目 會(huì)得到如下圖的目錄結(jié)構(gòu)
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: 對(duì)數(shù)據(jù)庫的操作
*/
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 + "http://@ sourceURL=initpoint"); //便于調(diào)試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: 計(jì)算兩點(diǎn)間距離
* @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炬称，在請求對(duì)應(yīng)地址時(shí)返回對(duì)應(yīng)sql結(jié)果汁果。node-mysql查詢結(jié)果無法通過return返回，需要傳入回調(diào)函數(shù)在回調(diào)中做res.send()玲躯，返回結(jié)果据德。views中頁面使用pug編寫，可以通過修改app.set("view engine","html")改成html語言或其他語言跷车。使用require引入sqls.js時(shí)棘利，項(xiàng)目未編譯，如下設(shè)置不會(huì)使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: 根據(jù)類別查詢對(duì)應(yīng)條目
* @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: 根據(jù)osm_id查找對(duì)應(yīng)條目
* @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: 根據(jù)名稱查找對(duì)應(yīng)條目
* @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: 分頁查詢對(duì)應(yīng)條目
* @param {查詢頁標(biāo)page,每頁條目數(shù)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:獲取點(diǎn)附近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;
調(diào)用
image.png
訪問對(duì)應(yīng)端口善玫，成功拿到數(shù)據(jù)庫中數(shù)據(jù)。
總結(jié)
由于本人系gis前端工程師密强，第一次自己做后端服務(wù)茅郎，文中不足之處希望在看本文的朋友多多留言指正，萬分感謝或渤！
