Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question
1  
Please edit your question to focus on one topic. Since choice of data storage is subjective (and likely to be closed as opinion-based), you would be best served by focusing on the Query Layer error (for which you would have to provide the table definition and the resulting SQL query). – Vince Feb 29 at 17:43
    
Per your request, I've asked a separate question here. gis.stackexchange.com/questions/182761/…. Will edit this question. – ccleve Feb 29 at 18:03
1  
You really need to provide both the table definition and the SQL statement you generated in the Query Layer UI, so we can interpret "The SQL statement was not a select statement" – Vince Feb 29 at 18:27

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.

share|improve this answer

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.

share|improve this answer
    
I tried to use QGIS a couple years ago and really didn't like it, but just tried it again and wow, what an improvement. It is pretty easy and worked perfectly with PostGIS right out of the box. – ccleve Mar 1 at 23:42
    
Same here. Tons of work has gone into it in past couple of years. My favorite features is the Db Manager Plugin -- there you can write ad-hoc PostGIS queries (even PostGIS raster queries) and dump the result on your Map View. We cover Db Manager and pgRouting Layer plugin in our upcoming book - locatepress.com/pgrouting. If you are interested in design tricks you'll want to check out locatepress.com/qmd which just came out in print yesterday – LR1234567 Mar 2 at 17:23

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:

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.