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.

Our EDN licenses expired on Oct 30th and while I knew this was coming, I did not get to installing our new 2008 licenses until they had timed out on our ArcSDE server(s).

While you can use the ArcSDE Post Install tool to authorize ArcSDE using your new keys, if you have more than a few databases, it's much faster and easier to apply the license manually. (I have >10)

The license information (at least for SQL Server) is stored in the SDE_Server_Config table. Just copy arcsdeserver line of your ECP file (looks something like this: arcsdeserver,92,ecp123457890,30-oct-2008,S0M3H45HTH1NG) into the CHAR_PROP_VALUE column for the AUTH_KEY row.

Alternatively you can use this sql:

UPDATE    SDE_server_config
SET char_prop_value = 'arcsdeserver,92,ecp123457890,30-oct-2008,S0M3H45HTH1NG'
WHERE     (prop_name = 'Auth_Key')

If you have 100's of databases, get crafty with some T-SQL to iterate over all the databases and run the query...

I ran across a very handy open-source tool today that I thought I'd share... but first some background...

Our current project requires that we support 4 simultaneous run-time configurations - Development, Testing, Training and Production. The application has the capability to switch between these on the fly, via  a convenient pull-down menu. 

Changing the configuration is actually somewhat simple - we just close the current map document, set a new "repository connection" and refresh the UI. From there, the application will start using the configuration in the active repository. Great.

Well, today I needed to actually create these 4 repositories. More specifically I needed to create 4 databases with the same schema AND the same data (as a starting point anyhow). In the past I had used a CodeSmith template that would write out both the schema and all the insert statements to re-populate it, but that was a pain to use. I did a little searching and found the Database Publishing Wizard which is a part of the SQL Server Hosting Toolkit on CodePlex (you can get the source!).

From the site...

The tool supports two modes of deployment:

1. It generates a single SQL script file which can be used to recreate a database when the only connectivity to a server is through a web-based control panel with a script execution window.
2. It connects to a web service provided by your hoster and directly creates objects on a specified hosted database

For me, I just needed the SQL script that would re-create the entire database with data. What's nice (besides being free) is that this integrates into Visual Studio's Server Explorer...

The the output is a text file with all the SQL to re-create the tables, views, stored procedures etc. and their contents. I was able to just drop this into SQL Management Console, and run it against the other 3 databases. Since it's just T-SQL, I can also upload it to our client's site, and run it there to populate their databases as well.

Command Line

A final gem in this tool is that it can be run from the command line - thus allowing you to effectively source control a database schema and data. While you would not want to do this for spatial data, it would work very well for configuration data. You could write a simple MSBuild script that would check out the copy from source control, use this tool to dump the database to that file, and then check it back into source control. Then you could execute this script anytime you want to update the copy in source control. Anyhow - the command line give you lots of options for integrating into your development process.

Some quick items from the plenary that were interesting...

ArcGIS Server
More mentions of supporting ArcSDE on PostGIS. Mild applause from the crowd, but it seems like this is still on track to ship at 9.3
Support for ArcSDE on Oracle Express
Image Server will be integrated into Server. Not clear if it will be an extension or part of the core.
On the Mobile ADF side of things, there will be an out of the box Mobile app that ships with 9.3 - so it's no longer just a developer toolkit - I think it's now called ArcGIS Mobile...
Javascript API and REST services for the mashup crowd.

ArcGIS Explorer
Really good demo of ArcGIS Explorer and ArcWeb Services. The data was really really fast - not clear if this was running "live" or from local servers, but it was screaming fast (maybe it was all pre-cached?)
Also notable was the ESRI Content Sharing service - apparently they will host your data for you if you agree to share it. And I may have the name wrong, but I think it's called ArcDataServices which gives you the ability to request (buy? was not clear) data, which is re-projected/extracted etc for you. What was cool is that it's powered by i-cubed's DataDoors on the back end. Kudos to the whole team at i-cubed!

Support Issues
The spin on the current huge backlog of support issues was that it's due to the massive and rapid adoption of 9.2, which has overwhelmed support. That may be, but it's also pretty buggy. Anyhow - the plan to address these issues was laid out. Over the last year they have been re-structuring their whole lifecycle to include "support" as an active element. Support personnel have been physically co-located with the developers. Additionally they said they will open up the entire issue database. Total transparency. What they see, we will see. This got a lot of applause, but we will see...

There were also some cool demos, and the requisite Jack Dangermond "grand vision" segments, but overall a very good opening session. I'm actually excited to talk to the ESRI people about some of the new stuff and some of the 9.2 stuff that I had not known about.

James has posted a "quick hits" rundown of ESRI's white paper on ArcGIS Server licensing, and upgrades for existing users. Give his post and the document a read if you've been trying to sort this stuff out.

I'm not going to say that there was a cause-effect relationship between my previous post on this topic, and the ensuing comment thread (96 comments as of today) over at James Fee's blog, but at least these questions have been clarified for the entire user community.

Ron Bruder & Derek Swingley pointed out that I had misunderstood the Single vs Multiple database thing, and the relationship to SDE vs DBO owned schema.

Since I missed this, and I've been using SDE since before they attached "Arc" to the front of it, I'm guessing some other people may have missed this as well (or like me, skipped reading the 9.2 documentation!).

My Logic (Wrong!)
I has assumed that the "single" and "multiple" were referring to the SDE system table repository.  Thus,  the "Single" database model  was  the traditional model where the system table repository was in one database - "SDE".  Additionally, I had assumed that "multiple" referred to  storing the table repository in multiple databased.  This is completely backwards.


How it REALLY works

Ron posted a link to a Forum posting by Derek, in which Derek posted a link to the explanation of this on the ArcSDE  on-line docs.  Since pictures are worth a 1000 words, I've linked the diagrams from that site here  (disclosure: these are ESRI diagrams, hosted @ EDN.)

Ran into a problem loading data from a Personal Geodatabase, and thought I'd share the solution.

Scenario:
Data was coming from ArcSDE on Oracle, exported to a PGDB, and then I'm importing it into ArcSDE on SQL. We had to go this route because Xml Workspace export threw errors.

Error Message:
The error effectively says that the field length exceeds the maximum for a datatype.


After some digging around, I figured out the problem:

1. The string field in a layer in ArcSDE on Oracle has a length of 2000
2. During the export to Personal Geodatabase, this field becomes a "memo" because a string field in access has a max length of 254.
3. When re-importing into ArcSDE, Catalog interprets the length of the field as 2147483647, which is too big, and the import fails.

The question is - how can I get this data back into ArcSDE?

Solution:

1. Export the schema for the layer as Xml
2. Edit the Xml and change the field length back to 2000
3. Create the featureclass from the Xml
4. Use the Object Loader to transfer features from the layer in the PGDB into ArcSDE.

I needed to investigate the various setup options for using ArcSDE 9.2 with SQL Server 2005. These are condensed notes that I made while checking things out and setting up a few instances.

SDE Setup Options

There are really three sets of questions which you need to think about when setting up ArcSDE 9.2:

• where will the SDE schema tables reside?
• how will the users connect?
• how will users be authenticated?

SDE Schema Location

For the last few releases (possibly since 9.0?), you could setup SDE in two ways - with the SDE schema, or "stand-alone" databases. At 9.2, this is up front and a choice during the post-install, and they are formally named  "dbo-schema" or "sde-schema".  This is really referring to the location of the sde_* and gdb_* tables which hold the "sde metadata" (i.e. domains, metadata, relationship definitions etc).

DBO Schema

In this option, all the SDE repository information is stored in the target database. You need to run the post-install for each spatial database you want to create. If you use "dbo-schema" with SDE services, you will need a separate service for each database - which is somewhat more complex to setup, but should be workable once you get layerfiles/templates setup. I see this setup as a better option if you are using direct connect. In that scenario, there are no extra services to setup, and since everything required for your spatial database in one place, you only have to backup\restore that one database - which really helps if you store a lot of static data in ArcSDE (i.e. rasters)

SDE Schema

This is the traditional model where the SDE schema information is in a separate SDE database. The only downside to this model is managing a mix of static and dynamic databases. In order to keep all your backups in synch, you need to back up the SDE database, as well as all of the databases containing spatial data. That said, this is certainly workable, as this is how most users have things setup.

Connection Type

As with 9.1, there are two connection types: SDE Service or Direct Connection. With the SDE service connection, the client connects to a running process on the ArcSDE server (giomgr.exe), which communicates with the database. Using Direct Connect, there is no running "SDE" process on the database server, and the client handles the details of interacting with the tables. Although direct connect was slower in previous releases, ESRI has done a lot of work to reduce the network "chatter", so the performance is close to being on-par with the SDE Service.

The really big upside of direct connect is the off-loading of the "SDE" work to the client. On a fully loaded SDE Server, approximately 1/2 of the load is the ArcSDE process (according to the ESRI System Architecture document & class). By moving this load to the client, the database server can handle almost twice the number of connections. When I heard this, my initial thought was - "that just makes the client slower". While this is true to some extent, as a percentage of the entire transaction, the SDE communcations is just a tiny percent as comparte to the rest of the client (ArcMap for example) Another way to put it is that the load gets big on the SDE box because it's the focal point for a lot of little processes from all the clients. Re-distributing that load out to the clients has little noticable impact, and your database scales way better. Which is good because DBMS licensing is not cheap.

It's worth noting that while Direct Connect has many upsides,  it does add complexity to upgrades. The version of the client dlls (installed with Desktop/IMS/Server etc) must exactly match ArcSDE. Thus, to do any upgrade you must upgrade all the software at once. As I understand it (and I'm still waiting for confirmation of this) this includes service packs. Conversely, if you use an ArcSDE service, this acts as a version proxy between the client and the underlying database, and thus you have more flexibility with mixing software versions when deploying an updated. Just something to keep in mind when designing your system.

Database Authentication vs Windows Authentication

Regardless of how you have setup the schema or the connection, you can now use either Windows or Database authentication. Windows Authentication is convenient in that you do not need to create additional logins in the database, and you can assign privileges to groups of Windows Logins from within ArcCatalog - thus simplifying the database managment.

Quick note on Performance...

In my experience, direct connect seemed slower during the inital connecting to the spatial database. Once connected, there was no noticable difference. Not sure if this is a function of my setup (I set up ArcSDE to use services, then I stopped the service and specified direct connect in ArcCatalog), but connections via ArcSDE were almost instant. Both were using Windows Authentication. My suggestion is to try it out and see what works best for you.

This week I'm attending the ESRI's System Architecture Design Strategies class, taught by Dave Peters. Last summer I posted about working with Dave at one of our client sites, and the class is a real transfer of his knowledge and experience in a very accessible form. If you are in charge of planning your GIS system design, be sure to get to this class.

Some interesting items from todays class:

I had never found the System Integration group's section of the ESRI web site, and they have a whole mess of performance test reports there - all kinds of intersting stuff like ArcGIS Server/ArcIMS performance on 64bit Windows Server, ArcIMS perfomance on Dual Core CPUs and a bunch of papers on high-availability solutions. Very cool if you are thinking about your technology platform.

We also discussed the 9.2 Server licensing situation.

First ArcSDE...
At 9.2 they have put a lot of work into addressing the "chatty" nature of the direct connect, so it's now supposed to be pretty close in performance to running ArcSDE on the server (giomgr.exe processes). So, that's nice. The upside of using direct connect is that you don't have to pay for an ArcSDE license for each socket on your DBMS box. So, this should be a cost savings since there is less load on your DBMS box (no giomgr.exe processes) - which means the same hardware can serve more users. And when you do scale up, there is no licensing hit to ESRI.

The downside is that for direct connect, everything must match in terms of version - so, your 9.2 clients must attach to a 9.2 database. Not bad, but service packs may also be linked - so if you patch the server, you'll need to patch all clients before they can connect again. So - while cheaper out of the gate, the admin complexity may make you think twice.

Which leads to a third option - running the ArcSDE service & giomgr.exe on a totally separate machine. [UPDATE 11/29: Apparently this has been an option for quite some time] Apparently this is a new option with 9.2, and although you have to pay for the sockets, it may be a better mix between SDE on the DBMS, or direct connect.

The ADF...
We also talked about the ADF, and as I noted, the current stance on the ADF is that the server it is deployed on must be licensed for all sockets.

I can't quote anyone on this, but apparently that there is quite a bit of room to negotiate with ESRI in regard to using the ADF to upgrade existing (particularly ArcIMS) applications.

That's it for today - tomorrow we're going to be talking about security & firewalls. This should be very interesting, as it will hilight some of the issues I see with having to have the web controls on the SOM/SOC box (or pay double)

For the last few weeks I've been working on three elements of an enterprise system - high-level functional requirements, the system architecture and the geodatabase design. I'm working from a mass of information collected from weeks of on-site meetings and a mountain of documentation supplied by the client. And, for the most part, things are going pretty well. Except for the actual geodatabase design experience.

The Present
The standard practice for creating a geodatabase model is to use Visio or Rational Rose to cookup the model in UML. Ok, I can see where this started - UML is handy for designing class model diagrams, and the various elements in the geodatabase are classes. But there is a lot more in the geodatabase than can not easily be managed in UML - projections, topologies, rasters. And the whole use of tagged values is truly painful. Overall, the entire process is horribly inefficient.

One issue is that there are several hundred possible "types" for a field...

While this makes sense for a Uml model, it's a pain for geodatabase design, as there are only 12 valid attribute types (plus the Domains in the model). Yet I still have to jump through this list for every single attribute on every single class.

Once you've finally created your model, and gotten it to validate, you then need to load it into a geodatabase. This is pretty smooth, but you need to manually define the projection for every featureclass (or dataset) in the model. And then you need to setup the topologies. Manually. If you need to do this iteratively - like most design processes - then you need to repeat this process several dozen times.

Don't get me wrong here - this is a million times better than trying to manually bake a geodatabase via the ArcCatalog interface, so thanks to ESRI for giving us this option. But, if we're wishing for 9.3 goodies, this would be a nice thing to fix.

The Future
What I'd like to see (either from ESRI, or anyone else who's got some time, and wants to make some money) is a tool which can handle all aspects of the geodatabase. I want to define my topologies and projections in the design, not later. If I've got rasters, let me have them in the model. Networks? Yep. The question is: How?

Lets start at the end - How can we get a geodatabase design into a geodatabase? The current method is to use the CASE Tools in ArcCatalog to load the Visio or Rational produced Xmi file. The other option is to use an Xml Workspace document. They store all aspects of the geodatabase, so it's an idea option. Typically these are used to transfer schemas (or data) between geodatabase instances - usually ArcSDE instances, but there is no reason that they could not be co-opted as a model storage format. Ok, that part is solved.

What we really need is a nice front end. The key here is to keep the interface simple. Designing a geodatabase is not rocket science, and should be really easy. Have some simple design surface, but I don't see a big need to show all the fields. Just the class name, it's type and the relationships it particpates in.

Have another design surface to show the topologies. The editor UI for the classes should be very easy to use - instead of crytpic tagged values for the geometry type - how about a pull-down? For relationships, just connect two classes with a line, and present a simple dialog for picking PK/FK, notification. Simple, focused, and all about the geodatabase. And if you offer it for $300, I think you'll sell a ton of them. Heck - maybe ESRI buys it from you and ships it with ArcCatalog, and all our lives are just a little bit better.

For now though, it's back to Visio for me. joy.

I'm doing some testing with ArcSDE direct connections and Windows Authentication, and needed a quick way to check how the settings were working for different users. Of course I could go to another PC, and login as one of the test users, or setup a bunch of virtual machines, log into each of them as different users, and test the connections that way, but it seemed like a lot of work when there is the "run as" command in Windows XP.

Basically "Run As" allows you to start up an application or process as a different Windows login. Here's the syntax (showing how to start ArcMap)

runas /user:your-domain\testuser /profile /savecred "C:\Program Files\ArcGIS\Bin\ArcMap.exe"

So, I happliy created some test users on our domain, and whipped up a quick batch file, and ran it, expecting ArcMap to fire up as the specified user. Not quite. I got an error when ArcMap was spinning up - a totally generic "ArcMap has encountered a Problem and needs to close" error. Usually this is a really bad type of error, but before I got too wound up, I thought about things a little, and when ArcMap starts up for the first time, it writes a bunch of stuff into the users profile. But this user does not yet have a profile on my machine since they have never logged in!

So I logged myself out, and logged in as the test user - which created the profile. I then promptly logged out and back in as myself again. Now when I run the batch file - ArcMap happily starts up as the specified user. Nice.

If you are using Windows Authentication with ArcSDE, this can be a useful tool - it can allow you to run in a more restricted mode most of the time, but when you need to you can switch over to a login which has rights to make schema modifications - without having to log out of your windows session.

If you have installed SQL Server 2005, and run into a COM error when opening Managment Studio that looks like this:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

the fix is a registry hack: (provided by Nicolas Buse on the MSDN forums)

I've pasted the registry section below as much for my reference, as to have another place on the net to locate this little gem. Hope this helps!

[Copy from HERE]
Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}]
@="IServiceProvider"

[HKEY_CLASSES_ROOT\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\NumMethods]
@="4"

[HKEY_CLASSES_ROOT\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\ProxyStubClsid32]
@="{B8DA6310-E19B-11D0-933C-00A0C90DCAA9}"

[HKEY_CURRENT_USER\Software\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}]
@="IServiceProvider"

[HKEY_CURRENT_USER\Software\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\NumMethods]
@="4"

[HKEY_CURRENT_USER\Software\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\ProxyStubClsid32]
@="{B8DA6310-E19B-11D0-933C-00A0C90DCAA9}"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}]
@="IServiceProvider"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\NumMethods]
@="4"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\ProxyStubClsid32]
@="{B8DA6310-E19B-11D0-933C-00A0C90DCAA9}"
[ ... TO HERE]

Simply copy the above text, and put it into a text file with a .reg extension. Then double click the file and click yes on the resulting dialog box.

This is a run down of things to be aware of if you are trying to edit attribute data stored in ArcSDE via Multi-Version Views.

I'm writing this up because the ESRI documentation on using multi-version views is rather lean, particularly with respect to managing ObjectID's and dealing with versions after you've made your updates.

Editing Data Via Multi-Version Views
Multi-version views can be used to edit attribute data stored in ArcSDE via SQL statements, but some very specific conditions must exist for this to occur. The most important condition being that when doing inserts (creating new records, which will lack a spatial representation), the multi-version view can not be referencing a version currently in use by ArcMap. This is explicitly stated in the Multi-Version View documentation.

While no explanation is given, my investigation leads me to believe that this is related to the fact that ArcSDE controls the value of the ObjectID column, but that inserts through the multi-version view's do not automatically utilize this functionality. Rather, the developer must manage the ObjectIDs. The easiest way to deal with this is to use SQL and get MAX(ObjectID) from the multi-version view , and add 1. However, if there is an ArcMap session that is also editing this same layer in the same version, this scheme does not work. This is because ArcMap "checks out" a block of ObjectIDs as soon as an edit occurs on a feature in a particular feature class. (This is what those i[reg#]_get_ids stored procedure is for).

Thus, if edits are occurring in ArcMap, and an multi-version view is used to insert a record, assigning ObjectID to MAX(ObjectID) +1, this feature will be in conflict with any newly added features that are created in the ArcMap editing session.

Overcoming ObjectID Issues
After some digging around, I believe that you can use the i[reg#]_get_ids and i[reg#]_return_ids stored procedures to "check out" a block of ObjectID's just like ArcSDE does. I say "believe" because the application I'm building does not require this because of it's business process (inserts go into another set of tables, which are never loaded into ArcMap or anyother ArcSDE client - thus Max(ObjectID) + 1 works just fine. Anyhow, as long as you "get" a block of ID's (I'd suggest 1 at a time so that you avoid having to "return" them) you should be able to insert records.

Named Versions
Another wrinkle, which is noted in the documentation, is that you can not make edits into a version currently in use by ArcGIS. If you try to run an UPDATE or INSERT query against the MVV while the version is open in ArcMap, an error is returned. If you run the query in SQL Analyzer, you'll get something like this:

Server: Msg 2627, Level 14, State 2, Procedure SDE_state_def_insert, Line 21 Violation of UNIQUE KEY constraint 'states_uk'. Cannot insert duplicate key in object 'SDE_states'. The statement has been terminated.

Thus, if there is any chance that there may be an edit session underway, you will need to create a named version and run the queries against that version. This works as shown in the documentation - just make a call to the sde.CREATE_VERSION stored procedure. What the documentation leaves out is how to then post & reconcile this named version back into the version that you actually wanted to work with!

While there are not stored procedures that can deal with this, the developer kit has is a sample which can help out with this part - check out the "Versioning Service" sample.
Essentially, you need to use some ArcObjects code to actually post and reconcile the newly created version. Again, this can be pretty simple, but if there are edits occuring in ArcMap, then there is a chance you will have conflicts, which leads to the question of how to resolve them. For my application, this is not a real problem the data being added via the multi-version view's is field data, and thus more correct than anything that would result from ArcMap edits. Additionally, the user who is adding this data into the system is the only user who actually has edit permission to the features that would be effected. (maybe I'll cook up a post on feature level security...) Anyhow, if a conflict occurs, the data from the multi-version view version always overwrites the other data.. However, this is just another fluke of our client's business process.

Summary
While Multi-Version views support editing, the limitations are onerous, and I suspect by design (I suspect someone wants you to use a pricy ArcSomething license to edit your ArcSDE data). However, for read-only usage, they are a really powerful way to create reports against your enterprise geodatabase.

[UPDATE]
In a comment on the original post when this was on ArcDeveloper.net, Neil noted the following:

Here's some additional stuff on the objectids issues you mentioned, at least for the version of sde I'm working with - 9.1 SP1 with MSSQL 2005.

It looks like you can just leave the OBJECTID column out of the insert statement.

There is an insert trigger on the multi-version view that calls the _get_ids & _return_ids stored procedures that you mentioned to generate the objectid.

It seems that some people have already located it, but I'll make this the "formal" announcement of the ArcDeveloper blog. This will be a group blog (more authors = more posts!), dedicated to Arc Developement topics. I think it's worth noting that although we all work together, this blog is not affiliated with our employer in any way - so while we have "added vitamin C", it will be "marketing free".

We'll be posting introductions, and some "real" content shortly. As for this blog, I'll keep posting on general .NET, GIS solution architecture, and other less developer centric topics.

See you over on ArcDeveloper!

The next step in the ongoing upgrade saga was to load an existing feature class into a subtype of one of the new featureclasses. Of course, the new schema has fields which are not in the original featureclass, or which are now a different type.

The approach I took was to add some additional fields to the old featureclass, then create a multi-version view for that feature class, and use SQL to populate the fields in question, and then load the features into the new class using the Object Loader.

Adding the Fields
To get this done quickly, I simply added the new fields via ArcCatalog. Only hitch here is un-versioning the data. Luckily for us, this application applies all edits to the Default version, so there was no issue dropping the existing versions.

Creating the View
Once the fields were created, I fired up our ViewMaker tool. This tool simplifies the creation of multi-version views by generating the sdetable commands for you. This is very handy when you need to create views for an entire geodatabase. It also has a companion tool which will generate lookup tables from all the domains in a geodatabase - very handy when you want to generate reports using SQL.



Updating the fields
Now, why do this when I could simply "calc" the fields in ArcMap? Well, multi-version views allow you to update attribute data via SQL. Thus, I could write one SQL statement, and run it in 3 seconds rather than fiddling with the field calculator. And I like multi-version views.

[SQL in QueryAnalyzer]

Loading the Data
I now had the data in a format that I could load using the Object Loader. For those not familliar with this tool, it's in both ArcMap and ArcCatalog, and it allows you to load data from one feature class into another, while defining field mappings.



This worked really nicely, the only beef I have is that the tool does not have any messaging upon completion. I then refreshed the view of the layer, and saw no records, and assumed something went wrong. So I ran it again. Again, there was no "X Records loaded" or "Processing Complete" or "Done!" message. This time, I disconnected from SDE and re-connected, where I saw that I had two copies of each record loaded. After dropping all the records (via edit session in ArcMap), I re-loaded the data, and all was well. Now I just need to get the data for the other 2 sub-types and load it

From here, the main tasks are systemic in nature. The schema changes that we applied concern a critical component of the data hiearchy - actual land ownership boundaries. It is at this level that we apply our spatial permissioning model - thus changes to this layer will have major ripple effects across the application. At this point, I'm mostly turning project over to other staff members, but I'll be posting on any hilights or problems we run across. Maybe I'll be able to convince them to start blogging too!

Problem:
We have a geodatabase which was originally modeled in UML, but the UML is out of synch with the geodatabase, and we don't have the time to update the model to match the geodatabase. But, we need to add some new featureclasses to the geodatabase, and we want to design and document these using UML. How do we get the new items into the old geodatabase?

Option 1: Import XMI into the geodatabase
I'd hoped that this would work, but ArcCatalog simply crashed. After some head scratching (mostly because my other options seemed overly difficult), I seemed to have a vague recollection that you need to import models through a connection that attaches to the database as the SDE user. It seems that you can import a model as any user who has correct permissions, BUT, if you import a model which references a domain which already exists, then you need to be connected as SDE. If not, you'll get another informative error:

Unable to aquire schema lock for {domain name}

I really wish ESRI put some better messaging into the CASE Tools - how hard would it be to pop a message reminding you to connect as SDE when starting the model import tool? And while I'm in this topic, the first time I tried to import my XMI file, I got this error:

Unable to load model from XML file {path}
An error occured while parsing the XML document.
Error number: 516

How useless is this? You can google this error, and get to this page at ESRI that tells you the problem - you need the uml.dtd file in the same folder as your xmi file. Now, since the code clearly knows what's wrong (error 516) why not just tell me!

Anyhow, turns out that you can apply a partial model into an existing geodatabase, if you connect as SDE. Of course this causes the newly created featureclass to be owned by SDE, and not the user you want to own it. So - back to the drawing board and...

Option 2: Import XMI into a new geodatabase, Export the featureclasses to XML schema, and import that into target geodatabase

This time, I created a new database in my SQL instance, set it up for SDE, and imported the model into it as SDE. Then exported an XML workspace document. And then attached to my target geodatabase as the 'owner', and imported the XML Workspace document. And it worked. In order to make sure I did not mess anything up, I had been working on a copy of the real geodatabase, so the real test was to try the same thing on the real one. It worked. Really. So, it appears that the XML schema stuff is the way to go. Hopefully ESRI will create a geodatabase designer which spits out this XML because it would be a whole lot better than messing with XMI/Visio/UML kludge.
Now this this is sorted out, I now have to figure out how to massage the existing data which is going to populate one of the 3 subytpes in one of the new feature classes. But that can wait for a few days.

Next item in the upgrade is addressing some schema changes.

When we started development, we designed a large geodatabase using Visio & UML. Nice. Over the following years, we wrote the ArcMap tools and other applications which use this geodatabase. Still nice. As is the case with most development, some changes must be made to the database model. In most cases, we were good, and updated the Visio UML model, but it's been a few years, and we're pretty sure they are now out of sync.

The question is: How can we find these changes so we can easily update the model so it's back in sync with the geodatabase?

Option 1: Manually check the UML against the contents of the geodatabase
Ok, this is not an option because I need to do this in hours, not weeks. And I'm pretty sure nobody on staff would actually do this!

Option 2: Reverse engineer the geodatabase back into UML.
Wishful thinking - this is still not possible.

Option 3: Compare XML Schemas
This is what I went with, and it should be straight-forward right? Just dump the workspace schema from the existing geodatabase, and from a geodatabase generated from the current UML model, and run them through a diff program, and see what's different? Oh were it so simple.

The Coded Value Domain Problem:
As part of the workspace schema export, coded value domains are written out, and this caused me some problems. Things may have been easier if we followed ALL geodatabase UML design guidelines. And we do for most things - just not Coded Value Domains. It's not that we don't create the domains in the model, we do. We just don't populate all of them. In our experience we've found that the domain values you get from a client at the beginning of a project are different from those needed at the end. Instead of editing & re-applying the UML model a zillion times (which is a pain, and requires that everything be un-versioned), we simply load the domains out of SQL tables that hold the values.
Anyhow - the problem is that the "real" geodatabase has all the domain values which have been loaded from the tables, and in the "new" geodatabase, the domains are empty, which means I can't compare the XML files directly.

Transforming the XML Export:
Since the XML schema has more information than I wanted, I figured I should be able to just extract out the information I was interested in. ( I should say here that I'm not an XML guru by any means).
After a little quality time with XMLSpy, I cooked up an xml transform (XSLT) that parses out the information I want - Featureclass name, the fields, their name, type, length, and domain if applicable.

After this, I thought I was good to go. Nope. Apparently the ESRI XML schema exporter writes out the xml in some order other than alphebetical by featureclass name. Thus, the order of the feature classes in the output files are not the same, so a simple diff will not work. XML.com had a simple example of sorting in xsl, and after sorting the featureclasses by name, and the fields by name I had something to work with.

So, here's what it outputs:


At this point I started looking for a simple diff tool so I could locate the differences between the files. Before I got too far on that, I noticed that the XML from the "live" geodatabase was 89k, and the XML from the geodatabase generated from the model was 59k. Not good.

It turns out that we had a whole bunch of new tables registered with the geodatabase. Part of the system involves the collection of very detailed forest information - measurements of indivudal trees. Once this data is loaded into the system, a bunch of processing is applied to the raw tree values to calculate timber volumes. All the tables used to hold the outputs from these processes were not modeled in the UML. Uggh. :-|

Since we don't have time to spend days entering all these tables into the model, and updating the model is not a priority for our client, it was time to figure out how to "nicely" merge two UML models into one operational Geodatabase...

So I upgraded our internal development ArcSDE serverfrom 8.3 to 9.0 (so it would be running on my EDN licenses and we could drop the "commercial" license). Anyhow, all went well, and I moved on to other issues (ArcGIS Server development, Ajax-ifying our GeoPortal etc). However, yesterday I noticed that one of our demo sites (http://dev.sanborn.com/kls) decided it was not going to make maps anymore.

Thus I followed the usual ArcIMS problem solving technique:
Step 1: Restart ArcIMS
Step 2: Re-boot the server.
Step 3: swear and threaten to kick the box

Actually step 3 was look at the logs, which non-specifically (I was only logging errors) indicated the issue was with a map service using ArcSDE. Thus I tried to connect to the instance from ArcCatalog, when low and behold - it popped up with a "No Connections Availible" message.
Luckily, I'd been roaming the GIS blogosphere, and recalled a post over at the ROKTechnologies developers blog about this very thing.

Apparently when you upgrade to 9.0, it handily sets the maximum number of connections to 48. I'm sure that this is noted somewhere in the release notes or elsewhere that any experienced ArcSDE person would completely ignore. Since this is a pretty critical item (i.e. your system will not work as it used to) I'd suggest that ESRI puts this on it's own dialog at the end of the install in bright red - i.e.

NOTICE: WE HAVE SET THE MAX CONNECTIONS TO 48. IF YOU CURRENTLY
USE MORE THAN 48 CONNECTIONS TO ARCSDE, YOU MUST CHANGE THIS SETTING

Anyhow, here's the quick solution:
Step 1: Open the SDE_SERVER_CONFIG table,
Step 2: set the CONNECTIONS value to whatever you want.
Step 3: Restart ArcSDE
This is easier than foolin with loading a defs file etc.

For some time we've been working with SDE's multi-version views in order to allow direct SQL access to versioned data stored in ArcSDE. We've used this exclusively for reporting, and it has worked really well. We've got a new project on the go where in we need to facilitate off-line (disconnected) editing of tabular data in a Geodatabase. I'm just in the R & D phase right now, but it looks like we'll be able to use these same views to do data editing. Granted, we can not facilitate editing of the shapes this way, but this application does not require that. The nice thing is that the data will still reside in a geodatabase, and thus will be accessible to all the tools etc built into ArcMap/Catalog.

The basic architecture follows a simple ADO.NET offline client application. The client app contacts a web-service and requests a set of data (as a DataSet), and then switches to off-line mode. The user makes edits as needed, and then re-connects, and the application sends the DataSet diffgram to the web service, which re-sync's the data. I'll post some code and links as I get further into this.