What's wrong with my ArcGIS - PostGIS setup?

Question

I have installed ArcGIS and PostGIS, and I'm having a hard time connecting them.

I've imported some shapefiles into PostGIS and I can see them and manipulate them in pgAdmin.

The trouble is that I can't see the geo data in ArcMap. I successfully created a database connection to my local Postgres server, and I can see a list of tables including the shapefile I added, but clicking "Add Data..." in ArcMap fails silently. If I try "Add Query Layer" and click the table, I get an error "The SQL statement was not a select statement."

What's wrong with my setup?

(I'm using ArcGIS 10.4, Postgres 9.4, PostGIS 2.1)

Answer 1

Here are a couple of items to explore to determine why ArcGIS is not registering your PostGIS tables as feature classes:

• ArcGIS will refuse to acknowledge tables that have mixed geometry types. To clarify, "POLYGON" and "MULTIPOLYGON" types can be mixed within the same column, but POLYGON and POINT cannot. In your case, coming from a shapefile, this is not likely the problem.
• ArcGIS reads the list of tables out of public.geometry_columns. As of PostGIS 2.0, this is a database view reading from pg_catalog. Do your imported shapefiles appear in this list? Do they have appropriate values in the coord_dimension, srid, and type columns?
• While it does read tables out of the geometry_columns view, you should also make sure that the schema in which you've placed your tables is accessible by the PostgreSQL account used to connect from within the ArcGIS application. If the schema does not match the user name, you may want to look into altering the search_path variable. You will also be unable to do much if there is not a schema with the same name as the user connecting to PostgreSQL. ArcGIS uses the schema that matches the user's name to store log tables. This documentation might help clarify some of your issues.
• You can always alter the table or create a view with type definitions (aka shape::geometry(Geometry,4326) as shape) to allow ArcGIS to accept these tables.

I've found ArcGIS to be very intolerant of completely valid PostGIS tables that do not fit its model of how data should be stored within an RDBMS. Have you tried opening one of your shapefiles within ArcGIS and simply exporting the data to your PostgreSQL database? (You will definitely need a schema that matches the connected user's name to do this. The user must also own the similarly-named schema.) Once you have a table that is recognized by ArcGIS, you can then use that as a model for configuring your other tables.

Answer 2

Is there a reason you need to use ArcGIS.

If you just need to view the data QGIS is a better choice for viewing/editing PostGIS data. You can do all that manipulation you need to do in PostGIS easily.

Worst case you can export the result to shapefile using shp2pgsql-gui or pgsql2shp command-line

If you are set on ArcGIS, check your PostgreSQL log file, should give the error ArcGIS is triggering.

Answer 3

I now see in Is the native ArcGIS database capable of doing generalized data manipulation?, to which I also responded, but that I didn't know was related to this one, that you attempt to run an UPDATE SQL statement via the Query Layer interface. That is not possible. Query Layers in ArcGIS are read-only.

You either need to buy an ArcGIS for Server license and upgrade your database to be an ESRI Enterprise Geodatabase, or, if you can live with the limitations of a Microsoft SQL Server Express database (10GB max, one core), you could install that and have a free Desktop Geodatabase, because that is part of your ArcGIS for Home Use license. Unfortunately, Desktop Geodatabases are only supported in SQL Server Express, not in any of the other geodatabases supported by ArcGIS.

I explained how to install a Desktop Geodatabase here: https://geonet.esri.com/message/118404#comment-118404

And I recently noticed ESRI finally seems to have better documented this as well, at least I can't remember seeing these pages before:

• http://desktop.arcgis.com/en/arcmap/10.3/manage-data/database-servers/setup-database-server.htm

• http://desktop.arcgis.com/en/arcmap/10.3/manage-data/database-servers/create-geodatabase-database-server.htm

As LR1234567 writes, QGIS may be a better option in your case.

I also wonder why you actually wish to do this type of SQL update through a GIS. If there is no real spatial component to your question, doing it command line in PostGIS is probably the way to go.

You generally resort to GIS when most of your manipulations are spatial, as the required SQL to do it could quickly become unwieldy.