This post may cover some similar ground to that which Dave has been treading but I’m just trying to document my experiences as they occur.
By virtue of my association with zigGIS, I’ve been involved with using PostgreSQL and PostGIS in the ArcGIS envrionment for some time now. One of the primary sources of excitement about 9.3 for me was that ArcSDE would finally support PostgreSQL as an RDBMS platform. ArcSDE has always been positioned as an enterprise platform and has, therefore, been rather expensive. This expense has been compounded by the need to also separately license an RDBMS platform such as Oracle, SQL Server or DB2 in addition to ArcSDE. PostgreSQL helps alleviate some of that cost while also providing very advanced capability.
PostgreSQL is an outstanding, enterprise-quality RDBMS platform. My hope is that the support by ESRI will raise its profile here in the States to the point that more organizations will realize how well it matches up to its better-known proprietary counterparts.
I have been experiementing with the ArcSDE 9.3 for PostgreSQL beta and release candidate. This post will describe what’s been working for me. Before going on, however, I stongly recommend reading Paul Ramsey’s post debating the need for ArcSDE. The post itself and the Nabble discussion it references are both outstanding discussions that contain many points I agree with. My post will assume that you intend to use ArcSDE with PostgreSQL.
One thing that becomes readily apparent is that the single-database model with direct connect is the way to go with PostgreSQL. If you run the default installation and post-installation, you end up with the typical “sde” database with all of the ArcSDE system tables and functions and the like set up as can be seen in this screen clip from PgAdmin. 
The post-installer will also give you the option to create a service to use a giomgr process. If you choose to do this, you will end up with the usual ArcSDE service using esri_sde on port 5151. This is where the design of PostgreSQL comes into play. PostgreSQL uses both databases and schemas. Schemas are contained within a database. Schemas are roughly equivalent to databases in SQL Server (I said roughly). PostgreSQL does not allow cross-database referencing so it’s important not to get caught up in “SQL Server thinking” when considering databases. This screen capture shows what I mean (I tried registering a PostGIS layer in the arcsde_test1 database using the esri_sde service which connects to the sde database).
What this means in terms of ArcSDE is that the old multiple-database model (one “sde” database and multiple user databases with spatial data) doesn’t really work here. With SQL Server, you could use the esri_sde giomgr process to access spatial data in any database on the server. In PostgreSQL, it gives you access to data in multiple schemas in one database. If you want to store data in another database, you need to run the post-installer to set up the ArcSDE objects in that database and let it create another service listening on a different port. You can see how this could get cumbersome.
So, really, it’s just time to give up the giomgr process and just use direct connect or do all of your spatial work in one database. My preference is for direct connect (which has been the ESRI-recommended approach for a while now, anyway). In this case, you still use the post-installer to set up the ArcSDE objects in your database but you don’t create the service. You’ll then use a direct connect string to access your data (sde:postgresql:localhost for example), specifying your database.
This means you’ll have the ArcSDE objects installed in each database you want to use with ArcSDE. This approach not unlike what you need to to with PostGIS. In fact, if you want to use PostGIS geometries with ArcSDE, you will need to install both PostGIS and ArcSDE in your database. In this case you need to create the database first using the PostGIS template database and then install the ArcSDE objects into the database. ArcSDE always installs in its own schema so its pretty well segmented from everything else.
I tried setting up a PostGIS/ArcSDE template database to simplify things but it simply didn’t work. For the ArcSDE objects to be properly configured, you have to use the post-installer. This is something that ESRI may want to take a look at in the future since using a template database in quite convenient and sort of the standard approach in PostgreSQL.
So now I have a couple of databases configured with the ArcSDE and PostGIS objects. Although I will eventually get around to testing ST_GEOMETRY, my real interest is in seeing what I can do with the PostGIS data type. In my next post, I’ll take a couple of different paths to get data into PostgreSQL/PostGIS/ArcSDE and see what types of things I can do.
To be continued…
Sounds like you’re using a versioned geodatabase. I may not fully understand your issue. Can you send me some screen shots? My e-mail is on the “About” page.
Thanks for the infos Bill.
I forgot to mention that I was able to do a query “SELECT * FROM username.A” or “SELECT * FROM username.D” (A&D table are the Add and Delete table for my FS1 table) through psql. Both A&D tables are using ST_Geometry.
Why can i do the “SELECT” query on both A&D tables ? they are using the same datatype as my FS1 table.. Is this because there is no primary key defined in my FS1 table ?
thank you so much for answering my question.
🙂
You’ll want to use PG_GEOMETRY instead of ST_GEOMETRY if you plan on querying through psql and want to use the PostGIS spatial functions. PG_GEOMETRY causes ArcSDE to use the PostGIS spatial type.
That said, you should be able to query the attribute data in psql regardless of which spatial type you use.
I addressed the primary key issue in item 1F of this post: http://geobabble.wordpress.com/2008/08/21/using-arcsde-93-with-postgresql-part-35/
Hello Bill,
I have managed to load data (i put it in feature class, called ‘fs1’) on Postgre with Postgis through arccatalog.
I want to see this ‘fs1’ through “psql to postgres” but i have no records.
SELECT * FROM fs1
when i tried to view the table through PGAdmin, “There is a warning that i should put Primary Key”.
When i view the table through arccatalog it returns some data. Why is this happening ? I used ST_GEOMETRY for the storage.
i am able to edit,add and delete the data on ‘fs1’ through arcmap.
Anyone could help me ?
That is the schema that is owned by the sde login role. This schema is created by the ArcSDE post-installer. If you want to create a use schema for any login role. Here is the SQL:
CREATE SCHEMA AUTHORIZATION ;
Please what is: “sde user schema” and how can i create it in the postgis database???
many many thanks
Answering to self; the key is here http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=35128 : “17. Follow the instructions in the Post Installation wizard. For all dialog boxes in the Post Installation wizard that require the database name, provide the name of the database that has PostGIS installed in it.”
Now I can see my postgis_template generated DB also has the sde schema. Still, it does not have the same privileges thant the stock sde DB I generated before, but at least I seem to be getting somewhere.
Hi,
Trying to dot the i’s and cross the t’s, I’m at a loss installing the the ArcSDE objects into the database. I got my template_postgis clone DB, but how do I get the ArcSDE objects in there ?
http://webhelp.esri.com/arcgisserver/9.3/dotNet/index.htm#geodatabases/using_th485132809.htm says : “if you want to use the PostGIS geometry type, you need to … manually create a database using the PostGIS template, an sde user, and an sde user schema in the database. Then you can complete the rest of the postinstallation steps using the wizard by clicking Custom on the first dialog box of the Post Installation wizard, clicking next, and unchecking Define SDE User Environment.”
I ran the ArcSDE post-installation process prior to installing PostGIS (didn’t know at the time postGIS needed to be there beforehand) so now I have to re-run it. I would appreciate a little more explanation or “ArcSDE for dummies” pointers as to how to do this.
TIA
Regina,
You are right about schemas in SQL Server 2005+. I knew that but was focusing on the difference in database behavior. I should have been clearer. Thank you for elaborating.
As for licensing, my understanding is that you will need a license for each server, at a minimum. There will probably be considerations for sockets/cores/chicken blood/burnt offerings/etc. Obviously, I can’t speak for ESRI and we’ll probably have to wait and see if they make changes to licensing at 9.3. They have made some moves to simplify licensing over the past year.
What I will say is that I was able to use both of my databases on one EDN authorization. Read into that what you may.
Bill
Bill,
Thanks for claring up some of my confusions.
So does this mean you don’t need to pay for an ArcSDE/ArcGIS license for each PostgreSQL server you have?
Slight nit-pick. SQL Server 2005+ supports schemas too and those are pretty much exactly the same as schemas in PostgreSQL, except stupid SQL Manager Studio 2005 shows all the tables together simply prefixed by the schema. This is one of the few ways I would say PgAdmin III excels over SQL Manager Studio. I hope they change that in SQL Server 2008.
But your point is well-taken that since PostgreSQL doesn’t allow cross-database access except via dblink which is pretty weak, the whole schema idea doubles for a database model.
that is what I have been waiting for – PostGIS and SDE connection to the same data, if I understand you right. Looking forward to hear more…