Alberto Asuero alasarr

Choosing what to do during your next vacation

It’s summertime and we have decided we are going to Madrid for our vacation! However, we don’t know what to do when we get there. Thankfully, we’ve got a list of activities, so all that’s left is to write a program to help us decide where to go.

The goal of this project is to write a web API to help select activities to do in Madrid. We’re ambitious, and we’re thinking of expanding it to include data about multiple cities in the future, and adding more complicated planning, e.g: taking into account the transportation between different activities. In this exercise we’ll focus in the first steps towards that goal, but think of it as the first milestone in a bigger project.

We estimate this test should take around a day of work. Don’t worry if you finish faster, or if it takes you a little longer, as it can vary a lot depending on how you approach the project.

Keep in mind the following:

Please include a document with your code explaining your thought process

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.

zoom_level	tolerance (m)	tolerance (deg)

      0 |  78271.516953125 | 0.703124999902124
      1 | 39135.7584765625 | 0.351562499951062
      2 | 19567.8792382812 | 0.175781249975531
      3 | 9783.93961914062 | 0.0878906249877655
      4 | 4891.96980957031 | 0.0439453124938827
      5 | 2445.98490478516 | 0.0219726562469414
      6 | 1222.99245239258 | 0.0109863281234707

7 | 611.496226196289 | 0.00549316406173534

##

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies` cluster by geom as 
select * from carto-demo-data.demo_tables.osm_pois_usa where subgroup_name='Pharmacy'

create or replace table `cartodb-on-gcp-backend-team.deckgl_summit.pharmacies_aoi` cluster by geom as 
select * except (geom),st_buffer(geom, 2000) as geom

	<!DOCTYPE html>
	<html>

	<head>
	<title> Filter data on map \| CARTO</title>
	<meta name="viewport" content="initial-scale=1.0">
	<meta charset="utf-8">
	<link href="https://fonts.googleapis.com/css?family=Montserrat:400,600,700\|Open+Sans:300,400,600" rel="stylesheet">
	<!-- Include Leaflet -->
	<script src="https://unpkg.com/[email protected]/dist/leaflet.js"></script>

	{
	"tilejson":"2.2.0",
	"tiles":["https://cartocdn-gusc-a.global.ssl.fastly.net/cartovl/api/v1/map/be2b5a9267b5c73a91ba17d11728d756:1560342716956/{z}/{x}/{y}.mvt?api_key=default_public"],
	"minzoom": 3,
	"maxzoom": 7,
	"tilestats":{"layerCount":1,"layers":[{"layer":"429bbc773dcf729a70eaa2897637acd1","count":25648,"adapters":{"aggregation":false,"dates_as_numbers":false}}]}}

	-- Switzerland
	create table carto-dev-data.public.derived_spatialfeatures_che_h3res10_v1_yearly_v2_interpolated cluster by h3
	as
	with q as (
	select `carto-un`.carto.H3_TOCHILDREN(h3, 10) as h3_children,* except(h3)
	from `carto-dev-data.public.derived_spatialfeatures_che_h3res8_v1_yearly_v2`
	)
	select h3, population/pow(7,2) as population, female/pow(7,2) as female, male/pow(7,2) as male
	from q, unnest(q.h3_children) as h3;

	{
	"version": 8,
	"name": "Dark Matter",
	"metadata": {
	"maputnik:renderer": "mbgljs"
	},
	"sources": {
	"carto": {
	"type": "vector",
	"url": "https://tiles.basemaps.cartocdn.com/vector/carto.streets/v1/tiles.json"

	----
	-- BIGQUERY
	---

	-- Switzerland
	create table carto-dev-data.public.derived_spatialfeatures_che_h3int_res8_v1_yearly_v2
	cluster by h3
	as
	select cast(concat('0x', h3) as int64) as h3, * except (h3)
	from carto-dev-data.public.derived_spatialfeatures_che_h3res8_v1_yearly_v2;