Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

RODARS pipeline #1103

Merged
merged 46 commits into from
Feb 14, 2025
Merged

RODARS pipeline #1103

merged 46 commits into from
Feb 14, 2025

Conversation

gabrielwol
Copy link
Collaborator

@gabrielwol gabrielwol commented Nov 29, 2024 •
edited
Loading

What this pull request accomplishes:

  • Construction event data (RODARs)!
  • rodars_pull.py daily DAG which runs on Morbius.
    • Pulls RODARs data from ITSC in two parts:
      • Issues: only the latest update (timestamputc) for each issueid.
      • Issue locations: corresponding location details for latest issues.
    • Data is processed using functions in rodars_functions.py, including: unnesting json data, converting binary coordinates to postgres readable format, data type conversion.
    • Finally, data is inserted into congestion_events.rodars_issues and congestion_events.rodars_issue_locations.
      • Notably older issue_locations are deleted using this on insert trigger. I used this method because I thought the number of locations per issue could theoretically change, resulting in orphaned rows, unaffected by an on conflict do update.
  • 5 new lookup tables taken from the documentation in itsc_factors schema: direction, lanesaffectedpattern, locationblocklevel, roadclosuretype_new, roadclosuretype_old. Used to convert codes to human readable format in issue_locations view.
    • Only the itsc_factors.lanesaffectedpattern lookup could use review: I devised the lane_open, lane_closed columns to give a numeric interpretation of how many lanes are open/closed. I used 0.5 for partial closures/slowdowns.
    • Also checkout the function itsc_factors.get_lanesaffected_sums which translates these codes into numeric columns for ease of use in the main view. A lanesaffectedpattern could be something like 'LOLCLCWO' (lane open, lane closed, lane closed, sidwalk open) (Try: SELECT lane_open_auto, lane_closed_auto, lane_open_bike, lane_closed_bike, lane_open_ped, lane_closed_ped, lane_open_bus, lane_closed_bus FROM itsc_factors.get_lanesaffected_sums('LOLCLCWO'))

Issue(s) this solves:

What, in particular, needs to reviewed:

  • Does this live in the right schema (congestion_events)? View/table names?
  • Decision to only keep latest issue definition (every update is logged in ITS Central).
  • Review the main view and the readme.

What needs to be done by a sysadmin after this PR is merged

  • Refresh data_scripts on Morbius.

@gabrielwol gabrielwol added the New Data for creating pipelines for new datasets label Nov 29, 2024
@gabrielwol gabrielwol self-assigned this Nov 29, 2024
@gabrielwol gabrielwol linked an issue Nov 29, 2024 that may be closed by this pull request
Explore New RODARS data #1088
Closed
@gabrielwol gabrielwol marked this pull request as ready for review January 10, 2025 21:54
@gabrielwol
Copy link
Collaborator Author

Ready to review! Will add some more usage examples to readme at some point.

@gabrielwol gabrielwol requested review from radumas and chmnata February 7, 2025 20:36
radumas
radumas requested changes Feb 7, 2025
View reviewed changes
Copy link
Member

@radumas radumas left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Commenting on the documentation.

Pleased that the docs are extensive, you have thought out the limitations of the data, done some exploratory analysis and the use of the callouts for extra punchy information.

I have some thoughts on re-ordering the README, but maybe we merge this and solicit feedback from users.

I have added some small editorial comments on language.

I think the big thing missing in terms of documentation is that parent folders should have a brief description of the contents of this folder so that people can know where to find it.

Also should this folder be more accurately called something like lane_obstructions? Or road_permits? Since the data are for events and construction (right?)

events/construction/readme.md Outdated Show resolved Hide resolved
events/construction/readme.md Outdated Show resolved Hide resolved
chmnata
chmnata approved these changes Feb 10, 2025
View reviewed changes
Copy link
Collaborator

@chmnata chmnata left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

just some v smol clean up request, :gabe-approved:

events/construction/sql/create-table-itsc_factors_lanesaffectedpattern.sql Outdated Show resolved Hide resolved
events/construction/sql/create-function-get_lanesaffected_sum.sql Outdated Show resolved Hide resolved
events/construction/sql/create-view-rodars_locations.sql Outdated
CASE iil.centreline_id WHEN 0 THEN NULL ELSE iil.centreline_id END AS centreline_id,
COALESCE(
centreline_latest.geom,
--find geoms for centreline that do not appear in centreline_latest
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

ohhhh nice

events/construction/sql/insert-rodars_issue_locations.sql Outdated Show resolved Hide resolved
@gabrielwol gabrielwol force-pushed the 1088-explore-new-rodars-data branch from 7f407b0 to 0e11ccf Compare February 11, 2025 17:21
@gabrielwol
#1088 explore new rodars data
fc69200
@gabrielwol
#1088 initial rodars schema
5d22d5a
@gabrielwol
#1088 add new divisions, fix null geometry issue, rename
9b23cca
@gabrielwol
#1088 RODARs DAG
3392d6e
@gabrielwol
#1088 bug fix: update each locationindex geom separately
09886de
@gabrielwol
#1088 readme updates
5bbfd3a
@gabrielwol
#1088 switch to congestion_events schema, separate issues & locations
444a208
@gabrielwol
#1088 update dag to 2 tasks
0ad9ef3
@gabrielwol
#1088 lookup tables
d7c3f90
@gabrielwol
#1088 fix issue insert
93edebe
@gabrielwol
#1088 lanes affected fixes
477712b
@gabrielwol
#1088 issues_locations -> issue_locations
33d2318
@gabrielwol
#1088 convert timestamps to eastern, data type fixes, misc
e82d5ea
@gabrielwol
#1088 vds_bot -> events_bot
7cc012d
@gabrielwol
#1088 add on insert delete trigger to locations
1a364bb
@gabrielwol
#1088 update permissions
15a84c0
@gabrielwol
#1088 add back date filters
f19268e
@gabrielwol
#1088 remove TM3 issues
0ff339f
@gabrielwol
#1088 itsc_locations updates
243f127
@gabrielwol
#1088 rename itsc->rodars
aa03126
@gabrielwol
#1088 rename itsc->rodars (harder)
212ef9a
@gabrielwol
#1088 smol readme update
be45817
@gabrielwol
#1088 sqlfluff
5fc4f5f
@gabrielwol
#1088 remove some dated exploration work
4bda1db
@gabrielwol
#1088 fix rodars_factors -> itsc_factors inconsistency
b91b205
@gabrielwol
#1088 rm outdated update sql
15bd9b4
@gabrielwol
#1088 readme, comment updates
9c8cf7d
@gabrielwol
#1088 format callouts properly?
04e15ab
@gabrielwol
#1088 format callouts properly? try 2
d6361b3
@gabrielwol
#1088 add lanesaffectedpattern codes+description
a1af45c
@gabrielwol
#1088 get centreline_geom even when not in _latest
e851353
@gabrielwol
#1088 readme updates; add example queries, data dictionary
9920cb4
@gabrielwol
#1088 remove extra >s
6d5d318
@gabrielwol
#1134 add data-sources tag to DAGs
cc9cd5b
@gabrielwol
#1088 fluff
f9c3e6f
@gabrielwol
#1088 Update rodars_pull.py
ee74b51
@gabrielwol
#1088 remove bdit_humans permissions from raw tables
471a236
@gabrielwol
#1088 minor sql+readme review updates
9608666
@gabrielwol
#1088 restructure events folder and add top level readme
9120a68
@gabrielwol
#1088 road_closures -> road_permits (not all are closed)
4f3718d
@gabrielwol
update higher level readmes, remove oudated road_closure folder #1088
f3a26ba
@gabrielwol
#1088 minor permissions updates, table comments
d58eab4
@gabrielwol gabrielwol force-pushed the 1088-explore-new-rodars-data branch from 0e11ccf to d58eab4 Compare February 11, 2025 17:26
@gabrielwol
Copy link
Collaborator Author

@radumas readme changes ready for re-review:

  • renamed folder to road_permits, moved existing contents of events into events/special_events and added events level readme.
  • updated reference in top level data-sources readme + removed old road_closure folder

radumas
radumas approved these changes Feb 13, 2025
View reviewed changes
Copy link
Member

@radumas radumas left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks great! Thank you!

@radumas
Copy link
Member

radumas commented Feb 13, 2025

Is there a bug in the linting action that it isn't annotating?

@gabrielwol
Copy link
Collaborator Author

Is there a bug in the linting action that it isn't annotating?

I also noticed previously on this PR there were some lines being erroneously annotated with line length sqlfluff error. Perhaps time to look into upgrading sqlfluff action.

@gabrielwol gabrielwol merged commit 6995363 into master Feb 14, 2025
5 of 6 checks passed
@gabrielwol gabrielwol deleted the 1088-explore-new-rodars-data branch February 14, 2025 14:25
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@radumas radumas radumas approved these changes

@chmnata chmnata chmnata approved these changes

Assignees

@gabrielwol gabrielwol

Labels
New Data for creating pipelines for new datasets
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

Explore New RODARS data
3 participants
@gabrielwol @radumas @chmnata