ArcGIS Connectors for Postgis
This post is a discussion of my experiences with a couple of ArcGIS connectors for Postgis. To me, a reliable Postgis connector would be so useful, as we simply don’t have the resources (or the inclination) to purchase the equivalent proprietary products. We are also looking for a solution that will provide a flexible backend for all sorts of interfaces, such as MapGuide Open Source, UDig, and so on, alongside ArcGIS.
The two connectors for ArcGIS that I have tried so far are Ziggis and PGarc. Neither of these are what I would call production-ready yet, and have some problems but they are being actively developed so there is hope!
Ziggis:
I spent a rather frustrating day yesterday trying to install the new release of Ziggis and thought it was worth blogging the steps I had to take to get it installed as I found information on this to be confusing and sparse. Digging around in the ESRI discussion forums and such like seems to suggest that my problems will be relevant to the subset of people who don’t have things like Visual Studio installed, and for all I know will not be relevant in ArcGIS 9.1 or later. The short version is as follows:
- Ziggis requires the Microsoft .net Framework and Arcobjects .net Assemblies to work,or the install will fail.
- To get the Arcobjects .net Assemblies, you must have Microsoft .net version 1.1 installed before you install ArcGIS and you must choose the custom install of ArcGIS Desktop (should be disk one).
- .net version 2.0 won’t work for ArcGIS 9.0 (don’t know about later versions).
- If you already have ArcGIS installed before you install .net then you will need to do a full uninstallation of ArcGIS, reboot, install .net, and then install ArcGIS. Trying to cut corners and running the repair/reinstall/modify option won’t work.
Once I had it installed, I had an extra button in ArcMap for adding layers from a postgis table. Ziggis using configuration files (.zig files) for providing the information about connecting to the database. There is an example file in the program folder that can be modified using a standard text editor. You add new layers by navigating to the appropriate zig file for the database you wish to connect to, then type the table name that you want to add. This is a slightly frustrating step because you have to know which table you want, ie by firing up some other Postgis/PostgreSQL administrator first. A method of browsing for available tables would be a bonus.
Unfortunately, so far I haven’t managed to get anything to display on the map! The layer appears in the table of contents, and I can open the attribute table, but the geometry column contains the value “no geometry” despite being able to display using a different connector (PGarc). The next step is to try and enable the debugging and logging options in Ziggis to establish the problem.
PGarc:
PGarc appears to be in a state of flux at the moment. Some development is ongoing as there are discussions on the postgis mailing list about it, but the website version has not been updated in some time. A few months ago I was given some updated code in the form of an ArcGIS project, and this version at least works for me- with an important proviso (more later).
PGarc installs itself as an extra toolbar with two buttons, one to add new data from a database, and one to save data back into a database. To connect to a database you need to create a system dsn with the connection details and provide the name of that file, along with your username and password to PGarc. PGarc does list the available tables, from which you can pick one and add it to your map.
Saving data back into a database seems to work, although it only saves the visible extent (so you have to zoom out to the extent of a layer before saving it).
My biggest problem with it is that if I have deleted a table in my PostgreSQL database, then PGarc cannot connect to it because it is still looking for the deleted table. I don’t yet know if this is related to the dsn, PGArc, or PostgreSQL.
Comparision:
Both projects are at an early stage and as you can see I have had problems with both of them. PGarc seems to have the best feature set at the moment (you can select features, which is not possible, afaik, with Ziggis) and save data back into the database. However, the idea of text-based configuration files is more convenient than a system dsn, as they can be easily transferred between computers. I need to investigate the geometry issue with Ziggis more thoroughly, and will keep following the discussions, and then I should be in a better position to comment.
There is at least one other connector available, but I didn’t get further than downloading the instructions! Furthermore, ESRI have intimated that Postgis connectivity will be available in a later release of ArcMap. This should be a good thing, but there are concerns as to how it will be implemented. Watch this space…
Hi!
You could try the FME ESRI Edition or its twin - the ArcGIS Data Interoperability Extension - to read PostGIS Data directly from ArcMap.
It works very well.
You can get a 15 day FME Eval on http://www.safe.com if you want to try it out.
Greetings from Switzerland,
Jeff
Hi Jeff,
Thanks for the suggestion. We are trying to go for free options if at all possible, but I will try out the evaluation.
Archaeogeek
[...] I have made a little more progress with evaluating the various free options for accessing PostgreSQL/Postgis database tables from ArcMap. I have to confess that some of the problem was down to my own lack of experience with Postgis! [...]
Quick comment on PgArc. The code looks at the table geometry_columns table in your database. If you simply delete a table in your database it does not update the geometry_columns table and will cause you problems. In Postgres/PostGIS run the command:
SELECT PROBE_GEOMETRY_COLUMNS()
to make sure everything is up to date.
Bruce
Hi Bruce,
Thanks for popping by. I figured out the issue with the geometry_columns_table in the end, so it all works fine now. Out of interest, do you know if anyone is still working on the PgArc project?
Thanks
Archaeogeek
I updated the code for PgArc to run on 9.1 and correctly sets the projection but have not posted it to the source site. I am watching zigGIS developments to see if it is a better solution. Let me know if you want the latest code I have.
Bruce
Hi Bruce,
Thanks for popping by, and thanks for updating the code to work with ArcGIS 9.1. ZigGis developments do seem to be coming thick and fast, but I think it’s worth having the two approaches, so I’d like the code if you could send it to me.
jo dot k dot cook at googlemail dot com
Thanks
Archaeogeek
zigGIS Update…
Paolo has posted an update to zigGIS. This update deals more effectively with data layers that have an SRID associated (SRID != -1). They now seem to overlay well. On-the-fly reprojection is still not working but he is working on it. ArchaeogeekÂ� recen…
Hi Can someone please give me the latest site where we can download zigGIS? Is there recent developments which enables writing back to PostGIS aswell?
Hi Francois,
You can get ziggis from here: http://code.google.com/p/ziggis/
AFAIK it doesn’t quite do writing back to Postgis yet but that’s very high on their list of priorities.
Thanks for stopping by!
Jo
Thanks Jo
Sorry for only comming back now. Good luck to everyone working on the Ziggis project, I think it’s great work!
Francois
Hey guys;
Thanks for posting those tips. I am having trouble installing ZigGIS on my ArcMap9.1 machine. I have .NETs 1.1 and 2.0 and have the 9.1 ArcObjects library. My current train of thought (thanks to your advice above) is that I installed .NET 2.0 after ArcMap9.1.
Also, I have been working with PGARC. My main purpose is to add support to Microstation. Which I have done as far as reading from POSTGIS.
Enhancements:
1. Added checkbox to load data only in display extent (leveraged off of Bruce’s work).
Improvements:
1. Allow re-clicking of Database Connection button (used to cause error). This allows changing of the ‘Load only Display Extent’ checkbox.
2. Added syntax to allow POSTGIS file names to contain capital letters (capital letter table names must have quotation marks around them).
Fixes:
1. In loading data from POSTGIS to temporary shapefile, the attribute field after the geometry column was getting dropped and causing errors in certain instances. This was fixed.
I am having some code organizational issues. Should I include Microstation and ArcMap support within the same VBA project?
I am open to thoughts.
Hi Bob,
Thanks for popping by! Your fixes to pgarc sound great- it would be good to see them merged with Bruce’s work. Regarding the organisational issues with your code- I don’t know but I guess if people have a look in on the VBA in (say) ArcGIS and see lots of additional code it might confuse them somewhat. That’s just my opinion though- go with whatever works best for you, and keep up the good work!
Jo
Dear All,
How can i connect Postgresql/PostGIS to ArcGlobe.Please any one can help me.It is Urgent.
Advanced Thanks.
Regards
Venkat.
Hi Venkat,
Since we don’t actually use ESRI products much any more, I can’t help you with this, though hopefully someone else might be able to. It is worth trying the ESRI support forums as well.
Thanks for stopping by
Jo