README.md

Create an API using CARTO as a backend

Imagine you're going to build a backend to analyze the air quality of a region in Madrid.

You have the following two public datasets in BigQuery ready to be consumed:

• cartodb-gcp-backend-data-team.code_test.airquality_stations. It has the info of the airquality stations and its location.

• cartodb-gcp-backend-data-team.code_test.airquality_measurements. Measurements of each_station:

  • timeinstant: time of the observation.
  • station_id: station identifier.
  • so2: μg/m3 de SO2 (sulfur dioxide). Threshold 180 μg/m3.
  • no2: μg/m3 de NO2 (nitrogen dioxide). Threshold 200 μg/m3.
  • co: mg/m3 de CO (carbon monoxide). Threshold 3 mg/m3.
  • o3: μg/m3 de O3 (ozone). Threshold 200 μg/m3.
  • pm10: μg/m3 de PM10 (particles smaller than 10 μm) Threshold 50 μg/m3.
  • pm2_5 : μg/m3 de PM2,5 (particles smaller than 2,5 μm) Threshold 40 μg/m3.

You need to create a public API on top of CARTO's SQL API.

You need to document and implement the following endpoints and send back to us through a GitHub repo. The language should be NodeJS.

Go to https://app.carto.com and create a trial account. CARTO automatically provides a CARTO Data Warehouse (that's a BigQuery under the hood) for each trial account.

Here you can see a request example to access the stations table using CARTO SQL API.

curl -G --request GET 'https://gcp-us-east1.api.carto.com/v3/sql/carto_dw/query' --data-urlencode 'q=select * from cartodb-gcp-backend-data-team.code_test.airquality_stations' \
--header 'Authorization: Bearer XXX'

Replace XXX with your own token, to understand how to get a token go to API DOC.

Statistical measurement for stations

It returns the requested statistical measurement for a given variable for each station.

Parameters:

• Time range to filter by.
• Variable (so2, no2, co, ...).
• Statistical measurement (avg, max, min, ...)

Spatial join

You need to modify the previous endpoint to include the population affected per each station.

CARTO provides a catalog of 10K datasets, this dataset could be useful to achieve this feature.

The population affected is just the grid where the station belongs to, so if the stations is inside of a grid where 100 people live, the affected population by this station is 100.

Timeserie for stations

Timeseries time! Consider that somebody want to know how the air quality is evolving over the time. We need to create a new endpoint to return a timeserie per each station.

Parameters:

• Time range to filter by.
• Variable (so2, no2, co, ...).
• Statistical measurement (avg, max, min, ...).
• Step (1 week, 1 day, 1 hour).

Bonus

The following points are completely optionals, if you are very busy no need to work on them, but if you have time you will probably enjoy with them.

Builder

You can use Builder to create a map with the data provided for this test.

Test

Create a test suite to test your API.

Tips

You need to have in mind when you work on a solution:

• The code is clean, easy to understand and easy to deploy.
• The simpler the better.
• You're familiar with SQL.
• Tests are optionals, but if you've time
• The work is well documented and easy to follow but an external person.