AnsweredAssumed Answered

ArcGIS desktop db connection to PostgreSQL w. POSTGIS empty?

Question asked by bogibrynjar on Jul 4, 2015
Latest reply on Jul 15, 2015 by ktuszynska-esristaff

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).

 

catalog_view.PNG

 

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!

Outcomes