topic Re: SDE Spatial Views and Spatial Indexes in Data Management Questions

SDE Spatial Views and Spatial Indexes

AdamZiegler — Tue, 15 Jan 2013 15:08:20 GMT

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

Re: SDE Spatial Views and Spatial Indexes

BrentPierce — Thu, 17 Jan 2013 19:42:00 GMT

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

Re: SDE Spatial Views and Spatial Indexes

anthonysanchez — Fri, 18 Jan 2013 01:51:17 GMT

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

Re: SDE Spatial Views and Spatial Indexes

AdamZiegler — Tue, 29 Jan 2013 18:55:35 GMT

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

Re: SDE Spatial Views and Spatial Indexes

AdamZiegler — Tue, 29 Jan 2013 19:31:46 GMT

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

Re: SDE Spatial Views and Spatial Indexes

AndresCastillo — Sun, 12 Dec 2021 04:08:14 GMT

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-tables-with-an-st-geometry-column.htm

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))‍‍

Re: SDE Spatial Views and Spatial Indexes

AndresCastillo — Wed, 13 Jun 2018 16:50:46 GMT

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

Re: SDE Spatial Views and Spatial Indexes

MichaelVolz — Wed, 13 Jun 2018 17:23:35 GMT

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

Re: SDE Spatial Views and Spatial Indexes

AndresCastillo — Wed, 13 Jun 2018 19:21:52 GMT

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.