Converting a Legacy Inhouse Database System

1
Converting a Legacy In-house Database System

• One Operators Experience
• Presenter Sarah Johnson
• Sunoco Logistics

PODS Users Group Presentation 2006
2
Overview

• Introduction
• Mapping History
• GIS Conversion
• Pipeline Correction
• Database Mapping/Conversion
• Ongoing Work
• QAQC
• Data Management
• Communication

3
Who is Sunoco Logistics?

• Master Limited Partnership
• Eastern Pipeline System
• Approximately 1800 miles located in PA, NY, NJ,
  OH, MI
• Primarily refined product pipelines
• The Terminal Facilities
• 8.9 million barrels of refined product terminal
  capacity
• 19.4 million barrels of crude oil terminal

4
Who is Sunoco Logistics?

• Western Pipeline System
• Approximately 3,600 miles located principally in
  OK and TX
• Crude oil pipelines
• Interest owned in several other pipelines across
  the US

5
System Map
6
Mapping History

• SunMap
• Custom-built mapping system created in late 1990s
• AutoCAD based system
• Database driven
• Live generation of alignment sheets
• Data tied to individual sheets
• Flexible data entry

7
New Mapping Needs

• Integrity Rule
• Data Access
• Data Standardization
• Risk Model
• One centerline for multiple applications
• New types of data
• ILI
• CIS
• GPS

8
Mapping Solution
ALIGN Database

• PODS-based
• Platform for
• Internet Mapping System
• Alignment Sheet Generator
• Risk Model Application
• Future Applications

9
GIS Conversion

• Pipeline Location
• Original Pipeline
• Challenges
• Three Phase Solution
• Data Mapping/Conversion
• Original Data
• Challenges
• Solution

10
Pipeline Location

• SunMap sheet-based
• Pipeline location visually correct
• /- 50 feet or better visual accuracy
• Relocations and PIs drawn and manipulated on each
  sheet
• Coordinate system created for program
• Aerial Photo background
• Not Ortho-rectified
• Stretched to fit

11
Pipeline Location

• Challenges after import
• Pipeline shape generally correct
• Pipeline position incorrect
• Distances varied
• Stationing position essential
• Pipeline position establishes confidence in maps

12
Correction of Pipeline Location

• Three-phase Solution
• Phase I - Initial overall system correction
• Vendor took hardcopy SunMap maps and compared to
  ALIGN Maps to perform corrections
• Pipeline moved to match visual location on SunMap
  sheets
• PIs moved to appropriate locations

13
Correction of Pipeline Location

• Phase II - SPL Quality Control
• Received simple pdf alignment sheets with aerial
  photo background
• Plan
• Check every sheet for first five pipelines
• Move to random checks
• All sheets were checked after errors discovered
  Pipeline is too important
• Used clerk personnel and GIS person

14
Correction of Pipeline Location

• Phase III Field checks
• Chose one knowledgeable field personnel to look
  through drawings
• Updates from people on the ground
• Variable thoroughness
• Field accountability
• Tracked any noted errors and reported information
  back to field checkers

15
Database Mapping/Conversion

• SunMap Data Structure
• Multiple pieces of information needed to identify
  an objects location
• Simple table structure
• Large tables
• Flexible data storage
• All information from historic paper drawings was
  converted into SunMap

16
Database Mapping/Conversion

• Challenge
• Object location identification
• Sheet-based system definitively identified object
  location using four inputs
• Pipeline ID
• Engineering Station Number
• Drawing Number
• Equation Number
• New system uses Station Series ID system

17
Database Mapping/Conversion

• Solution
• One-time mapping connecting the two systems

Pipeline ID
Drawing
Station Series
Equation
18
Database Mapping/Conversion

• Challenge
• SunMap had four main tables
• Crossing, Linear, Material, Object
• ALIGN has 30 or 40 tables where that data belongs
• SunMap user could choose any code or description
  flexible, yet not standard

19
Database Mapping/Conversion

• Solution
• Extensive data mapping project
• Mapped Field-to-Field
• Ex. SunMap Field CONST_DATE in Material table
  equals ALIGN InstallDate in the PipeSegment
  table
• A lot of relevant data contained in SunMap
  description fields required row-by-row mapping

20
Database Mapping/Conversion

• Crossing information
• SunMap codes were not sufficient to split
  crossings into relevant tables in ALIGN
• 1800 unique types of records to review
• DESCXING and MULTIDESC SunMap fields
  essential
• Determine types of crossings
• Additional crossing information

21
Database Mapping/Conversion

• Used unique descriptions in two fields and
  matched each to the correct table and field in
  ALIGN

22
Database Mapping/Conversion

• Challenge
• All information off old paper alignment sheets
  brought into SunMap
• Variable Data Input
• Information on each sheet customized to reflect
  old sheets

23
Database Mapping/Conversion

• Solution
• Find a home for all the data
• Choose what to display on maps for the future
• Requires field input
• Requires good communication

24
Ongoing Work - QAQC

• QAQC of data migrated from SunMap to PODS
• Required implementation of tool for map
  production
• Imminent rollout
• SunMap sheets will be compared to new Alignment
  Sheets
• Data corrections managed through a corrections
  log with prioritization
• Challenge to review maps that do not always look
  the same

25
Ongoing Work Data Management

• Standardized Information has so many advantages
• Easily Queried
• Similar Vocabulary Used
• Logical information structure and breakdown
• Ability to connect data
• BUT someone has to manage it to keep it consistent

26
Ongoing Work Data Management

• Important lesson
• Standard structure gives you buckets for your
  data
• But as the user, you have to choose how to use
  those buckets
• Example Water drains are a Waterway or a Foreign
  Line Crossing?

27
Ongoing Work Data Management

• User choice for which fields to populate and how
  to populate them
• Only a minimal amount of required fields ALIGN
  acts like a platform
• What would you store about a valve?
• Type what are your types?
• Size Nominal? ID?
• Which leads straight into

28
Ongoing Work Communication

• Communication is ESSENTIAL
• Data management cannot happen in a bubble!

29
Ongoing Work - Communication

• Example Corrosion Control Data
• GIS personnel not corrosion control experts
• Objects needed to be better defined Rectifiers,
  Test Stations, Coating, etc
• Several meetings with Corrosion Control manager
  and technicians

30
Ongoing Work - Communication

• Questions we asked
• What objects do you need to see on a map?
• What data is important about those objects?
• How are data changes currently communicated?

31
Ongoing Work - Communication

• Current efforts continue to communicate with
• Field offices
• Corrosion Control
• Operations
• Right of Way
• Health, Environment and Safety
• Integrity

32
Ongoing Work - Communication

• Communication lessons
• Know your customers
• Build faith
• Accept and respond to comments explain why
• Communicate corrections
• Use their expertise

33
Questions?