Work with a spatial View in Python

[AllenScully]

Having a difficult time with what should be a simple task - 

In a python script, i'm trying to write records from a view in an SDE database (sqlserver) to either a table or feature class (whichever works - the idea is to write a set of records to an table/FC that serves as an archive, with a date marking the date of the run time for the script)

I've tried CopyRows, Append, ArcSDESQLExecute - all fail, for a variety of reasons - geometry type not supported (Append) or an error relating to being unable to insert NULL values into OBJECTID column - fair enough but my understanding is that because the target table is registered with the database, and would automatically generate an OBJECTID (this is for ArcSDESQLExecute method with an 'Insert' SQL statement).  

My hunch is something is not right with the view (the 'source' data) - but am looking for suggestions/experiences around working with views in Arcpy tools in a python (not desktop ArcGIS) environment.

Thanks - 

Allen

[JoshuaBixby]

What is your spatial storage type?  SQL GEOMETRY, GEOGRAPHY, or SDEBINARY?  How was the view created?  Is the view in a geodatabase?  Is the view registered with the geodatabase?

[LukeWebb]

Im not sure I understand the question! 

 If you are copying FROM the database into a FC, any copy rows / featureclass to featureclass should work, as objectID is managed by the shapefile / FileGDB without problems.

I understand why you may be having difficulty storing data inside the database as I have had similar issues, but your workflow is the opposite way round no?

Anyways, I only ever manged to insert data into SQL via SQL queries 1 row at a time in a loop, using the functions described here to get the next objectID:

Next_RowID—Help | ArcGIS Desktop 

[AllenScully]

So the view in question was created in our SDE database using arcpy.CreateDatabaseView_Management.  

The work needed is simply writing certain records from the view in the SDE database into an FC or table within that same SDE database after a bunch of python code is executed.

The view appear to be registered with the database, giving that the option to do so is grayed-out in the catalog view in Arc desktop.  

However, when trying to add the view in ArcMap, it is asking for a Geometry type, and to identify what field to use as unique identifier (even though there is an OBJECTID column in the view).  

This leads me to believe the spatial view was not created correctly.

[VinceAngelo]

A spatial view is a database object. It is not registered with the geodatabase in any way. Therefore, when you access it via a Query Layer, ArcGIS needs to ask  for the rowid column and geometry type (this lack of meta-information is the defining characteristic of a Query Layer). The ability to register views to a geodatabase was added at ArcGIS 10.5, so if you're running 10.4.1 or lower, Query Layers is your only mechanism to access views.

- V

[AllenScully]

Thanks Vince - I've always been a little murky on the details of working with spatial views.

We are running an old SDE version (10.1), with desktop 10.3.1 in this case (yes, I know, and we are in the process of upgrading databases to allow us to work with current versions of ESRI software).  

The upside of this is that we can still use the command line to create views (using 'sdetable'), and views created in this way behave essentially as feature classes in the Arc Desktop environment.  Add it in ArcMap, and the geometry/objectid questions do not come up - it has the geometry of whatever SHAPE field you include in the query to create the view.  

I had forgotten we had this ability (command-line view creation) - and a new spatial view created with this method works as expected in  a python script (arcpy append with the view as the 'source' data).  

[VinceAngelo]

Even when I had the ability, I did not use sdetable to create views -- I used SQL scripts in the database exclusively. Where necessary, I would sdelayer -o register layers, but with Query Layers, I find it's not necessary.

- V