ArcSDE Tip: Finding an ArcSDE instance’s port

One of my main tasks right now is to document many of the details of maintaining ArcSDE geodatabases so I anticipate having several blog posts on this topic that are re-writes of documents I am working on. I am presuming that the person will have no ArcSDE experience so I am documenting very detailed information.

Almost all of the ArcSDE commands require that you specify which instance (service/port) the command applies to by using the “-i” parameter.

ArcSDE Instance Parameter
ArcSDE Instance Parameter

Since we have multiple ArcSDE geodatabases, I like to have a handy-dandy sticky note with all the geodatabases and their respective ports on the side of my monitor.

But when that is not handy and I can never remember the ArcSDE command line syntax to get a list of instances and their ports–I mean remembering “sdeservice -o list” is difficult at my age.

sdeservicelist

The quickest and most reliable way I’ve found to get the instance number is just to check the properties of your SDE connection file in ArcCatalog, right-click on it and select “Connection Properties”.

connectionproperties

And the port is right there in the service entry (5164).

connectionproperties2

SDEINTERCEPT & SDEINTERCEPTLOC

Awhile ago, I had a ArcSDE problem that required ESRI technical support to help trouble-shoot. The problem was odd but was resolved by rebooting the server.
During the process, though, the support person had me set a couple of environment variables for logging SDE activity on the client machine.

The settings were SDEINTERCEPT and SDEINTERCEPTLOC.

From ESRI’s Help, SDEINTERCEPT specifies what activity to log and SDEINTERCEPTLOC specifies where to save the log files.

I recently deleted the directory I made for the log files but did not remove the variables and I noticed that one of my python scripts reported a weird error (but continued to run, I think). I tracked it back to these variables and realized what I had done.

Googling SDEINTERCEPTLOC did lead me to some helpful information like:

The SDEINTERCEPT blog where Ken posts ArcSDE help.

This ESRI post about troubleshotting geoprocessing problems.

And this ESRI technical article about diagnosing ArcSDE Connections.

Feature classes and Tables with names starting with “nd_”.

Random luck me to discovering a bug related to feature classes whose names start with “nd_”.  It appears that you are allowed to create feature classes starting with “nd_” but ArcCatalog will not display them.  Further research shows this behavior also occurs for table and for ArcSDE (PostGres) geodatabases,  personal geodatabase, and file geodatabases–I am using ArcCatalog 10.0.

I first noticed something odd was occurring while importing a series of shapefiles into a geodatabases.  After importing 15 shapefiles, I only had thirteen feature classes despite receiving no errors during the process.  The two shapefiles that failed to import were named ND_oil_and_gas.shp and ND_Bendix_Study.shp.  Subsequent attempts to import them individually returned an error “Invalid Target Name”.

I discovered in pgAdmin III (Postgres SDE Geodatabase) that the table existed and there was an entry in sde.sde_layers for the feature class but ArcCatalog refused to show it.

I used some un-supported methods to try to resolve the problem and despite some sweating, I failed to find a way to get ArcCatalog to display these feature classes.  I did, however, at least found a way to delete them–arcpy can detect that the feature classes exists so it is able to delete them.

At least by deleting them, I can prevent leaving “invisible” feature classes from hanging out in my geodatabase.

I suspect the problems stems from how ESRI has implemented the Network dataset table-naming structure –dirty areas are stored in tables named nd_<itemid>_dirtyareas  and nd_<itemid>_dirtyobjects.  Possibly the developer  working on the ArcCatalog GUI ended up suppressing showing feature classes and tables whose names start with “nd_”.

And, just for posterity’s sake, here is a python code snippet listing the feature classes in a workspace:

import arcpy

arcpy.env.workspace = “c:/temp/_nd/F.gdb”

print arcpy.env.workspace
for fc in arcpy.ListFeatureClasses():
print fc

print “Done!”

Renaming Raster Dataset and arcpy.Exists()

Discovered something today. I was working on an arcpy script that copies a raster dataset from a file geodatabase into a Postgres SDE geodatabase and then does some boring routine tasks–building stats, creating a mosaic dataset, adding the raster to the mosaic dataset and making a couple referenced mosaic datasets.

It sometimes has trouble with the initial step of uploading the raster because of the sheer size of if (1m elevation raster for counties) and it failed today on one. It failed today so I used the ArcCatalog GUI to copy the raster and renamed it.

I then proceeded to run launch my script. Before each step, I use arcpy.Exists() extensively to check to see if various items exist before I attempt to create them. It was continuously reporting that my raster set did not exist even though I could see it in ArcCatalog.

Finally, I realized that I needed to close ArcCatalog before arcpy recognized the fact I had renamed something. To note, I was running arcpy from a separate PythonWin window, not from the ArcCatalog session I had renamed the raster dataset with.

Once I closed ArcCatalog, arcpy recognized the renaming and life was good.

I’m also suspicious now about a problem I often have running statistics on my rasters.  The ArcTool reports no errors when I create them but for some reason the raster does not show that it has statistics afterwards.  I normally have multiple ArcApplication sessions open and now suspect that perhaps this problem is due to sessions not letting go of the connection.  Stay tuned for further developments on this.

Sorting a Coded-Value Domain Add-In (ArcGIS 10)

I am working on an data-entry application to edit feature classes that contain several coded-value-domains. The problem with some of the domains, however, is that some entries have been added after the initial creation.  So the first 25 entries are in alphabetical order and there are some stragglers at the end that are in the order they were appended.

This can be confusing for users–they go to select “Milli Vanilli” and look between “Madonna” and “Motley Crue” but can not find their favorite band there–they have to go to the end of the list to find their selection.

In the past, I have gone through the tedious process of exporting the domain to a table, sorting the table, removing the domain from the necessary field(s), deleting the domain, re-importing the table back in a new domain and finally re-applying the domain to the necessary field(s). Let’s just say I didn’t do this until someone asked a few times and I didn’t have anything more exciting–like a root canal–I could busy myself with.

But this new application contains more domains than any of other datasets so it was time to find a better solution. ESRI does have a Domain Sort Developer Sample.  It, however, did not play nice with ArcGIS 10.

So I went ahead and update it from VB 6 to VB.Net/ArcObjects 10.  I made an Add-In that can be installed by downloading the .esriaddin file and double-clicking on it.  The source code is also available.

This will add an ArcCatalog Toolbar that can be added by going to Customize-Toolbars-Domain Sorter Toolbar.

This will add a toolbar with one button.  The button enables whenever you select a geodatabase with at least one coded-value domain.

This brings up a Windows form that lets you sort any domain by either the code or description, ascending or descending.  Once you hit “OK” it re-sorts your domain.

The only problem I have had is that only the owner of a domain is allowed to edit it on an SDE geodatabase.

But other than that, the button allows you to easily keep your domains sorted.

http://edndoc.esri.com/arcobjects/9.2/CPP_VB6_VBA_VCPP_Doc/COM_Samples_Docs/Geodatabase/Schema_Creation_and_Management/Sort_a_domain/e826c5a8-9740-4f0b-86b6-d3b834735574.htm

Revoking ‘Public’ Permissions in PostgreSQL ArcSDE

While banging my head on how to grant access to a referenced mosaic dataset , I did something out of frustration that I normally would not do–I granted ‘public’ access to some data.

Then, after figuring out the problem, I went to revoke public access using ArcCatalog and received this error message:

Error 999999: Error executing function.

The Object being referenced does not exist [ERROR:  role “public” does not exist::SQL state: 42704]
Failed to execute (ChangePrivileges).

I actually expected that would occur–ArcSDE isn’t aware of PostgreSQL’s public account.

The solution was to go into pgAdmin II and revoke the privileges there.

Loading Tiled, Same-Name Data in Batch Mode.

I have been loading existing raster data into a geodatabase to be included in a new Mosaic Dataset–a very cool and useful addition to ArcGIS 10. The most time-consuming part of the process for the human (at least this human) has been getting the names of the rasters right.

Our existing data is organized by tiles with the directory name representing the tile name and then the data within each tile directory having the same name.

For example:
C:\GIS_dataAdamsparcels.shp
C:\GIS_dataBuchetteparcels.shp

This makes batch loading the data less efficient because I end up having to rename the data or else end up with a series of feature classes named parcels, parcels_2, parcels_n.

So I hacked out a quick script that takes an input raster and figures out the final name I want it to have based of the directory name.

First, I used the Copy Raster (In ArcToolbox: Data Management-Raster-Raster Dataset-Copy Raster) and copied on sample to my geodatabase.

Then, I went to the Results Tab (Select Geoprocessing from the Menubar, Geoprocessing-Results) and right-clicked on the Copy Raster result and selected “Copy as Python Snippet”.

I then created a new python script and pasted the one line.

I added some imports, accepted a parameter, some string manipulation, and some result outputs and I had a quick & easy script. In added the script in ArcToolbox and now I can right-click on it and run it in Batch mode. I do a quick search in Windows Explorer to get all the rasters I want to run it on and select & drag them to my ArcToolbox Batch Dialog.

Actual code can be downloaded HERE and you don’t need to worry about WordPress messing up the spacing.

import arcpy
import os, sys

inRaster = sys.argv[1] 
basedir = os.path.basename(os.path.dirname(inRaster)).lower()
outRaster = "Database Connections/mgs_lidar.lidar.sde/mgs_lidar.lidar."+basedir

def printit(inMessage):
    print inMessage
    arcpy.AddMessage(inMessage)
    
if not (arcpy.Exists(outRaster)):
    printit ("Importing: "+basedir)
    arcpy.CopyRaster_management(inRaster,outRaster,"#","#","#","NONE","NONE","#")
else:
    printit ("Skipping: "+basedir+" because it already exists!")

Multiple geodatabases and ArcSDE services from one PostgreSQL server.

To better organize our ArcSDE data, we wanted to create multiple geodatabases and multiple ArcSDE services using one PostgreSQL database cluster (a cluster containing 1 machine at this point).  A side question is why can’t tables and raster be placed in Feature Datasets?  This wouldn’t be an end-all solution for what we want to do but there are some messy consequences of this limitation.

ESRI has instructions on Setting up multiple geodatabases in one PostgreSQL database cluster on Windows which was helpful but we repeatedly got an “The ArcSDE Repository was unsuccessfully completed.  Would you like to view this error?” error during the Repository Setup step, step two of four, of the ArcSDE Post Installation process. The odd thing, however, was that the wise_err.log was blank so I had no clue on why it was failing.  After much Googling and head-banging, I came across a post from Kim Doty on ESRI’s forums reporting the same problem but they were able to create a service by just continuing through the process .  Figuring I had nothing to lose, I thought I might as well try to go through the entire ArcSDE Post Installation process and see what happened.  Although I received blank error messages, I was able to successfully create the second ArcSDE instance.

I will touch on some of the highlights of the process.  In my case, I was creating a database & service with these parameters:

  • Database name: mgsgdb_lidar
  • Service name:  mgs1_sde
  • Tablespace folder: D:mgsdb1dmgsgdb_lidar

Following ESRI’s instructions, I first made custom copies of dbinit.sde, dbtune.sde, and giomgr.defs that contain the service name (mgs1_sde) within the file name.  These copies were named dbinit_mgs1_sde.sde, dbtune_mgs1_sde.sde, and giomgr_mgs1_sde.defs.

When I have trouble with the ArcSDE Post Installation, I like to go through the four steps individually so I select a “Custom” install.

The four steps making up the ArcSDE Post Installation process are shown on the next dialog.

I was able to complete the first step, Define SDE User Environment, without any problems.

Make sure to set your database name, default tablespace, and tablespace folders are distinct from your first installation.

During the second step of the ArcSDE Post Installation process, Repository Setup, make sure to use the custom files you made earlier when you come to the ArcSDE configuratino files dialog.

Later in the Repository Setup, make sure to use the correct database name:

This is the first error message I received in the process (still during Repository Setup):

Clicking “Yes”, however, shows an empty wise_err.log file:

After viewing this, I canceled out of the Repository Setup step.

Taking a look in pgAdmin III, however, I can see that both my database (mgsgdb_lidar) and tablespace (mgsgdb_lidar) were created:

I received the same error message during the third step, Authorize ArcSDE, as in the second step:

But running the fourth step, Create ArcSDE Service, resulted in this sweet message:

After editing my pg_hba.conf and opening the port in my firewall, the service was created, running, and visible.  So far, it seems to be fully functional and without problems.

Using ArcGIS 9.3.1 Desktop to Extract Data From an ArcGIS 10 Server Geodata Service

In building our Enterprise GIS Database, we need to support users with different needs.  Some of our users just need to see the data on a map while others may want to download a copy of the data so they can use it within their own desktop system.

After doing some exploring, one of the options that looks like it will feel the bulk of our internal needs is to create a Map Service/Geodata Service pair–by creating a Map Service, we can make an easy-to-use visual representation of our data.  Combining that with a paired Geodata Service–a Geodata Service with the same name as the Map Service–we can accommodate those users who want to extract the data to their hard drive.

A user who has added paired Map & Geodata Services to an ArcMap dataframe can use the Distributed Geodatabase Toolbar to extract data to a local geodatabase.  The Extract Data button is on the far right of the Distributed Geodatabase Toolbar.

 

Unfortunately, my first attempts at testing this did not work.  It would look like it was processing, I would end up with a .mdb or .gdb file on my hard drive but I could not open it.  I would get a “Failed to connect to database. This release of the GeoDatabase is eitehr invalid or out of date. [Unknown GeoDatabase release.]  The Microsoft Jet database engine cannot find the input table or query ‘GDB_ReleaseInfo’. Make sure it exists and that its name is spelled correctly.” error.

 

When exporting to an .mdb file, I could open it in Access and see the data.

Luckily the error is fairly helpful–I was getting an ArcGIS 10 Geodatabase even though I am running ArcGIS 9.3.1 on my desktop.  I was able to open the exported geodatabases using a copy of ArcGIS 10 on another machine.   The reason for the inconsistency is pretty interesting.  Our office is, and for the foreseeable future, running in a mixed-version environment.  We have a mixture of 9.3.1 and 10 desktops, a 9.3.1 ArcSDE server and a 10 ArcGIS Server.  The Extract Tool is a server-side function apparently that creates a version 10 Geodatabase.

The way to get around this is to extract the data to XML and then import the XML workspace into a geodatabase.  Not sure if that will work for all schemas but it was successful for me.

 

Checking to see if a Field Index Exists Using Python (geoprocessing 9.3).

NOTE:  I have a post here that shows how to check if a field exists using arcpy in ArcGIS 10.0.

In developing a python script to reload a geodatabase, I wanted to create any necessary indexes.

No problem creating the index, for example:

gp.AddIndex_management(tablename, field, IndexName, "NON_UNIQUE", "NON_ASCENDING")

But before creating the index, I wanted to verify that it did not exist.  I tried the ever-popular, exists but could not get it to work–either it does not detect indexes or I just never got the fully-qualified name for the index right (ArcSDE using a postgres datastore).

gp.Exists(mgs_c5ix_fullname)

I finally found this ArcGIS Desktop Help 9.3 – ListIndexes method from ESRI.  Unfortunately, it doesn’t work-it did not like the “while” loop construction.  I’m guessing it worked in 9.2 and despite ESRI’s own warning about differences in 9.2 & 9.3, they did not update the sample code.

A key is to make sure you create a 9.3-version geoprocessing object and the following code can be used.  The caveat that I need to include is that the code only checks one table, if the index is on a different table, it will give you a false-negative.

gp = arcgisscripting.create(9.3)

def indexExists(tablename,indexname):
 if not gp.Exists(tablename):
  return False

 indexList = gp.listindexes(tablename)

 for iIndex in indexList:
  if (iIndex.Name == indexname):
   return True

 return False

To call it, just pass the table and indexname you are looking for.

indexExists(tablename,indexname)