Empty ArcSDE spatial view not being recognized

[KevinGooss]

AGS/SDE 10.1 direct connect

I can create a spatial view and it renders fine in catalog/arcmap/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. my ags map service fails and ags reports that it has no support for this geometry type

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]

What database are you using?

What geometry storage is used in the base table?

What exact changes do you make to the SQL?  Do you change column names or
the number of selected columns?

Is the view registered with ArcSDE? [present in the table_registry and layers table]

- V

[ShannonShields]

How are you creating the spatial view?

If you are using the sdetable -o create_view command then the view is "registered" with the sde_layers table and there is metadata available about what entity type the view contained at the time it was registered.

If you are using database tools or the Create Database View GP tool then the resulting view is not registered with the geodatabase and there is no metadata about the entity type that the spatial column contains. This information is determined on the fly using the first record in the view. If the view contains no data, then there is nothing to define an entity type (or spatial reference or extent, all of which are required by ArcGIS software).

-Shannon

[KevinGooss]

Sql server. Geometry type. I am creating the view using catalog. I only modify the where clause, not fields. Are you saying that views created with sde command line are different than views created in catalog? I thought we were supposed to abandon command line?

[KevinGooss]

Is it too much to ask for the metadata to be gathered from the only spatial column present in the base table?
So now I have to revert back to sde command line? How many more versions is that going to be around?

[MarcoBoeringa]

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?

Yes, it does, like Shannon wrote.

Is it too much to ask for the metadata to be gathered from the only spatial column present in the base table?

It probably is.

The answer we are getting now is to use the command line tool to create the views and make sure they are registered with sde.
Apparently with the new style views the geometry metadata is gathered on the fly from the first record in the table.
That makes sense to me. But what about when there are no records? Seems like a better system would have been to get the metadata from the only spatial column in the view - the one in the base table - which is a feature class. A feature class can be empty and sde/ags still knows it is a feature class.

One could argue that the collection of metadata on the (potential) first record in a view as opposed to the true metadata in the (singleton) spatial column of the base table that was the progenitor of said view is synonymous with proprietary software manufacturer laziness.

I don't agree, I think you are heavily underestimating the consequences of the task you are requesting ArcMap to do, that is, to determine the feature type of a spatial view or Query Layer with no records in it, and not pre-registered with ArcSDE or the geodatabase, based on an evaluation of the SQL expression and digging down to extract information from the base table. As Vince wrote:

In your particular use, registration of the existing view is necessary (it is not possible for a query
cursor to mine the database query plan to extract the information you expect it to have), but that
only requires 'sdelayer -o register', not recreation of the existing views (be sure to use 'sdelayer
-o describe_long' to extract the SRID of the base table for the -R flag in the register options).

The underlined and bold sentence may sound a bit cryptic, but to make the argument more clear, you have to realize that database views or Query Layers offer maximum flexibility. You can insert / use any valid SQL expression your RDBMS supports, with just a few unsurprising restrictions outlined here if the data needs to be used straight in ArcGIS via Query Layer as well. This flexibility comes at a price though...

While your particular "case" might seem straightforward, with probably a single base table, no joins, no GROUP BY, SUM, AVG, COUNT, no spatial type conversion or whatever particular ANSI/ISO SQL DML, or database SQL spatial type, statement one can use (E.g. ST_Intersection, ST_Centroid), users in practice may make use of any of these options.

The result of this is a whole bunch of "headaches" coming up once you dig down these kind of SQL statements. The one that probably hurts most in the context of your arguments, is that it also means that users will be able to even perform "feature type" conversion on the fly. Yes, that is, to convert a Polygon base table to for example a respective centroid "Point" type spatial view or Query Layer based on a spatial type SQL statement like ST_Centroid, or, as another example, a "Polyline" layer representing the outlines of the original polygon features.

I guess I don't have to explain to you for this particular example involving feature type conversion, that the result of a dig down to the base table, won't result in anything useful that would solve your issue...

An example of such type conversion in a recent thread here in the Geodatabase & ArcSDE forums:

Registering a polygon table to point in a m. view using SDO_CENTROID/sdelayer error

Since there are marked differences in the implementation of (spatial type) SQL for each database ArcGIS supports, the headache becomes even bigger.

Another major issue is that views could be based on other views in the database, requiring a recursive operation as well to find that elusive "spatial column"...

I really think your issue is more of a workflow / application design issue. You chose spatial views, while in practice, you require geodatabase functionality, or at least registration of the layers with the ArcSDE system tables.

Anyway, if you want changes to ArcGIS that accommodate your particular "simple case", you are probably better off posting an enhancement request in the ArcGIS Ideas website.

And as Vince wrote:

Hopefully, there will be a ArcPy tool that will register views with geodatabase metadata once that which was once ArcSDE is gone.

It seems likely such a tool will be part of a future release of ArcGIS.

And ESRI is making a serious request to us all to state issues regarding the deprecation of the ArcSDE Command Line tools, and the need for ArcPy or Arctoolbox user interface replacements. You could list your issue in this sticky thread as well:

Alternatives to using SDE command line tools - Blog Discussion

OK, just noticed you posted a response at the blog as well 😉

[KevinGooss]

I understand your points, and you make several very valid ones at that.
But i can't help but feel there is a little bit of role-reversal here.
I am a customer of the software, I pay for the right to use the software.
ESRI is the manufacturer of the software.
If the manufacturer is going to change the product it is incumbent upon them to see that there is a plan for users to move forward.
I'm tired of doing that part of the job for them.

sde command line will be deprecated in 10.3
my workflow will no longer work, others will have the same issue
there will be a patch to 10.3 to address the issue
that patch will fail
a fix will be promised in 10.4
10.4 will morph into 11.0
11.0 will address the issue by forcing a different workflow rather than repairing the issue.
11.1 will contain the appropriate documentation about the issue

All of this could have been avoided with a right-click menu in ArcCatalog that says 'Register with Geodatabase' when i right -click on my spatial view.