registering spatial view fails: "invalid entity type"

[ThomasIsraelsen]

I need to create a spatial view on a feature class in a SQL Server ArcSDE Geodatabase. The Feature class uses GEOMETRY storage and so will the view.

The way I understand it, I should create the view using SQL, and then register it with sdelayer -o register.

I have created then view, which is quite simple: It joins two simple non-spatial tables to the feature class and uses those to filter out some of the rows in the feature class.

When I try to register the view I get this:

ArcSDE 10.0  for SQL Server Build 1937 Tue Aug 16 16:08:18  2011
Layer    Administration Utility
-----------------------------------------------------
Error: Invalid entity type (-29).
Error: Cannot Create Layer.

I am using this command line (omitting connection stuff):

sdelayer -o register -l viewName,shape -e nslc+ -t GEOMETRY -R 3 -C OBJECTID,SDE -P HIGH -E 220997.27000,5266971.36500,1014476.46500,6613192.02000

The -e, -R and -E parameters are using values derived from sdelayer -o describe_long executed for the feature class, which provides this result:

ArcSDE 10.0  for SQL Server Build 1937 Tue Aug 16 16:08:18  2011
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Database              : [removed]
Table Owner ..........: [removed]
Table Name ...........: [removed]
Spatial Column .......: SHAPE
Layer Id .............: 105
SRID .................: 3
Auth SRID.............: 25832
Minimum Shape Id .....: 1
Offset ...............:
  falsex:   -5120900.000000
  falsey:   -9998100.000000
System Units .........:        200.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.01
Spatial Index ........:
  parameter:    SPIDX_MSSQL
  exist:        Yes
  array form:   -6,0,0
Layer Envelope .......:
  minx:    220997.27000,        miny:   5266971.36500
  maxx:   1013943.23500,        maxy:   6613192.02000
Entities .............: nslc+
Layer Type ...........: In-Line Spatial Type/GEOMETRY
Creation Date ........: 09/22/12 17:35:38
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: PROJCS["ETRS_1989_UTM_Zone_32N",GEOGCS["GCS_ETRS_1989",DATUM["D_ETRS_1989",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIME
M["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",500000.0],PARAMETER["False_Northing"
,0.0],PARAMETER["Central_Meridian",9.0],PARAMETER["Scale_Factor",0.9996],PARAMETER["Latitude_Of_Origin",0.0],UNIT["Meter",1.0]]

Layer Configuration ..: DEFAULTS


I am using SQL Server 2008 R2 sp1 on Windows Server 2008 R2 sp1 with ArcSDE 10.0 sp4.

Any thoughts?

[ThomasIsraelsen]

Follow up:

If I modify the view, so that it does not actually return any rows, sdelayer -o register succeeds. Following that I can roll back the view change and the layer now seems to work fine in ArcCatalog and ArcMap.

Very odd...

[VinceAngelo]

The 'sdelayer -o register' command does a full table scan looking to be sure that
all rows conform to the geometry you have specified (lines, simple lines or nils).
Since a zero-row view doesn't fail, that indicates you have something other than
lines, simple lines, or nils in the real view.  It would probably be useful to scan
the table for invalid geometries ('sdelayer -o stats' would suffice).

- V

[ThomasIsraelsen]

@vince: Thanks for the quick reply.

I'm not having much success with sdelayer -o stats...

I'm getting this response:
The requested operation is not supported.

This is whether I try to get stats for the view or for the underlying feature class.

sdelayer -o si_stats gives this:
Error: The operation requested is unsupported (-210).
Error: Cannot get Map Layer Spatial Index Statistics for the layer

Neither the view, nor the underlying feature class reports any errors, when I try to display them in ArcMap.

[VinceAngelo]

I wouldn't expect an 'si_stats' to work on GEOMETRY storage, but I would expect the
plain old 'stats' to return the number of geometries and the types to which they mapped.
The errors you provided look like 'si_stats' output -- Did you try 'stats' as well?

I know the ArcSDE back-end wasn't coded for 'feature_info' to be able to skip invalid
features, but it too should be able to scan the table to report types.

- V

[ThomasIsraelsen]

sdelayer -o stats -l MyFeatureClassOrView,shape -i sde:sqlserver:dbServer\dbInstance -s dbServer\dbInstance -D GeoDB -u user -p pwd


ArcSDE 10.0  for SQL Server Build 1937 Tue Aug 16 16:08:18  2011
Layer    Administration Utility
-----------------------------------------------------
The requested operation is not supported.

[VinceAngelo]

And 'sdelayer -o feature_info'?

I know 'sdequery' would function.

-V

[ThomasIsraelsen]

sdelayer -o feature_info on the view gives this:
[lots of data...]
Total rows examined:      169169
Total invalid shapes:          0


sdelayer -o feature_info on the feature class underlying the view gives this:
[lots of data...]
Total rows examined:      176444
Total invalid shapes:          0

not really sure what to do with sdequery or how to access it.

Is there a way to get sdelyer -o register to tell us specifically which row or rows it is not happy with?

[VinceAngelo]

If it's not written to the application server log, I doubt there's any way to see
what 'sdelayer' is rejecting (besides giving the data to Tech Support to evaluate).

If you have the Unix 'grep' tool avaliable you could parse the "lots of data" for
geometry type, to see if anything other than the supported types is present.
But as long as the registered rowid column is unique, you may just have to add
a "WHERE 1 = 0" to the view long enough to register it then set it into the final
view content.

- V