Indiana Department of Natural Resources - Division of Water

1. Saving Tax Dollars through Geographic Research for Internal Data (GRID) Robert Wilkinson Lacey Duncan Indiana Department of Natural Resources - Division of Water

2. Introduction • History of GRID • Credits • Name • Problem • Describe old way of business • Problems with Unity alone • Live Data • How to get data from SQL database • What we get with XYevents • Unity Jump Tool • VBA Scripting • Customizing Tool bars • How GRID is used

3. Combination of people and skills- Bringing GRID Together • Kurt Babcock • GRID 0.0 and 1.0 • Mitch McConnell – Unity programmer • Wrote majority of VBA scripts • Juan Lorenzoni – Unity Program Administrator • Wrote Unity Views for GIS • Gary Todd – Unity Program Administrator • Working VBA to VB.net • Lacey Duncan – DOW • GRID Administrator • Robert Wilkinson • Coordinator

4. Geographic Research for Internal Data or GRID

5. Problem • Before G.R.I.D. : • Lengthy time to research new applications with Unity alone. • When a new application for building in a floodway was received by the Division of Water (DOW) the area surrounding the proposed site had to be examined for any previous permits or violations. This process was labor intensive. • Staff would have to search the DOW Unity database looking for files that were located on the same water body, in the same county, or in the same section/township/range. This search could yield a large number of files needing to be checked individually, then located on a quad map individually to determine the relationships and possible effects between the new proposal and what currently exists.

6. DOW UNITY • SQL 2000 database • Master database for regulatory information across DOW • Used daily by DOW personnel and by other State Agencies • Constantly updated • Used to track status of files submitted by the public

7. DOW Unity Search’s • Search's by… • -Water Body • -County • -Township/Range • Will return ALL files for that search • Requires: • - Searching flat files for Quad Maps • Each point manually plotted • searching filing cabinets for files

8. GRID: Using GIS As A Solution • Geographic Research for Internal Data • GRID is a geographic representation of the Unity Database. • All DOW Unity records contain UTM coordinates and other pertinent data and displayed as XYevent points by said coordinates • FLEXIBLE Map • Has a set data package that changes over time • User’s choose how and what to display • User can add additional data, but not save it to map • Adjusts with changing requirements • Application data is live • Data changed in Unity updates in GRID with refresh • Cost effective research tool • Estimates show savings of 20 minutes to 2 hrs per time an application is reviewed

9. GRID • GRID is a ESRI MXD • Regulatory Events • Applications,NAD83 • Applications, NAD27 • Flood Hazard Area • Calc’d Discharges • DNR Dams • Significant Water Withdrawal Facilities • Reference shape files • Scenic Streams • Navigable Rivers • NHD • Indiana Roads • City/Towns • Counties • 2005 Ortho Imagery • USGS Topo Maps Denotes XYevent from sql view

10. SQL Database to GIS Shapefile • How do we get data out of Unity into GRID? • Create views in Unity with sql query • Create OLE database connections in Catalog • Add view table to Table of Contents of GRID • Display data as XYevent

11. SQL View of Unity Data from the SQL Data Manager SQL Tables Diagram • Where the data is in the database SQL Query statement: Determines the data seen in the view This is why Juan is so important!

12. Data Base connections - OLE Connections The views written for Unity from Arc Catalog ESRI help menu can guide you through setting up your connections:

13. Adding Data to TABLE OF CONTENTS - Source Live views from Unity Draws attribute data from Unity via SQL query statement Displayed live via “Display XY data”

14. Display XY Data Right click on file Display XY Data Set x field from table Set Y field from table Set Coordinate system Hit OK

15. GRID DATA from UNTIY Sample of applications from Unity Identical information to Identify tool!, BUT… Unity data box Application data is live from Unity!! Attribute table from XY event shows no records – thus we created the Jump Tool!

16. The Jump Tool • VBA by Mitch McConnell! • Unity has tons of data that could never be shown in GRID • 11 separate data tabs with different types of data collected for the record. • To get to this data • Developed a jump tool from GRID to the selected Unity File • VBA programming • Opens Unity to the file selected

17. UNITY record screen TABS of Information General Information Site Location Status Related Files Staff Assignments Interested parties Fee Information Images Document Locations Compliance Routing

18. GRID Data from UNTIY Identical information to Identify tool!, BUT… Unity data box Application data is live from Unity!!

19. Unity Jump Tool 1. Unity Jump Tool (select by depressing) 2. Select application 3. Unity data box will appear Application data live from Unity

20. Microsoft VBA Scripting

21. Forms Built For Information Search Part of the VBA process

22. Excel Spread Sheet used for bridge Communications bridge between GRID and Unity - Tells ArcMap where to go in Unity database to get data

23. Getting the UICButton

24. Also built in Zoom To A File on GRID When you Know what you want: Next slide for results:

25. Unity in the specified record

26. Technical Services Section • Administer permit programs for shoreline and lakebed alterations, ditch reconstructions, navigable waterways, and construction projects located within a floodway • 2009 Permits • 250 Public Freshwater Lake • 510 Floodway, Ditch Reconstruction, Navigable Waterways • 2010 Permits • 210 Public Freshwater Lake • 470 Floodway, Ditch Reconstruction, Navigable Waterways • Provide flood insurance information and floodplain map interpretation • 2009 Flood Analysis/Regulatory Assessment (FARA) • 852 requests received • 2010 Flood Analysis/Regulatory Assessment (FARA) • 1200 requests received

27. Determine Site Location Site Location Layers From GRID: City, County, Township Civil, Quadrangle Map, Basins (1-28), Section, Township, Range

28. Hydro Screen

29. Additional GRID Layers • Army Corps Districts • FEMA Special Flood Hazard Area • National Hydrography Dataset • Navigable Streams • Salmonic Streams • Scenic Streams • Exceptional Streams • Division of Water – Basin ID (North, Central, & South) • HUC – Basins • Imagery • Highlighted: • Public Freshwater Lakes – Polygon • FEMA Panel Index • Flood Elevation Dots • SWWF, Dams, Conservancy Districts, & Lake Control Structures

30. New Layers In GRID FEMA Panel Index Public Freshwater Lakes

31. Site Location Map Site Location Related Files

32. Research in GRID INFIP.DNR.IN.GOV

33. Public Freshwater Permit Research

34. Floodway Permit Research in GRID New Permit Flood Elevation Dot Navigable Waterway

35. Hyperlinks in GRID • Allows user to view the Archived Permit’s - Scanned Images by selecting the Hyperlink Tool

36. Other Section’s Use of GRID • The Water Rights / Use Section investigates ground water and surface water conflicts, administers the licensing program for water well drillers, and regulates well construction and plugging requirements. This Section also maintains the water well record and water use databases. • The Dams / Levees Section inspects dams and levees under State jurisdiction, reviews dam / levee construction plans, and provides safety improvement recommendations to dam and levee owners. • Project Development Section provides technical support for local and State funded water resource construction projects, administers the Conservancy District Act, reviews flood control and rural water supply revolving fund loan applications, and provides technical advice to River Basin Commissions. Water Rights - Significant Water Withdrawal Facilities Dams / Levees - Low, High, Significant Hazard Dams Project Development - Lake Control Structures - Conservancy Districts