Report not found

There was a problem finding this report.
Created in the Mode Community
August 16, 2014 · Refreshed about 3 years ago
Bus Route Data - Shaped to Center of Route

Collaborators

Run History
SELECT sub.time_of_day, sub.route, sub.stop_id, sub.stop_name, sub.direction, sub.stop_seq - ROUND((MAX(sub.stop_seq) OVER (PARTITION BY route)/2)) AS seq, sub.avg_load, sub.avg_on, sub.avg_off FROM ( SELECT CASE WHEN timestop BETWEEN '7:30:00' AND '9:00:00' THEN 'Morning' WHEN timestop BETWEEN '17:00:00' AND '18:30:00' THEN 'Evening' ELSE NULL END AS time_of_day, route, stop_id, stop_name, stop_seq, CASE WHEN dir = 0 THEN 'Outbound' WHEN dir = 1 THEN 'Inbound' ELSE NULL END AS direction, AVG("on") AS avg_on, AVG(off) AS avg_off, AVG(load) AS avg_load FROM asadatathon.muni_passenger_count WHERE day IN (1,2,3,4,5) --limit to weekdays only AND (timestop BETWEEN '7:30:00' AND '9:00:00' --limit to commute hours OR timestop BETWEEN '17:00:00' AND '18:30:00') GROUP BY 1,2,3,4,5,6 ) sub /* This section joins demographic data, collected using this demo crawl script: https://github.com/lionelc/asadatathon-sfbusdata/tree/master/src Because of the response time of datascience toolkit (coordinates to statistics), we rounded latitudes and longitudes so that we would have to look up fewer points.*/ LEFT JOIN ( SELECT sub.*, lat_lon.latitude, lat_lon.longitude, RANK() OVER (PARTITION BY sub.stop_id ORDER BY us_population DESC ) AS stop_rank FROM (SELECT DISTINCT stop_id, demographics.* FROM asadatathon.muni_passenger_count muni -- LEFT JOIN asadatathon.lat_lon_demographics demographics ON ROUND(muni.latitude*100)/100 = demographics.lat AND ROUND(muni.longitude*100)/100 = demographics.long ) sub LEFT JOIN (SELECT stop_id, --join full lat/lon back onto data max(latitude) AS latitude, max(longitude) AS longitude FROM asadatathon.muni_passenger_count muni GROUP BY 1 ) lat_lon ON sub.stop_id = lat_lon.stop_id ) demographics ON demographics.stop_id = sub.stop_id AND demographics.stop_rank = 1
SELECT sub.time_of_day, sub.route, sub.stop_id, sub.stop_name, sub.direction, sub.stop_seq - ROUND((MAX(sub.stop_seq) OVER (PARTITION BY route)/2)) AS seq, sub.avg_load, sub.avg_on, sub.avg_off FROM ( SELECT CASE WHEN timestop BETWEEN '7:30:00' AND '9:00:00' THEN 'Morning' WHEN timestop BETWEEN '17:00:00' AND '18:30:00' THEN 'Evening' ELSE NULL END AS time_of_day, route, stop_id, stop_name, stop_seq, CASE WHEN dir = 0 THEN 'Outbound' WHEN dir = 1 THEN 'Inbound' ELSE NULL END AS direction, AVG("on") AS avg_on, AVG(off) AS avg_off, AVG(load) AS avg_load FROM asadatathon.muni_passenger_count WHERE day IN (1,2,3,4,5) --limit to weekdays only AND (timestop BETWEEN '7:30:00' AND '9:00:00' --limit to commute hours OR timestop BETWEEN '17:00:00' AND '18:30:00') GROUP BY 1,2,3,4,5,6 ) sub /* This section joins demographic data, collected using this demo crawl script: https://github.com/lionelc/asadatathon-sfbusdata/tree/master/src Because of the response time of datascience toolkit (coordinates to statistics), we rounded latitudes and longitudes so that we would have to look up fewer points.*/ LEFT JOIN ( SELECT sub.*, lat_lon.latitude, lat_lon.longitude, RANK() OVER (PARTITION BY sub.stop_id ORDER BY us_population DESC ) AS stop_rank FROM (SELECT DISTINCT stop_id, demographics.* FROM asadatathon.muni_passenger_count muni -- LEFT JOIN asadatathon.lat_lon_demographics demographics ON ROUND(muni.latitude*100)/100 = demographics.lat AND ROUND(muni.longitude*100)/100 = demographics.long ) sub LEFT JOIN (SELECT stop_id, --join full lat/lon back onto data max(latitude) AS latitude, max(longitude) AS longitude FROM asadatathon.muni_passenger_count muni GROUP BY 1 ) lat_lon ON sub.stop_id = lat_lon.stop_id ) demographics ON demographics.stop_id = sub.stop_id AND demographics.stop_rank = 1
<div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="container mode-grid"> <div class="row"> <div class="col-md-12"> <mode-table id="table_1d6871ec5e49" dataset="dataset" options="table_options"></mode-table> </div> </div> </div>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query. Get help here.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled