Using ArcSDE 9.3 with PostgreSQL, Part 3.5

I thought I was done with the series a while back but I’ve been getting a steady stream of questions through other channels so I thought I’d wrap up a lot of the common stuff in another post. Most of the inquiries come from people trying to integrate ArcSDE for PostgreSQL with open-source tools in one way or another. Here are a few notes:

1. If you plan to load data into PostgreSQL with ArcCatalog but plan to edit it with tools such as uDig, gvSIG or zigGIS, then you want to do the following:
   1. Always load your data with the PG_GEOMETRY configuration keyword. This will cause your spatial objects to be stored in the PostGIS format. None of the open-source tools I’ve used can understand ST_GEOMETRY.
   2. Don’t version your data. uDig, gvSIG and zigGIS (what I’ve used) only “see” the DEFAULT version anyway. They can’t take advantage of versioning.
   3. Although ArcCatalog does put an entry in the PostGIS geometry_columns table, it lists the geometry type as GEOMETRY. gvSIG edits this fine but it seems to monkey with uDig a little. It’s not a bad idea to update this with the actual geometry type. These will be string values such as “POLYGON”, “MULTIPOLYGON”, etc. You’ll need to do this if you plan to edit your data with zigGIS from an ArcView desktop.
   4. Related to the previous item. If you were to load data using shp2pgsql or any other method that calls the PostGIS AddGeometryColumn function, you would end up with constraints on your table limiting the geometry column to a homogenous geometry type (e.g only polygons) and a homogenous SRID. Loading via ArcCatalog doesn’t do this (and I’ve run into other tools that don’t as well). Quite frankly ArcSDE manages all of that for itself so it doesn’t need such constraints. Without them, however, you can use other tools and mix your geometry types and SRIDs in the table. It’s a good idea to add these constraints and I have noticed no ill effects on ArcGIS if you do.
   5. As we discovered with zigGIS, there is a difference between the spatial references that are defined by default on the PostGIS spatial_ref_sys table and the ArcSDE sde_spatial_references table. This makes it possible to load data via ArcCatalog that doesn’t have a corresponding SRID in the PostGIS table. This would effect your ability to add the previously discussed constraint. It will also throw off open-source tools that look at the spatial_ref_sys table. It’s a good idea to update spatial_ref_sys to include those from ArcGIS that are not already supported.
   6. ArcCatalog doesn’t define a primary key on your spatial table. uDig really doesn’t like this so it’s best to add a primary key on the objectid column (this has no effect on ArcGIS that I can tell) using the following syntax:

      ALTER TABLE myTable
      ADD CONSTRAINT mytable_pkey PRIMARY KEY(objectid);
      

      gvSIG additionally expects the column with the PK to “auto-number” so you may want to experiment with adding a sequence to the column. The syntax I used to do this is:

      CREATE SEQUENCE mytable_gid_seq
      INCREMENT 1
      MINVALUE 1
      MAXVALUE 9223372036854775807
      START 408
      CACHE 1;
      ALTER TABLE mytable_gid_seq OWNER TO postgres;
      ALTER TABLE myTable ALTER COLUMN objectid SET DEFAULT nextval(’mytable_gid_seq’::regclass);
      

      If you’re experienced with ArcSDE, you get nervous mucking with the objectid column. I have made both the of the above changes to several data sets and made edits in uDig, gvSIG and ArcMap with no ill effect.

2. If you plan to edit/manage your data using ArcGIS but serve it out using an open-source application like GeoServer or MapServer then there are a few considerations:
   1. If you plan to use versioning, then be diligent about posting and reconciling with DEFAULT so that any changes want to have show up will. Of course, this assumes you would just be connecting to the PostGIS data. If you connect through ArcSDE, this may be unnecessary.
   2. Again, use the PG_GEOMETRY keyword.
   3. The SRID issue discussed in item 1e above may be relevant here as well.
3. The implementation of ArcSDE on the PostgreSQL platform is pretty solid. I have been able to do everything I could do on other platforms. So, if you plan to stay in the ESRI environment, go forth an conquer. I think you’ll be pleased with PostgreSQL. You may want to bone up on PostgreSQL database administration, though.

Ultimately, support for PostgreSQL by ArcGIS opens up the possibility of numerous technology mixes. It’s somewhat uncharted territory and it’s not a bad idea, as with any system integration task, to do a little testing and experimentation before moving on with an implementation.