Utilizing a Geodatabase for Asset Inventory Tracking
Utilizing a Geodatabase for Asset Inventory Tracking Larry Wilke Burbank Water & Power
Electrical Engineering GIS/CAD Coordinator
The Agenda • Burbank Water and Power – Who them guys? • The Need – Do you have a similar situation • The Old Process – Paper is being pushed • The Solution – TUGE Transformer Unit Geodatabase Editor • Review – Do you get it?
Burbank Water and Power • Located 12 miles North of Los Angeles • BWP - formed in 1913, municipal utility • Electric, Water, and Telecom (dark fiber) • Burbank’s population is just over 100,000
in 17 square miles (dense)
• Home to NBC, Disney, ABC, Warner
Bros many other media companies
The Need • Query Data – Tough with card files • What is in stock? – Disparate Access database • What is in the GIS – Does not account for in stock, on order, disposed, etc.
The Old Process • Full time position to
maintain and service any request for info on transformers.
Transformers Purchased • P.O.’s stored in a
book • Notes to determine if
BWP has received • No data backup
Transformers Received • Checks bill of lading
to actual serial numbers. • Sends paperwork
through slow interoffice mail
Test • Tests transformers
and maintains their own paper and electronic files. • Latency of
knowledge when a transformer fails test.
Transformer Invoiced • Legacy mainframe
input so that Finance can pay the vendor. • Uncertainty of BWP
receiving all transformers that have been invoiced.
Transformers Moved • Installations • Removals • Back to stock • Re-Tested
Transformer Disposal • Separate record for
Disposals. • Tough to query or
search the paper. • Cradle to Grave
requirements
The Solution
Out of the Box?
The Solution • Get this guy to use
the computer. • Early Retirement
Incentive?? • Have the responsible
parties enter directly into the related geodatabase tables.
Purchase New Transformer • Engineering or
Purchasing enters the data per specific purchase orders. • Data persists for
multiple entries
Transformers Received • Warehouse Clerk
enters P.O. number and checks off all that he is currently receiving this shipment. • Submit changes the
status to “Test”
Test • Enters all their data
into the Transformer Units Table • Changes status to
“Stock”
Transformer Invoiced • Clerks enters P.O.
number and the find button queries the transformers to find those with status not equal to “Purchased” • All checked of can be
viewed by accounts payable.
GIS Editor Activities • Locate and Relate – Not create related object • Unrelate object – Change field STATUS to removal location. – Transformer remains in units table forever.
Transformer Disposal • Trail is created to
establish if the old unit was sold, recycled, or trashed.
Purchased Transformer Report • What is still on
order?
Transformer In Stock • What is on hand?
The Key Piece Multi-Versioned Views • A part of core ArcSDE – –
Created by the ArcSDE SDETABLE command Enabled on individual tables by an administrator
• Allow direct SQL access to versioned
GeoDatabases
Implemented through database views, stored procedures and triggers – Only available on Oracle and SQL*Server databases –
• Operate on rows for a specified “version” –
Application developer issues SQL Select, Insert, Update, Delete statements as if on a simple DBMS table
The Key Piece Multi-Versioned Views • Creating a Multi-Version View: sdetable -o create_mv_view -T transunit_mv -t transunit -i 5452 -s trinity -u ele -p ele
• Opening a view for edit: EXECUTE sde.version_util.set_current_version('SDE.DEFAULT'); EXECUTE sde.version_user_ddl.edit_version('SDE.DEFAULT', 1);
Review • The Need – –
Disparate databases Large paper trail
• The Old Process – Cumbersome – Low Quality (No domains) • The Solution – – –
Utilize the Geodatabase units table Single source of data High Quality Data (Domain driven)
Questions
Electrical Engineering GIS/CAD Coordinator
The Agenda • Burbank Water and Power – Who them guys? • The Need – Do you have a similar situation • The Old Process – Paper is being pushed • The Solution – TUGE Transformer Unit Geodatabase Editor • Review – Do you get it?
Burbank Water and Power • Located 12 miles North of Los Angeles • BWP - formed in 1913, municipal utility • Electric, Water, and Telecom (dark fiber) • Burbank’s population is just over 100,000
in 17 square miles (dense)
• Home to NBC, Disney, ABC, Warner
Bros many other media companies
The Need • Query Data – Tough with card files • What is in stock? – Disparate Access database • What is in the GIS – Does not account for in stock, on order, disposed, etc.
The Old Process • Full time position to
maintain and service any request for info on transformers.
Transformers Purchased • P.O.’s stored in a
book • Notes to determine if
BWP has received • No data backup
Transformers Received • Checks bill of lading
to actual serial numbers. • Sends paperwork
through slow interoffice mail
Test • Tests transformers
and maintains their own paper and electronic files. • Latency of
knowledge when a transformer fails test.
Transformer Invoiced • Legacy mainframe
input so that Finance can pay the vendor. • Uncertainty of BWP
receiving all transformers that have been invoiced.
Transformers Moved • Installations • Removals • Back to stock • Re-Tested
Transformer Disposal • Separate record for
Disposals. • Tough to query or
search the paper. • Cradle to Grave
requirements
The Solution
Out of the Box?
The Solution • Get this guy to use
the computer. • Early Retirement
Incentive?? • Have the responsible
parties enter directly into the related geodatabase tables.
Purchase New Transformer • Engineering or
Purchasing enters the data per specific purchase orders. • Data persists for
multiple entries
Transformers Received • Warehouse Clerk
enters P.O. number and checks off all that he is currently receiving this shipment. • Submit changes the
status to “Test”
Test • Enters all their data
into the Transformer Units Table • Changes status to
“Stock”
Transformer Invoiced • Clerks enters P.O.
number and the find button queries the transformers to find those with status not equal to “Purchased” • All checked of can be
viewed by accounts payable.
GIS Editor Activities • Locate and Relate – Not create related object • Unrelate object – Change field STATUS to removal location. – Transformer remains in units table forever.
Transformer Disposal • Trail is created to
establish if the old unit was sold, recycled, or trashed.
Purchased Transformer Report • What is still on
order?
Transformer In Stock • What is on hand?
The Key Piece Multi-Versioned Views • A part of core ArcSDE – –
Created by the ArcSDE SDETABLE command Enabled on individual tables by an administrator
• Allow direct SQL access to versioned
GeoDatabases
Implemented through database views, stored procedures and triggers – Only available on Oracle and SQL*Server databases –
• Operate on rows for a specified “version” –
Application developer issues SQL Select, Insert, Update, Delete statements as if on a simple DBMS table
The Key Piece Multi-Versioned Views • Creating a Multi-Version View: sdetable -o create_mv_view -T transunit_mv -t transunit -i 5452 -s trinity -u ele -p ele
• Opening a view for edit: EXECUTE sde.version_util.set_current_version('SDE.DEFAULT'); EXECUTE sde.version_user_ddl.edit_version('SDE.DEFAULT', 1);
Review • The Need – –
Disparate databases Large paper trail
• The Old Process – Cumbersome – Low Quality (No domains) • The Solution – – –
Utilize the Geodatabase units table Single source of data High Quality Data (Domain driven)
Questions
