Automation Using Python for WVDOT GIS Data Management

Automation Using Python for WVDOT GIS Data Management Yueming Wu, Ph. D. , GISP GIS Manager May 9, 2012

Agenda §Introduction §WVDOT Geospatial Data Repository §Automation Implementation §Key Issues & Solutions §Future Work §Questions & Answers

Introduction

History Historically the West Virginia Department of Transportation (WVDOT) has been utilizing geospatial (GIS, Remote Sensing, & GPS) technology for planning, analysis, and mapping purposes ever since the technology became available. In 2005 the WVDOT GIS Section was created to meet the growing needs for geospatial data and services in transportation. In 2007 the GIS Section was renamed as the GIS Unit and merged with the Highway Data Services (HDS) Unit, a program responsible for processing changes in roadway information, and the update and maintenance of the Roadway Inventory Log, to form the current Geospatial Transportation Information (GTI) Section.

Program Planning & Administration Division Geospatial Transportation Information (GTI) Section GIS Unit Highway Data Services Unit - Geospatial Data Management -Roadway Inventory Log -Geospatial Services -Roadway Statistics -Public Certified Mileage

GTI Mission & Vision Statements §Mission Statement To improve customer service to the citizens of West Virginia by supplying the latest geospatial transportation information. §Vision Statement The West Virginia Department of Transportation Geospatial Transportation Information (GTI) Section is committed to managing an enterprise Geospatial Information System that will improve the productivity of the West Virginia Department of Transportation, meet growing customer needs, and play a leading role in West Virginia’s geospatial community.

GIS Implementation §Esri Shop §Enterprise License Agreement (ELA) §Esri Technology §Staff § 7 GIS Professionals § 2 Cartographers § 1 GIS Coordinator at District 7 §Enterprise GIS §WVDOT Enterprise GIS Roadmap §Enterprise Resource Planning (ERP)/Asset Management System

Major GIS Projects in 2012 §Enterprise GIS Implementation §New Transportation GIS Data Model §District Level GIS Implementation §Workflow Manager §Transportation GIS Data Collection (MS 4, Railroad Crossings, Landslides, Outdoor Advertising, Right-of-Way, Trails, Meta Data, etc. ) §Highway Map Reproduction §Web Mapping Applications (STIP, Park & Ride, Functional Classifications, etc. ) §Mobile GIS Applications (MS 4, etc. ) § 3 D GIS Application Development §GIS Portal Upgrade §Project. Wise Implementation §ERP/Asset Management System Implementation

WVDOT Geospatial Data Repository

Shapefile s for Download Source Data TRANSPORTATION GIS DATA • Commissioner Orders • Paper Maps • Field Data • Consultant Reports • Etc. Editing Version - Edit GTI_EDITING WVDOT Geospatial Data Repository Update Publication GTI_PUB_UTM REFERENCE GIS DATA • WV GIS Tech Center • WVGES • USGS • Esri • Etc. Downloa &Add dd & Add Metadata Shapefile s Upload Reproje ct GTI_PUB_WM Export Geospati al Analysis Map Producti on Web Apps IMAGERY • Aerial Photos • Shade Relief • Hillshade • Topo • Etc. Store Imager y GIS Service s

Automation Implementation

Automation Implementation §Four Automated Data Management Operations §GTI_EDITING GDB GTI_PUB_UTM GDB §Shapefiles GTI_PUB_UTM GDB §GTI_PUB_UTM GDB GTI_PUB_WM GDB §GTI_PUB_UTM GDB Shapefiles for Publications GP 1 Arc. GIS Model Builder GP 2 Data Model Export Python Script GP … GPn Windows Scheduler Pythonwin Improve Deploy Task

Key Issues & Solutions

Database Lock §When a GDB is updated, it’s been accessed by clients (Arc. GIS Desktop, Arc. GIS Server, etc. ). Database locks are created accordingly. #Remove Database Locks on GTI_PUB_WM Import pyodbc cnxn = pyodbc. connect('DRIVER={SQL Server}; SERVER=dotarcgis 02; DATABASE=GTI_PUB_UTM; UID=xxxxx; PWD=xxx' ) cursor = cnxn. cursor() cursor. execute("delete FROM [GTI_PUB_UTM]. [dbo]. [SDE_process_information]" ) cnxn. commit()

Scheduled Task §After a Python script passes test, use Windows Scheduler to create a task so the script would run on a regular basis. §Make the task independent to individual’s login

Email Notification? §If a task fails, notify the data repository manager by email #Email import smtplib, MIMEText try: … except: sender = 'admin@wvdohwebserver. com‘ receivers = ['wuyueming@gmail. com', 'wu_yueming@yahoo. com'] msg = MIMEText("The GTI_PUB_UTM_2_Web_Shapefiles script failed. Please check!") msg['Subject'] = 'Python Script Failed' msg['From'] = "Admin@WVDOHGISWeb. Server. com" msg['Reply-to'] = "GTI Core Team" msg['To'] = "Admin@WVDOHGISWeb. Server. com" smtp. Obj = smtplib. SMTP('localhost') smtp. Obj. sendmail(sender, receivers, msg. as_string())

or Log §After a task is created, use a log file to track. #Log Import os, sys, time root. Folder = r"C: Geoprocessing Scripts\" # This is where the log file is located text_file = open(root. Folder + "PScript. Log. File. txt", "a") text_file. write("nn") text_file. write("Running GTI_EDITING_2_GTI_PUB_UTM. py began at " + str(time. strftime('%X %x %Z')) + ". nn") try: … text_file. write("Running GTI_EDITING_2_GTI_PUB_UTM. py successfully ended at " + str(time. strftime('%X %x %Z')) + ". ") except: text_file. write("Running GTI_EDITING_2_GTI_PUB_UTM. py failed at " + str(time. strftime('%X %x %Z')) + ". ") text_file. close()

Future Work

Shapefile s for Download Source Data TRANSPORTATION GIS DATA • Commissioner Orders • Paper Maps • Field Data • Consultant Reports • Etc. Editing Version - Edit GTI_EDITING WVDOT Geospatial Data Repository Update Replicat e Publication GTI_PUB_UTM REFERENCE GIS DATA • WV GIS Tech Center • WVGES • USGS • Esri • Etc. Downloa d &Add Metadata Shapefile s Upload Reproje ct GTI_PUB_WM Export Geospati al Analysis Map Producti on Web Apps IMAGERY • Aerial Photos • Shade Relief • Hillshade • Topo • Etc. Store Imager y GIS Service s

Editing GTI_EDITING Headquarters Update Replicat e Publication GTI_PUB_UTM Shapefile s Upload Publication Replicat e GTI_PUB_UTM FGDB Reproje ct GTI_PUB_WM Imagery Districts 1 - 10 WVDOT Geospatial Data Repository

Yueming Wu GIS Manager WVDOT (304) 558 -7437 yueming. wu@wv. gov