Technical Tips
Tuning ESRI’s ArcSDE for Map Production
by Michael Black, Senior Consultant/Product
Manager |
Introduction
The Spatial Index
Some Guidelines for Spatial Index Tuning
Displaying Spatial Index Statistics
Updating the Spatial Index
Summary
Introduction
High quality map production is increasingly being undertaken
using automated techniques underpinned by database driven
cartography. Organisations are moving towards platforms which
link map production toolkits such as Geographic Information
Systems (GIS) with spatial data stored in Relational Database
Management Systems (RDBMS). ESRI’s ArcGIS, ArcSDE and
the Mapbook Utility are examples of such tools used by many
organisations involved in map production. Although organisations
invest significant effort in developing automated routines,
attention is not always focused on the performance of the
mapping platform, especially with regard to the speed of accessing
spatial data stored within the RBDMS.
Figure
1: Spatial Vision Mapbook Process
Poor access performance can lead to slow response (screen
refresh) time for data editors and can also decrease the performance
of map output routines based on ESRI’s Mapbook utility.
Tuning the ArcSDE spatial index is one method for improving
access performance in an ArcGIS / ArcSDE/ Mapbook Utility
map production system.
back to top
The Spatial Index
The spatial index for a Geodatabase feature class is used
by ArcSDE to process queries that involve a spatial filter
(for example, panning and zooming). A well “tuned”
spatial index grid can make such operations significantly
faster. This is particularly important for large sheet based
mapping projects where hundreds of map sheets are created
using automated routines.
Determining the optimum spatial index grid for a Geodatabase
feature class SDE layer is an iterative process and depends
on individual feature geometries that may vary from feature
class to feature class. There is no “one” single
rule that can be used to determine the correct spatial index
and as such, a process of investigation and testing is the
most effective way to determine the appropriate spatial index.
Although adequate performance can be achieved on feature classes
with small numbers of features (less than 10,000), even with
an inappropriate spatial index value, performance will decease
as the number of features and layers increase. Optimal spatial
index grid values for feature classes with large numbers of
features (more than 10,000) can significantly improve the
performance of these layers when accessed from ArcMap.
The spatial index grid index is a two-dimensional index that
spans a feature class in a similar way to an index grid in
a normal street directory. Each row in the spatial index table
represents a unique feature/grid combination. As shown in
Figure 1, each feature is “overlayed” with the
index grid and the ID of the feature as well as the grid cell
it intersects with is stored in the spatial index table. Typically,
the smaller the spatial index grid, the more records are needed
in the spatial index table to record these grid/feature ID’s.
Figure
2: Spatial Index Grid
back to top
Some Guidelines for Spatial Index
Tuning
It is important to remember that the aim of a well tuned
spatial index is to deliver acceptable performance for the
end user application which in this example is ArGIS’s
ArcMap. As such, testing and analysis of the performance provided
by various spatial indexes is an essential part of the spatial
index tuning process. The following are some general guidelines
for tuning spatial index grids for feature classes used in
map production:
• For feature classes with large numbers of features
and large extents, don't always keep the default settings
for the spatial index created through the data load process
in ArcCatalog or ArcMap.
• Generally only use one spatial index grid level per
feature class. Although you can have up to 3 spatial index
grids per feature class, high volume drawing tasks almost
always perform best with a single spatial index grid level.
Features are only referenced by one level of spatial index
tiles, small features at the first level and larger features
at the second and third levels. Accordingly, ArcSDE must search
each level of a multi-level spatial index during spatial queries.
To do this, it issues a separate query for each grid level.
As a result, a first level spatial index, even with a sub-optimal
grid size, generally always outperforms multi-level indexes.
• Maintain a high percentage of features wholly in
one Grid (>90% = great, > 80% = good, <80% = change
the index).
• Don't let the Grids/Feature ratio exceed 2, or at
most 4. Ideally, keep this ratio just above 1.
• Maintain the majority of features with <= 4 spatial
index records. This rule is less applicable for feature classes
with small numbers of features (less than 10,000) and variable
geometry sizes.
• Don't let the maximum number of features per grid
exceed 2000 to 4000.
• For unknown or variable data, start by defining one
grid level with a cell size three times the average feature
extent size. Query the feature table to obtain the average
feature size with the following SQL statement:
select (avg(emaxx - eminx) + avg(emaxy - eminy)) / 2 from
f<N>
(where <N> is the layer number of the feature class)
• Keep average number of features per grid between
100 and 300.
• Make the spatial index grid large for ArcSDE layers
that contain point features. Spatial queries generally process
point geometries faster than other geometry types.
• Base the spatial index on the map extent. For example,
if most mapping is between 1:100,000 for a 30km by 20km map
extent, then make sure the spatial index grid for that layer
is somewhere in that range. By doing so, the application is
probably viewing exact entries in the spatial index table.
This helps to size the spatial index table suitably and reduces
the amount of processing because fewer candidate feature IDs
must be evaluated against the feature table
back to top
Displaying Spatial Index Statistics
Within the RBBMS, the SQL statements can be used to create
a quick listing of the spatial index grid values for all layers.
|
select layer_id, table_name
gsize1 from sde_layers
|
For detailed analysis, the SDELAYER command’s spatial
index statistics option, SI_STATS can help you determine optimum
spatial index grid sizes. The syntax of the SDELAYER command
is as follows:
| sdelayer
-o si_stats -D <database name> -u <user name>
-p <password> -i <sde instance> -l <layer
name,spatial column> |
For example, to report the spatial index statistics for the
feature class Transport_vdr_roads:
| sdelayer
-o si_stats -D gis -u sde -l Transport_vdr_roads,shape
|
Figure
3: Spatial Index Statistics
In this example, the feature class has a spatial index of
2407 (the default created when the data has been loaded from
ArcCatalog). This default index is reasonable as the grid/feature
ratio is close to1. However, the average and maximum number
of features per grid are very low and it would appear that
the grid size is too small for the optimal view extent and
scale. In this example, we will increase the spatial index
to 10,000 and re-evaluate the statistics.
back to top
Updating the Spatial Index
The spatial index grid for an SDE layer can be altered using
the SDELAYER command with the ALTER option.
sdelayer -o alter -D <database name> -u <user
name> -p <password> -i <sde instance>
-l <layer name,spatial column> -g <grid size>
|
After changing the spatial index grid, we then analyse the
new statistics
sdelayer
-o si_stats -D gis -u sde -l Transport_vdr_roads,shape
|
Figure
4: Updated Spatial Index Statistics
As we can see, changing in spatial index grid has improved
the average and maximum features per grid values and maintained
an acceptable grid/feature ratio. Further testing in ArcMap
confirms improved drawing performance based on the new spatial
index value.
back to top
Summary
The performance of map production system based on ArcMap,
ArcSDE and the Mapbook utility can be improved through well
tuned ArcSDE spatial index grid values for large feature classes.
This is particularly important when producing large numbers
of map sheets using automated routines. We have found that
the performance and stability of ESRI’s Mapbook utility
increases when accessing feature classes with well tuned spatial
index grids.
Although determining the optimal spatial index for a feature
class is an iterative process, the guidelines outlined above
provide a starting point.
For further information about how Spatial Vision may assist
you, please contact:
Michael Black (03) 9691 3000
email: michael.black@spatialvision.com.au
back to top
Copyright © Spatial Vision, Wednesday, 13-December-2006 |
