Spatial Index for ArcSDE Feature Class based on a view

[Anonymous User]

Is it possible to create a spatial index for an ArcSDE feature class that is based on a (SQL Server) view?

[VinceAngelo]

You can't build an index on a view. Views exploit the indexes of the base table(s).
If the base table has a spatial index, the optimizer is able to use it.

- V

[Anonymous User]

Vince, thanks for the reply.  This is apparent in the "s" views that appear with the view-based feature class but for some reason a few of the functions in ArcGIS don't recognize the spatial index.  First, map tips in the feature layer properties is disabled because no spatial index is recognized.  Second, when the analyze map function is run for the Map Service Publishing toolbar it returns a caution message state the layer has no spatial index.  We are concerned this may result in limited functionality in the ArcGIS server environment.  The only workaround we see right now is to copy the view-based feature class to a table-based feature class.  Any other workarounds that you are aware of that would prevent duplication of data?

Thanks!

[VinceAngelo]

First off, what versions (and service pack) of software (database, ArcSDE, ArcGIS) are you using?

What does 'sdelayer -o describe_long' report on both the base spatial table and view?

Have you tried rebuilding the spatial index on the base table?  Does that change the 'sdelayer'
description output?

- V

[Anonymous User]

Below are the results of sdelayer -o describe_long for the base feature class and view.  We are using ArcGIS Desktop 10.0 (ArcEditor).  Please note I have masked the database, account and table names wherever "[x..x]" is found.


ArcSDE 9.2  for SQL Server Build 1239 Thu Jan 24 09:35:02  2008
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Database              : [Database]
Table Owner ..........: [Account]
Table Name ...........: [Base Feature Class Table]
Spatial Column .......: SHAPE
Layer Id .............: 63
SRID .................: 6
Minimum Shape Id .....: 1
Offset ...............:
  falsex:       -400.000000
  falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
  parameter:    SPIDX_GRID,GRID0=0.125,FULL
  exist:        Yes
  array form:   0.125,0,0
Layer Envelope .......:
  minx:      -123.84864,        miny:        26.16145
  maxx:       -68.75716,        maxy:        48.45206
Entities .............: npc
Layer Type ...........: SDE
Creation Date ........: 01/21/10 14:19:38
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1
984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.017453292
5199433]]

Layer Configuration ..: DEFAULTS




ArcSDE 9.2  for SQL Server Build 1239 Thu Jan 24 09:35:02  2008
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Database              : [Database]
Table Owner ..........: [Account]
Table Name ...........: [Feature Class View]
Spatial Column .......: SHAPE
Layer Id .............: 69
SRID .................: 6
Minimum Shape Id .....: 1
Offset ...............:
  falsex:       -400.000000
  falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
  parameter:    SPIDX_GRID,GRID0=0.125,FULL
  exist:        Yes
  array form:   0.125,0,0
Layer Envelope .......:
  minx:      -123.84864,        miny:        26.16145
  maxx:       -68.75716,        maxy:        48.45206
Entities .............: npc
Layer Type ...........: SDE
Creation Date ........: 01/25/10 09:06:22
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1
984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.017453292
5199433]]

Layer Configuration ..: DEFAULTS

[VinceAngelo]

Have you tried rebuilding the base table's spatial index? 

Have you tried using 'sdetable -o update_dbms_stats'?  SQL-Server is tempermental when it comes
to index statistics.

Do you have any plans to upgrade your ArcSDE release?  [9.2 is getting long in the tooth, and most
of the databases it supported are at or beyond end-of-life.]

- V