Jackson Electric Cooperative

1. Jackson ElectricCooperative Facilities Management Lee Panter

2. You might be a GIS Professional if: • You can navigate a southbound trip without turning the map upside down. • You can give directions without mentioning “where the old William’s barn used to be”. • You assume that the people around you are yawning because they are tired! www.gisnuts.com

3. Objective • Provide a possible development process to move from paper to a Facilities Management System • Show one possible way to do this with limited resources

4. Overview • Where we were (pre-2009) • Where we are (2010) • Where we could be going?

5. Where We Were(pre-2004) • Hand Drawn Maps • “Flat world” map • No color • Hard to distinguish between features • Limited information • Hand update of each map • Little spatial accuracy • Essentially a road map

6. What We Did(2004 – 8) • Purchased ESRI ArcView single user license in early 2004 • Obtained digital land base from Jackson County for our service area • Roads, water courses, etc • Section and Quarter Section lines • Parcels • Orthophoto(s) • Manually digitized all line/customer data from hand-drawn master map • Tried to match customer locations to parcels and Orthophoto • Placed devices, peds, etc

7. What We Did(2004 – 8), continued • Tried to integrate StakeOut • Extracted ArcView shapefiles for use as background maps • Used LookOut • Created new symbology (very limited capability compared to ESRI) • Tried to use StakeOut export to ESRI • Cumbersome process • Limited usefulness • Significant problems • Ended up re-drawing Work Orders in ESRI

8. What We Did(2004 – 8), continued • We produced paper maps in 2004, 5, 6, 7, & 8 • Updated as the Linemen found errors • Conducted limited on-site surveys • Added new builds, rebuilds, retirements, etc. as they happened • Each year evolved to achieve “readability”

9. What We Did(2004 – 8), continued • Our line locating is currently done by USIC Locating Services, Inc dba SM&P Utility Resources , Inc. • We provided an electronic map • Based upon ArcView shapefiles • Slow to load, but useable • Provided just the minimum they needed to locate our lines • Currently, a yearly update for 2008, 9, & 10 • SM&P seemed to like each version as it evolved

10. What We Did(2004 – 8), continued • The final printed version: • 2008 Color Map • Master Sheet is 36-sections • 9 – 4 section detail sheets below each master • Inserts of various sizes are nested below those • “Locator” info at bottom • Significant data loaded in the supporting databases, but not available for printing – much too crowded

11. Old vs New:Comparison

12. ArcView, used through 2008 • No Geodatabase • Our Map included; • Several hundred shapefiles • Overhead and underground primaries were separated by Sub and circuit, for example • Obtained shapefile data from the County Land Management office • Obtained Orthophotos initially from the County, then the State • ArcView is very limited – good for making papermaps • The new paper map was easier to read, had marginally better “information”, was heavier, and more complex • We needed to be able to obtain more “information” than was available from a paper map

13. Our Need • To answer more in-depth queries on such subjects as: • When was this or that procedure done? • What is the age of a certain item? • What areas can be sprayed? • Etc. • The questions are all familiar to us, but the “answering process” is usually “cumbersome” • How do we streamline the process?

14. Step One: “Look at what you have” • Sources of “information” • Customer Information • Computer Programming Services, Inc. (CPS) provides our Customer and Financial management system • UNIX relational database • Work Order Files • Paper back to the ‘30’s • Indexed by year-only in the file location • Separate card catalog that referenced WO to Customer Location • Card catalogs • WO card catalog - time consuming to find the specific WO in the WO paper files • OCR card catalog – tracked serial number maintenance and a partial track of where that serial number had been • Paper files on regulators • “Miscellaneous” • Assorted “lists”, drawings, and “data” scattered in various file locations

15. Step Two: “Integrate” • Customer Information (CPS) • Used Synergy DE to create an ODBC link from CPS to MS Access • Created various queries in MS Access to select data from multiple tables in CPS and array it in a manner quickly useful for the Linemen • The results of these queries were then exported to MS Excel tables that could be either joined or related to specific data points in ArcView shapefiles • Process is time consuming and “quirky” if you are not trained in database development

16. Example: “Integration” Blue = CPS Purple = Access Green = ArcView

17. Integration: “Work Orders” • The Work Order locator catalog • MS Access Data Base searchable by Customer Location or Work Order Number • Created MS Excel tables that are related to Customer Locations in ArcView • Integrated PDF images of StakeOut Work Orders (since 2003)

18. Integration: “Work Orders, continued”

19. Integration: “Work Orders, continued”

20. Integration: “Work Orders, continued”

21. Integration: “Card Catalogs” • OCR and Regulator data • Tracks maintenance data by serial number • Tracks location data by requirement

22. Integration: “Miscellaneous” • Consolidation of: • Line clearing projects • Line inspection • Pole inspection • “No Spray” parcels • Work Plan • Grounding • Etc • The information for these items is stored in various forms

23. Lessons Learned • Defined Goal – if you don’t know where you’re going, it’s hard to know if you got there • Planning – databases are complex; changes can collapse the whole process • Training – trial-and-error methods have some benefits, efficiency isn’t one of them

24. 2009Transition Year • We upgraded our license to ArcEditor • We obtained a copy of the ESRI data model from USG • The model has many options and attributes • You need significant planning up-front on what you want the outcome to be • Failure to know the endpoint = confusion, back-tracking, redesign, time-lost, etc. • We began the transition of shapefiles to a File Geodatabase • Long process • USG assisted us moving from the File Geodatabase to SQL Express Geodatabase • We built the Electric Model • Defined source and sink • Connected all the parts • Final scan to look for disconnected items • Great time to look for discrepancies in CPS data • Very time consuming and tedious

25. Where we are Now(2010) • We have a SQL Express Geodatabase that incorporates: • CPS data on Customers, transformers, etc • Tracks Work Order history by location • Tracks OCR and Regulator maintenance • Provides an electronic map to the linemen, office personnel, and SM&P • Connections still require manual update • We have a good base for a Facilities Management system

26. Where are we going? • Replace StakeOut with something that integrates easier with ArcEditor? • Select an Outage Management system? • Place more laptops in trucks? • Find some way to make it easier for the Linemen to turn in changes • ArcReader? • ArcPad? • GPS? • System Inventory?

27. Summary • We started with a hand-drawn “road map” • We had scarce resources • We tried to do all the work “in-house” • We did not have a specific end-point/goal • We spent a lot of time trying various things • We “evolved” to where we are now

28. Summary,Continued • We have a good, basic, Facilities management System • We need to broaden and deepen the “information” contained • We learned a lot, but do not recommend this approach except in a constrained-resources environment

29. Jackson Electric Cooperative • Lee Panter, GIS & IT Tech • lpanter@jackelec.com • 1-800-370-4607 • N6868 County Rd F • Black River Falls, WI 54615-546