0

While updating query on query layer in ArcMap is nightmare process, it seems with ArcGIS Pro this could possibly be better using Python CIM Access.

I have tried following code and it works in the way I am able to get / replace SQL query on layer:

aprx = arcpy.mp.ArcGISProject('current')
am = aprx.activeMap
lyr = am.listLayers()[0]  # Picks first layer in active map
lyr_cim = lyr.getDefinition('V2') # Reads CIM definition
query = lyr_cim.featureTable.dataConnection.sqlQuery # Reads SQL Query
query += ' WHERE GEOM.STX > 0' # Example of query update
lyr_cim.featureTable.dataConnection.sqlQuery = query # Set query in definition
lyr.setDefinition(lyr_cim) # Update definition on layer

The problem is at that moment all features from layer disappear and I get red exclamation symbol next to layer name with tool tip 'Click to repair layer's data source.'

There is nothing wrong with the query or data source, ArcGIS Pro just thinks for some reason there is. If you click through user interface of layer query properties, the updated query is there, and is correct. You just must pretend edit (e.g. delete space and add space) to be able to manually click on 'Validate' button, and 'Next', and 'Finish' button in the next window.

How do I tell ArcGIS Pro programmatically that the layer is alright?

This is a workaround in ArcGIS Pro to be able to programmatically filter features for export to ArcGIS Online. Filtering by extent on layer does not work and sends all data to AOL, filtering by definition query fails too with error - seems publishing can't deal with definition query containing underlying DB methods like STEnvelope, STCentroid etc. Ultimately, what I want is to find some way to be able to clip layers to current map extent on the fly for publishing to ArcGIS Online

4
  • 3
    If the service is in ArcGIS Online, it's possible that the database isn't SQL Server, which would explain why SQL Server-specific syntax doesn't work. It's also possible that RDBMS-specific usage has been disabled to prevent SQL injection threats. This question has the hallmarks of an XY Problem. You might be better off asking about the original problem, not the workaround.
    – Vince
    Feb 4, 2021 at 0:51
  • @Vince Thank you. With definition query I did bad assumption. I just read in documentation publishing tool uploads all data anyway together with definition query, so that makes sense. Currently I really like the approach with Query Layer and python CIM Access, I believe there must be a way to just somehow fix the problem with 'Click to repair layer's data source' and all good.'
    – Miro
    Feb 4, 2021 at 1:11
  • I don't see why you're doing this, it appears you're trying to implement a crude spatial index when server side already uses a very good spatial index to reduce the number of bytes sent for each refresh, otherwise large datasets would need to be sent over a thin network which would make each client download your entire dataset every time a refresh is called. Forgive me if I'm missing the point you're trying to make but it seems you're reinventing the wheel here. Feb 4, 2021 at 1:51
  • @MichaelStimson I try to clip data of whole Australia for local projects on the fly based on clipping area to be available via ArcGIS online. I do not want to upload whole huge dataset, I do not want to make local copies of clipped layers. I just need to provide data for selected extent of each project to ArcGIS online. Hence Query layer where I can adjust query the way I get only filtered data for desired location. Nothing to do with any index.
    – Miro
    Feb 4, 2021 at 2:08

1 Answer 1

3

The issue is that reading the CIM returns an array of None values in the queryFields property of the data connection. When setting the definition back on the CIM with the array of None values, the data connection on the layer breaks.

A solution is to remove the queryFields attribute from the CIM and then setting the definition.

layerCIM = layer.getDefinition('V2')
dataConnection = layerCIM.featureTable.dataConnection
# retrieving the CIM will result in a queryFields array with None values, which breaks the data connection in ArcGIS Pro.
# We need to set it as an empty array/delete the attribute when saving the CIM definition, otherwise we will have to repair the data source in Pro.
delattr(dataConnection, 'queryFields')
layer.setDefinition(layerCIM)

Also see this community post:https://community.esri.com/t5/arcgis-pro-sdk-questions/why-does-cimsqlquerydataconnection-fromjson-choke/m-p/1026292

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.