Caltrans GIS Data - Load datasets

[kengodleskidot]

This post is informational about the GIS related files available and managed by Caltrans which can be found at the following link: https://gisdata-caltrans.opendata.arcgis.com/search?tags=Highway

As we look to incorporate GIS data into PeMS, there are two datasets that are of particular interest under the Highway Datasets section:

• All Roads (https://gisdata-caltrans.opendata.arcgis.com/maps/2d56e65de89c418780056651640291e8): The Caltrans All Roads Linear Referencing Services (LRS) dataset provides the base geometry for federally required Highway Performance Monitoring System (HPMS) business data, functionally classified roads for the California Roads System (CRS) (a requirement for federal funding of local agency projects), and the State Highway Network (SHN), which supports a wide range of internal Caltrans business needs.
• SHN Postmiles Tenth (https://gisdata-caltrans.opendata.arcgis.com/maps/c22341fec9c74c6b9488ee4da23dd967): Postmiles are valid postmile points at 0.1 (1/10th) mile intervals, based on the Caltrans Linear Referencing System. Exceptions that are included are the beginning and ends of routes (and other situations) that don't necessarily begin or end on the even 0.1 mile interval. This geometry is based upon the extraction from TSN on 10 October 2022.

Other GIS data sets include district, county, legislative boundaries as well as a bottleneck data set. As other GIS data is encountered this issue should be updated accordingly as needed.

[ian-r-rose]

We have experience loading geospatial data from ArcGIS Online into Snowflake (@britt-allen should be able to see the link, but others won't as it's a private repo). This would be relatively straightforward to adapt to the use-case here. There are a couple of options for how to operationalize such a data loading script:

• Run it on an ad-hoc basis
• Schedule the script to be run using Airflow
• Schedule the script to be run using GitHub Actions
• Adapt the script to a Snowpark UDF and schedule it as a Task

The last one might be an interesting pattern to explore, as it would require less orchestrating infrastructure (although github actions is also fairly infrastructure-light)