Available passenger-census endpoint

[jaronheard]

Informed by discussion with @znmeb - I think that there should be an endpoint that returns what passenger-census quarterly ridership summaries we have.

Flexible on return format, but should at least return the dates of all available quarterly ridership data in passenger-census.

[znmeb]

Sounds like an SQL "SELECT DISTINCT"

[bhgrant8]

@jaronheard new endpoint is up:

http://service.civicpdx.org/transportation-systems/passenger-census/passenger-census-info/

SELECT DISTINCT "passenger_census"."summary_begin_date", 
    COUNT(DISTINCT "passenger_census"."route_number") AS "total_routes", 
    COUNT(DISTINCT "passenger_census"."stop_seq") AS "total_stops",
    EXTRACT('year' FROM "passenger_census"."summary_begin_date") AS "year" 
FROM "passenger_census" 
GROUP BY "passenger_census"."summary_begin_date", EXTRACT('year' FROM "passenger_census"."summary_begin_date") 
ORDER BY "passenger_census"."summary_begin_date" ASC; 

Let me know if this is what you were thinking

[bhgrant8]

Updated to now include data rollups, as per slack conversation w/ @jaronheard documenting the sql queries for aggregations behind django ORM methods.

This endpoint makes three queries:

1. Weekday ridership totals, and total routes:

SELECT DISTINCT "passenger_census"."summary_begin_date", 
  SUM("passenger_census"."ons") AS "weekday_sum_ons", 
  SUM("passenger_census"."offs") AS "weekday_sum_offs", 
  COUNT(DISTINCT "passenger_census"."route_number") AS "weekday_total_routes" 
FROM "passenger_census" 
WHERE UPPER("passenger_census"."service_key"::text) LIKE UPPER('%W%') 
GROUP BY "passenger_census"."summary_begin_date" 
ORDER BY "passenger_census"."summary_begin_date" ASC; 

args=('%W%',)

2. Saturday ridership numbers

SELECT DISTINCT "passenger_census"."summary_begin_date", 
  SUM("passenger_census"."ons") AS "saturday_sum_ons", 
  SUM("passenger_census"."offs") AS "saturday_sum_offs", 
  COUNT(DISTINCT "passenger_census"."route_number") AS "saturday_total_routes" 
FROM "passenger_census" 
WHERE UPPER("passenger_census"."service_key"::text) LIKE UPPER('%S%') 
GROUP BY "passenger_census"."summary_begin_date" 
ORDER BY "passenger_census"."summary_begin_date" ASC; 

args=('%S%',)

3. Sunday ridership numbers

SELECT DISTINCT "passenger_census"."summary_begin_date", 
  SUM("passenger_census"."ons") AS "sunday_sum_ons", 
  SUM("passenger_census"."offs") AS "sunday_sum_offs", 
  COUNT(DISTINCT "passenger_census"."route_number") AS "sunday_total_routes" 
FROM "passenger_census" WHERE UPPER("passenger_census"."service_key"::text) LIKE UPPER('%U%') 
GROUP BY "passenger_census"."summary_begin_date" 
ORDER BY "passenger_census"."summary_begin_date" ASC; 

args=('%U%',)

I am then using Python for zipping these into a single dict:

sorting_key = operator.itemgetter("summary_begin_date")
for i, j in zip(sorted(weekly, key=sorting_key), sorted(saturday, key=sorting_key)):i.update(j)
for i, j in zip(sorted(weekly, key=sorting_key), sorted(sunday, key=sorting_key)):i.update(j)

then calculate total sums for census by adding the weekday, saturday, sunday numbers:

ons: weekday['ons'] + saturday['ons'] + sunday['ons']
offs: weekday['offs'] + saturday['offs'] + sunday['offs']