Migrating Integrated CADGIS Users from FileBased Storage to Oracle Locator S'F' Dept of Public Works

1
Migrating Integrated CAD/GIS Users from
File-Based Storage to Oracle Locator S.F. Dept
of Public Works Bureau of Engineering

• An End user perspective on implementing Oracle in
  the engineering environment
• Greg BraswellManager of Sewer InformationMIS-GIS
  -CADCity and County of SF-Dept of Public
  WorksBureau of Engineering1680 Mission STSan
  Francisco, CA 94103
• greg.braswell_at_sfdpw.org

2
SEWER GIS More then Just Maps

• Hydraulics section, DPW maintains information
  about the sewer system for the City County of
  San Francisco. Data for about 1000 miles of sewer
  pipes, 27,000 Manholes, 24,000 Catch basins and
  various structures forming components of the
  sewer system has been captured in GIS ready
  format. Data captured include the location,
  geometry and various attributes of the pipes
  node structures. This central GIS data has become
  one of the core databases to which is used in day
  to day functionality of the Hydraulics section.

Maps Published on the intranet
Paving/ Sewer project management system
EXAMPLES OF USE OF GIS DATA
As-Built Viewer -View engineering drawings
associated with GIS data
Sewer Inspection / TV Taping Management system
GIS DATABASE SQL Server database AutoCAD
files ESRI shape files
3
Existing SEWER Information Workflow In Sewer
Engineering
Maintenance
Web Mapping
SHP
SQL Server
Base DWG
TV Tape Inspection
Reports
Doc Mgmt
RR Project Planning
Project DWG
City GIS Basemap
Autodesk Map
Engineering Design
Plans
Specs
Other GIS Apps
4
The Dream

• One Big Database of all CAD Drawings
• Big CAD database updates Asset mgmt database
• CAD and asset mgmt are completely integrated
• All happens In 1 fell swoop
• Updates, edits, corrections
• Easily get out data for all applications
• Planning and Analysis
• Modeling
• Design projects

5
Early Attempts Fits and starts

• Experimenting with Oracle
• Expensive! ?
• Download for free!!! ?
• 3-4 attempts to install oracle ?
• It finally works! ?
• How to connect to work with data?
• ODCB-Access front end ?. Were good at that ?
• All other applications are in SQL server. ?
• Instructions and how to????
• Google! ?
• Experimenting with ACAD
• Directions say See database admin. What a sec,
  we are the database admin???
• Saved something and was able to retrive it ?
• What about attributes???? ?
• Lines, polygons, etc?
• VBA??? ARX?

6
The Reality

• ACAD to Oracle is not easy
• No previous oracle experience at BOE
• ACAD map Oracle interface is not intuitive, does
  not have mechanism to maintain strict data
  schema, assure data integrity and network
  connectivity
• Costly
• Learning curve for admin, GIS technicians, etc
• Difficult to
• Users Learning curve and established processes
  already in place become impediments to
  implementation by engineering line staff

7
Options?

• ACAD Map Queries
• Already in use
• Can link to databases
• SDE?
• Attributes??? Drivers for ACAD?
• Develop custom Apps
• VBA API not available
• ARX
• Maintenance
• Where is Autodesk going????
• FDO vs OSE?
• 3rd Party Apps

8
Decision to Use Munsys

• Off the shelf App for Oracle/ACAD integration ?
• Relatively simple schema for utility ?
• SF is combined system, Munsys has storm and sewer
  separate
• Maintains network integrety quite well ? but
  requires practice and training ?
• Spatial Data Manager has a lot of flexibility ?
• New to the States ?
• Willing to make a good deal ?
• Strong background in ACAD and GIS ?
• less so in engineering solutions ?

9
BOE-CCSF Phased Deployment of Oracle/MunsysPhase
1 Data Loading, Training, Initial Use

• Phase 1 Combined sanitary and storm system
  network connectivity and validation
• Data loaded
• 1000 miles of pipe (27 000 links, 28 000 nodes)
• Servicing 153 000 parcels
• Sewer And City GIS Basemap
• Convert attribute data from SQL Server
• Change GIS/Engineering Process
• Autodesk Map/Munsys- Train 5 editors, 30 Drafters
  and Engineers
• Automate shape file generation for other users
• Adapt Schema to facilitate new data
  storage/retrieval (Oracle)
• Use for GIS updates, corrections
• tease the drafters
• Wait for management to catch up

10
Phase 2 - Workflow Integrating Data Design
11
ACAD-Munsys DemoStartup
12
ACAD-Munsys DemoLogin
13
ACAD-Munsys DemoSpecify Area of Interest
14
ACAD-Munsys DemoData Retrieved
15
ACAD-Munsys DemoDetailed View
16
ACAD-Munsys DemoQuery-Edit Asset Information
17
ACAD-Munsys DemoMapguide Updated in Real Time
18
ACAD-Munsys DemoShow Direction Flow
19
Auxiliary Water Supply System

• High pressure firefighting system in SF
• Data of record is paper based although there are
  various digital files available
• Data is not current updates sporadic
• Not enough major infrastructure projects

20
AWSS Network
21
AWSS Image Blocks
22
AWSS - Old and New data
23
AWSS Data of Record Hyperlink
24
Official data of record
25
Official data of record
26
BOE-CCSF Workflow Changes

• Before
• Managing separate spatial and attribute data
• Developing custom data capture and maintenance
  tools
• Creating SHP files from Map and SQL (6 hour
  process, difficult to batch)
• Limited access to data for engineers
• Various Data sources not in synch
• Unable to keep network connected when edited by
  several people at once

27
BOE-CCSF Workflow Changes

• Before
• Managing separate spatial and attribute data
• Developing custom data capture and maintenance
  tools
• Creating SHP files from Map and SQL (6 hour
  process, difficult to batch)
• Limited access to data for engineers
• Various Data sources not in synch
• Unable to keep network connected when edited by
  several people at once

• After
• Single data source for spatial and attribute data
• Off the shelf tools in Munsys provide 95 of the
  functionality
• Create SHP files in batch mode time reduced to
  3-5 minutes
• Free form access to current data for other
  applications
• Data sources all in once place
• Creation of a single connected network that can
  be continuously updated by Multiple BOE engineers
  directly

28
ROI Indicators

• Creation of SHP files from 6 hours to 3 minutes
• Creation of connected network which can be
  constantly improved
• In service from day 1
• Data integrity improved (two sources to one)
• Productivity improvement with off the shelf tools
  (investment change from creating and maintaining
  tools to using tools)
• Convenience and ease of access to the current
  data greatly improved
• System architecture that enables integration into
  the enterprise Phase 2

29
BOE-CCSF Future Integration

• Bring Mechanical section up to speed
• As-built viewer Document Mgmt
• Hydraulic and hydrological modeling software
  integration DHI-Mike Urban/ Wallingford
• Other data sources
• Space planning
• Facility BIM?

30
Lessons Learned

• Implement in phases
• Flexible goals and timelines
• Wait for engineering team to catch up
• Be clear but flexible about ways to integrate
  into Business processes
• Be cost effective but with knowledge that its an
  investment that might take a while to pay off
• Keep the dream alive

31
Oracle/GIS to Modeling Integration
BOEGIS ORACLE
A
C
Modeling User 1
PGB
Scenarios
Geometry created out of Munsys in the form of SHP
files using MuneXport
Model
Linked tables with base model data
B
SEWPIPE
Modeling User 2
Pipe_ID
Import using data bridge thus enabling everyone
to have the same base model
LinkTable
PGB
Links
ASSETNAME
Scenarios
Model
ODBC link to tables in Oracle
Munsys schema
Modeling User 3 etc
PGB
Scenarios
Model
F
UPDATE
UPDATE PROCESS GATEWAY for base data validation
and updating
Collection of changes required to base data
E
D