If your data maintenance process does the have the proper sde locks (or doesnt kick people when ran) it could have been that a user still had a lock (for example, GDB editing in ArcMap or ArcObjects will create a workspace-level lock) and you dropped some tables from sde, while the gdb did not know this was going on) and thus the GDB metadata tables were left in inconsistent state.
Without knowing more about your process is hard to guess more.
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:
- Use the materialized view as a Query Layer
- 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.
Best Answer
Yes, absolutely, you can do that. The principle is to define a function-based index. The steps are like this:
Assume I have a table like this:
1) Define a function that transforms the long and lat columns into a geometry. Note that should any of the input values (longitude or latitude) be null, the function returns null (meaning the geometry will not be indexed and not searchable).
Notice that the function must be defined as deterministic.
2) Setup the spatial metadata:
Specify the expression that produced the geometry using the function you just defined. Note that you must specify the name of the owner of the function (here SCOTT)
3) Create the spatial index on the function:
4) You can now perform spatial searches on that table. For example to find all customers within 10 km of one of our stores.
5) You can also now define a view on that table, like this
and use that view in your queries:
If you want to also see the content of the view on a map (using some GIS tool), you will probably also need to define metadata for the view. This is NOT needed for spatial queries, but is a common requirement for GIS tools.
There will be a tiny performance penalty obviously, since the function will be called repeatedly, but the cost is negligible.
UPDATE:
As Travis mentions, you can actually do all the above without defining an explicit function: just use the default SDO_GEOMETRY constructor. Here are the steps:
1) Setup the spatial metadata. Notice that you need to explicity specify MDSYS as the owner of the function:
2) create the function-based spatial index:
3) Example of a spatial search:
4) Again, use a view to hide the constructor call:
and use it in the queries just like in the previous case:
NOTE: This approach works only if all rows have their LONGITUDE and LATITUDE columns populated! If some are missing (=set to NULL), then you need an explicit function to handle those (and return a NULL geometry). Passing NULL values for X and Y to the SDO_GEOMETRY results in invalid geometries (and the index creation will fail).