Provide Micromobility Data for PARD Analysis

[johnclary]

Via Parking Enterprise—Parks & Rec is asking for micromobility data for analysis relating to parkland usage.

Spoke with Amanda (PARD) and she agreed the following trip dataset would meet their needs:

Where each record represents a vehicle trip origin or destination, and records include only trips on the Butler trail or in/adjacent to city park land.

Columns:

• Type (Origin or Destination)
• Latitude
• Longitude
• Distance Traveled
• Day of Week
• Hour of Day
• Device Type (Bike or Scooter)

[johnclary]

@alan-deanda I'll let you know when I have the data ready for you.

[johnclary]

this should work. downloading now:

-- origins
SELECT dockless.vehicle_type,
    round(dockless.trip_duration, -1) as trip_duration,
    date_part('hour'::text, timezone('US/Central'::text, round_time(dockless.start_time))) AS hour,
    date_part('dow'::text, timezone('US/Central'::text, round_time(dockless.start_time))) AS day_of_week,
    dockless.end_latitude,
    dockless.end_longitude,
    dockless.trip_distance
   FROM api.dockless where dockless.start_time > '2018-12-31' and dockless.end_time < '2019-08-31';

-- destinations
SELECT dockless.vehicle_type,
    round(dockless.trip_duration, -1) as trip_duration,
    date_part('hour'::text, timezone('US/Central'::text, round_time(dockless.end_time))) AS hour,
    date_part('dow'::text, timezone('US/Central'::text, round_time(dockless.end_time))) AS day_of_week,
    dockless.end_latitude,
    dockless.end_longitude,
    dockless.trip_distance
   FROM api.dockless where dockless.start_time > '2018-12-31' and dockless.end_time < '2019-08-31';

[johnclary]

@alan-deanda the data is here: https://drive.google.com/drive/u/1/folders/1uJxnfKtyB-SgIwZlH4B0gv6Kh6C4DHiI

Using each of these datasets, would you create a point dataset that contains only points that started or ended on COA parkland, or the Butler trail? You'll need to add a column for if the record is an origin or a destination; I forgot to do that. Thanks!

[alan-deanda]

@johnclary sounds good, thanks!

[johnclary]

@alan-deanda your contact at PARD is amanda.ross@austintexas.gov.

[alan-deanda]

@johnclary the origins table seems to be the same as the destination table. I test-geocoded 1000 trips from each and they all coincide. From looking at your code above, I think I may have spotted the error:

 ...
  dockless.end_latitude,
   dockless.end_longitude,
...

Would changing it to start_long/lat fix it?

-- origins
SELECT dockless.vehicle_type,
    round(dockless.trip_duration, -1) as trip_duration,
    date_part('hour'::text, timezone('US/Central'::text, round_time(dockless.start_time))) AS hour,
    date_part('dow'::text, timezone('US/Central'::text, round_time(dockless.start_time))) AS day_of_week,
    dockless.start_latitude,
    dockless.start_longitude,
    dockless.trip_distance
   FROM api.dockless where dockless.start_time > '2018-12-31' and dockless.end_time < '2019-08-31';

Thanks!

[johnclary]

@alan-deanda good catch! yep, will re-run the query

[alan-deanda]

@johnclary I'm moving this project to Review/QA until or if PARD has any further requests.