Design and Implementation of a Geodatabase for New York State Canal Inspection Data

1. Design and Implementation of a Geodatabase for New York StateCanal Inspection Data Ruth Ann Trudell NY State GIS Users Conference Lake Placid, NY October, 2009

2. Topics • Project Objective • Canal History • Inspection Methods, Data Storage and Need for modernization • Database Development • Lessons Learned

3. Project Objective Provide a single storage location for all spatial and tabular data pertinent to Canal Inspections.

4. Canal History

5. Travelway for Commerce

6. Current Canal System

7. Canal Management • Operated and maintained by the Canal Corporation, New York State Thruway Authority • Inventory performed by NYS DOT through MOU • Canals managed primarily for recreational purposes and historic values

8. Canal Construction • Parts of canal are embedded in the landscape; parts raised above. • Portions are raised above the surrounding landscape with the use of levees or embankments.

9. Typical High Embankment

10. Embankment Failure

11. Inspection Process • Inspect every 5 years. • Inspection segments delineated on paper maps. • Embankment conditions documented in field. • Pictures taken at points of interest. • Segments are ranked and rated. • Miles of segments summarized by rating. • Report written summarizing findings.

12. Inspection Segment Map

15. Current Data Storage • Paper maps, inspection forms and reports. • Disparate spatial files stored on CC and DOT servers. Solution? A Database!

16. Database Development

17. Database Development Steps • Determine Business Needs/Database Structure • Decide upon Database Elements • Populate Database • Test by Querying Database

18. 2009 Business Needs Meeting • Validate data input and output needs (business functions) • Discuss Database Considerations • Spatial representations- feature classes vs. linear referencing • Appropriate route • Aggregation/disaggregation of data • Image hyperlinks

19. Database Structure

20. File Geodatabase!

21. Database Elements

22. Element 1- The Value of Terrains

24. Element 2- Linear Representation; Events or Features Two Critical Questions: Use linear events or line features to represent inspection segments? If events are to be used, which route is appropriate? which measure?

25. Events vs. Feature

26. Another Example

27. Selection of Linear Route • Canals Corporation routed lines (measure in meters) • Canal Corporation lines calibrated to stationing in feet. • National Hydrography Dataset (NHD)- National Standard

28. Elements 3-5 – Features, Data Tables, Relationship Classes Event Feature

29. Element 6- Domains

30. Element 7- Metadata And YES, of course a Data Dictionary!

31. Database Maintenance

32. Complexities of Routes Requires for both Events and Feature Classes 1. Linear referencing requires event tables with measures, WHILE: 2. Field data collection using GPS and mapping query results both require features classes

33. Tabular Data Input-Existing Feature

34. Testing and Data Reporting

35. Extracting Data-Making Query Table

36. Example: SQL Expression • Segment2009.SegID = Inspection.SegID AND Inspection.AdjTerrain = ‘DevRes’ • Segment2009.SegID = Inspection.SegIDAND Inspection.InspectionID = OtbdChar.InspectionID AND OtbdChar.DrainStrID = DrainStruct.DrainStrID AND DrainStruct.StructCondition = ‘Good’

37. Lessons Learned • Relationship classes in GDB depend on exact match of field names in feature attribute and data tables. • Use descriptive codes for coded domains. Exported tables contain codes, not descriptions! • Ensure topology of routes is uni-directional and calibration point measures increase sequentially in up route direction. • Take advantage of NY State GIS Help Desk; They are an excellent resource!

38. Final Result Successful development of a database that addresses data storage, data retrieval and data display needs!

39. Credits Dr. Patrick Kennelly, Graduate Advisor Assist. Professor, Long Island University, and Penn State University Jamie DeLuca, NY State Canal Corporation Steve Heiser, Department of Transportation Ricardo Lopez and Tim Daly, Department of Environmental Conservation

40. END

41. Hydraulic Modeling with NHD

42. Spatial Placement, Canal Prism vs NHD Route

43. Event Table, cont.

44. Example of Implementation Data Input- New Segments Ensure database contains empty (temporary) segment feature related to inspection table (required to GPS new features), Check out DB to GPS, Collect data - database tables will be updated through relates, Check in DB, Use “Locate Features along Routes” to obtain measures and RID,

45. Data Input- New Segments (cont) Rename Segment ID with measure info., Append this attribute table to event table, Recreate “permanent” feature class, Empty contents of the “temporary” feature class for future use with GPS.