I have done this before with out too much trouble using ArcGIS 10.1 and 10.2 unfortunately it does not work with 9.3 and postgres 9.2 I think from memory.
I used the drivers from esri.
Logon to the ESRI customer care site although I think this has changed since I wrote the instructions.
- Select your version
- "Software Downloads"
- "DMBS Support Files"
Scroll down until you see "PostgreSQLQL Client Libraries (Windows)", should be 2.21 MB in size.
For PostgreSQL/PostGIS in the downloaded file should be the "pg_client_windows86" client set of libraries containing the necessary 32 bit version of libeay32.dll, libiconv-2.dll, libintl-8.dll, libpq.dll, and ssleay32.dll. Copy these to your ArcGIS bin directory.
On my computer using 10.1 it was:
C:Program Files (x86)ArcGISDesktop10.1bin
If you are using 32bit windows it would be something like:
Once you have done this you should be able to connect to and add data from your database. To use a data query layer you will first need to connect to your database.
In ArcGIS 10.1 you will need to go to File>Add Data>Add Query Layer
The only thing to watch for is that the data being returned has to have a unique field that can be used as a primary key by ArcGIS. Sometimes you may need to specify it if you are using more than just a basic query and ArcGIS cannot work out which field to use. You can do this by:
- First validate the SQL query by clicking on the Validate button
(ArcGIS needs to generate a list of fields that are in the select
statement to populate the advanced options dialog box).
- Once the SQL had been validated then click on Show advanced options
the finish button will change from Finish to Next>
- Click on Next> and you can then access the advanced options dialog.
- In the advanced options dialog you are given a list of all the
fields for the table. Select one field that will act as a unique
identifier field - the default is to have all the fields selected.
- You can also set the spatial reference system of your data if it is
appropriate to do so.
- Click finish and then your layer will be added to your map.
You can also run the spatial queries against the postgres data base with relative ease although you have to manufacture an id field on the fly. e.g. Here is an example of doing a 100km buffer.
SELECT row_number() over(order by cities.the_geom)::integer as oid,
ST_BUFFER(cities.the_geom, 100000) AS the_geom,
FROM mygis.public.cities As cities
Not only that you can also save any query layer as a layer file and pass it through some of the standard ArcGIS tools as well. I have not tested this too much. So adding columns and stuff like that I could see that causing havoc. I think you could make spatial SQL calls with other databases like SQLServer and Oracle as well with a bit of fiddling to create the on the fly id field.
I did a full tutorial a while back at: