Helpful Hints

By not providing a way to use table hints for spatial indexes in ArcSDE, it seems like ESRI is encouraging developers to write code that directly queries SqlServer tables, bypassing ArcSDE.

More on table hints here.

I wonder if having multiple spatial indexes on a column would make sense. For example, if I find the best index for a point in polygon spatial join differs from the best index for a polygon overlay, I could just have two indexes and specify the appropriate one using a hint, depending on the type of join.

Table hints are usually frowned upon for non-spatial tables, but I think it will be a long time before query optimizers are smart enough to pick the best spatial index.

Is there a PluginWorkspaceFactory for SqlServer 2008?

With SQL Server 2008 supporting spatial types, it seems natural to write Plugin Workspace for it. Why hasn’t this been done? ArcGIS Server (Basic) 9.3 is required if you want to use Sql Server 2008.

Seems like it wouldn’t be hard to write a PluginWorkspace. Intead of storing coordinates in a file, as the SimplePoint example does, it would store a connection string along with a table name or sql expression.

This plugin would allow ArcView 9.2 to view SqlServer 2008 without ArcGIS Server.

For editing, a command could be written that creates a local scratch copy for editing. Then after edits, another command could insert/update/delete based on changes.

Now if only ESRI would provide an alternative to the required COMException in IPlugInCursorHelper.NextRecord() …

Faster Calculations in Arcmap

Somebody has some splaining to do.

I like to think of myself as a patient person, not in a hurry. However sometimes when I use the Field Calculator in Arcmap my patience is challenged.

I have a featureclass of sidewalks related to a street centerline network. 284000 sidewalks and 54000 street centerlines. When I calculate a field on the sidewalks, setting it equal to a field from the street network, it takes several hours. That’s in a file gdb with the key field indexed. Somebody has some explaining to do.

Last time I read ESRI’s EULA, I recall it prohibiting publication of benchmark statistics, so I have not included precise numbers. Looks like they are following Oracle’s policy.

Unlike Oracle, ArcGIS does not allow me to determine what execution plan it is using (see EXPLAIN PLAN). I’m quite certain it always uses the same plan, which in this case is a very poor one.

To make it faster, I cache the key/value pairs from the street attribute table into memory using a System.Collections.Hashtable. This takes about 1.4 seconds. I then open a cursor on the sidewalks table and loop through it, looking up the value from the hashtable using the key field value from the sidewalk featureclass. This takes less than a minute.

Interestingly an update cursor is slower than using a cursor created via IFeatureclass.Search. I think this is just with a file geodatabase though, on ArcSDE I believe an update cursor is generally faster, assuming proper rollback segment sizes are configured.

Maybe what ESRI should do is to beef up the MemoryRelationShipClass so that it allows the user to examine and/or specify an execution plan. That way I could tell it to use a hashtable when it does the join, alleviating me from having to roll my own.

For a good comparison of hashtables, sortedlists and dictionaries, see this post.

The shortcoming of this approach is that I can’t do field calculations, just simple assignment. Some day I’d like to try using CodeDOM to generate code from an expression the user has provided. It would need to substitute in the field values for field names. Since square brackets are used in C# I’d need different field name delimiters.