Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database to Dot Com: Using Query Layers and ArcGIS Online to Maintain Up-to-Date Data

Database to Dot Com: Using Query Layers and ArcGIS Online to Maintain Up-to-Date Data

John Phillips, TxDOT

More Decks by Texas Natural Resources Information System

Other Decks in Technology

Transcript

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

    View full-size slide

  2. 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

    View full-size slide

  3. 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

    View full-size slide

  4. 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

    View full-size slide

  5. 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)

    View full-size slide

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

    View full-size slide

  7. 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

    View full-size slide

  8. 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

    View full-size slide

  9. QUERY LAYERS

    View full-size slide

  10. 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

    View full-size slide

  11. 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

    View full-size slide

  12. 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

    View full-size slide

  13. Custom User Input into GUI
    13

    View full-size slide

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

    View full-size slide

  15. 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

    View full-size slide

  16. DOWNSTREAM USES
    ArcGIS Online, Statewide Planning
    Map, Project Tracker

    View full-size slide

  17. 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

    View full-size slide

  18. 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

    View full-size slide

  19. 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

    View full-size slide

  20. 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!

    View full-size slide