Well, 9.3 is now shipping so the sun is setting on my time with the release candidate. One update from my first post: I posted the problem I was having with sdelayer -o register not calculate a default extent on the Beta forum and I got a reply that it shouldn’t work that way and they would look at it. I’ll probably see shortly whether a fix made it in this time or if I have to wait for a service pack.
The last piece I wanted to cover was working with the layer that I loaded in from a shapefile using ArcCatalog. As I indicated before, I used the PG_GEOMETRY keyword to force the use of PostGIS geometry.
I first left the layer as a simple feature. Within ArcGIS everything behaved as would be expected. I could note no difference in behavior versus what I would expect dealing with any other ArcSDE layer. I was also able to add the layer into uDig, but with a little anomalous behavior.
The layer I was working with was a polygon layer. When I loaded the layer into uDig, it displayed as a polygon layer but the uDig TOC showed a line symbol:
Additionally, the fema_regions layer was not editable in uDig. The other layer editable but had the same display issues. To be fair, I had similar behavior with some layers I had loaded into PostGIS from shapefiles using FWTools. In that case, it didn’t happen with all of the layers I loaded. In this case, I have not experimented with enough data sets to determine if the behavior is consistent or intermittent. I would also need to test whether it’s the loader, PostGIS or uDig. Time has not been a friend here.
Next, I did some experimentation with versioning. I versioned my milbases layer, created a test version, added a feature and save the edit. As expected, when I connected in uDig, it connected to the DEFAULT version and I did not see my edit. Since this layer was editable in uDig, I added a feature here and refreshed my ArcMap display. In this case, I was able to see the uDig-created feature (in DEFAULT) in both versions (again, as expected).
My previous posts have brought a few questions about raster catalogs and the like. I have been able to load raster data and create raster catalogs. This is not, however, raster data in PostGIS. This is ArcSDE raster data next to PostGIS. PostGIS has no idea the raster data is there (although the raster footprints display in uDig as rectangles).
So, depending on how you implement it, ArcSDE for PostgreSQL can provide interoperability between ArcGIS and an open-source stack. uDig can be leveraged in a pinch to edit data loaded into ArcSDE using the PostGIS geometry type, allowing you to expand to meet demand. Additionally, ESRI has implemented the full geodatabase capability within PostgreSQL, providing a great alternative to shops that was GIS but not necessarily Oracle/DB2/SQL Server.
In future posts, I examine different technology mixes that can be used to build some compelling hybrid stacks.
Now, where’s my 9.3 shipment?
geoMusings 
Comments
16 responses to “Using ArcSDE 9.3 with PostgreSQL, Part 3”
[…] brings to the ArcGIS stable at 9.3. Bill Dollins has been working on a couple blog posts about Using ArcSDE 9.3 with PostgreSQL. Just seeing ArcMap push to PostgreSQL and then using uDIG to view the data is powerful. Says Bill: […]
Great Job, Bill!
One question about using geometry types with the ArcCatalog-input way, have you tested postgis functions with those geometries?
Gustavo,
Great question! Yes, I did some simple tests with some of the conversion functions (WKB, WKT, GML, etc.) and they worked fine.
Bill
Hi Bill,
i´m thinking in postgis spatial queries ( geometries isValid results, geometries with holes, multigeometries…) and networks analysis ( taking postgis data and building networks… I have no access to 9.3 yet but got a lot of question :). Any experiment?
That shouldn’t be too hard but my computer is about 900 miles away from me at the moment. I’m currently on vacation. 😀 I’ll test those when I get back.
Bill
Nice work Bill! And by the way: have a nice vacation! 😀
@Paolo
Thanks! I plan to!
I’ve been doing a lot of testing with this stack also, and find it to be quite good thus far. I have built a geometric network with PG_GEOMETRY storage, and have so far not found any issues.
One note: I prefer Quantum GIS to uDIG. No real reason other than the uDIG installations I’ve tried all seem to have issues connecting to the PostGIS data, while Quantum GIS picked it straight up. http://qgis.org/
Interestingly, I’ve had the exact opposite experience. The last couple of QGIS releases have given me problems on Windows whereas uDig has worked flawlessly for me. I guess I have different gremlins on my box. 😉
Hi,
New Gremlin in tha house! my soft option ( at his moment) for Postgis jobs is GvSig. Edition and connection control works fine… But i´m a newbie traveller into QGis because its python support. Now one can connect qgis,r,postgis,enthougth python, etc… glued with python. Imagine creating new data analysis, integrating with a matematical model and viewing the output as a mesh in mayavi… Now this is possible with these tools, but integrate it with Arcgis is difficult…
Arcgis working with postgis it´s a good idea, but an arcobjects python api will be a great one too!
I have also played with gvSIG and been impressed. I also recommend Jo Cook’s portable GIS work. I am posting from my mobile so it’s hard to provide a link but her blog is called Archaeogeek. With ArcSDE supporting PostGIS, all of these technologies are now related to a degree. I plan to explore these kinds of technology mixes in future posts. After my vacation of course.
Hi Bill,
your testing is much appreciated, being a long time sde and arcgis user I got inspired to download udig and try out the postgis type. But I ran into problems with editing the postgis data in udig, I was wondering what did you have to nudge to get it to work.
I did the following:
– create the table with three columns
– id column became the pk – per a postgis blog post
– execute the addGeometryColumn function adding vertexes for a single polygon, i specify the type to be POLYGON, sde registeres the data as GEOMETRY, but according to Dave Bowman’s blog that caused problems, so I used POLYGON, gave it a srid of 2226 which is in North Carolina
– created gist index on the geom column
In udig, I used the POSTGIS connection, when I bring that postgis data in to udig, udig displays it as newLayer not by it’s name, draws it , I can select a feature but when I click on the edit tool it tells me that it can not edit that layer.
Would you have any suggestions? sorry to post a newbie question, but you were able to do this so I must be missing something.
Kate
Kate,
I’ll try to tackle your questions next week when I am back from vacation. Thanks for stopping by.
Bill
Kate,
Every time I loaded a layer into ArcSDE for PostgreSQL using ArcCatalog, it was not immediately editable in uDig. Compared to what happens when I load data via shp2pgsql, here are the differences that I have noted:
– There are no constraints on geometry type defined in PostgreSQL so I assume ArcSDE is managing that
– There are no constraints on dimension
– There is not a sequence/default value defined for the objectid column
– objectid is not defined as a primary key
It does create a spatial index and it also creates a unique index on objectid.
Consistently, the thing that finally makes the layer editable in uDig is when I define objectid as a primary key using syntax similar to the following:
ALTER TABLE myTable
ADD CONSTRAINT mytable_pkey PRIMARY KEY(objectid);
I discovered that gvSIG would allow me to edit the layer without the PK defined but the NOT NULL definition combined with a lack of a sequence caused an error so you *may* want to also define a squence on the objectid column with something similar to:
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);
I hope that helps.
Bill
Oh yeah, I should re-iterate that I have had similar things happen when data is loaded using other tools so it doesn’t seem to be unique to ArcGIS.
Bill
[…] – bookmarked by 6 members originally found by ascola on 2008-12-17 Using ArcSDE 9.3 with PostgreSQL, Part 3 https://geobabble.wordpress.com/2008/07/01/using-arcsde-93-with-postgresql-part-3/ – bookmarked by […]