JSON API copy to DB

[ddylanlinn]

controller

const trailsModel = require('../models/trails')
const axios = require('axios')
const proj4 = require('proj4')

proj4.defs([  // 定義需要的座標資源
  [
    'EPSG:4326',
    '+title=WGS 84 (long/lat) +proj=longlat +ellps=WGS84 +datum=WGS84 +units=degrees'],
  [
    'EPSG:3826',
    '+title=TWD97 TM2+proj=tmerc +lat_0=0 +lon_0=121 +k=0.9999 +x_0=250000 +y_0=0 +ellps=GRS80 +units=公尺 +no_defs'
  ]
 ]);
 
 var EPSG3826 = new proj4.Proj('EPSG:3826');//TWD97 121分帶
 var EPSG4326 = new proj4.Proj('EPSG:4326');//WGS84
 
 
 function transCoordinates(x, y) {
   var result;
   if (proj4) {
       var p = { x: parseFloat(x), y:parseFloat(y) };
       result = proj4(EPSG3826, EPSG4326, p);
   }
   return result;
 }


const pic = [
'https://recreation.forest.gov.tw/Files/RT/Photo/001/05/001.jpg',
'https://tluxe-aws.hmgcdn.com/public/article/2017/atl_20180628130517_581.jpg',
'https://cdn.walkerland.com.tw/images/upload/poi/p61844/m80395/ccb0bf36c3ba490ccbfef352bb697d21712dcf58.jpg',
// ....
] 


const routeMap = [
'https://recreation.forest.gov.tw/Files/RT/Photo/001/01/001_MAP201902152.jpg',
'https://recreation.forest.gov.tw/Files/RT/Photo/002/01/002_MAP.jpg',
'https://recreation.forest.gov.tw/Files/RT/Photo/003/01/003_MAP.jpg',
// .....
]


const trailsController = {


  copy: (req, res) => {
    axios.get('https://recreation.forest.gov.tw/mis/api/BasicInfo/Trail')
      .then(response=>{
        for (let i = 0; i < 126 ; i++) {

          const data = response.data[i]
          let WGS84 = {x: 0, y: 0}
          if (data.TR_ENTRANCE[1] ) {
            WGS84 = transCoordinates(data.TR_ENTRANCE[1].x, data.TR_ENTRANCE[1].y)
          } else {
            WGS84 = transCoordinates(data.TR_ENTRANCE[0].x, data.TR_ENTRANCE[0].y)
          }
          

          let trailInfo = {
            author_id: 1,
            title: data.TR_CNAME,
            description: data.GUIDE_CONTENT,
            location: data.TR_POSITION,
            altitude: data.TR_ALT,
            length: data.TR_LENGTH_NUM,
            situation: data.TR_PAVE,
            season: data.TR_BEST_SEASON,
            difficulty: data.TR_DIF_CLASS,
            coordinate: {
              x: WGS84.x,
              y: WGS84.y
            },
            cover_picture_url: pic[i],
            map_picture_url: routeMap[i],
            required_time: data.TR_TOUR
          }
          console.log(trailInfo)
          trailsModel.add(trailInfo, (err, results) => {})
        }
        res.end('success')
      })
      .catch((err) => {
        console.log(err)
      })
  }
}

module.exports = trailsController

model

const db = require('../db')

const trailsModel = {
  add: (trailInfo, cb) => {
    db.query(`INSERT INTO trails
    (author_id, title, description, location, altitude, length, situation, season, difficulty, coordinate, cover_picture_url, map_picture_url, required_time) 
    VALUE (?, ?, ?, ?, ?, ?, ?, ?, ?, ST_PointFromText("POINT(? ?)"), ?, ?, ? )`, 
    [trailInfo.author_id, trailInfo.title, trailInfo.description, trailInfo.location, 
      trailInfo.altitude, trailInfo.length, trailInfo.situation ,trailInfo.season ,trailInfo.difficulty, 
      trailInfo.coordinate.x, trailInfo.coordinate.y, trailInfo.cover_picture_url, trailInfo.map_picture_url, trailInfo.required_time], 
    (err, results) => {
      if (err) return cb(err)
      cb(null)
    })
  },


}


module.exports = trailsModel