ArcGIS Pro doesn't recognise Postgres spatial property

Question

Greetings,

I have two mirroring deployments (Dev & Prod) of postgres with postgis extsnsion (i.e. not esri enterprise geodatabase) on AWS rd servers . The database on Prod was accidently deleted and then restored from old backup files each taken for individual schemas. Now, when I drag the database tables as query layers to ArcGIS Pro, it doesn't recognise the spatial properties despite having geom column. Instead it gets added to TOC as standalone table:

I got in contact with Esri tech support and by comparing Dev instance that's still working normally to Prod instance, they suggested that the login/user connecting to the database on Prod doesn't have enough permissions on the database objects (db, schema, extension, functions, etc) to that on Dev. However, when I gave that user access to all items to replicate Dev setup, ArcGIS Pro still doesn't recognise spatial properties.

Some troubleshooting steps I did before esri's involvment was to make sure geom column is existing, SRID is existing.

Any idea on what I can check to view data spatially from Prod?

eterra · Answer

I had a similar issue happen with some layers in my organization's PostgreSQL+PostGIS database. Make sure your layers have a spatial index assigned to their geom.

QGIS, which is what we use internally, auto-assigns a primary ID and spatial index. However, when testing our partner agency connections through ArcGIS Pro, I ran into the same prompt as your screenshot. Turns out some of our database layers did not have a primary key set, nor a spatial index on their geom. Once I ran the SQL commands to assign these, the issue was resolved. This also lessened the loading time for a layer in arc pro.

CalvinHarmin · Answer

Since you mentioned the database / objects were not created as an (enterprise) geodatabase, I am guessing the ESRI documentation here is not so relevant.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-postgresql/restore-geodatabase-postgresql.htm

In the case of EGDB's, the restore process has a few specific steps/orders that must be taken. But since your data is not in an EGDB, I don't know if any of that is relevant, especially since you are showing normal behavior in QGIS.

Hal-AhmedShehata · Answer

Yes, I have a username called "geouser" and there is a schema in the database called "geouser". I can connect to the db using this user. However, the schemas having the geospatial data are different, which is the same set up in Dev. Also, "geouser" became the owner of the (db, public schema, geouser schema, spatial-ref_sys table in public schema and more).

Our IT did the restore, I'm not sure if they followed a specific order or workflow.

Worth mentioning that I can connect to this db from QGIS and open the data normally