Inconsistent access to and rendering of tables with PostGIS geometries

[rmcooper_wcpss]

Our authoritative data store is a PostgreSQL database with the PostGIS extension enabled. All geometries are PostGIS geometry types and we have no plans to use Esri's ST_Geometry type. While we have no issues accessing and rendering spatial tables in QGIS, the same cannot be said for ArcGIS Pro. I have tried to load PostGIS tables in both ArcGIS Pro 2.9.6 and 3.0.4 with identical results. For most tables, regardless of number or complexity of features in the geometry column, we run into issues that make Pro unusable for accessing data from our authoritative data store.

Problem 1: Features draw inconsistently across scales

The animation below, though obfuscated, illustrates the problem. At all scales the map background (white) should not be visible. However, it is not until the map is zoomed in to a large scale (1:9,028) that all features are rendered. Interestingly, if I use the Select tool and click in a white area at the smaller scales, features that are not visibly rendered can be selected. Based on my testing of our tables, this appears to only impact some, but not all, tables with MultiPolygon geometries.

Problem 2: Shape Integrity error

These tables that are not rendered properly in the map also throw a Shape Integrity Error in at least a couple of places.

First, if I open the attribute table for one of these spottily rendered tables, I receive the following message:

Failed to load data. Error: Shape integrity error

My impulse after receiving this error was to use the Check Geometry tool. However, this tool fails on all spatial tables from our database throwing ERROR 000278. However, in the case of the tables that do not render properly, the tool includes an error message indicating a shape integrity error.

I have been unable to find any helpful information about the shape integrity error or how to find what shape(s) is/are causing the error.

Problem 3: DBMS table not found

You may also notice another message in the error above: DBMS table not found. Again, I have been unable to find any helpful information about the message. The tables I've run the Check Geometry tool on have been successfully added to a Pro map, albeit with the inconsistency in rendering noted in Problem 1. I have no problems connecting to the source PostgreSQL database and listing the layers in the Catalog pane in Pro.

Things I've done

Geometry validity checks

I have checked the validity of the tables in our database using QGIS's Check validity Processing tool. Although this does not test for valid Esri geometries, it only found at worst a few expected ring self intersections. It's worth noting, that the poorly rendered geometries in the animation above were all valid geometries according to QGIS. 

Export PostGIS table to another format

Using QGIS 3.28 I was able to export the PostGIS tables directly to a File Geodatabase. The resulting feature classes were able to be rendered in ArcGIS Pro without issue. Our goal is to directly access spatial data from PostGIS, not File Geodatabases, but perhaps this confirms that the geometries are OK. 

Load PostGIS tables as query layer

I have loaded the tables using the Make Query Layer tool with no difference in outcome. I have limited the columns returned in the SQL query to just the geometry and unique IDs

Check for primary key

I have confirmed that all tables I have tested have a primary key.

Additional Details

Test Machine & Software

• Windows 10
• Pro versions: 2.9.6, 3.0.4
• QGIS versions: 3.28.0
• GDAL/OGR: 3.5.3

Database Server

• PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
• PostGIS 3.1 (PostGIS geometries only)

Conclusion

I am looking for any tips or ideas on how we can access our data in ArcGIS Pro directly from PostGIS without exporting data to a different format or using Esri's ST_Geometry. In terms of desktop GIS usage, we are platform agnostic with some team members favoring ArcGIS Pro over QGIS or vice versa. There's also the larger matter that PostGIS is a long-established, stable technology and spatial tables should be as simple to access as a Shapefile.

Has anyone run into anything like the problems I've described? If so, how did you address them? Does anything I've shared here raise obvious red flags or suggest other lines of inquiry I may be missing? Happy to provide additional details upon request. Thanks in advance for your time and consideration of this post.

[GIS_Boras]

Hello, 

yes we have experienced the same problem. We used arcgis pro 10.9, still same problem in arcgis pro 11.1.

The problem, for us, was that some objects in the polygon layer contained curves.

To check the geometry type in PostGIS use this SQL:

SELECT ST_GeometryType(geom) from [SCHEMA.TABLE]

You'll get a list and if there is a object that is ST_MultiCurve (or something with curve) then arcgis pro cant read it. It seems that ArcGIS pro only accepts ST_Polygon or ST_MultiPolygon.

Might be easy to fix if there is just a few objects with curve. We corrected the tables with FME because we had a lot of curve geometries.

After the geometries were corrected we set a Check (constraint) on every polygon table in PostGIS, this check only allows the table to contain ST_Polygon and ST_MultiPolygon:

st_isvalid(geom) AND (geometrytype(geom) = ANY (ARRAY['POLYGON'::text, 'MULTIPOLYGON'::text]))

Hope this helps.

[ts_noaa]

This issue has also been causing some major issues for our application at the National Weather Service over the last year or so. Here are a couple of pieces of information related to instance... but the general behavior is exactly as you've described, and it was happening in both Arc Pro and in an AGS map service.

• One thing I found is that the features that are failing to render at various zoom levels are not the same features with problematic geometries. As you pointed out, the engine can clearly render them for highlighting when you click in the white space, and I even tested this out with some simple shapes in the data that failed to render as well.
• That leads me to believe that the entire rendering engine is crashing when it gets to a problematic geometry, and nothing after that is rendering. The fact that the application does not throw an error when this happens is absolutely unacceptable. (we weren't even getting the shape integrity error or anything, and could open the attribute table and view the records of all features).
• If I export the query layer data to a GDB in Arc Pro, the tool/process runs just fine, but only the features that rendered show up in the new data file (which is of course only a portion of our full dataset). Again, the fact that this can happen without any error being visible within the Arc Pro application is a MAJOR BUG.
• We thought we solved the problem by simplifying the features at a low threshold (either in PostGIS, or pre-load in GeoPandas), which did reduce the vertice count significantly and seemed to get everything to render in that case... but we've found another instance of it happening just recently, which is how we ended up finding your post (thank you!). The simplification is not solving our problem this time.
• We don't have any curve geometry types in our data, just polygon and multipolygon (thanks for the tip GIS_Boras)
• We had a premium support ticket open with ESRI, but it was closed because they couldn't reproduce the issue, even though I exported the data and tested and verified the behavior in a completely fresh EC2 instance.

Curious if you ever got anywhere with this?

[rmcooper_wcpss]

There hasn't been any resolution to the problem of rendering spatial tables via direct connections to PostgreSQL/PostGIS. We did get ArcGIS Enterprise deployed recently and published services pointing at PostGIS tables. Tables that had rendering issues with direct DB connections rendered properly when passed through Enterprise though performance was not stellar. We have very few team members who need to access the DB data directly and typically will be making a local copy of the data they need anyway. As such, we currently just advise our team to access Postgres tables via QGIS. We're essentially putting off dealing with this issue until it becomes more pressing...a dangerous game!

While I can't offer a solution, I am glad to hear we're not the only ones running into this issue. I got to speak with the Pro dev team at UC this year and while they couldn't explain the issue in the moment, I shared this thread with them and they promised to look into it. We'll see if they get a chance to do so!

[Waan]

@ts_noaa, which ArcGIS Server version are you using? It's my understanding that v11 had some fairly major revisions compared to the 10 series.
Also, have either you or @rmcooper_wcpss tried accessing the tables directly from ArcMap instead of Pro? I realize it's imminently sunsetting (and if you're a Fed it might already be uninstalled), but we've had some really strange and/or kludgy Pro behavior with direct-connections to enterprise geodatabases (i.e. just using drivers with no intervening Portal) that seem to vary by minor releases--e.g. between 3.1.0 and 3.1.2.

[ADRIENHOFF1]

I’m working through some similar rendering issues in portal. In my case I’m being data in from a WFS and making its a Postgres table before sharing as a reference service. 

I found this article which mentions working with curves in an ESRI and PostGIS environment and might be helpful. 
https://pro.arcgis.com/en/pro-app/3.1/help/data/geodatabases/manage-postgresql/data-types-postgresql...

I would confirm that your EPSG/WKID is compatible with your geometry or geography type. Ie, if using state plane use geometry vs WGS which you’d use geography for. 

I had the issue where some of my records were also returning as ST_GeometryCollection which ESRI doesn’t recognize. I used ST_collectionextract to pull out just the polygon or multi polygon features.

SET geom = ST_CollectionExtract(geom, 3) WHERE ST_GeometryType(geom) = 'ST_GeometryCollection';" Using this in an update statement allows me to remove the junk.