FROM DATABASE TO DOT COM Using Query Layers and ArcGIS Online to Maintain Up-to-Date Data John N Phillips 

Agenda §  Overview of Data Management at TxDOT –  Geospatial Roadway Inventory Database –  Oracle Spatial §  Query Layers –  SDO Geometry object –  Custom solutions using query layers §  AGO, Project Tracker, and other downstream uses of TxDOT Data 2 

The Astronomical Amount of Daily Vehicle Miles Traveled in Texas 3 •  ~213,000 miles of road •  120 assets (attributes) to maintain Total Daily VMT Today Total Daily VMT 1985 Daily Truck VMT Today 

GRID: Geospatial Roadway Inventory Database 4 §  Custom GIS Database Maintenance and Reporting application §  Went live 2 years ago §  Comparable to ESRI’s Roads and Highways Extension 

GRID: Geospatial Roadway Inventory Database 5 §  Updates made by district personnel §  Minimal GIS Experience necessary §  Uses TxDOT Statewide Planning Map Basemap (based on our current data) 

GRID: Geospatial Roadway Inventory Database 6 §  Users can also use the Texas Imagery Service (Google) basemap §  Business validation rules help maintain data integrity 

GRID: Geospatial Roadway Inventory Database (Back-End) 7 §  Oracle Spatial database §  SDO Geometry Object (Similar to Esri’s @SHAPE token) §  Dimensions §  SRID §  Point Information or Line Information (Info Array and Ordinate Array) §  SDO Geometry Object information used for Query Layers 

Displaying Oracle Data in ArcMap §  Oracle SDO Geometry Object allows direct connection through ArcMap §  Can be useful for visualization, but slow to render and difficult to use due to cryptic database naming conventions §  Read Permission to display, write permission to edit 8 

QUERY LAYERS 

What is a Query Layer? §  A layer or stand-alone table that is defined by a SQL query §  Make Query Layer Data Management Tools.tbx > Layers and Table Views §  Queries must be written in SQL style of database you are connecting to (i.e. Oracle, SQL Server, etc.) –  Check Query in IDE §  Uses information from Geometry object to create spatial layer 10 

What is a Query Layer? §  Changes in the database will be reflected in the in-memory Query Layer (like a spatial table view) §  Save as .lyr file to preserve symbology and query §  Custom column titles and attribute table schema §  Access to much more SQL capability than ‘Select by Attributes’ 11 

Custom Query Layer Tools §  arcpy.MakeQueryLayer_management §  Custom SQL queries inserted into the Python script §  Tkinter (part of the standard Python installation) §  Easy to make simple GUI 12 Standalone custom export GUI with pre- packaged exports 

Custom User Input into GUI 13 

Custom Query Layer Exports 14 §  Result layer for Reference Markers at the end of a segments §  Targeted SQL queries to investigate data 

Other Libraries and Customization Options 15 §  Cx_Oracle (Oracle) §  Pymssql (SQL Server) §  Custom ArcMap Toolboxes §  Custom ArcMap addin toolbars §  Save coded domain values in database 

DOWNSTREAM USES ArcGIS Online, Statewide Planning Map, Project Tracker 

ArcGIS Online 17 §  Regular exports from production database to online services §  Task Manger, Query Layers and ArcGIS Pro (interfaces well with AGO) allow regular updates from production database §  Accurate, up-to-date data 

TxDOT Open Data Portal 18 §  Most TxDOT data available here for your consumption §  Speed Limits, Functional Classification, Control Sections, Reference Markers, etc. §  www.txdot.gov/opendata 

TxDOT Statewide Planning Map 19 §  Custom web map application available for internal/external use §  When updates are made, they are reflected in the SPM which is then used as the basemap for GRID 

Project Tracker 20 §  View construction status, location, and cost information for projects throughout the entire state §  Combines information from six TxDOT systems §  Updates coming with even more data §  Visit the TxDOT Project Tracker booth! 

QUESTIONS? John Phillips [email protected]