SDE Spatial Views and Spatial Indexes

[AdamZiegler]

Hello all,

I have a question in regards to Spatial Views. I am trying to minimize redundancy in my parcel dataset by using spatial views. I have different tabular sets that I join to the managed parcel geometries so that I don't need to re-process feature classes constantly to get updated geometries. This has been successful to this point in ArcMap and consumed in a MXD based map service.

I recently tried to use the spatial view in a msd based map service and received a High Warning that my layer did not have a spatial index. I found this odd at first, because the feature class that is the source of the geometry does have a spatial index, so why isn't that used in the spatial view? I tried to create a spatial index on the spatial view and that isn't allowed (which I expected).

I guess I'm just looking for confirmation that the best way to use my RDBMS is to redundantly store geometry.....which would sort of laugh in the face of having a RDBMS. I mean SDE feature classes are essentially views anyway.

Thanks,
Adam Z

[BrentPierce]

When creating a spatial view, ArcGIS will not create a spatial index (or any index) on the view (DBMS' may not support indexes on view at all unless it is a materialized view), the key aspect is to make sure the underlying database tables that are referenced in the view have the appropriate indexes.

Hope this helps,
-Brent

[anthonysanchez]

Hi,

I too have seen this issue before long ago.  Please post your environment info along with a sdelayer -o describe_long of the feature class and spatial view.

In my situation, i believe my problem was passing the wrong srid to view creation command that prevented the use of the spatial index.

good luck,

Anthony

[AndresCastillo]

Hi Anthony,

Can you please expand on this?

We can message if you like?

I am trying to publish a view as a map service, but I get the high warning message saying that the view does not have a spatial index.

Any thoughts?

I  am on

Microsoft SQL server 2014.120.5579.0

EGDB is 10.4.1

I found these resources, but would like some help:

http://desktop.arcgis.com/en/arcmap/10.4/manage-data/using-sql-with-gdbs/create-spatial-indexes-on-t...

But there is no information on doing this on Microsoft SQL server.

Listing 2 on this (http://www.esri.com/news/arcuser/1012/files/querylayers.pdf) article says:

create spatial index MyTABLE _ shape on MyTABLE _ tbl (shape)
USING GEOMETRY _ GRID WITH (BOUNDING _ BOX = (Xmin,Ymin,Xmax,Ymax))‍‍

[AndresCastillo]

Spoke to ESRI support, and, as Brent above said, Spatial Indexes cannot be created on views.

They can only be created on the underlying fc and table that the view references.

Another option may be to add multicolumn attribute indexes on fields from the underlying fc’s and tables that will be queried in the view.

Read

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/tables/creating-attribute-indexes.htm

[MichaelVolz]

Andres:

Have you been able to publish your view as a mapservice?  If so, have do you plan on using the data (e.g. Do you intend to perform queries on the mapservice and it's associated view?  I have been working with a view from Oracle and I am encountering fundamental flaws with how the client is communicating back to the database (where clause and sort by appears to be dropped when the SQL statement is sent to the database.

Here are some threads that touch on these issues and I don't believe they are database specific so it would occur in Oracle, SQL Server, Postgres:

Fundamental flaw when opening attribute tables in ArcGIS Pro? 

https://community.esri.com/thread/216276-available-values-for-a-query-layer-subset-selection 

[AndresCastillo]

I was able to publish the view as a map service.

I created the view using the 'create database view' tool

Then, registered the view with the geodatabase with ArcGIS Pro or ArcMap 10.5

I am using this map service in a agol web map to display the latest inspection record from a table, symbolized from a field.

In other words, the view is used to symbolize desired records.

I will not perform further queries on the view.

The only reason I wanted to create attribute index was so that the view would run faster (because I heard that over time, as data grows, views start lagging in performance draw times).

I will read up on the threads you posted, and let you know if I can think of something else regarding your situation.

[AdamZiegler]

When creating a spatial view, ArcGIS will not create a spatial index (or any index) on the view (DBMS' may not support indexes on view at all unless it is a materialized view), the key aspect is to make sure the underlying database tables that are referenced in the view have the appropriate indexes.

Hope this helps,
-Brent

Okay, that is good.  As long as the geometries being used are indexed everything should be fine.  I can ignore the High Warnings that is given by ArcGIS Server when publishing the view out as a service.

Thanks!
Adam Z

[AdamZiegler]

Hi,

I too have seen this issue before long ago.  Please post your environment info along with a sdelayer -o describe_long of the feature class and spatial view.

In my situation, i believe my problem was passing the wrong srid to view creation command that prevented the use of the spatial index.

good luck,

Anthony

Anthony,

Thanks for the reply.  The view I created was actually using the geometry of an existing feature class, so I did not need to specify the SRID (not using Oracle Spatial). Our RDBMS is Oracle 11g (11.2.0.3) on AIX 6.2. SDE is 10.0 (and I actually just discovered that no SPs have been applied...which will be remedied tomorrow). The long description of the table is attached.

Thanks,
Adam Z