topic Re: Spatial Index for ArcSDE Feature Class based on a view in Data Management Questions

Spatial Index for ArcSDE Feature Class based on a view

Anonymous User — Fri, 03 Jun 2011 10:30:51 GMT

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

Re: Spatial Index for ArcSDE Feature Class based on a view

VinceAngelo — Fri, 03 Jun 2011 11:19:04 GMT

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

Re: Spatial Index for ArcSDE Feature Class based on a view

Anonymous User — Fri, 03 Jun 2011 11:31:18 GMT

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!

Re: Spatial Index for ArcSDE Feature Class based on a view

VinceAngelo — Fri, 03 Jun 2011 11:51:31 GMT

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

Re: Spatial Index for ArcSDE Feature Class based on a view

Anonymous User — Tue, 07 Jun 2011 11:19:57 GMT

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

Re: Spatial Index for ArcSDE Feature Class based on a view

VinceAngelo — Tue, 07 Jun 2011 12:05:22 GMT

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