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've the following two public datasets ready to be consumed:

• test_airquality_stations. It has the info of the airquality stations and its location.

• 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 an 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 either NodeJS, or Python or Ruby.

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.

We provide you a 1km grid dataset where you can get the population of Spain in squares of 1km x 1km. To see how this dataset looks like check this map

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).

Filters

At this point, let's try to build two filters for all the previous endpoints:

• stations: to return only the data for the requested stations.

• geom: to return only the data of the stations which intersects with the provided geometry. For example, if you provide the following polygon it should only returns 3 stations:

{"type":"Polygon","coordinates":[[[-3.63289587199688,40.56439731247202],[-3.661734983325005,40.55618117044514],[-3.66310827434063,40.53583209794804],[-3.6378740519285206,40.52421992151271],[-3.6148714274168015,40.5239589506112],[-3.60543005168438,40.547181381686634],[-3.63289587199688,40.56439731247202]]]}

Authentication

The API is not public anymore. How would you implement a mechanism to restrict and identify API's users?

The idea behind this is to control the usage per user to bill them based on the usage.

We don't need you to write code here, we just want here a written explanaition about how you'll address this feature requirement.

Bonus: deploy (not required)

Find a way to deploy your code.

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 and PostgreSQL.
• You're used to work with tests.
• The work is well documented and easy to follow but an external person.

spatial_filter.png