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.