#arcsde

Source: http://desktop.arcgis.com/fr/desktop/latest/manage-data/using-sql-with-gdbs/example-creating-a-spatial-view-in-sql-server-using-sql.htm

La commande sql utilisée est “CREATE VIEW”, l'exemple effectue une jointure attributaire entre des données attributaires et une référentiel spatial avec la clause join / on.

We are finally moving forward, I am in the process of upgrading beyond ArcSDE. I have maintained a love:hate relationship with ArcSDE for the last ten years. Moving past it takes faith. There are a ton of quirks I have grown used to dealing with, and so far I have found 10.3.1 has put them all to rest. I have been stuck at 10.1 sp1 as we experienced a wholesale bloodletting in the IT dept (retirements mostly). We are finally upgrading to Oracle 11g (been waiting on this since mid 2011) and with it a fresh batch of databases. I have convinced the DBA to allow me to turn all 9 into geodatabases. So I am modifying a ton of custom python scripts I wrote to manage data across one db and one gdb in oracle to handle all 9 new gdb’s. I am relieved to walk away from all the db links I have relied on for years. Now I can get access to tables the way I wanted to years ago… without all of the painful contortions.

Step 1 rewrite scripts to mange data on old and new databases simultaneously Step 2 modify all map services (10.1) to point to new gdb’s Step 3 mass deployment of oracle 11g r2 clients 32b & 64b (400 desktops) Step 4 update a pile of layerfiles to point at new gdb’s Step 5 upgrade servers from 10.1 to 10.3.1 Step 6 mass deployment of arcgis upgrade to 10.3.1 (400 desktops) Step 7 change old gdb passwords Step 8 drop all updates from scripts on old data locations Step 9 go on extended vacation

I am confident in my abilities to accomplish everything but the oracle client deployments. I despise using the Oracle gui’s, but I have not tinkered with it enough to install from a command prompt. I wish it was as easy as the arcgis upgrade bat files I wrote. I have a little over a week to get it all done… if the DBA gets all the data migrated. I wish there was a way to connect to oracle using a dll call or something rather than all the client install BS. An ezconnection without a client installed would be super sweet. Having to install both 32 and 64 is a real momma dog. My one fellow developer tells me to only use the 32b, but then my 64b background gp behaves erratically and reminds me why I need both.

I will spend much of the next two weeks staring at text editors clutching a hydroflask full of black coffee… debugging, tweaking, tuning, and cussing, lots of cussing. I pity my kids and wife, maybe the vacation will work out and I can repay them for the grumpy hell they will soon live through. Once it starts there will be no turning back. Time to roll with the punches.

Trust error messages. Error messages arising from some software or human error are extensively well documented and always tell you the underlying problem. Take this message for instance:

"Server object instance creation failed on all SOC machines. The base table definition string is invalid. Bad User."

Obviously, the problem is the existing cache on the server with an identical name. You should manually delete the folder on that machine. All of the errors messages you get will be similarily helpful and to-the-point.

User experience is for end users, the software is supposed to be hard

A lot of tools exist to automate common database operations - many exist as simple command line scripts (often paired with SQL) and provide a good way of managing large datasets and effect complex model changes with proper testing and without human error. A lot of web frameworks now support a migration pattern, where schema changes can be made and reversed in a maintainable, testable, and reliable fashion (see Rails Migrations, Django South, and very soon EFCodeFirst from Microsoft).

But that junk is for scrubs. Far better for you to push some buttons in a wizard than have a clean config file paired with a command one-liner that can be easily integrated into maintenance scripts. Database migration and automation tools are for chumps who don't want the continued experience of pushing "next" to recreate the network. Otherwise what excuse would you have to bring the server down?

It's totally necessary to have some middleware between you and your database

Modern relations database management systems are really unreliable/untested and none of them really support spatial types. Better to abstract away that nonsense with some DCOM goodness.

It's especially useful to have a multiple ways to use the middleware to effect some database change - like deleting a domain - that silently fails if it isn't in the right program or uses/doesn't use a toolbox tool.

Eliminating complexity is for people who don't want job security

Use every possible complex ArcSDE feature, that is what they are there for! If you don't start using, say, a versioned geometric network feature class dataset, some DBA without any GIS training might get it in his/her fool head that they can do what you do. Nothing is more dangerous. ArcSDE helps you avoid this cruel fate by making a simple insert statement into a tremendous pain.

Fear your database tables

If you've just installed ArcSDE, you will notice it has convienently created a number of tables with obscure names. This is just it's gentle way of letting you know that you no longer really own this data or have much control over it. It's important to avoid the inclination to start using that database in the same manner you might other databases - creating convienent views or reoccuring jobs that better insure data accuracy and validity. If you want to do anything like that, go open up ArcMap or Visual Studio with an ArcObjects template.

Jump into the new version immediately