Creating spatial view from materialized view?

Question

I have a materialized view that runs nightly and performs an intersection of point locations and polygons. The materialized view works as designed and returns the desired results. It contains attributes from the point and polygon used in the intersect operation, including a unique objectid field and geometry field.

Now, I need to create a spatial view. First, can I create a spatial view that gets its data from a materialized view? If so, how?

I used the SDE command line to create a view and modified the query to include some additional fields. After my edits, the spatial view seems to work fine in TOAD. Performing a SELECT against the spatial view returns the data expected.

When I try to add the SV to ArcMap, I get the following message:

could not add the specified data object to the map.
Attribute column not found [schema.spatial_view][STATE_ID = 718731]
Attribute column not found [schema.spatial_view][STATE_ID = 718731]
Attribute column not found [schema.spatial_view][STATE_ID = 718731]

I tried dragging & dropping the spatial view from the Catalog onto the map surface as well as the Add Data button. Both produced the same message. This is causing me question my process and wondering if a) there's something on the backend that isn't wired up correctly or b) this is just not possible.

In ArcCatalog and the Catalog window in ArcMap, the icon adjacent to the spatial view is the icon used for data tables. It's not the point, line or polygon icons I see adjacent to the spatial views that work correctly. So arc thinks this spatial view is a data table and doesn't contain the polygon data it actually contains?

Answer 1

If you have a materialized view with a geometry column, you don't need to "create a spatial view" -- it's already created!

At this point you have two options:

1. Use the materialized view as a Query Layer
2. Register the view with your enterprise geodatabase

Unfortunately, in the crush to retire ArcSDE application servers and the admin utilities that let users corrupt their geodatabases at ArcGIS 10.3, the use case where views could be registered using ArcPy/ArcToolbox got neglected. There is a viable workaround, but it requires 10.2.2 admin utilities with a Direct Connect connection string.

The sdetable -o create_view capability is a relic from the time before native geometry columns in databases, when the only storage option was SDEBINARY, and a mechanism for populating parallel views for the feature (Fn) and spatial index (Sn) tables was required for views to work at all. It has always been best practice to create views with native or SDE.ST_GEOMETRY geometry columns using database tools, then register the result with sdelayer -o register (where it is of supreme importance to specify a USER-set rowid column with -C columnname,USER)

At ArcGIS 10.1 Esri added a the Create Database View (Data Management) geoprocessing tool to integrate view creation within ArcGIS. However, this tool was targeted toward the use of Query Layers, so it has never offered the ability to register the view within the geodatabase (views in general can't participate in geodatabase behavior, because versioned editing requires an SDE-set rowid column).

I was a certified instructor for using ArcSDE technology before it was even called SDE, and I'm not confident of my ability to avoid corrupting the geodatabase by making edits to views which are currently registered with the SDE repository. It has always been best practice to use sdetable -o delete (or right-click delete from Desktop) to remove registered views (Note: tables registered with the geodatabase by Desktop tools should always be maintained with Desktop tools -- common failure to do this is one of the many reasons why the admin utilities were eliminated from the product suite), recreate them as necessary, then re-execute sdelayer -o register.

Once corrupted, it can be very difficult to remove improperly modified views; it may take assistance from Tech Support to repair your geodatabase. You should certainly make a database backup snapshot before attempting repair.

Answer 2

I believe you will need to register your materialized view in SDE so that it recognizes the view as a spatial table. If that is not an option, you may want to refer to the documentation and possibly make a conventional view that accesses the materialized view, but calls the appropriate functions to retrieve the data in a fashion suitable for ArcGIS Desktop.