Mark Harley
The new Arc 8 software offers the opportunity to add tools and functionality to existing ArcSDE systems. This session will explore ways to transition the data in existing SDE environments into the new Arc 8 Geodatabase environment with a minimum of cost and frustration. Tips and techniques will be described to assist with performing a trouble-free upgrade of existing datasets to allow maximum use of the new Arc8 tools, including ArcMap, ArcCatalog and ArcToolbox.
Most experienced SDE users have heard about the new "Arc8" technology, and planned to take advantage of it. This paper covers the basic steps of converting an SDE version 3.x database to use the new technology, and should help with the transition. Despite the initial effort, it is most likely a worthwhile exercise. ArcSDE 8 will do most everything earlier SDE versions have done, with numerous performance improvements and added features. In addition, one can take begin to take advantage of the extensive Arc8 tool set, and the new data modeling opportunities offered by the Geodatabase structure.
If you have an existing SDE 3.x database, it can be fairly easily migrated to the ArcSDE 8.x environment. For those who remember the transition from SDE 2.x to 3.x, this is a welcome change. Back then, it was necessary to completely reload the SDE tables due to significant data structure changes. There was a huge payback in the way of increased performance, but it was painful to rebuild larger databases from scratch. With ArcSDE 8 there are numerous data structure enhancements and new tables added. The core structure of SDE version 3 is still there, however, making the initial transition much easier. There is no need to reload the database.
The following diagram shows, in simple form, the basic RDBMS table structure that is in place before starting the conversion to ArcSDE version 8. There are two system tables stored under the "sde" user, and three tables per data layer residing in one or more user areas. In this example, two layers are shown, ROADS and LAKES. These tables will remain, with some modifications, as part of the ArcSDE 8 data structure. A large number of new tables will also be added.
The initial releases of ArcSDE, versions 8.0.1 and 8.0.2, are available for either of two relational databases, Oracle or Microsoft SQL Server. There is an Esri "Installing ArcSDE" guide available in PDF format on the distribution CD for each database. One should follow the detailed instructions in that document to perform the installation. The basic steps to follow include:
(*) Note that when installing for SQL Server creation of the tcp service and configuration of the license manager are performed as part of the ArcSDE install process.
If you are converting an SDE 3.x database, there is one additional step which must be performed prior to launching the new ArcSDE 8 server software. The VERSION and LAYERS tables shown in the preceding diagram must be updated to Arc8 format, and additional tables must be created. Fortunately, Esri has provided a utility to do this automatically. Before performing this step, be certain to have a backup of all of the old tables, just in case something goes wrong.
The utility to run is called "sderelease", found in the bin sub- directory under SDEHOME. The command syntax looks like this:
sderelease -o upgrade -p db_admin_password_here
"sderelease" will upgrade the SDE 3.x system tables LAYERS and VERSION, and will add the ten new ArcSDE 8.x system tables as shown below. Note that these are the Oracle table names. When using SQL Server, each of the system table names, including LAYERS and VERSION, will be created with a prefix of "SDE_". Also, there is a new table called VERSIONS, which is separate and distinct from the VERSION table. VERSION stores a single record identifying the version of (Arc)SDE server software that is compatible with the database tables. The VERSIONS table is one of a series of tables which deal with versioning data. That is, allowing users to create and edit different named versions of the same base geography.
The GEOMETRY_COLUMNS and SPATIAL_REFERENCES tables are created by "sderelease" and populated according to any previous contents of the LAYERS table. These tables are used by ArcSDE to support OGC, that is, Open GIS Consortium, Inc. compatible queries. All of the additional tables added to the SDE system area are used to support new ArcInfo 8 functionality.
A quick synopsis of the tables added / modified:
Once sderelease has been executed, it should be possible to bring up the ArcSDE 8.x server and begin working with the new software. The server is ready at this time to begin delivering data to ArcSDE clients, with similar functionality to using SDE version 3.x. Most likely though, the next step, creating the Geodatabase Metadata Tables, should be followed to fully prepare the database for use with the new Esri client-side utilities: ArcCatalog, ArcMap and ArcToolbox.
To prepare the new ArcSDE server for storing Geodatabase Feature Datasets, it is necessary to create the Geodatabase Metadata Tables. This is easily accomplished with the "gdbs" utility, provided in the "tools" subdirectory under SDEHOME. When creating a Geodatabase for the first time, including conversion of older SDE 3.x data, the first argument should be CREATE, as in this example:
gdbs CREATE server_name sde_service_name sde_admin_password [database_name]
The "database_name" argument is used when converting a SQL Server database, but not with Oracle.
When upgrading to a newer ArcSDE 8.x release, the first argument should be UPDATE, instead of CREATE. Some early versions of the Esri documentation did not mention the UPDATE parameter.
Once the "gdbs" command has executed successfully, the following twenty-five tables are added to those already present in the SDE system area. The ArcSDE server is ready to begin working with with the full set of Arc 8 features.
A quick synopsis of the Geodatabase tables:
After running the gdbs command, each layer from the previous SDE 3.x database should be registered as a Geodatabase. This is done from inside ArcCatalog. Use the left mouse button in the ArcCatalog Tree Pane to select the layer (now known as a Standalone Feature Class.) Open the right mouse click pop-up menu from the ArcCatalog Tree Pane and select the "Register with Geodatabase" option, as shown below.
With ArcSDE 8.x running, and at least one layer of data present, the server is ready to begin interacting with the ArcInfo 8 tools. There are some steps that can be followed to improve the performance, enable new Arc8 features, and add useful information to assist subsequent users. A few suggestions are included in this section, along with some things to watch out for during the process of beginning with Arc 8. Given the extensive set of enhancements to the tool set and spatial data modeling capabilities in ArcInfo and ArcSDE 8.x, the possibilities for experimentation are nearly endless.
ArcCatalog has the ability to remember ArcSDE connections, storing them with or without usernames and passwords. The advantage here is that you can set them up once, and don't need to remember the details of each connection at subsequent accesses. In the catalog tree pane, usually found along the left side of the ArcCatalog window, is a hierarchical display of disks, folders and geographic data. Below the physical disks, is a special folder icon called "Database Connections." One subcategory under Database Connections is an icon called "Add SDE Connection," as shown below.
Double clicking on Add SDE Connection will bring up a dialog box where the connection can be defined and saved. The Server, Instance, and Database must be entered, followed optionally by the User Name and Password. (The Database parameter is only used with SDE for SQL Server or Sybase.) If the User Name and Password can left blank, they must be reentered each time the connection is reestablished. This provides added security, at the expense of some convenience.
Be aware that entering this information into the Database Connections dialog does not guarantee that the parameters are correct. It is possible to enter data in the fields and click "OK" leaving an invalid connection defined. This is because the SDE database is not actually opened until it is browsed; an error would result at that time. The SDE Connection dialog box does have a button labeled "Test Connection." It is recommended that this be used whenever a connection is defined or modified to verify it as usable. Notice in the previous picture that the connections to systems angola and croatia have small red X's next to them, indicating that they are defined, but not active (and possibly not even valid in the current network context.) The connection to system markh30632 is active and open, showing some geography and tables that can be browsed. There is no red X next to this server name.
After converting some data from SDE 3.x to ArcSDE 8.x, the geography may be represented more uniformly in different applications if some Arc 8 Layers are defined.
The Arc 8 system defines the term "Layer" in a new way. In SDE version 3, an "SDE Layer" is a collection of geographic features, grouped and stored together. The first figure in this paper shows ROADS and LAKES as two distinct SDE Layers. In ArcSDE 8, the SDE Layer entity is referred to as a single Feature Class. Old-style ArcInfo Coverages and Shapefiles are now also referred to as Feature Classes. In ArcSDE 8, the newly defined "Layer" does not contain the physical geographic features itself. Instead, it references a Feature Class and defines characteristics about how it is represented when displayed or printed.
An example may help to clarify this distinction. Previously, when adding a shapefile as a theme in ArcView, one would typically want to describe how that data would appear. Specifically, if the shapefile contained a road network, classified by road type, one might want to symbolize the limited-access highways as thicker green lines, state highways as medium-width red lines and more local roads as thinner black lines. The method of data presentation would be set up in ArcView and applied to the theme. Each time the ROADS theme was displayed, the theme properties would cause the lines to be drawn appropriately, based on the road type. These theme properties were saved as a part of the ArcView project file, and could contain many more characteristics than line color and line width. If that same raw road network shapefile was inserted as a theme into a different ArcView project, the street representations would have to be set up once again, in a way appropriate for the second project.
With the new ArcMap, the same thing can be done with a raw shapefile. That is, it can be inserted on the map as a new layer, and the symbology can be defined describing how that shapefile will appear. These layer characteristics would be saved in the specific ArcMap "Map Document," just as theme properties were saved with an ArcView project. However, the ArcMap Layer can also be written out to a "Layer File," usually named with a .LYR extension and shared with other maps. The .LYR file does not contain the data itself, but rather it points to a data source and describes all of the characteristics of how that data is to be represented. Arc 8 Layers are separate entities that can be browsed and viewed -- and even created -- with ArcCatalog, and inserted into ArcMap. It is entirely possible to define several different Layers, each of which points at the same underlying data, or Arc 8 Feature Class.
A significant advantage of the Arc 8 Layer concept is that when appropriate, the representation for a Feature Class can be defined, and then reused in many different maps. Often, there is considerable effort involved in setting up the symbology; it need only be done once. The fact that it can be maintained in a single, central .LYR file means that each of the maps which reference that layer can benefit from improvements made to the layer definition. An individual or department responsible for the data in the Feature Class can define how their data is best rendered and all subscribers can benefit from a consistent representation. Of course, when central control of a map layer is not desired, individual symbolization can be saved locally in the Map Document.
Thumbnails are small map images, which provide a basic idea of what a geographic data source is like. They are shown when browsing Feature Datasets and Layers with ArcCatalog. Thumbnails are pre-calculated, fixed, image snapshots that draw quickly because the actual geography is not retrieved each time they are needed. These static images are not created automatically. It is up to the user to build thumbnails manually for Feature Datasets that are converted from SDE 3.x data layers.
If a thumbnail exists for a data source, ArcCatalog will show it on the first page of the Metadata tab, and when specifically viewing items by thumbnail in the Contents tab. If no thumbnail has been created, the Contents thumbnail display will merely show a generic icon representing the data type (indicating a data source of points or lines or a raster image, for example.)
In the absence of a thumbnail, the only way in ArcCatalog to see an image of a data source it to actually retrieve the geography and draw it, using the Preview tab. Preview mode retrieves and draws the entire layer, zoomed out to its full extent. When examining an ArcSDE layer containing millions of features -- all roads for the entire United States, for example -- it is impractical to attempt to draw the complete layer. Though ArcCatalog will stop the attempted Preview when the ESC key is pressed, no quick data view is available unless a thumbnail exists for that data source.
A thumbnail is easily created in ArcCatalog by selecting a data view with the Preview tab, optionally panning and zooming to a representative area (unless the full extent is desired,) and pressing the "Create Thumbnail" button. Note that if the data or symbology represented by a thumbnail changes appreciably, the thumbnail should be manually updated. A thumbnail can be deleted using the metadata editor, by removing the "Browse Graphic." The exception to creating thumbnails manually is when ArcMap creates a Map Document. ArcMap automatically generates a thumbnail image as part of the .mxd file when a Map Document is saved.
The following diagram shows four thumbnail images, displayed under the Contents tab in ArcCatalog. Notice that the ROADS and Cellular Towers Feature Datasets have not had a thumbnail created. They are showing the ArcCatalog default icons for a line and point Feature Dataset, respectively. STATES and Northeast States are two layers for which thumbnails have been defined. Notice, for example, that the STATES image was zoomed in to show only the "lower 48" states, rather than including Alaska and Hawaii which are included within the total dataset extremes.
The concept of metadata, or "data about data" is complex. There are numerous metadata standards; the importance and level of use varies widely from site to site. Metadata usually describes both the parametric properties of a data source, and documentation about it. For example, metadata could contain the geographic extremes parameters for a dataset, and the name of the individual who created it, with a brief text description of the geography. Included with ArcCatalog are three different stylesheets for viewing metadata. There is an Esri presentation, a raw "XML" format view, and an "FGDC" (Federal Geographic Data Committee) version. The latter follows the US federal "Content Standard for Digital Geospatial Metadata" and includes indicators of which fields are required to be populated to meet the standard. The user may also define their own metadata view for ArcCatalog to use.
When converting a data source from SDE 3.x to ArcSDE, some metadata fields are filled in automatically by ArcCatalog the first time the Feature Dataset is browsed. The user must add other information, if and when desired. For example, the "Bounding Coordinates in Decimal Degrees" are transferred from SDE 3.x and automatically added to the metadata in ArcSDE, stored in the table GDB_USERMETADATA. If the user wishes to manually fill in additional fields, an editor can be launched by pressing the "Edit Metadata" button on the ArcCatalog metadata toolbar. The user can substitute their own editor, in the event they wish to change the type of metadata to record.
Metadata is saved in XML format, whether created for an ArcSDE Feature Class, or a traditional format such as a shapefile. When, for example, metadata is created for the shapefile "ROADS.SHP", a new XML text file is created in the same directory, named "ROADS.SHP.XML". In the ArcSDE Oracle table GDB_USERMETADATA, the metadata is stored in a LONG RAW field named XML. In ArcSDE for SQL Server, the XML field is a VARBINARY type. If a thumbnail image has been manually created for the data source, that image is stored as part of the XML data.
Versioning is an important, major new feature of the ArcSDE software. ArcSDE 8 allows users to create multiple, persistent views of the database, without making a full separate copy of the information for each view. This allows different users to simultaneously edit any part of the data without interfering with one another. Conflict detection and resolution software exists within the system to aid in merging separate versions, in the event that two or more users modified some of the same feature(s). Before versioning can be used on a feature class, it must be registered with the versioning system. This is done in ArcCatalog by right-mouse-clicking on the feature class, and selecting the "Register As Versioned" option from the menu.
Versions can easily be created using ArcCatalog, by the methods described in the Arc 8 documentation. (Note the version "Change" button in the earlier Sample Database Connection dialog box.) However, there are two significant independent tasks to undertake to make good use of the versioning capabilities. The first, is to learn the mechanics of how Arc 8 versions work and the functionality that Esri provides. The second, and probably more important, is to apply that knowledge to a specific situation. One must evaluate the people, tasks, and work flow where versioning is to be used and define how it will actually be deployed. Questions to answer include how many versions will be created? Who will control them? How frequently will they be posted back to the master database? What will each one contain? Who will be permitted to create additional versions? Who will be responsible for general management of the versions?
This paper will not attempt to even begin to be a tutorial on how to design and implement a complete geodatabase model for a GIS system. Because the concept of a Geodatabase has not existed prior to Arc 8, previous data does not readily -- or automatically -- import directly into a full object-relational model. New, custom feature types may be created with user-defined connectivity and behavior. Note though, that "old" SDE 3 data layers, coverages or shapefiles converted and loaded into a full version and gdbs-enabled, ArcSDE 8 database are in fact, geodatabases. When an independent SDE 3.x layer is imported into ArcSDE 8, it arrives as a stand-alone feature class as part of the new geodatabase. These approximate steps should be followed to add more advanced Geodatabase functionality to the older, newly imported data:
When a Geodatabase is created, it seems fairly obvious that the definitions of objects, relationship classes, associated metadata and so forth are stored in some of the twenty-five new database tables with the "GDB_" prefix. There is additional information created as part of the overall Arc8 system that is not specific to a Geodatabase. For example, database connection definitions, thumbnails, metadata for shapefiles, and layer definitions are preserved. ArcCatalog retains aspects of its layout and presents them to the user in the same way upon subsequent execution.
The database connection information, and the general state of one's personal ArcCatalog setup are saved separately by user. This means, for example, that multiple users of the same Windows client must each SEPARATELY define the database connections they wish to browse. On Windows/NT version 4, they are saved with the User Profiles in a directory called (presuming the operating system is installed on the "C:" drive:)
C:\WINNT\Profiles\user_name_here\Application Data\Esri\ArcCatalogBe aware that ArcCatalog is not simply a "passive" browser! Numerous files (.xml, .aux, ...) will begin appearing in various directories, just because someone happened to view items in a certain way using ArcCatalog. For example, browsing a picture (such as a .jpg file) or a table will cause a .aux file to appear when either the Contents or Preview tab is selected. A .xml file is created automatically when the metadata tab is selected (though this can be disabled with a set up option.) Fields in this metadata .xml file are immediately populated by ArcCatalog with the information it finds already available to enter.
A few pitfalls to be aware of during the transition to Arc 8:
To summarize the steps one might follow to move from their existing use of SDE 3.x to the new Arc 8 environment:
Mark Harley
Principal Engineer, Geographic Data Technology, Inc.
11 Lafayette St.
Lebanon, NH 03766-1445
Telephone: (603) 643-0330
Fax: (603) 643-6808
E-mail: mark_harley@gdt1.com
Web: http://www.geographic.com