Created
April 2, 2018 20:53
-
-
Save ivanmalagon/492fc7a92e54118df77f7a469cd277bb to your computer and use it in GitHub Desktop.
BICIMAD AND SQL API
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const axios = require('axios'); | |
| const CLIENT_ID = 'YOUR_BICIMAD_CLIENT_ID'; | |
| const PASSKEY = 'YOUR_BICIMAD_PASSKEY'; | |
| const USERNAME = 'YOUR_USERNAME'; | |
| const TABLE_NAME = 'YOUR_TABLE_NAME'; | |
| const API_KEY = 'YOUR_CARTO_API_KEY'; | |
| const BICIMAD_URL = `https://rbdata.emtmadrid.es:8443/BiciMad/get_stations/${CLIENT_ID}/${PASSKEY}/`; | |
| const BATCH_QUERY_URL = `https://${USERNAME}.carto.com/api/v2/sql/job?api_key=${API_KEY}`; | |
| const MAX_JOB_SIZE = 16000; | |
| const types = { | |
| NUMBER: 'number', | |
| STRING: 'string' | |
| } | |
| const recordFields = { | |
| id: types.NUMBER, | |
| name: types.STRING, | |
| light: types.NUMBER, | |
| number: types.STRING, | |
| address: types.STRING, | |
| activate: types.NUMBER, | |
| no_available: types.NUMBER, | |
| total_bases: types.NUMBER, | |
| dock_bikes: types.NUMBER, | |
| free_bases: types.NUMBER, | |
| reservations_count: types.NUMBER | |
| } | |
| function getTimestamp () { | |
| const now = new Date(); | |
| return `${now.getUTCFullYear()}-${now.getUTCMonth()+1}-${now.getUTCDate()}T${now.getUTCHours()}:${now.getUTCMinutes()}:${now.getUTCSeconds()}Z`; | |
| } | |
| function createGeometry (latitude, longitude) { | |
| return `ST_SetSRID(ST_MakePoint(${longitude}, ${latitude}),4326)`; | |
| } | |
| function renderString (value) { | |
| return `'${value.replace("'", "''")}'`; | |
| } | |
| function getRecordKeys (recordKeys, skip) { | |
| skip = skip || []; | |
| return recordKeys.filter(key => skip.indexOf(key) === -1); | |
| } | |
| function getRecordValues (record, recordFields, skip) { | |
| skip = skip || []; | |
| return Object.keys(recordFields) | |
| .filter(recordKey => skip.indexOf(recordKey) === -1) | |
| .map(recordKey => { | |
| return recordFields[recordKey] === types.NUMBER | |
| ? record[recordKey] | |
| : renderString(record[recordKey]); | |
| }) | |
| } | |
| function buildValuesStatement (record, timestamp) { | |
| const geom = createGeometry(record.latitude, record.longitude); | |
| const values = getRecordValues(record, recordFields).join(', '); | |
| return `(${values}, ${geom}, '${timestamp}')`; | |
| } | |
| function buildInsertQuery (username, table, timestamp) { | |
| const keys = getRecordKeys(Object.keys(recordFields)).join(', '); | |
| return `INSERT INTO ${username}.${table} (${keys}, the_geom, "timestamp") VALUES`; | |
| } | |
| function sendJob (job, url) { | |
| axios({ | |
| method: 'post', | |
| url, | |
| data: { | |
| query: job | |
| } | |
| }) | |
| .then(response => { | |
| console.log(`${response.status}: ${response.data.job_id}`); | |
| }) | |
| .catch(error => { | |
| console.error(error); | |
| }); | |
| } | |
| function buildInsertJobs (username, table, url, stations) { | |
| const now = getTimestamp(); | |
| const jobs = []; | |
| const insertQuery = buildInsertQuery(username, table, now); | |
| let currentJobQueries, currentJobLength, currentValues; | |
| function resetCurrentJob () { | |
| currentJobQueries = []; | |
| currentJobLength = insertQuery.length; | |
| currentValues = []; | |
| } | |
| function createJob () { | |
| currentJobQueries.push(`${insertQuery} ${currentValues.join(', ')};`); | |
| jobs.push(currentJobQueries); | |
| } | |
| resetCurrentJob(); | |
| stations.forEach(station => { | |
| const query = buildValuesStatement(station, now); | |
| if (currentJobLength + query.length <= MAX_JOB_SIZE) { | |
| currentValues.push(query); | |
| currentJobLength += query.length; | |
| } else { | |
| createJob(); | |
| resetCurrentJob(); | |
| currentValues.push(query); | |
| currentJobLength += query.length; | |
| } | |
| }); | |
| if (currentValues.length > 0) { | |
| createJob(); | |
| } | |
| jobs.forEach(job => sendJob(job, url)); | |
| }; | |
| exports.syncBicimad = (req, res) => { | |
| axios | |
| .get(BICIMAD_URL) | |
| .then(response => { | |
| const query = buildInsertJobs(USERNAME, TABLE_NAME, BATCH_QUERY_URL, JSON.parse(response.data.data).stations); | |
| res.status(200).end(); | |
| }) | |
| .catch(error => { | |
| console.error(error); | |
| res.status(500).end(); | |
| }); | |
| }; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment