Making visible the invisible

the L Train closure

Jorge Sanz · jorge@cartodb.com · @xurxosanz

Eurostat 2016 Visualization Workshop

Agenda

Problem definition
Current status
Proposed solution
Technology

About us

CartoDB: location intelligence SaaS
Since 2011
~100 employees
Offices in Madrid, NYC and Tartu
~200K users

Problem definition

After Sandy hurricane, the NY Canarsie tunnel was affected
Authorities (MTA) are planning to shut down the L train line
This situation can last years
How this line shutdown will affect to New Yorkers?

Overview the L Train

Major connection between Manhattan an Brooklyn
~27.000 passenger per workday (2014 data)
With its modern signaling, L line can have more trains

Current status

Data available

Demographics from American Community Survey (ACS)
Home and work dynamics from LODES
L entrances

Analysis

Objective: study the relation between people from Brooklyn commuting to Manhattan and the L line.

Census blocks as base geographic information
Relation with L entrances using walking route and travel time

Walking paths

Map of number of Brooklyn walkers using L train within a 30 minutes range

SE and NW areas of Brooklyn are not covered by any other subway. We limited to 30 minutes ride and Census data doesn't reflect commuters taking a bus to arrive to the subway entrance.

Commuting time

~500K people from Brooklyn work at Manhattan
~200K L Train commuters per day
~114K use the L train to work at Manhattan

People try to live close to their jobs

Scenario

closing the Canarsie tunnel

Affected population

L is the closest line for ~19K low income households

Affected population

Most affected areas would be Brownsville and East New York
More wealthy population can look forother transport methods (uber, lyft)
MTA suggested bus shuttles

Proposed solution

Bus shuttles to substitute the Canarsie tunnel

Williamsburg to Lower East Side
~20 minutes ride
Is it worth?

Bus shuttle: best choice?

Shuttle is the best choice for ~75.000 riders per day

1154 bus rides (65 pax/bus)
One bus every 90 seconds
This almost means NYC needs a new bridge!!

Increasing bus time

Shuttle are only interesting if they take less than 29 minutes
Then all commuters would better use M line

Technology

CartoDB as an analysis platform

Postgres + PostGIS
Mapzen Valhalla routing exposed as SQL functions
Custom python functions embedded

CartoDB Data Services API

Expose third party services through SQL:

Geocoding: Mapzen, HERE, Google
Routing: Mapzen
Trade Areas: HERE

Example: Geocoding + Routing

SELECT
  st_transform(shape, 3857) as the_geom_webmercator
FROM
cdb_route_point_to_point(
  cdb_geocode_street_point('Calle Moreno Nieto, 2, 28005 Madrid'),
  cdb_geocode_street_point('fernandez de la hoz 35, madrid'),
  'car',
  ARRAY['mode_type=shortest']::text[]
)

Technology: geocoding + routing

Looking forward

A beter UI
Reference data
More analysis functions, beyond PostGIS
Better LDS

For everyone, everything at https://github.com/CartoDB/

Thanks!