A look at PostgreSQL and ArcSDE

A Look at PostgreSQL and ArcSDE

May 9th, 2008 · 23 Comments · GIS, Google Earth

Dave Bouwman has some thoughts on using PostgreSQL as a RDBMS for ArcSDE (or ArcGIS Server Enterprise as we should be calling it).

Thus far I’ve simply come to realize that I have a lot to learn. I need to grok a lot more about Postgresql and PostGIS to start, and then add ArcSDE into the mix.

While everyone is really excited about PostgreSQL support at 9.3, remember it won’t be as easy to administer as SQL Server is (at least to those who already use SQL Server). Just keep that in mind before abandoning SQL Server outright for PostgreSQL.

Tags:

23 responses so far ↓

1 Andrew Larcombe // May 9, 2008 at 2:00 pm

Mind you, SQL Server isn’t as easy to administer as PostgreSQL (at least for those who already use PostgreSQL)…
2 AlbertW // May 9, 2008 at 2:18 pm

Andrew: I don’t think too many ArcSDE admins are running Postgres right now.
3 George Silva // May 9, 2008 at 5:40 pm

I´m with Andrew. Some time ago i sent you a message James, about PostGIS and SDE, remember?

You supported me, and now i can tell you!

I got really into it, and i´ll tell you. It´s something to look for. Nice enviroment, really easy to use, and really good community.
4 artlembo // May 9, 2008 at 6:02 pm

we’ve had some really good experiences with PGAdmin. Its really easy to use. Having already used SQLServer Management Studio, it was easy to make the transition.

We’ll be putting out some results of our findings from a special topics class (http://faculty.salisbury.edu/~ajlembo/415/415.html ). When its done, I’ll let James know. Maybe it will be helpful to some people.
5 George Silva // May 9, 2008 at 6:10 pm

SQL Management Studio is great and easy to use.

Another great thing for PostgreSQL!
6 zac spitzer // May 9, 2008 at 11:38 pm

Really? I find the SQL Management Studio in 2005 to be a horrible clunky .NET application which shakes, jitters,resizes and grinds a lot.

I preferred the 2000 equivalent which was much snappier and old school.

Postgres SEQUENCES are much better than SQL Server’s auto stuff.. much more flexible for complex apps, scripting and dataloading
7 Arnold // May 10, 2008 at 8:50 am

I tried to install Postgresql a couple months ago and had a ton of trouble. SQL Server 2005 is much easier by far.

I can’t speak to Postgresql itself vs SQL Server (I’ve had no problems with SQL Server) but admin wise I felt very out of my league.

We’ll probably stick with SQL Server as we know it, we are comfortable with it and it works well for us. I’m sure there are folks that prefer one piece of software over another, but I think the out of the box SQL Server system is much easier.
8 Luis // May 10, 2008 at 10:28 am

Postgres-Postgis-QGIS installation worked pretty well for me, even better than using MySQL (however, I have only tried Windows XP). You can load shapefiles straight to the postgres server without having to use a third party software, and you can also do it using QGIS plugins.
We don’t have SDE at work, so I’ve put on my list of things to learn how to use postgres-postgis replication features, which is what we need at work right now. Another nice feature from postgres is that it implements spatial queries using SQL (I couldn’t find these in MySQL right away, does anyone know?), but I haven’t had much time to test these applications. I’m sure learning will take a lot of coding, but the postgres-postgis-qgis bundle seems to provide a very good alternative to running queries on spatial datasets compared to regular ArcGIS tools. I’m very pleased Postgres will be supported in ArcGIS 9.3.
9 Bill // May 10, 2008 at 10:53 am

I have SQL 2005, SQL 2008 and PostgreSQL/PostGIS running. I have found PostgreSQL to hold up very well against SQL Server. PGAdmin does a great job and, IMO, does a better job than SQL Server Managment Studio and holds up well against the old SQL Server Enterprise Manager.

Spatial functions is PostGIS far outstrip SQL 2008 right now but PostGIS doesn;t have a geodetic data type similar to the SQL 2008 Geography data type (which I’m liking better every day).

That said, PostgreSQL is different and there is a little bit of a learning curve but I highly recommend putting in the effort.
10 Andrew Larcombe // May 10, 2008 at 1:41 pm

Sorry. Didn’t mean to start a ‘my Dad is better than your Dad’ thread, just merely to point out that one man’s ‘easy to administer’ is another’s ‘horribly clunky’…

@Luis: MySQL does have spatial queries, but IIRC these operate only on the bounding box of a feature. Rubbish, I know…
11 Cellulose // May 11, 2008 at 10:01 am

Hey, what about a thread for us crazies who’ve been waiting for Postgres so we can abandon Oracle?
12 AEMPhil // May 12, 2008 at 5:52 am

Hi all. Long time reader, first time poster.

From the perspective of someone who has been using PostGIS for a while, doing a bit of GIS analysis mostly using SQL queries, since spatial joining and spatial where clauses are so easy, what are the reasons why I would want ArcSDE on top of it all? I suppose the question holds true for any SQL spatial DB, not just PostGIS.

I can imagine that it would enable connectivity for ArcGIS desktop products, but other than that I am at a loss. Granted, that is a big deal, but projects like zigGIS can provide the connectivity as well.

As for other features, like replication, clustering, etc., PostgreSQL is likely to have extensions to do that anyway. Does ArcSDE play nice with things like PgCluster or Slony-I?

Any thoughts?
13 Doug // May 12, 2008 at 7:23 am

@AEMPhil: With SQL Server 2008 coming out soon with spatial abilities, I have wondered the same thing. I think one of the main answers is that ArcSDE provides some advanced tools such as versioning, archiving, topology, geometric networks, network datasets (i.e. routing), etc. For definitions of these, check ESRI’s website. I don’t know how many, if any, of these are available in a multi-user RDMS without ArcSDE. If you don’t need to use these, I think you don’t need ArcSDE.
14 AEMPhil // May 12, 2008 at 12:40 pm

@Doug: thanks for the reply. Your point on routing, geometric networks, etc. is well taken. These are core GIS functionalities that eluded me. I do believe that there exists a project called pgRouting for that, but I will simply admit ignorance on the issue and gratefully accept your point.
Topology rules are more easily implemented in PostGIS that SDE though IMO (PostGIS at home, SDE on top of Oracle at work, as a user, not maintainer); setting triggers to make sure that two geometries relate in the proper manner is rather easy on PostGIS.
As for your other points -versioning, backup, etc.- do you not think that these would be better left to the RDBMS administrator to handle? These issues have to be managed regardless of whether the data is spatial or not; shouldn’t we be looking for measures that are independent of the spatial character of the data as well? Another way of putting it is: shouldn’t we perform DB maintenance tasks with DB tools and GIS tasks with GIS tools? SDE feels to me as though it muddled that demarcation line.
15 Doug // May 13, 2008 at 7:28 am

@AEMPhil: Versioning and archiving in ArcSDE are different concepts than similar sounding RDBMS concepts. Check out ESRI’s website for more info. Back to the main point, I’m not saying that ArcSDE can do a lot of stuff PostGIS can’t. I don’t really know what PostGIS can do, so I can’t make an adequate comparison. I’m saying that there are things ArcSDE can do that simply spatially enabling an RDBMS can’t account for. GIS users often need more than a spatial data type and some functions that allow you to query based on geography. I’m not an expert on these things, so take my comments for what they’re worth.
16 BigB // May 13, 2008 at 9:59 am

Support for PostGIS as the RDBMS for SDE is just another tool in the toolbox. I think you would be hard pressed to find commonly used analytical functionalities that ESRI will provide that PostGIS does not. However for data creation and data editing tasks on the PostGIS via SDE connectivity will be a good option for some.
17 Paul Ramsey // May 13, 2008 at 10:20 am

@Doug, @AEMPhil, the elephant in the room is ArcMap. 95% of the value ArcSDE provides is the integration with ArcMap, and of course that value is artificially created by ESRI’s determination to force all spatial database interaction through the ArcSDE API, either locally through the cynically mis-named “direct connect” or remotely through a more conventional ArcSDE middleware server. ArcMap provides the GUI tools for data management and presentation, and there is value in that tool set, so much value that ESRI can use them to force their client base to pay for a whole extra layer of unnecessary software.
18 Volkmar // May 15, 2008 at 4:09 am

Well, I’m looking for alternatives to the proprietairy ESRI databases for a long time. Our problem is the imlementation of a data model including many non spatial objects which are related to spatial tables. The ESRI Geodatabase (Access or SDE) does not support the database constraints as it should. It just uses the database as a storage space- or am I wrong? I would love to have full integration with the database capabilities. I try to use PostgreSQL/PostGIS to implement my data model and use ArcMap for editing spatial tables (using ZigGIS). Trigger / Procedures in PostGIS will then insure consistency/topology etc.. However an extension for editing related alphanumerical data in ArcMap has to be programmed in any case. Has anybody an alternative?
19 Doug // May 15, 2008 at 6:11 am

@Volkmar: There are problems with using ArcSDE feature classes like tables in a normal database. I’m sure you know that you can’t have unique indexes on anything but the objectid if your feature class is versioned. You might think that’s no problem if you don’t need to edit this feature class using versioning, but ArcSDE forces you to version the feature class if you want to set up topology and other advanced features on the feature class. It can be very frustrating for someone trying to use it like a real RDBMS. There are work arounds, but none of them are ideal.
20 Tyler // May 17, 2008 at 12:09 pm

Longtime SQL Server user and recent PostGIS convert…
A year ago I was using ArcSDE w/ SQL Server and now I am using PostGIS exclusively, although we still have an ArcSDE Server running to support old projects. The primary reasons for the switch were (1) the need to escape the license server and (2) my frustration with ArcSDE’s limited spatial views (i.e. sdetable –o create_view constructs) which severely limit what can be done with rich normalized data models, a point also mentioned by Luis, Bill, and AEMPhil. (My background was first in database design and later in GIS, so I have found the simplistic ArcGIS joins to be limiting.) Since the initial switch I have found PostGIS to be superior in pretty much every everything I use it for… although it’s probable that ArcSDE provides some additional functionality that I haven’t found a use for.

@AEMPhil: I agree with your point on the DB vs. GIS functionality, but I would add that I think it is appropriate for the DB to provide the basic geospatial functions (i.e. intersection, buffer, etc) that are commonly considered ‘GIS’. Another way to think of the division of functionality would be to have the DB handle the data (storage, filtering, and and standard spatial analysis) and the GIS handle the presentation (i.e. UI) and any spatial functionality that the DB doesn’t do.

And in regards to the original point of this thread, I have found PostgreSQL to be as easy to administer as SQL Server. And once you understand how the schemas work, you have another powerful tool for organizing your db objects and for setting up authorization.

Overall, PostGIS is an amazingly useful and beautifully designed thing…
21 Stefan Holzmann // May 19, 2008 at 1:38 am

I have a question.

Is it correct that at the moment I cannot access ArcSDE with QGIS?

We have an ArcSDE server at work and I would like to use QGIS. Are there any workarounds so I can do it?
22 ChrisW // May 19, 2008 at 12:11 pm

Tyler: “My background was first in database design and later in GIS, so I have found the simplistic ArcGIS joins to be limiting…”

Glad it’s not just me!

Chris (20 years Oracle, 9 months GIS - so far!)
23 Gary // May 19, 2008 at 6:45 pm

@Stefan
QGIS doesn’t have the ability to use ArcSDE layers. This would require compiling the underlying GDAL/OGR with ArcSDE support and a custom plugin to allow QGIS to load the data.