Carto Super Camp

batch_api.md

#The Plan to fly Pennsylvania Airways

A brand new airline, Pennsylvania Airways, is celebrating its inaugural day of flights by giving away FREE tickets for 4 hours! But you have to buy your tickets at a Pennsylvania airport, they're so new there's no website yet. And right now you're all in New York City. How can we find out which airport we can drive to, and the route to take, before the sale ends?

1. Geocode CARTO HQ with Geocoding API
2. Make 2.5 hour isoline from CARTO HQ with Isolines API - got to get to the airport with time for security!
3. Find airports in Pennsylvania
4. Find those PA airports that intersect our isoline, and select the nearest one
5. Create a route between CARTO and the Airport with our Routing API
6. CartoDBfy our tables
7. Make a map to get us there!

And since time is of the essence, let's do it all in one API request!

DATASETS:

https://team.carto.com/u/athompson/tables/states_table/public

https://team.carto.com/u/athompson/tables/ne_10m_airports/public

Chained-query-post-noisoline.json

{
  "query": [
    "SELECT * INTO carto_geocode FROM (SELECT cdb_geocode_street_point('201 moore st, brooklyn, ny') as the_geom, 'CARTO HQ' as name) AS job",
    "SELECT * INTO carto_pa_airports FROM (SELECT a.*, b.name state, b.postal FROM states_table b JOIN athompson.ne_10m_airports a ON ST_Contains(b.the_geom, a.the_geom) WHERE postal = 'PA') AS job",
    "SELECT * INTO carto_nearest_airport FROM (SELECT pa.* FROM carto_pa_airports pa, carto_geocode hq ORDER BY hq.the_geom <-> pa.the_geom LIMIT 1) AS job",
    "SELECT * INTO carto_philly_route FROM (SELECT r.duration, r.length, r.shape as the_geom FROM carto_geocode g, carto_driveable_airports d, cdb_route_point_to_point(g.the_geom::geometry,d.the_geom::geometry, 'car') r) AS job",
    "SELECT cdb_cartodbfytable('athompson', 'carto_philly_route')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_nearest_airport')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_pa_airports')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_geocode')"
  ]
}

Chained-query-post.json

{
  "query": [
    "SELECT * INTO carto_geocode FROM (SELECT cdb_geocode_street_point('201 moore st, brooklyn, ny') as the_geom, 'CARTO HQ' as name) AS job",
    "SELECT * INTO carto_drivetime FROM (SELECT the_geom, st_transform(the_geom, 3857) as the_geom_webmercator FROM cdb_isochrone(cdb_geocode_street_point('201 moore st, brooklyn, ny')::geometry, 'car', ARRAY[9000]::integer[], ARRAY['mode_traffic=disabled','quality=3']::text[])) AS job",
    "SELECT * INTO carto_pa_airports FROM (SELECT a.*, b.name state, b.postal FROM states_table b JOIN athompson.ne_10m_airports a ON ST_Contains(b.the_geom, a.the_geom) WHERE postal = 'PA') AS job",
    "SELECT * INTO carto_driveable_airports FROM (SELECT pa.* FROM carto_drivetime drive JOIN carto_pa_airports pa ON ST_Contains(drive.the_geom, pa.the_geom)) AS job",
    "SELECT * INTO carto_philly_route FROM (SELECT r.duration, r.length, r.shape as the_geom FROM carto_geocode g, carto_driveable_airports d, cdb_route_point_to_point(g.the_geom::geometry,d.the_geom::geometry, 'car') r) AS job",
    "SELECT cdb_cartodbfytable('athompson', 'carto_philly_route')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_driveable_airports')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_pa_airports')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_drivetime')",
    "SELECT cdb_cartodbfytable('athompson', 'carto_geocode')"
  ]
}

To drop and try again:

http://yourusername.carto.com/api/v2/sql?api_key=keykeykeykeykey&q=DROP+TABLE+carto_geocode,+carto_drivetime,+carto_pa_airports,+carto_driveable_airports,+carto_philly_route