node + express 搭建后端服務(wù),連接 mysql 數(shù)據(jù)庫,存入 geojson 數(shù)據(jù)

建立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，在請(qǐng)求對(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ù)，文中不足之處希望在看本文的朋友多多留言指正，萬分感謝！

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容