So the session was hampered by some corrupted data on the USB sticks, and was more a loosely guided tour of the stack than anything else. I had PostGIS up and running, but could not get my data (loaded into ArcSDE on PostgreSQL using the PostGIS datatype) to load into GeoServer. Tried some other data, and same problem. Unclear what the issue is.

Many of the sample SLD's would not validate (hence could not be loaded), and I could never get the WMS group layers to work. I did get some data up in Google Earth, and open layers, but overall this session confirmed for me that the open source stack learning curve is steep from the get go. I need to get into OpenLayers as the demo's were pretty cool but until I can get the back end all working smoothly it's somewhat moot.

I think that the docs for this will be up somewhere. The software installers are below.

Downloads: http://files.opengeo.org/where2/stick/software

Thanks to Ron Bruder who left a comment on the previous post with the link directly to the documentation for using the PostGIS geometries in the geodatabase. I did have some weirdness trying to access the link - of course you'll need to login because it's still beta documentation, but even after, that I'd see the page, and then it would jump me back to the login screen. I was able to access it by saving the link to Html and then viewing it. Moving on...

Using PostGIS Spatial Format

The ArcSDE doc says that to store data in PostGIS format, you need to create the "database" (presumably the "sde" database) using the postgis template, then create the sde user and sde schema before running the post install. Ok. Since I'd already done this, I dropped the sde database, then re-created it as per the instructions and... yeah.

So the post install failed. It did handily suggest looking at some logs buried away in the sde folder tree, and low the log did have a handy little gem:

db_table_grant_access: Grantee public does not have USAGE permission on schema sde.

Ok. Back into Navicat, grant USAGE to Public on the sde schema, and the post installer is completed. While my end goal is to use Direct Connect, to start, I'm just going to run the SDE service until I know everything else is working.

The next step is to get ArcGIS to use the PostGIS format. For this you need to edit the "dbtune file". From past experience with ArcSDE on SQL Server, I thought that "dbtune" was now a table, and it's a whole lot easier to edit a table than fiddle with the dbtune export/import mess. So, I simply updated the GEOMETRY_STORAGE parameter in the DEFAULTS section of the SDE_DBTUNE table to be "PG_GEOMETRY". So now I should be able to use ArcGIS Desktop to edit / manage data in PostGIS, while still accessing the data as native PostGIS format for say GeoServer and uDig.

Adding and Editing Data

I used the standard Import feature in ArcCatalog, and this worked smoothly. I then fired up uDig to see if it would show up a native PostGIS layer... yep... along with all the other tables in the sde schema. I think that there are likely better ways to organize your data, so this is likely not a "real" issue.

I had some issues applying a thematic renderer to the layer - for some reason it would only apply the renderer to the vectors. When I looked at the SLD xml, everything was setup as a <sld:LineSymbolizer>... since it looks fine in ArcMap (see below), and the original shapefile loads into uDig as polygons, this may be an "beta" issue. Ordinarily I'd check the beta forums right now, but I'm writing this at 38575ft en route to Where 2.0, so that will have to wait.

Loading data via ArcSDE into ArcMap works very smoothly.

And after telling ArcMap to let me edit "unversioned" data, I could edit as usual. uDig says I can't edit the layer. Being really new to this whole stack, I'm not sure if it's permissions (although I connect as the same identity in ArcMap), or a limitation (heck - I don't even know if uDig can edit vanilla PostGIS layers!)

I also wanted to try a direct connection - I stopped the ArcSDE service. Worked just fine - note the "service" name.

Summary

I've got a few other things I want to mess with, but I've got the basics working. It's been a few miss-steps but not too bad once I read the documentation. Although I've been using ArcSDE on SQL Server for a long time, it's definitely worth reading the docs when setting this up. Clearly I'd need to do more digging on optimizing the database structure, permissions and other sundry tuning things, but if you are looking to trade some time learning PostgreSQL for the $$$ involved buying Oracle or SQL Server, it's pretty compelling. I can't say much about performance as this is just one small layer (US Counties) running on my notebook. I do know people who are running some big databases on PostgreSQL + PostGIS (like millions of spatial features in a layer) and they've got it blazing.

So my company is sending me out to Where 2.0 - I've never been and expect it will be quite different from many of the other GIS conferences I've been to - more MacBooks and soul patches I'm guessing.

I'm particularly excited about the pre-conference workshops, as it will be a chance to get the low down on some technologies I have not worked with in the past.  I'm hitting the Geo-ify your Web Site session in the morning (Mapstraction) and the The Open Geo-Stack: OpenLayers, GeoServer, PostGIS session in the afternoon.

In prep for the workshops I thought I'd get PostGIS (with ArcSDE 9.3 beta) installed on my notebook - ideally I'd like to roll into the session with a little background so I can ask relevant questions!

Installing Postgresql + PostGIS + ArcSDE 9.3 (beta)

Overall, not exactly the smoothest installation I've ever done. No step was particularly difficult, there were just points where I had to do some head scratching, Googling and then "just do it". I have to give ESRI credit as the ArcSDE installer was smooth as silk, and they included Postgresql 8.2 on their media. However, having not used PostGIS before, exactly how this fits into the mix was a little unclear. I will say that if you have used ArcSDE in the past, and just want to get rolling without paying for a database - the install is really pretty easy.

As noted, I installed PostgreSql 8.2 directly from the 9.3 beta media - this is super simple. I then downloaded the PostGIS Installer for PostgreSql 8.2. Again this was pretty easy, after you locate and download it. As per the ArcSDE instructions, I then ran the ESRI post installer, authorized ArcSDE and all looked good.

I could immediately connect to the sde database as the sde user, and used ArcCatalog to load a shapefile into PostGIS. Smooth.

But how do I know that this is actually using "PostGIS"? Would this data be accessible to non-ESRI clients?

Enter uDig. This was a quick and painless install, and uDig was installed.

When I browsed to my local PostGIS server, things were not looking good.

Where was my data? Clearly something is not lining up here. When I tried to connect to ArcSDE via uDig (after I located the jar files) it could not connect. Not sure if this is related to using the beta 9.3 jar files, or something else.

Anyhow, I decided I should try loading the same data via the command line post gis tools. Here I ran into a bit of a wall. The documentation for the PostGIS tools is not bad, but certainly aimed at the unix sys admin type of person. The basic process is to convert your shapefile into a Sql file, and then run that in Postgresql. Seems simple. A quick little batch file ought to work this right out. The sql file was created just fine, but it failed to load.

Sorting this out really meant it was time to do a little learning about Postgresql. After sorting out schemas, and learning that the most likely encoding of my shapefile was "LATIN1" I was able to load my data and "see" it in uDig.

Actual loading the layer into uDig never completed. The application just sort of hung stating that the "Project command" was executing. This was just a map of all the counties in the US, so not sure what the issue was, but I'm pretty sure it was my fault.

I then tried to connect via ArcCatalog to see if I could "see" this layer... and not too surprisingly I could not.

Summary

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. I'm sure that if I read more of the ESRI documentation I'll find some answers, but at this point it's unclear to me if they will be supporting direct access to PostGIS data, or if they will be supporting the PostGIS spatial format for data stored in traditional ArcSDE schemas (i.e. with all the GDB_ and SDE_ tables). I also learned that I've been sheltered from the reality of "data" for many years!

I'll continue this series as I make progress getting things sorted out.