37

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.

21 Answers 21

29

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.

  • 10.0 (this page may not view correctly in Chrome, so I have used IE to read)
22

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.

18

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:

  1. use PostGis with ArcSde (so you need an ArcSde license, and ArcEditor if you need to edit data)
  2. use zigGIS: http://www.obtusesoft.com/ (note that is not tested on ArcGis 10.0). You will just need an ArcView box to connect to it, even for writing data.

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

  • 4
    -1, not because it was a bad answer, but because the passage of time and consequent changes has made it so (zig discontinued, now native arcgis support for Post geometry) and this answer shouldn't be at the top anymore. – matt wilkie Dec 12 '14 at 14:17
9

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.

  • I followed Bills blog, and the esri documentation to successfully get Postgresql and PostGIS working with ArcSDE. I agree that you need to use the version supported by esri, even though they may sometimes be hard to find e.g Postgresql 8.4.1 was tricky to locate. – Ando Mar 21 '11 at 22:34
5

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.

  • Version 3 will be open source. The only issue with it right now is that it does not management through ArcCatalog - meaning you need to use PgAdmin or SQL to admin your DB. Other than that you are good – TheSteve0 Jul 23 '10 at 6:38
  • To add to SteveO's comments, here's the ZigGISv3 Road Map: abegillespie.blogspot.com/2010/06/on-to-30.html The provider model will be a huge improvement. – Jay Cummins Jul 23 '10 at 11:34
  • Is thee free version 1.0 still available? – fmark Jul 30 '10 at 4:05
  • Yep, free version is available here: code.google.com/p/ziggis But it's oooooold, we don't support it any longer, and you can't do editing (which was the major feature introduced in version 2.0). – xanadont Aug 7 '10 at 23:58
  • I saw this post come through another list source recently that stated "zigGIS has officially reached its end-of-life since the next version of ArcGIS should support direct read / write interoperability with PostGIS (thereby rendering zigGIS moot)." groups.google.com/group/ziggis/browse_thread/thread/… – RyanDalton Aug 12 '11 at 15:41
4

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.

postgis1 postgis2 postgis3

As an alternative, there is also arcgis-ogr, which allows connections to all OGR vector types as an ArcGIS plugin.arcgis-ogr. It is also read-only at the moment.

3

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

  • 1
    I saw this post come through another list source recently that stated "zigGIS has officially reached its end-of-life since the next version of ArcGIS should support direct read / write interoperability with PostGIS (thereby rendering zigGIS moot)." groups.google.com/group/ziggis/browse_thread/thread/… – RyanDalton Aug 12 '11 at 15:43
3

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

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/What_are_database_servers_in_ArcGIS/003n0000004r000000/

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

  • 2
    I've had success using ArcSDE SQL Server Express geodatabases with multiple users/editors, but it has been a few years since we set it up. The GDB is created on one user's workstation, other users in the office connect to its instance, and each user does edits in his or her own version, which were then posted to the default version when desired. This was all installed from the Desktop install media, no ArcGIS Server involved, I'm pretty sure there were no licensing implications beyond each user having his Desktop ArcEditor/ArcInfo-level license. – MC5 May 8 '13 at 21:10
3

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.

Download and instructions here

  • hi, is this still read-only? – Matt May 20 '13 at 9:00
  • 1
    programmatically you can actually write through the ExecuteSQL functionality. I just have not built an interface that allows to do it through ArcMap. So you can write through code, but not through the GUI yet. – Ragi Yaser Burhum May 20 '13 at 22:32
3

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]

  • I heard st-link is only free for one year. After which you need to have a license. Isn't it true? – OPENGISTECH Jun 7 '12 at 15:02
3

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.

3

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.

  • Mike, looking @ the documentation ST-Links seems like it may have awesome potential. Have you used it personally? Do you have any impressions of its speed and usability you can share with the community? – RyanDalton Jul 21 '11 at 23:27
  • Actually, I'm still figuring out the install process .. I have ArcGIS 9.3 installed with a custom configuration, but I don't see pgMap 1.0 in the Extensions yet .. hopefully someone else can share their experience – Mike T Jul 21 '11 at 23:34
  • 1
    Please forgive the link to my own blog, but I took a look at PgMap after the announcement that zigGIS was shutting down. My observations are here: blog.geomusings.com/2011/08/09/taking-a-look-at-pgmap Given that it looks like ArcMap will not be able to directly edit spatial databases at 10.1, PgMap looks all the more compelling. – Bill Dollins Aug 19 '11 at 11:03
2

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.

  1. Select your version
  2. "Software Downloads"
  3. "DMBS Support Files"
  4. Scroll down until you see "PostgreSQLQL Client Libraries (Windows)", should be 2.21 MB in size.

  5. 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:

  1. 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).
  2. Once the SQL had been validated then click on Show advanced options the finish button will change from Finish to Next>
  3. Click on Next> and you can then access the advanced options dialog.
  4. 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.
  5. You can also set the spatial reference system of your data if it is appropriate to do so.
  6. 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,
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

2

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 licemse levels since ArcGIS 10.0.

  • Vince, what is the process to set up a writable non-SDE PostGIS database connection in ArcGIS 10.4+? Trying to add a database connection via ArcCatalog Advanced 10.5 gives a "connections to system databases are not allowed" error. – Rudy Stricklan Jun 8 '17 at 16:19
  • 1
    There is no process, beyond creating the database, administering it to contain data, and connecting to it. If you want to try this, and it fails, you can ask a new question here (providing all the details). If you're trying to work in the master database as the postgres user, you really need to get a book on RDBMS use first. – Vince Jun 9 '17 at 1:33
  • Thanks Vince. Yes, the error message is a bit confusing, as I'm trying to connect to a spatial table in a vanilla PostGIS database ("gis")-- not the master database. Your advice is right on, though-- I'll document the issue and submit a new question. – Rudy Stricklan Jun 9 '17 at 17:02
1

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

1

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.

  • Can your 'power users' edit in the ArcGIS environment or is it still read only? – user17963 May 9 '13 at 20:04
  • yes GISquirrel allows full editing in ArcGIS. – Matt May 9 '13 at 21:28
  • Interesting! What about Topology checks? – user17963 May 9 '13 at 22:13
  • It works is by creating a temp geodatabase for the user's selected features and locks those features to the user for editing, using ArcGIS you can then edit the feature in the geodatabase, once the edits are saved, the features are updated in PostGIS. It is also possible to edit all the feature in a layer at once. – Matt May 9 '13 at 22:43
  • 1
    Thanks Matt I'm going to test out using GISquirrel and see if it works with our data! – user17963 May 10 '13 at 16:32
1

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

1

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

0

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.

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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