Spatial Views stopped working when we upgraded to 10.1

[JoeWeyl]

Hello -

We are a data warehouse environment where we have both business and spatial data inside the same Geodatabase, but the business data is not managed by GDB/SDE. We did that as a design decision, and it was backed up by Esri technical staff. Anyhow, in order to support our GIS applications we have built our own custom Spatial ETL using Python and ArcPy, then at the end of the process the data is loaded to ArcSDE and all of our business and spatial data is joined via a spatial view to be consumed by ArcIMS, ArcGIS Server and .NET. This code has been running for years, and with each upgrade we have handled the changes for Python and ArcGIS Desktop as they came out. ArcGIS 10.1 has thrown us a wrinkle that is killing our process, and it happens in two ways:

1) Our code is taking much longer to process the data, from minutes in some Feature class to HOURS. This is counterproductive to the idea of an upgrade and we will be reviewing the changes in ArcPy from the two versions to see where some code issues might be occurring. If we find any that will go to the Geoprocessing forum.

But the second issue 2) is the one that is really hurting us. When we load the new refreshed data using our process to ArcSDE using direct connect (as we have for years, like since ArcGIS 9.0 as SDE services, then direct connect) the views stopped working. In ArcCatalog or ArcMap it makes the views look like they are empty, or if they draw, you can't zoom in, out or pan because the features, if present disappear. We have tried rebuilding the spatial indexes, no change in behavior. We have tried recreating the views and relaoding them with our code (this test is still pending). I have done traces on the queries that get generated when you view a spatial view, and I can capture that the ArcSDE sp's that call, join etc the data show up in SQL Server. But the ArcGIS tools are not seeing them, or displaying them. As I am writing this, I am wondering if it is a client issue, but since we use both ArcGIS Server and ArcIMS I am trying to rule that out. Anyhow, it will be no small feat for us to have to recreate all of our Spatial Views across all of our data marts, especially if the next day's data load produces the same problem.

so I am wondering, has anyone seen this type of erratic behavior from ArcSDE/ArcGIS after upgrading to 10.1 with Spatial Views? What GDB changes were made that may have affected this?

So like I said before, we are SQL Server 2008 R2, ArcGIS 10.1 shop, and Python 2.7.

Thanks,
Joe

[MarcoBoeringa]

In all cases the data are (supposed to be) WGS84.  We are relying on the Esri tools to correctly detect and handle that during data migration.

...

The issue we face now is that writing data to the new databases is unbelievably slow- only a few features a second when writing polygons averaging 2,100 vertices per feature- and we have millions of features that have to be written.  We can't possibly get the job done in a time frame that allows us to maintain our regularly daily data update production schedule.  (Joe alluded to this in the first message in this thread;we are pretty sure now that the GEOMETRY data type is the culprit based on other threads that we've read.)

Vince (and Keith): you are noticing that ArcGIS may actually be performing a full re-projection including datum transformation during the load using these 2100 vertices / feature datasets with "millions" of features? What would be the consequences of that during the data load?

I have no real-world experience with these numbers to share... but I would sure love to hear the answer!

I also wonder what happens if the data is just loaded like it is, with the projection of the Feature Class set to that of the data, to avoid re-projection.

EDIT: I now see Vince reported 1700 features as point features, so loading 1700 individual vertices / second on a laptop with encryption into SQL Server GEOMETRY... and 113 features per second with 2100 vertices per feature = +/- 240K vertices per second for the generated circles.

The data load of 14700-18900 vertices / s on real server hardware may not be that unexpected than... depending on the hardware, network and other possible issues (reprojection going on).

[VinceAngelo]

I doubt reprojection is occurring, and if it is, then the SDEBINARY layer would be working under
the same handicap (though any host of greater capability than my toaster-oven should be able
to reproject more than a couple of 2100-vertex shapes per second).

I will say that you don't want to be doing a simple append when there are millions of features
involved.  Array-insert enabled steam on a load-only I/O layer is certainly called for by this
situation.  I regularly load 8 million GeoNames points in under 20 minutes on my servers;
I wouldn't want to waste my time with a simple insert with a trivial commit interval.

- V

[KeithAdams]

Hi-

Thanks Vince.  Is it possible to accomplish the same task, at similar speeds, using the geoprocessing tools provided with ArcGIS Desktop?  We have a bunch of tables and views scattered across a half-dozen databases, and I'm trying to do as much of the work as I can in a testable, repeatable fashion with a Python GP script.

If I have to rely on the SE_TOOLKIT, what tool do I use to transfer data from one SQL Server-based feature class to another?  The sdecopy tool comes back with a "Not yet supported" message... so I'm kind of at a loss as to how to get from one SDE / SQL Server feature class to another with the toolkit.

Thanks-

-= Keith Adams =-

[VinceAngelo]

The workaround for a missing 'sdecopy' is to export via 'sdequery' and pipe the result into 'asc2sde':

set TZ=GMT
set SDEUSER=username
set SDEPASSWORD=passw0rd
set SDEDATABASE=arcgis
set SDEINSTANCE=...
sdeping
sdequery -t mylayer -f -,CTL=mylayer.ctl -w 1=0
sdequery -t mylayer -f - | asc2sde -o create -f - -C mylayer.ctl -l newlayer,shape -g automatic -k keyword -v -vI 1m -c 2000

Note that 'asc2sde' is not a geodatabase-aware tool, so using GP tools is the "best" way (but it's very
possible that 'asc2sde' will be more time-efficient for simple feature classes -- it once added 680 million
points to a simple point table in 20 hours [actually, it took that long in each of three different RDBMSes
on identically configured PCs]).  I haven't tried comparing my loaders to the new da cursors at 10.1.

- V

[KeithAdams]

Hi Vince-

Thanks for the extended example.  Since asc2sde is not geodatabase-aware, I assume that I'll have to find a way to register the results (and get them into the desired feature datasets) separately?

[MarcoBoeringa]

Hi-

Thanks Vince.  Is it possible to accomplish the same task, at similar speeds, using the geoprocessing tools provided with ArcGIS Desktop?

You probably already saw it, but have a look at my post in the other thread you posted in:
http://forums.arcgis.com/threads/87986-Spatial-View-so-slow?p=313416&viewfull=1#post313416

[MarcoBoeringa]

Hi Vince-

Thanks for the extended example.  Since asc2sde is not geodatabase-aware, I assume that I'll have to find a way to register the results (and get them into the desired feature datasets) separately?

10.1 extended upon the number of tools you can use to administer a (geo-)database, and you can register datasets or enable geodatabase functionality easily from the toolboxes and geoprocessing tools available.

Also, you can use Query Layers to access the data in read-only mode directly, without the need of registering the data with a geodatabase.

See also my PDF here:
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=295462&viewful...
and the "Future" PDF here:
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=303021&viewful...

[VinceAngelo]

Hi Vince-

Thanks for the extended example.  Since asc2sde is not geodatabase-aware, I assume that I'll have to find a way to register the results (and get them into the desired feature datasets) separately?

The ArcSDE 'C' API doesn't interact with feature datasets at all (they do not exist in that API).
Any table created by 'C' or Java API functions will register the table with ArcSDE, but not with
the XML geodatabase (right-click "Register with Geodatabase" will still be available). ArcSDE
SDK functions work fine with tables that happen to be in feature datasets, BUT:

• They will not honor topologies


• They're unlikely to handle CAD objects properly


• They will not modify geodatabase metadata

In addition, the 'se_toolkit' tools will not insert, update, or delete within versioned tables
or tables where archiving is enabled, or interact with ArcGIS-only raster types.  Finally,
the ArcSDE 'C' API will be deprecated with the release of 10.2, so long term plans for
toolkit use should be limited.

You should really determine where your I/O bottleneck is, since there isn't any clear
indication of why your database is so slow.

- V

[KeithAdams]

10.1 extended upon the number of tools you can use to administer a (geo-)database, and you can register datasets or enable geodatabase functionality easily from the toolboxes and geoprocessing tools available.

Also, you can use Query Layers to access the data in read-only mode directly, without the need of registering the data with a geodatabase.

See also my PDF here:
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=295462&viewful...
and the "Future" PDF here:
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=303021&viewful...

Hi-

This is a data migration exercise at this point--- I am "just" (note caveat below) trying to get existing data from databases that have been through three SQL Server version updates as well as at least two (maybe three) ArcGIS platform upgrades, into fresh, shiny, unadulterated 10.1 / SQL Server 2008 R2 native data.  At the end of the process, the data have to look exactly as they did when I started, except for having all the shape data in SQL Server GEOMETRY instead of SDE_BINARY.

(Any time someone uses the word "just" in such a context, it's a tip-off that they are not entirely aware of the implications of what they are saying.  I include myself in that, in this context.)

I've already discovered that I have to use the SDE command line tools to get spatial views to be included in SDE_LAYERS, without which the Spatial Join cannot use them.  The "Register with Geodatabase" tool will not register views.

In my question about asc2sde, I was merely trying to ascertain and verify that I would have to take some other measure to get that to happen for data transferred by the method Vince outlined.

-= Keith =-

[KeithAdams]

The ArcSDE 'C' API doesn't interact with feature datasets at all (they do not exist in that API).
Any table created by 'C' or Java API functions will register the table with ArcSDE, but not with
the XML geodatabase (right-click "Register with Geodatabase" will still be available). ArcSDE
SDK functions work fine with tables that happen to be in feature datasets, BUT:

• They will not honor topologies


• They're unlikely to handle CAD objects properly


• They will not modify geodatabase metadata

In addition, the 'se_toolkit' tools will not insert, update, or delete within versioned tables
or tables where archiving is enabled, or interact with ArcGIS-only raster types.  Finally,
the ArcSDE 'C' API will be deprecated with the release of 10.2, so long term plans for
toolkit use should be limited.

You should really determine where your I/O bottleneck is, since there isn't any clear
indication of why your database is so slow.

- V

I'm not writing any C code directly, so the C API doesn't enter into it from my side.  It's either command window or Python, so whatever theSDE command line tools and se_toolkit offer is what I have to work with, along with the other geoprocessing tools available through arcpy.

None of our data are versioned, CAD-based, archive-enabled, or raster.

As for the postulated I/O bottleneck: we only see this issue when copying data that are being converted from SDE_BINARY to SQL Server GEOMETRY.