ArcGIS desktop db connection to PostgreSQL w. POSTGIS empty?

[BogiBjornsson]

Hello,

We are experiencing a rather weird bug/behavior when trying to add a database connection in ArcGIS Desktop 10.3.1 (ArcMap or ArcCatalog) to a PostgreSQL database (version 9.3.9) which has the POSTGIS extension installed (version 2.1.7 r13414 ). The connection succeeds without any errors in ArcGIS but when we look at the contents pane it is completely empty but should be full of tables with data (see image below).

A database user has been created to make the connection and this user has been granted all the necessary roles/permissions in order to at least view (SELECT) data in this database so this can not be the problem. We have also tried connecting as the database owner/database admin user and the same problem is present.

When checking the PostgreSQL logs we find these errors for the arcgis database user and the dbo user respectively:

-- arcgis (not owner of the database)--

2015-07-03 12:03:45 GMT [11157-1] arcgis@nytjavatn ERROR:  must be owner of database nytjavatn

2015-07-03 12:03:45 GMT [11157-2] arcgis@nytjavatn STATEMENT:  ALTER DATABASE nytjavatn SET SEARCH_PATH="$user",public,sde

2015-07-03 12:03:45 GMT [11157-3] arcgis@nytjavatn ERROR:  relation "sde.sde_layers" does not exist at character 22

2015-07-03 12:03:45 GMT [11157-4] arcgis@nytjavatn STATEMENT:  select count from sde.sde_layers

--dbo user--

2015-07-03 12:12:42 GMT [11253-1] dbo@nytjavatn ERROR:  relation "sde.sde_layers" does not exist at character 22

2015-07-03 12:12:42 GMT [11253-2] dbo@nytjavatn STATEMENT:  select count from sde.sde_layers

Its as if the connection thinks it is connecting to an SDE Geodatabase but as I understand the "Add database Connection" tool you can use it to read non SDE Geodatabase enterprise databases, at least it used to in previous versions of ArcGIS desktop. We had this database previously set up on a PostgreSQL 9.1.3 w. POSTGIS 2.0 and then it worked perfectly when connecting with ArcGIS desktop 10.2.2. Then all you needed was to copy the PostgreSQL client libraries to the correct bin folders of ArcGIS desktop and voila! (see resources.arcgis.com/en/help/main/10.2/index.html#//002p0000003q000000). It even read the POSTGIS geometry directly as shape information without any issues, it was perfect!

Has anyone else encountered this issue and if so have you found a solution to this?

Thanks in advance for any feedback and/or answers.

Cheers!

[AsrujitSengupta · Accepted solution]

logically, it should work...however we might have to test this further to check if this is a Bug..

Can you report this to Esri Tech Support for further analysis..

[BogiBjornsson]

I will do that, thank you for taking a look at it

[BogiBjornsson]

Hello again Asrujit,

You were completely right, it should work by default and we found out that the PostgreSQL/Postgis database setup was somehow corrupted because when we set up the database again we could connect without any issues.

The exact details of what was causing the problem remains unknown however and we will not be investigating further since the problem has been resolved now.

Thanks again.

[tKasiaTuszynska]

Hello Everybody,

The most common reason for not seeing data created in a nongdb, regardless of type is that it is not owned by the user that owns the schema.

For example:

- you are logged into pgAdminIII as postgres

- you are creating a table in the map schema

- since you are logged in as postgres user the table resides in the map schema but it owned by the postgres user

- in this case you will NOT see that data in Desktop

- solution is to alter the table to grant ownership to the schema owner

ALTER TABLE map.table OWNER to MAP;

The second most common reason why nongdb data would not be visible in Desktop is that the objectid column is not defined with the "NOT NULL" constraint.

The references to the pg trace showing that sde_layers table does not exist, is a just a check for the presence of that table, which in the case of a nongdb is correct in it's absence.

Sincerely,

Kasia