Virginia GIS Conference

1
Streamlining Data Management Using SDE Spatial
Views
Virginia GIS Conference October 23, 2006
Stuart Blankenship Geographic Information System
Manager
2
Software

• ArcSDE 9.1 (SQL Server)
• ArcIMS 9.1
• ArcGIS Desktop 9.1 (ArcInfo, ArcView)
• 3-D Analyst, Spatial Analyst, Network Analyst
  Extensions
• ERDAS Imagine
• ArcGIS Server (Future Development)

3
Database Management

• Update Data Using Versioned Editing in SDE
• Script Runs Nightly to Reconcile and Post all
  Versions and Compress the Database
• Edits Display on Website by Next Day
• Use SDE Spatial Views to Join SDE Layers and
  Attributes Stored in Separate SQL Server
  Databases
• Allows GIS to Maintain Geography While Other
  Divisions Maintain Attribute Information

4
Versioning at VEDP
5
Automated Reconcile/Post/Compressof SDE Database

• Batch Script Runs Nightly When Usage is Low
• Rename Old Shapefile Exports by Appending Date
  to Name
• Create Backup Shapefiles of Frequently Edited
  Layers
• Alter Main Version to Public for Reconcile/Post
• Run Automated Reconcile/Post Script
• Downloaded from ArcScripts
• Drop All SDE Users
• Delete Versions
• Compress Database
• Create Versions

6
VEDP Central Database
Websites
VEDP Internal Applications
VirginiaScan
VirginiaAllies
GIS
Crystal Reports

• Assures that all VEDP employeesand applications
  are drawing from the same data source
• Helps to prevent duplication of effort between
  divisions
• Allows for better organization of data

Output
SQL Server
Databases
Demographics
Higher Education
GIS Data (SDE)
Sites
Schools
Degrees OfferedEnrollment
PopulationHouseholds
Test ScoresEnrollment
SitesCounties
Sites GeographyCounty Geography
Input
7
SDE Spatial Views

• Join Between Spatial and Non-Spatial Data That
  Takes Place at the Database Level
• Specialized Version of Typical Database View
• Views are Also Created for Feature and Spatial
  Index Tables

8
SDE Spatial Views Versus ArcMap Joins

• Advantages
• Can Take Advantage of Full Capability of RDBMS
  (More Complex Queries Can Be Performed)
• Any Client That Can Consume SDE Layers Can Use
  Spatial Views (Joins in ArcMap Are Saved in an
  .mxd or .lyr file)
• Disadvantages
• More Complex to Setup and Required Some RDBMS
  Experience
• Versioned Feature Classes Require Special
  Treatment (more later)

9
Spatial View Examples

• SDE Layer Joined to Data in Separate
  Geography Database Maintained by Other
  Divisions at VEDP

10
Complex Queries
11
Complex Queries
12
Spatial View Examples

• Allows for Joins to Multiple Records in Other
  SQL Server Databases

13
Spatial View Examples
14
Spatial View Examples Web Applications
15
Creating Spatial Views - SDE Command Line
sdetable -o create_view -T SV_HIGHER_ED -t
"HIGHER_ED,dbo.v_Higher_Education" -c
"HIGHER_ED.SHAPE, HIGHER_ED.NAME,
v_Higher_Education.Type" -w HIGHER_ED.INSTCODEv_
Higher_Education.Instcode -u username -p
password -s server -i sde_instance -D database

• Dont Need to Specify All Columns in Command
  Line
• View Can Be Modified Using RDBMS Management Tools

16
Modifying/Maintaining Spatial Views - SQL Server
Enterprise Manager
Considerations

• Important to Maintain Table Structure
• Select Columns Individually Instead of Using
  Select
• Consider Using Trigger to Notify GIS Staff When
  Records Have Been Added or Deleted

17
SDE Spatial Views and Versions

• Spatial Views Are Not Version Aware and Will NOT
  Pull Data from Adds and Deletes Tables
• SDE Database Needs to Be Fully Compressed
  Regularly Which Will Push All Changes Into the
  Business Table
• Until Compress is Performed, View Will Not
  Function Properly
• Possibly Use SDE Multiversioned Views Which
  Will Allow Access to Multiversioned Business Table

18
Conclusions

• Spatial Views Can Be a Great Tool for Data
  Management If You Need to Pull Together Data From
  Multiple Sources
• Be Aware That There Is Not a Great Deal of
  Documentation ESRIs Support Center Does Have
  Some Good Discussions on the Subject Among SDE
  Users
• If Youre SDE Database Is Versioned and Data Is
  Regularly Edited, Regular Database Compression
  Should Be Built Into the Workflow
• The New Release of ArcSDE 9.2 Will Have an
  Impact on How Spatial Views Are Managed
  (Non-Versioned Editing)

19
New VirginiaScan Interactive Map
20
Thank You
http//www.yesvirginia.org http//gis.vedp.org