search

cagov / caldata-mdsa-caltrans-pems

CalData's MDSA project with Caltrans on Performance Measurement System (PeMS) data
https://cagov.github.io/caldata-mdsa-caltrans-pems/
MIT License
7 stars 0 forks source link

Identify data loading method for Caltrans' Lane Closure System #70

Open ian-r-rose opened 8 months ago

ian-r-rose commented 8 months ago

One of the primary data sources for interpreting PeMS VDS data is Caltrans' Lane Closure System. It's not clear what the best way to incorporate this data into our pipeline is just yet. There is data available in the web portal, but there are also internal users of the system which connect directly to an on-prem database.

We create a plan for how to load this data.

ian-r-rose commented 7 months ago

@kengodleskidot and @ZhenyuZhu-Caltrans would you mind updating this issue with what you learned about the LCS pipeline and how it will be loaded to the Snowflake warehouse?

kengodleskidot commented 7 months ago

@ian-r-rose We recently obtained the database connection details and user credentials to access the Source System of Record (SSOR) for LCS data. We will be reviewing the tables to see which data points come directly from the SSOR into the PeMS tables and which ones are aggregations of LCS and/or other data sets that are specific to PeMS.

ian-r-rose commented 7 months ago

Thanks for the update!

kengodleskidot commented 6 months ago

@ian-r-rose There are 10 table in PeMS that have LCS related data. I have included a document with additional details about those database tables. Samples seem to be received every 5 minutes based on the PeMS Real Time Data collection table. Let me know if you need anything else. PeMS-LCSTableSizeInfo_04192024.xlsx

junlee-analytica commented 5 months ago

@pingpingxiu-DOT-ca-gov will link an example data schema.