March 2008

2. Abstract 2

3. California CAD Solutions, Inc. Started 1987 Celebrating 21st Year One of the oldest Resellers under original management Known in the GIS/CAD Industry Registered Developer for Oracle, Autodesk, Safe, & Others Published in EOM, GeoWorld, etc. Provide Solutions Consulting, Services & Software

4. Objective Like every city, Turlock, California needed a precise, up-to-date infrastructure map of their area. Working in a mixed environment of SQL Server, AutoCAD Map, ESRI, and Autodesk MapGuide, they faced difficulty getting information consistently from field crews to the drafting department without losing data integrity. This session uncovers how Turlock used Autodesk MapGuide for its web-based GIS and bridged the gap between field staff and the drafting department with FME.

5. Field Staff… Needs: Update infrastructure data (i.e. Sewer Lines, Manholes, Pressure Manholes, etc.) Obstacles: Field Staff do not know how to use AutoCAD Too much time is required to train in AutoCAD City does not want to incur the expense of additional AutoCAD licenses

6. Public Works… Needs: To accurately and quickly incorporate updates requested by Field Staff into existing AutoCAD infrastructure drawings Obstacles: Public Works (PW) Staff are stretched thin on data update responsibilities It’s too time consuming to investigate required edits Many times it is necessary to call on Field Staff to verify changes

7. Timing Issues It often takes weeks or even months for PW Staff to make updates after request is filed by Field Staff Time delay makes it hard for Field Staff to remember the details of the request when later questioned by PW Staff The perceived lack of responsiveness causes antagonism toward PW Staff Field Staff stop reporting necessary changes to PW out of frustration “because it won’t make any difference on the problem getting fixed”

8. Data Errors Data flow prone to error and data loss Change requests are not logged Update process is not tracked or logged Paper change requests are often lost or misunderstood by PW Staff

9. Undesired Results The infrastructure map is severely outdated Field Staff are dispatched against incorrect, incomplete or inaccurate data resulting in significant costs and potential legal action One “unknown/ forgotten” sewer pipe problem resulted in a lawsuit Since the infrastructure map is outdated, Field Staff often have the only intimate knowledge of the system As Field Staff retire the potential loss of institutional knowledge is huge That knowledge must be captured

10. Key Points When formulating a solution the following key points must be considered: IT Department realized that there needs to be a more centralized data store with the ability to track change requests and updates Field Staff cannot update infrastructure data directly without AutoCAD training and purchasing additional AutoCAD licenses PW Staff members are unwilling to give up control of the data to other departments since they are ultimately responsible for the data maintenance and integrity Multiple departments directly editing the same data would be cause for finger-pointing

11. Looking for a Solution Fire Personnel are already updating hydrant and address point locations through the existing MapGuide interface

12. Web-interface The IT department liked the idea of using the existing MapGuide interface The interface has proven itself as reliable and updates are easy to track The interface is familiar and easy to maintain Through MapGuide, Field Staff can move, add, or remove existing node features (i.e. Manholes, Pressure Manholes, Cleanouts, etc) and can put in new linework (i.e. Sewer Lines) through the redline feature

13. Challenges Linear and polygonal features are stored as shape files Used by MapGuide& ArcView Point features are stored in MS SQL Server tables If Field Staff make edits in the SQL Server database and PW Staff maintain AutoCAD drawings of the infrastructure, how do we keep the database and AutoCAD drawing in sync?

14. The Solution The City already uses FME to process AutoCAD drawings (the source data) into SHP and database information for presentation in MapGuide Using available resources of FME in conjunction with MapGuide, the City is able to meet the needs of both Field Staff and PW Staff while maintaining data integrity and accuracy Field Staff make edits via the MapGuide interface PW Staff use FME to incorporate updates made on-line into the AutoCAD sewer infrastructure drawings

15. How does it work? Field Staff edit “Muni” tables in the SQL database. These tables are considered to be the master data. “PW” tables in the SQL database reflect the current state of the AutoCAD drawing file

16. One button processing… On a regular (or special request) basis, PW personnel run an FME routine which compares the “Muni” tables (which have been edited by field personnel through the MG interface) with the “PW” tables and creates an AutoCAD drawing noting all entities which have been either moved, added, or deleted

17. Mechanics PW Staff insert the “Update Drawing” (which contains all points that have been added, moved or deleted) into the sewer drawing to see which entities need editing. Features from the “Update Drawing” are placed on layers that note the type of edit required (i.e. add, move, or delete) Associated linework edits are made by PW personnel based on the location of the point node in question This frees Field Staff from making time consuming line edit notations which are made evident simply by moving the associated point feature

18. New Sewer Linework Presented Problems Accurately placing lines within the browser interface (snapping to nodes, etc) is not practical or feasible Redlining in MG allows Field Staff to add the new linework via the MapGuide interface. New Sewer lines are drawn starting and ending at nodes PW Staff use ACAD tools to accurately snap lines to nodes.

19. Line size, direction and notes captured When digitizing new Sewer Lines the challenge was how to easily attach the information about the line to the feature for use by the PW editors in AutoCAD. FME’s new AutoCAD Object Data writer solved this problem Field Staff digitize new Sewer Lines and choose the line size from a drop down menu. FME processes the redline data entered by Field Staff into a new “Update Drawing” for PW Staff to use as a guide for new line creation.

20. Completing Edits When the AutoCAD updates are complete, a Second FME routine is run which processes all of the linework and nodes and updates the PW version of the SQL tables with the current state of the drawing in preparation for the next change detect run of FME

21. Data Integrity and Tracking A transaction log is kept recording all edits made via the MapGuide interface Data inputs are constrained via the interface to eliminate “incorrect” or “invalid” entries Data accountability is established since there is an accurate log of transactions and dates of requests

22. Questions ?