How can I connect to a PostGIS database from ArcMap using ArcGIS Desktop 9.3 and later?
I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-spatial joins, filtering etc.) rather than just dumping the contents of a table.
I don't want to use the ArcSDE spatial extensions, I want to use the PostGIS spatial extensions in ArcGIS Desktop.
If you are using ArcGIS 10.0 or later, then you can directly connect to the PostGIS Data using a Query Layer, there is more information on this available in the help of each version:
To use the PostGIS geometry type, the database administrator must install PostGIS on the PostgreSQL database cluster. PostGIS is a third-party, open source installation. Once installed, the database administrator can use the PostGIS template database to create a database containing the PostGIS geometry type, or configure an existing database to use the PostGIS geometry type.
ArcGIS 10.1 and ArcGIS 10.2 both natively support PostGreSQL and PostGIS data types. Included in the help for both versions is a walkthrough of gettting set up, and configuring tables to use the PostGIS geometry types.
Give a look at this post on my blog: http://www.paolocorti.net/2008/06/06/spatial-database-for-postgres-and-arcgis-users-how-to-choose/
Basically you have 2 options:
Note that if you need geodatabase support (domains, topology etc...) or ArcCatalog support, the first solution (with ArcSDE) is the only way to go at this time.
As far as I have heard (I did not directly test it) on ArcGIS Desktop 10 you can make a read only direct connection to PostGIS without the ArcSDE gateway.
zigGIS is no longer active and the website is offline
I have a few posts on doing it with 9.3. The first is here and you can get to the rest of them from there: http://geobabble.wordpress.com/2008/05/28/using-arcsde-93-with-postgresql-part-1/
I've done it once with 10.0 and have had no issues. I will say that, when using PostgreSQL and PostGIS with ArcSDE, that I strongly recommend sticking to whatever versions are supported by Esri.
Easiest would be zigGIS from Obtuse Software. Currently you have to pay for it, but word on the street is that version 3 will be open source.
According to the Google Code Archive on ziggis:
zigGIS v1.2 is no longer supported although it will remain available here for download. zigGIS v2.0 is now a commercial product and includes major stability improvements, performance increases, and full editing capabilities. For more details, please see Obtuse Software's website.
and the link to Obtuse Software's website seems to be broken.
ArcGIS 10.1 SP1 can connect to PostGIS 2.0.0 databases natively, but the connection is read-only, and mostly functions as a cleaner front end to query layers (in fact, it just loads any layers as query layers). The database connection just allows you to view all the tables and layers in the database in catalog.
As an alternative, there is also arcgis-ogr, which allows connections to all OGR vector types as an ArcGIS plugin.
. It is also read-only at the moment.
First of all: You will only be able to use ArcGIS with PostgreSQL using OLE DB connections, meaning, you will only be able to read common tables and collumns (you even will be able to read the spatial collumns, but ArcGIS cannot do anything to them,
To use ArcGIS and PostgreSQL + PostGIS (meaning that you need to see spatial data), you will need ArcSDE or ZigGIS.
With both options you can query, edit and analyze data stored in PostGIS, inside ArcMap or other ESRIs tools.
ArcSDE is a middleware provided by ESRI, changing the whole workflow (of installing, configuring a geodatabse, etc) of work and ZigGIS is desktop tool (I mean, only used when ESRIs desktop tools are involved).
If you have ArcEditor or ArcInfo desktop level, you have the ability to use SQL Server Express. Even though only one user can edit at a time, the locking and unlocking may be better -- you might try this out first. There lot's of documentation on how to do it, and you don't have to be a dba -- though I like postgres. No offense QGIS folks ;)
Also, make sure that your Esri sales guy gives you a quote for "ArcGIS Server Workgroup", not Enterprise. See below -- you can have 10 concurrent editing connections. It should be more like $3-5k. Pricing http://www.esri.com/software/arcgis/arcgisserver/pricing
See also this post for a good explanation of the licensing and links about SDE and SQL Server Express ArcSDE desktop license
"You create geodatabases and perform other administrative tasks for database servers through the Database Servers node in the Catalog window or ArcCatalog. Performing the administration of the database server and its geodatabases through ArcGIS Desktop means there is no extra software or database administration expertise required for you to create and use these types of ArcSDE geodatabases.
Connections to the geodatabases on a database server are always direct connections; they use the ArcSDE library files in the client to make the connection. In this case, the client applications are ArcGIS Desktop at the ArcEditor or ArcInfo license level, ArcGIS Engine, and ArcGIS Server Workgroup.
The media for these products include installation files for SQL Server Express. Once you have created the SQL Server Express instance and run the wizard to enable the instance to store geodatabases, the libraries within the client application allow you to connect to and work with the database servers and create and work with geodatabases on the database server.
With ArcGIS Desktop (ArcEditor and ArcInfo) and ArcGIS Engine, you can set up a database server and create ArcSDE geodatabases that can be accessed by a few users and edited by one user at a time.
With ArcGIS Server Workgroup using ArcGIS Desktop, you can set up a database server and create ArcSDE geodatabases that can be accessed by up to 10 users at a time, all of whom can be editing concurrently. When using the database servers licensed through ArcGIS Server Workgroup, you can also connect to the geodatabases using Web applications, for which there is no connection limit."
I wrote a plugin that gives ArcGIS access to 50+ vector formats (including PostGIS). It is still experimental, but you can try it out and tell me how it goes.
There are advantages of using this approach over the built-in functionality in ArcGIS (look at the FAQ), but again, it is still experimental.
I have been monitoring this post and the wider web for a solution to this as I wanted a similar tool. Today I bumped into my (our) solution via RSS feed to James Fee's blog . And I believe the solution you seek is PgMap by ST-Links.
I have tried the ArcGIS 9.3 version and it's impressive. Still struggling with edits though since I'm still a PostGIS newbie (identity column thing). It also comes with a sleek ESRI to PostGIS loader and above all it's FREE! [Tested with OpenGeoSuite Community Edition 2.4.1]
Go to Start -> Control Panel -> Performance and Maintenance -> Administrative Tools -> Data Sources.
Go to the System DSN tab.
Click Add.
Scroll down in the list. You should be able to see your PostgreSQL ODBC drivers there if you installed them. Click the first PostgreSQL ODBC driver in the list.
Enter your connection details in the form. If the connection is on the same machine as the PostgreSQL database, write localhost in the server field; otherwise, the name of the computer on the network. You'll have to make some changes in the pg_hba.conf file to connect to your database on the network. Read about it in the PostgreSQL manuals in the User Authentication section. After this is done, click Finish.
Add all the PostgreSQL ODBC drivers you find in the list similarly.
Click OK. You may now connect to the PostgreSQL database through the ODBC drivers. The drivers only needed to be directed to the database with connection information.
As of 2011 era, try ST-Links SpatialKit. The software is freeware, and works with ArcGIS 9.3 / 10.0 / 10.1 / 10.2.
The download has a nice PDF to document the capabilities, which include viewing, editing, etc.
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.
Scroll down until you see "PostgreSQLQL Client Libraries (Windows)", should be 2.21 MB in size.
Click Downloads
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: C:Program FilesArcGISDesktop10.1bin
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:
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,
cities.name
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: http://www.gisuser.org.nz/resources/tips-and-tricks/look-mum-dad-no-hands
Effective with ArcGIS 10.4, you can read and write to PostGIS geometry in supported PostgreSQL databases without need for any additional extensions. I've only used an Advanced license for this, but I believe a Standard license can also connect to a non-geodatabase PG server database and use that workspace as a destination for vector creation tools. It's more cumbersome, but you can also use Basic license clients to write to tables using SQL with Python (via arcpy.ArcSDESQLExecute). Read-only Query Layers have been an option with all license levels since ArcGIS 10.0.
Will PostgreSQL 9 work with ArcGIS 10?
For Editing Postgis ZigGIS 3.0
http://groups.google.com/group/ziggis/browse_thread/thread/8e17f4c2ac57f428?hl=en
Read Only can be done via the right ODBC Postgres Drivers and doing a direct connection in ArcCatalog
GISquirrel does the job at a fraction of the cost of SDE. It supports arcgis connection to both MSSQL and PostGIS. Very simple to setup (able to import to postgres from shapefile/featureclass) and simple to maintain. For a small number of users who need multi-user edit capability, its just fine.
We use GISquirrel/Arcgis for our GIS 'power users' and QGIS can connect to the same PostGIS server for our 'basic users', which saves on licence costs.
GISquirrel does the job very well for MSSQLserver, and I am quite sure it will work well for Postgres. I work in a mixed ESRI/Qgis environment, and I use GIS squirrel also to import shapefiles etc. into the database. In SQLserver GISsquirrel keeps track of the geometry columns, I use this information to update the geometry_columns table used by Qgis. Very handy ...
PgMap was replaced by st-links spatialKit and not only supports PostGIS, but also supports SQL Server 2008. It works with ArcMap 9.3, and ArcMap 10. It just meets your requirement. Check it out at www.st-links.com
I believe you have several options outside of using SDE (although I will point out you can use PG_Geometry in SDE, therefore accessing data via ESRI software or PostGIS compatible OS software). You have the ESRI Data Interoperability extension, ZigGIS, and you could probably install a copy of geoserver or mapserver and connect via a WMS service in ArcGIS. Similar to the previous post about zigGIS and the need to manage queries via pgAdmin, you would need to use it to create your queries with geoserver/mapserver. Ideally, if you were reusing the same queries, you could savee them as views in postgresql and access the data that way.
ST-Links SpatialKit is an ArcMap Extension for directly connecting to spatial databases with No ArcSDE, No ArcInfo, No ArcGIS Server.
According to their License, it costs $188 CAD, but they state "We will continue to issue free licenses for those users that can not afford the license fee. Free licenses have time limits. If you request a free license, please give the reason in your license request email."
An easy way of adding PostGIS data to ArcMap is by adding an 'Interoperability Connection'. To do this, the 'Data Interoperability Extension' is required.
It's considered a good idea to add a 'Numeric Index' and 'Primary Key' to the PostGIS Database Table before creating the connection.
SDE.DLLstill exists, and forms the basis for all connection files (.sde), but "there is no SDE, only enterprise geodatabase."