ozri 2013 brisbane, australia - geodatabase efficiencies
DESCRIPTION
Walter Simonazzi and Christopher Brown.TRANSCRIPT
Walter Simonazzi &
Christopher Brown
Geodatabase
Efficiencies
Agenda
• Geodatabase design and how it can assist you
• Geodatabase Storage and Migration
• Versioning
– When to use?
– How to manage
• Geodatabase Replication
• Performance and Maintenance
Department of Spatial Awareness
Dave
• New Spatial Data Administrator
• Existing Organisation
Planning Operations Media
Department of Spatial Awareness
Dave
• Lots of Corporate Data stored in “silo’s”
• A lot of project data stored on peoples computers
• No specified data standards
• The data has been stored in SDE
– Where no one knows what is there
– Everyone complains about poor performance
Department of Spatial Awareness
What should Dave do now?
• Identify the workflows in the organisation
• Create a data governance document
– What do the users need to do to make the data useful to
the organisation
• Create a Data Model
• Import the data into the new data model
• Setup an edit workflow
• Manage the ongoing performance of the Database
What’s in a geodatabase design?
Single-user Multiuser
Tools
GIS behavior
Elements
Rules
Geodatabase
Geodatabase functionality
Subtypes
Single-user Multiuser Attribute domains
Relationship classes
Topology Attachments
Default attribute values
Workflow: From design to prototype
Add
data
Obtain
schema
Modify
schema
Prototype geodatabase
Design the geodatabase
What should Dave do next?
• Speak to Barry
• Barry is the Corporate DBA
– It is Barry’s job to make sure that the
databases perform
The key to Enterprise GIS is a
collaboration between the business
systems and the spatial architecture
Diagrammer & Case Tools (free tools)
• ArcGIS Diagrammer is a
productivity tool for GIS
professionals to create, edit or
analyze geodatabase schema.
• Is a visual editor for Esri’s .xml
Workspace Document
• *Supports complex geodatabase
elements such as topology and
parcel fabrics
http://www.arcgis.com/home/item.html?id=5af1723bab4e413bae63dd89daa87984
ArcGIS Geodatabase and CASE Tools
If I build it, they will come
CDB
What’s in the Geodatabase?
What’s in the Geodatabase?
What’s inside the Geodatabase?
What’s inside the Geodatabase?
- Pre-configured table - 370 configuration parameters
Database Server
ArcSDE and the DBTUNE table
Client Translator
Select…
DBTUNE
Repository
ArcSDE compressed binary
• Better performance for advance geodatabase elements • Reduce the storage size in the RDBMS • Difficult to access with SQL commands and third party applications
Spatial field types
• The default format in ArcSDE • Easy access with SQL operators and spatial functions.
Supported Storage types
Migrating spatial types
Supported migration paths
• SDE binary to Geometry • SDE binary t Geography
• SDE binary to ST_Geometry • SDO_Geometry to ST_Geometry
SQLServer
Oracle
Storage and Migration
Put it in the bucket
WS
Like a Version
SHAPE NAME OBJECTID SDE_STATE_ID
1 Riverside NE 1 0
2 Riverside SE 2 0
3 Riverside W 3 0
SHAPE NAME OBJECTID SDE_STATE_ID
SDE_STATE_ID SDE_DELETES_ROW_ID DELETED_AT
Unique object ID
Constant for all versions of a row
Database state
Identifies when row was added
A77
D77
CELL_TOWERS
#
#
#
Like a Version - Adding
SHAPE NAME OBJECTID SDE_STATE_ID
4 4 1
A77
D77
CELL_TOWERS
SHAPE NAME OBJECTID SDE_STATE_ID
1 Riverside NE 1 0
2 Riverside SE 2 0
3 Riverside W 3 0
SDE_STATE_ID SDE_DELETES_ROW_ID DELETED_AT
#
#
#
#
Like a Version - Deleting
SDE_STATE_ID SDE_DELETES_ROW_ID DELETED_AT
SHAPE NAME OBJECTID SDE_STATE_ID
4 4 1
0 2 2
Created at state
Deleted at state
A77
D77
CELL_TOWERS
SHAPE NAME OBJECTID SDE_STATE_ID
1 Riverside NE 1 0
2 Riverside SE 2 0
3 Riverside W 3 0
#
#
#
Like a Version - Updating
SDE_STATE_ID SDE_DELETES_ROW_ID DELETED_AT
SHAPE NAME OBJECTID SDE_STATE_ID
4 4 1
1 4 3
0 2 2
4 Riverside Mid 4 3
A77
D77
CELL_TOWERS
SHAPE NAME OBJECTID SDE_STATE_ID
1 Riverside NE 1 0
2 Riverside SE 2 0
3 Riverside W 3 0
#
#
# Riverside Mid
Versioned Editing Performance
• Performance affected by: – Volume of states
– Stale statistics
– Unnecessary versions*
– Proper workflow
• Manage your version – Reconcile
– Post
– Compress
State 0
Version
pinning
state tree
0
v2
v1
v4 DEFAULT
To Version or not to Version
Version Creation and Management
Who’s afraid of the big bad version?
CDB
What is Geodatabase Replication?
• Geodatabase replication allows you to create copies of data across two or more geodatabases and to keep data synchronized. • Geodatabase replication allows to replicate just the elements you want.
Three types of Replication
Two-way
One-way
Check-out/ check-in
PARENT CHILD Sync both
directions
Sync one
direction
Check-out once
Check-in once
Preparing data for replication
Data requirements
Stored in high-precision spatial format
Registered as fully versioned in 2wR (no move to base)
Non-versioned or fully versioned in 1wR (no move to base)
Must have global ids
The database user must have write permission to the data
Create a Replica
• Distributed Geodatabase toolbar • Create Replica tool • Arcpy
Using Replication
Replica versus Replicant
WS
No LAN/WAN between offices?
Replication using Geodata services
Real time edits
Use Replication to avoid service interruptions
Privileges
• Data set privileges managed from ArcCatalog
Performance Bottlenecks
User interaction
TCP/IP
TCP/IP
ArcMap
ArcMap
Results presented
SQL
SQL
Multiuser geodatabase
DBMS server
Client
interaction Network
Server
hardware
DBMS/
ArcSDE
Attribute indexes
WHERE value > 10
Use Index
Spatial indexes
Select by Location
Use Spatial Index
Statistics
Full Table Scan
Use Index
Small table
Large table
Scheduling tasks
Windows Scheduler
Python Script
arcpy.RebuildIndexes_management… arcpy.AnalyzeDatasets_management…
Responsibilities
Dave Barry
Compress
Analyse
Attribute Indexes Attribute Indexes
Spatial Indexes
Database Statistics
Performance and Management Techniques
Keep on Running
ALL
Summary
• Design and maintain your geodatabase to support your business
processes.
• Every minute you spent thinking about the design of your geospatial
database can save you hours of frustration.
See you at the Booth
* The view of the presenters is not always the views of anybody else
Training
• Building Geodatabases
• Configuring and Managing the Multiuser Geodatabase
• Implementing Versioned Workflows in a Multiuser Geodatabase
• Distributing Data using Geodatabase replication
Training website
http://esriaustralia.com.au/training
Other Resources
• Come chat to us at the Industry Solution booth on the conference
• ArcGIS Resources – Geodatabase Community
http://resources.arcgis.com/en/communities/geodata/
• GIS Wiki
http://wiki.gis.com/wiki/index.php/Main_Page
• Esri Online Training Gateway
http://training.esri.com/gateway/index.cfm
Questions?
Thank you