Query Layers — Include features with null shape in attribute table

[Bud]

ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB (edit: SDE.ST_Geometry):

I have feature classes that have rows where the shape is null. If I create a query layer on one of those FCs, the query layer will automatically hide the rows that have null shapes from the attribute table.

Video:

It's confusing when the query layer attribute table has fewer rows than the underlying datasource. The user isn't warned that the rows get automatically hidden. We also have specific cases where we want to see null shape rows in the attribute table so that we can investigate them. Like this: 
https://community.esri.com/t5/arcgis-pro-questions/export-fc-while-maintaining-objectid-values/m-p/1...

Null shapes are perfectly valid. We can create them using the attribute table, field calculator, python, and SQL. For example, we have a workflow where we load data into a FC from a non-spatial source (like a spreadsheet) and then populate the shapes after the fact. If null shapes are valid, then I don't think query layers should hide them from the attribute table.

To be honest, I've always had trust issues with query layers. They often silently exclude data, which makes analysis problematic. So, I end up avoiding ArcGIS Pro for queries and using other platforms that don't silently exclude rows. Improving things like this would go a long way in terms of making query layers more reliable.

Note: I've only talked about null shapes here. But this idea also applies to features where the shape is not null, but the geometry is nil or null. See states #2 and #3 here: Select features that have blank shapes. Such features should also be included in the attribute table.

[JonathanNeal]

@Bud have you considered using Check Geometry (Data Management)—ArcGIS Pro | Documentation

It will identify your null geometries.

[Bud]

@JonathanNeal I don’t think that tool supports SDE.ST_Geometry.

[JonathanNeal]

@Bud Thanks confirmed (and learned) that ST_Geometry is not supported for the Check Geometry tool.

[AlfredBaldenweck]

I actually ran into this issue a bit ago. My query layer had about 1,000 records until we exported it, then the copy had 5,000 records. We eventually figured out that a case statement deciding geometry stuff was addressing the stuff we had cases for and ignoring everything else. Pro very gamely figured out that we probably didn't want to see that stuff on our map or our table, but apparently forgot when it came time to export. The workaround was to add a where clause excluding what we didn't have cases for, but either entirely ignoring that stuff OR showing it but somehow marking it as null geometry would be helpful.

[Bud]

Esri mentioned the info below in an message about documentation feedback:

ArcGIS Pro was previously applying a spatial filter when querying for all rows in a spatial table, which was incorrect and led to fields without a spatial value being excluded. That was fixed in ArcGIS Pro 3.3, though.

The behavior across the platform can be inconsistent if the table does not contain unique ID values or if you create a join that results in duplicate ID values, which makes it difficult to state exactly what behavior you will encounter. That is why we opted for the general statement about the unique ID requirement.

That said, though, I notice that the page on which you commented does not include the statement about the unique ID. I suppose it wasn’t called out because all layers—not just query layers—must have a unique ID. But I think we can add a bullet to the list of things to keep in mind and link it to the unique identifier fields page.

Other things that would prevent you from seeing all the rows are mentioned in each page about creating a query layer. They’re the spatial property settings. I’ll see if I can work in a bullet about those too.

@JonathanNeal, should this idea be closed?