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]

Finally,
the ArcSDE 'C' API will be deprecated with the release of 10.2, so long term plans for
toolkit use should be limited.

There is a little bit more breath here, it is supposed to be deprecated after 10.2 according to these deprecation plans, so with the release of ArcGIS Professional Application (ArcGIS 11 probably).

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

x2, but maybe also give some more information about the configuration details. What hardware is the server? What network bandwidth do you have (still 100Mbit/s or Gigabit?) etc...

[VinceAngelo]

Deprecation is public declaration of the intent to stop supporting something, so deprecation
"at 10.2" means it won't be around with whatever follows 10.2.  Deprecation "after 10.2"
would mean two more releases, which is not the case.

- V

[VinceAngelo]

I'm not writing any C code directly, so the C API doesn't enter into it from my side

This, unfortunately, is not true.  Knowing that 'se_toolkit' and the ArcSDE command-line utilities
are written using the 'C' API, not ArcObjects, gives you an indication of what geodatabase-ish
things are possible.  This dichotomy, and the confusion it engenders, is one of the reasons why
the API has been deprecated.

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.

Which is why I provided the circles.ctl file, so you could further characterize the nature of your
performance issue.  If your brand new server doesn't get at least the performance of an older
laptop with disk encryption, with both SDEBINARY and GEOMETRY storage, then you need to
review the system configuration with an expert.

- V

[MarcoBoeringa]

Deprecation is public declaration of the intent to stop supporting something, so deprecation
"at 10.2" means it won't be around with whatever follows 10.2.  Deprecation "after 10.2"
would mean two more releases, which is not the case.

Ah, well, there you go if something is not your mother tongue...

but anyway, the explanation you gave, is how I interpreted the deprecation plans too, and what I intended to say in my post: 10.2 as the last release to include the ArcSDE Command Line tools and C/Java API.

[KeithAdams]

... x2, but maybe also give some more information about the configuration details. What hardware is the server? What network bandwidth do you have (still 100Mbit/s or Gigabit?) etc...

So I thought I had responded to this but I don't see it.

When I ran the code from my workstation (which has a 100 MBit network card and probably some other network bandwidth throttling that I can't see; it's Windows 7 with 4 GB of RAM and a bunch of other things running), it was taking 30 - 40 minutes to copy 4800 polygon features.

When I ran the same code from a server, which has a gigabit connection, it took about 1.5 minutes.  Problem solved, or at least worked around.  Not sure how the customer's going to feel to have me using their servers as if they were my private workstation, but it will at least enable me to complete the task before I reach my retirement date a few years hence.

So now here's a new conundrum: why is it that if I point Catalog at a table-based feature class and choose "Preview" it takes up to 30 seconds to get a response (there's even a several-second lag time before the cursor changes or any other visible reaction takes place), whereas feature classes built as spatial views connecting to the same underlying data respond within a second or two?

-= Keith =-

[KevinGooss]

At 10.1, it is normal for a Spatial View, or for that matter any table or database view with a spatial column, to initially show up as a table. Once you click it, ArcGIS will read the first record(s?) and determine the geometry type and show it in the catalog tree.

This behavior is because ArcGIS can't "know" the feature/geometry type beforehand, as it isn't "stored" like in a table registered with an ESRI geodatabase, where such system metadata is stored in the ArcSDE Repository.

Here is the issue I am seeing. I can create a spatial view and it renders fine in catalog and ags. But if i modify the sql OR if a situation arises that makes my view devoid of records then all of the sudden sde and ags get really 'stupid' and have no clue what my geometry type or coord sys are for that view.

Am i to believe that a spatial view MUST always have at least one record in it?
I work with data that expires after a certain time. So as time elapses records become removed from a view such that at some points in time there will be no data that satisfies the view. When that happens everything goes downhill.

[VinceAngelo]

Since this is a very different issue, you should post it as a new thread, with all the software and
version information.  You should explicity state what type of connection is in use (e.g., Query Layers
vs Direct Connect). Referencing this thread wouldn't hurt, but the post should be self-contained.

- V

[SaraShullaw]

Hello, we are experiencing the same issue with spatial views becoming corrupt (i.e. some or all features missing) after appending new data. We are still using ArcGIS 10.0 gdb so I don't believe this issue to be 10.1. The issue seems specific to a very large polygon feature class with a global extent. We've been using ArcGIS 10.0 gdb for years as well as the ETL process that deletes and appends new features. This is the first we've seen this problem.

Did you ever find a solution to your problem?

Thanks