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:
- 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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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:
[sourcecode language=”sql”]
ALTER TABLE myTable
ADD CONSTRAINT mytable_pkey PRIMARY KEY(objectid);
[/sourcecode]
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:[sourcecode language=”sql”]
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);
[/sourcecode]
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.
- 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:
- 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.
- Again, use the PG_GEOMETRY keyword.
- The SRID issue discussed in item 1e above may be relevant here as well.
- 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.
Would be great! Thanks.
Hmmm. I will need to look at it more. I have not experienced this issue.
sure, if the button versioned editing is checked there is no editing possible, because the FC is not registered as versioned.
That’s not the problem. I dont want to edit versioned data.
The error seems to me as if ArcMap wants to call a function that ensures the unique ObjectID but since the layer is registered wit user maintained OID there is no such a function.
Sounds like it wants to treat your data like it’s versioned. Have you checked the options in the ArcMap Editor toolbar to ensure that it’s not trying to use versioning?
Editor->Options->Versioning
I have tested the ArcSDE with PostGIS and was able to Import data and display it with ArcMap and Quantum GIS and SpatialCommander (no success with uDig since it is not displayed).
I can even edit Date with all this Clients but when I create a feature with ArcMap and after this one with another Client then there is an error: “duplicate key value violates unique value constraint”. Its the same the other way around.
The OBJECTID is maintained by SDE:
sdelayer -o register -l layer1,the_geom -e a+ -x -5120900,-9998100,1000 -z 0,1 -G 25833 -C gid,SDE -t PG_GEOMETRY -D postgis -g RTREE -k PG_GEOMETRY -s xxserverxx -u xxx -p xxx -D postgis -i
I think it has something to do with the sequence on the GID Field – maybe it is not updated properly???
Any ideas? How do you managed this?
Thanks,
André
That’s probably it exactly. When you register the layer and say ArcSDE will manage the OBJECTID, that means that ArcSDE will calculate the next value for the column. If you have that column set up with a sequence, there’s a good chance it will collide.
When you register the layer try using “-C gid,USER”. This will signal ArcSDE not to attempt to calculate a value.
Hi Bill,
I tried this – but then you can not edit/create features with ArcMap. It throws an error:
Error: Function or Procedure does not exists
SQL-Statement from Serverlog: SELECT postgis.krb.i58_get_ids($1,$2)
ERROR: function postgis.krb.i58_get_ids(integer,integer) does not exits at character 8
???
Jonathan,
I don’t know that we have tested that particular scenario. Right now, zigGIS doesn’t “see” views unless you manually edit them into the geometry_columns table. You may also need to remove a reference to the mixed geometry table.
That said, we are working on a roadmap to address these issues (among others) at 2.1.
I would also suggest posting your question to the zigGIS Google Group. That’ll get it out to the whole development team and a good chunk of the user community.
Thanks for stopping by.
Bill
Bill,
Great information… thanks. I have a question regarding the object type constraint in the data sets. I am using a different GIS desktop app (that I fell in love with from a previous job), and my GIS person uses ArcGIS (nonSDE). PostGIS & ZigGIS looks like a great way to work (at the very least) on the data I’m managing without having to translate. My application can deal with datasets with multilple GIS object types. My question is: if (editable) view relations are set up in PostGIS with only one object type, will this work, and avoid having to limit each base table to only one object type?
Thanks
Jonathan
Regina,
It doesn’t expect a field called OBJECTID. If you use the sdelayer -o register apporach, you just need to tell it what your PK is but it can be named whatever you want. (It does need to be an integer, though.)
If you create your layer by loading data in via ArcCatalog, it will create a column called OBJECTID.
Position of the OBJECTID column doesn’t matter, either. I like your way. It’s much more elegant.
Bill,
In regard to your item f. Still haven’t done much with my ArcGIS install – been tied up with mostly non-GIS projects. So ArcGIS expects a field called objectid?
Is there any reason not to do this or does ArcGIS expect the objectid to be first?
ALTER TABLE mytable DROP COLUMN objectid;
ALTER TABLE mytable ADD COLUMN objectid serial PRIMARY KEY;
Just thinking it would be cleaner since this will drop the sequence object as well if you ever delete the table. Then again I guess if you want the same sequence across all your tables your approach is better.
No problem. I’m glad you found it useful. Sounds like you’re having fun. Currently, I am running in sand….er….working with Oracle.
I guess I should have forced a page refresh before posting my answer to self ;-). Thanx for this post series Bill. Helped me a lot.
Replying to self again (sorry to expose my ongoing learning of ArcSDE; I hope it helps another clueless ArcSDE user) : when importing a feature class through ArcCatalog, click the “environments tab” and set Geodatabase Settings/Output CONFIG keyword to “PG_GEOMETRY”. To verify the data was indeed uploaded with a PostGIS geometry, look at the SQL Pane of the “shape” column in pgAdmin and make sure you see something like
… ADD COLUMN shape geometry;
A feature class that shows
… ADD COLUMN shape st_point;
was not uploaded with a PostGIS geometry.
At least that´s what i think is going on.
In the “Feature Class to Feature Class” tool, you have to scroll down to the bottom and you’ll see a header titled “Geodatabase Settings (Optional)”. Expand that and there will be a dropdown for configuration keywords. If you pull that down, you’ll see PG_GEOMETRY as a choice. Also, you can just type it in there.
I haven’t taken the step of setting it in my dbtune.sde. No particular reason why I haven’t, though.
I don’t think you can view it directly in pgAdmin. Although, after using the spatial results pane in SQL 2008 Management Studio, that would be a nice feature. Of course pgAdmin is open-source and I’m sure that contrib would be appreciated. 😉
A little more dotting i´s and crossing t´s : “Always load your data with the PG_GEOMETRY configuration keyword”. I set this as DEFAULTS in the dbtune.sde file :
GEOMETRY_STORAGE “PG_GEOMETRY”
I couldn´t find a way to mention PG_GEOMETRY in ArcCatalog or did I miss something ? Can I use pgAdmin to see what geometry was stored? Thanx for you very helpful post series.
Thanks for the information Howard. I also should have been clearer that my statements regarding versioning applied if you planned to connect through PG. If you use the SDE C or Java APIs, versioning shouldn’t be a problem. Thanks for pointing that out.
Bill
If you do version your data, GDAL 1.6 will be able to take advantage of it. 1.6 will be the next GDAL release and is expected by the end of the year.
MapServer has been able to handle versioned SDE layers since about 4.8 or 4.10 (assuming you’re connecting using the SDE SDK instead of directly to PG).