Spatial Views stopped working when we upgraded to 10.1

JoeWeyl · ‎05-17-2013

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 · ‎05-17-2013

You don't state from what version you are upgrading (9.3? 10.0? what service pack?), and whether or not you installed SP1 with 10.1 and any applicable patches.

Anyway, I do know that Multiversioned Views changed from 10.0 to 10.1. They are now not only called "Versioned Views", but the associated stored procedures and functions in the ArcSDE Repository also underwent changes. Versioned Views now are also *fully automatically* created as soon as you register a layer as versioned.

These changes have to do with the introduction of the ArcGIS Spatial Data Server, and a general better possibility to use - and edit - these views programmatically and through SQL or programming. There is now a new editing option that allows you to edit the DEFAULT version through a Versioned View (actually, this last thing is not entirely new but had issues at 9.3.1 and below, and only now seems fully implemented).

This 10.1 Help page talks of the need to re-create Multiversioned Views 10.1 as Versioned Views in order to be fully useable (see one of the "Note" remarks), so this change may have effected your Spatial Views (assuming they used Multiversioned Views), and they may indeed need to be re-created.

I would really try and see if you can create a new test dataset and 10.1 style Versioned View by importing a layer, and registering as versioned. Than use the Query Layer option of 10.1 to add the data to ArcMap, or simply browse the database connection in ArcCatalog and add from there. If that layer with its 10.1 Versioned View still doesn't display properly, there may be a deeper underlying issue. If it does display properly, than you know it's not the database connection or Versioned View functionality by itself, but maybe something with your "legacy" Spatial Views.

JoeWeyl · ‎05-17-2013

Hi -

Thanks for the reply. We upgraded from ArcGIS 10, SP 5 to ArcGIS 10.1 SP1 on SQL Server 2008 R2. No multi-versioned views, we don't even use a lot of Geodatabase behavior, simple feature classes (points and polygons) are that types of data we create. Since they aren't versioned views I am not sure what benefit that would get us, but we had thought about creating a 10.1 Geodatabase and then migrating all of our data to it, and run our code against it and see if the behavior repeats. That might be a weekend test.

Joe

MarcoBoeringa · ‎05-17-2013

What happens when you create a new spatial view using the options now available in the context menu of a Database Connection as described here?:

Creating a database view in ArcGIS for Desktop

You may also wish to use this page as a quick reference for what to do when attempting the option described in the link above:

Example: Creating a spatial view in SQL Server using SQL

By the way, did you switch storage type (SDEBINARY to SQL Server native "Geometry")?

JoeWeyl · ‎05-20-2013

What happens when you create a new spatial view using the options now available in the context menu of a Database Connection as described here?:

Creating a database view in ArcGIS for Desktop
The data shows up as a table, no spatial rendering occurs.

You may also wish to use this page as a quick reference for what to do when attempting the option described in the link above:

Example: Creating a spatial view in SQL Server using SQL

the issue with recreating the views is that we have many, more than 30 that would have to be recreated and we don't know that one our ETL runs again the views will work. We are still testing that.

By the way, did you switch storage type (SDEBINARY to SQL Server native "Geometry")?
Storage is still SDEBinary - we haven't even looked at changing it yet.

MarcoBoeringa · ‎05-21-2013

What happens when you create a new spatial view using the options now available in the context menu of a Database Connection as described here?:

Creating a database view in ArcGIS for Desktop
The data shows up as a table, no spatial rendering occurs.

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.

But anyway, the data should render in ArcCatalog, if that isn't the case even with the standard "New\View" option, than there is a deeper issue...

the issue with recreating the views is that we have many, more than 30 that would have to be recreated and we don't know that one our ETL runs again the views will work. We are still testing that.

To be honest, I can't think of any reason why the ETL should influence the functioning of any database view or not. Spatial Views are like any other normal database view. They just store the SQL statement necessary to collect or process the tables and perform any joins etc. If the underlying tables change (new or deleted records), it has no consequences for the SQL statements, UNLESS there are schema changes (e.g. changes in field names, field types).

This raises a question though:

Can you see any changes to schema, and especialy the keyfields that perform the join between the existing spatial tables in your database and the extracted business data between the old and new setup?

Maybe the join fails at 10.1?

By the way, did you switch storage type (SDEBINARY to SQL Server native "Geometry")?
Storage is still SDEBinary - we haven't even looked at changing it yet.

It may be time to contact ESRI support...

JoeWeyl · ‎05-21-2013

I already have an open incident with Esri Technical Support I just didn't think I would have been the first to encounter this issue with Spatial Views and SQL Server 2008, ArcGIS 10.1 since 10.1 has been out for so long.

The Table never converted to a spatial table after clicking on it. The select statement in ArcGIS has the right columns, it just isn't recognizing it as spatial.

The joins are all still good. They haven't changed in about 5-7 years....

As well as the spatial ETL we created, that has been running since 2005 loading the base tables used in the views for the Geography/Geometery, and that is why we can't understand why it stopped working at 10.1. What we aren't sure of, is if it is a ArcGIS Desktop issue? or an SDE/GDB issue, since the tables shows all the records, but the rendering of the data is messed up.

MarcoBoeringa · ‎05-21-2013

I am wondering if it is some kind of projection / spatial reference issue, with the stored SRID of the shapes somehow being messed up?

In your first post you were slightly unclear, suggesting that in some cases, you did see shapes appearing? ("... 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...")

Do you, or don't you ever see shapes appearing in ArcMap / ArcCatalog?

KeithAdams · ‎07-15-2013

I am wondering if it is some kind of projection / spatial reference issue, with the stored SRID of the shapes somehow being messed up?

In your first post you were slightly unclear, suggesting that in some cases, you did see shapes appearing? ("... 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...")

Do you, or don't you ever see shapes appearing in ArcMap / ArcCatalog?

Hi-

I'm a colleague of Joe's and have been at the pointy end of the conversion stick. Starting with the answer to the immediate question above:

It depends. In some cases, we see the initial feature draw when viewing the spatial view at full extent, but any extent change causes some or all features to fail to show up.

In other cases the features draw, but Identify operations are unable, ever, to find any features from which to select attributes. In such instances, attempting to display the view as a table instead of as a map shows an empty table.

In yet other cases, no features ever draw.

In no case did we change the join conditions when we dropped and re-created the spatial views during conversion.

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.

Now on to the longer, more sordid history.

We have tried dropping the views, converting the data, then re-creating the views. They seem to work initially, but the instant we refresh the spatial data they fail again. Our refresh method uses SQL-based business and spatial data as inputs, file GDBs to build the data, then the APPEND tool to bring it back into an existing feature class in SQL Server. The views are built on these existing feature classes, which are emptied and reloaded each time the data are refreshed rather than being dropped and re-created, so there can't be any schema change happening once the views themselves have been created after data migration.

One side issue was that we created them on the first attempt with the new Create Database View tool, but the resulting views could not be used in Spatial Join operations because they didn't show up in the SDE_LAYERS table (and why would they?). Creating them with SDETABLE -o create_view made the Spatial Join tool happy, but then something else broke instead.

We also tested the supposition that perhaps it was broken spatial indexes that were the culprit, and proved to our satisfaction that that was not the cause. Our test method was to delete and re-create the spatial index after a data reload, letting the tool calculate the grid sizes from the re-loaded data.

At this stage we have abandoned the idea of convert-in-place and are headed down the path of creating completely new, empty DBs in an isolated environment, so that they will never have gone through either SQL Server or ArcGIS upgrades and conversions-- they'll be ArcGIS 10.1, SQL Server 2008R2 from the get-go. We plan to create brand new spatial views, again using SDETABLE, from the newly-transferred data once the tables themselves have been copied. Once everything is ready, we will use the new DBs to replace the existing production version and, hopefully, everything will be hunky-dory.

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.)

We've seen that there's a patch for ArcGIS 10.1 when used with SQL Server 2012 that supposedly addresses this issue but since we're at SQL Server 2008R2 we haven't gone to get it.

Thanks for any light you can shed on this vexing problem. We seem to be stuck between two equally non-viable courses of action!

-= Keith Adams =-
SAIC Systems Analyst
HRSA Data Warehouse

VinceAngelo · ‎07-15-2013

I'm running SQL-server 2008R2 on a laptop with disk encryption, and just loaded 500k points into
both SDEBINARY and GEOMETRY tables. SDEBINARY is faster, but I'm still getting 1700+ transactions
per second from GEOMETRY:

% bzcat data/pop_places-txt.bz2 | asc2sde -o create -l tmp_places1,shape -k SDEBINARY -g 2 -f - -C etc/pop_places.ctl -vI 50k -# 500k

ASCII to ArcSDE 10.1 Loader Utility      Mon Jul 15 16:42:21 2013
------------------------------------------------------------------------
     50000 source records read...
    100000 source records read...
    150000 source records read...
    200000 source records read...
    250000 source records read...
    300000 source records read...
    350000 source records read...
    400000 source records read...
    450000 source records read...
    500000 source records read...

Results:
        Records read: 500000
        Rows created: 500000
         Insert time: 1.88 min (4424.94 TPS)
        Elapsed time: 2.54 min

% bzcat data/pop_places-20130604.txt.bz2 | asc2sde -o create -l tmp_places2,shape -k GEOMETRY -f - -C etc/pop_places.ctl -vI 50k -# 500k

ASCII to ArcSDE 10.1 Loader Utility      Mon Jul 15 16:45:27 2013
------------------------------------------------------------------------
     50000 source records read...
    100000 source records read...
    150000 source records read...
    200000 source records read...
    250000 source records read...
    300000 source records read...
    350000 source records read...
    400000 source records read...
    450000 source records read...
    500000 source records read...

Results:
        Records read: 500000
        Rows created: 500000
         Insert time: 4.86 min (1715.56 TPS)
        Elapsed time: 7.81 min

I repeated the test with 10k pseudo-random circles with 2100 vertices, and again did not see a significant
performance defect for SQL-Server GEOMETRY:

% asc2sde -o create -l tmp_circles1,shape -g 2 -C circles.ctl -vI 2000 -c 1000 -k SDEBINARY

ASCII to ArcSDE 10.1 Loader Utility      Mon Jul 15 16:59:07 2013
------------------------------------------------------------------------
      2000 source records read...
      4000 source records read...
      6000 source records read...
      8000 source records read...
     10000 source records read...

Results:
        Records read: 10000
        Rows created: 10000
         Insert time: 56.68 secs (176.43 TPS)
        Elapsed time: 1.00 min

% asc2sde -o create -l tmp_circles2,shape -C circles.ctl -vI 2000 -c 1000 -k GEOMETRY

ASCII to ArcSDE 10.1 Loader Utility      Mon Jul 15 17:00:33 2013
------------------------------------------------------------------------
      2000 source records read...
      4000 source records read...
      6000 source records read...
      8000 source records read...
     10000 source records read...

Results:
        Records read: 10000
        Rows created: 10000
         Insert time: 1.47 min (113.48 TPS)
        Elapsed time: 2.05 min

These performance numbers are for a layer with load-only I/O mode (bulk loading)
enabled; if you want to reproduce my load, download the appropriate se_toolkit,
set the SDEUSER, SDEPASSWORD, and other environment varialbles, set a
RANDOME_SEED_VALUE variable to 12345, and execute the above commands
with this control file:

COORDREF_XY     -400,-400,1000000
COORDSYS        GCS_WGS_1984

EFLAGS          "na+"
REGISTER        objectid(SDE)
ROWLIMIT        10000

COLUMNS
objectid        Sequence                                -       10      N
shape           RndCircle(-179,-89,179,89,1.0,1.0,2100) -       10      Y
END

I'm not sure what's happening at your site, but you should be able to out-perform an
encrypted laptop. Even though it does not appear to be easily reproducible, you should
at least contact Tech Support to see what they might be able to see.

- V