Making PostGIS polygons ready to use in ArcGIS

I have experienced similar issues, where data that is perfectly fine according to PostGIS and QGIS (tested with ST_MakeValid in PostGIS, and by loading the data in QGIS and running QGIS's geometry validity checker), still couldn't be loaded reliable in ArcGIS.

Note that the data in my case was stored in WGS 1984 (SRID 4326) "geometry" storage (so not "geography").

I finally solved it by using three strategies:

• Run ST_Buffer in PostGIS with a tolerance of 0 after ST_MakeValid, which is a quite common practice according to what I read to solve some difficult to resolve geometry errors. In case of Polygon data, it is also recommended to create a statement including ST_CollectionExtract, with 3 as the indicator for extracting Polygon type geometry only. This avoids creating GeometryCollections.

• Run ST_Normalize. Despite the PostGIS documentation stating this is "Mostly only useful for testing purposes", I found clear cases where putting in an extra ST_Normalize allowed data that reported a "Shape integrity" error in ArcGIS, to be suddenly perfectly readable in ArcGIS after running this command. The documentation is vague about exactly what it does though, besides reshuffling parts of a polygons, and I have difficulty understanding why it allows ArcGIS to read the data afterwards (maybe something to do with internal rings an outer shells requiring a specific order in ArcGIS?).

E.g. so a complete command, except last recommendation, could look like this: ST_Normalize(ST_Buffer(ST_CollectionExtract(ST_MakeValid(<GEOMETRY_COLUMN_NAME>),3),0))

• If that still fails to return a valid data set for ArcGIS (and it wasn't enough in my case...), I recommend deleting any Polygon geometry record having an area less than 1 square meter.

To do this, I calculated the geodesic area for all Polygons in PostGIS, and subsequently deleted the ones with area < 1 m2. The latter action solved my issues, and allowed ArcGIS to finally accept the data as 100% valid and load it without issues as an ArcGIS Query Layer.

If you want to calculate the geodesic area as well, you can use:

• If your data is in SRID 4326: ST_Area(<GEOMETRY_COLUMN_NAME>::geography,False)

• Other projection: ST_Area(ST_Transform(<GEOMETRY_COLUMN_NAME>,4326)::geography,False)

However, depending on your data, I don't know if this latter action is acceptable, although arguably, Polygons with an area < 1 m2 are likely erroneous in most GIS datasets (sliver data etc.). There is not much use for such tiny geometries in the real world of GIS daily practice and databases.

By the way, I have no idea why ArcGIS isn't capable of using these tiny shapes, while QGIS happily ingests them. Looking at the WKT representation of problematic features in DBeaver, apparently showed a valid representation. Maybe it is something to do with maximum coordinate precision in ArcGIS, and conversions (transparently executed - not visible to user) needed to get the data in ArcGIS ready format for display, and / or the combination with storage of coordinates in lat/long SRID 4326 instead of a more regular planar projection like Web Mercator, but this is all speculation...