"Seegrid will be due for a migration to confluence on the 1st of August. Any update on or after the 1st of August will NOT be migrated"

ArcSDE for GeoServer application schema



This page was written based on my experience on setting up GeoServer application schema on Eclipse with an Oracle 11g ArcSDE 9.3 back end. The SDE was installed on a Windows Server 2003 virtual machine.

Install ArcSDE

TerryRankine installed ArcSDE for me. I then had to run the post installations.
  • Choose complete installations It will create sde tablespace and user first (if not already there).
    Log in as "sys" (default password is "password").
  • It will then ask to authorize the software. Choose to register by internet, put in your details and the serial number: See me or TerryRankine for the license key.
  • Then it will create and start the service. Put in "sde" as username, and the password.
    Default sde password is "sde". (If you don't know what it is, find it in createsdeoracle.sql).
    If you're locked out, you have to log on to your DBMS and unlock the user.
    If you still don't know the password and need to reset the password:
    1. Go to cmd.
    2. Type in:
      sqlplus "/ as sysdba"
      show user
      ( It will show USER is "SYS")
      passw <username>
      passw system
    3. It will prompt you to enter new password.
    4. Now you can log in to your oracle DBMS as system with your new password.

In "net service name" field, put in the service name, eg. cgisrv6. If you get an error message for wrong password, you need to reset the password (see above).
If you get an error indicating zombie process:
    1. Go to cmd:
    2. List services:
      sdeservice -o list
    3. Delete zombies that aren't running. Specify with -i if there are more than 1, otherwise it will delete all. Eg.
      sdeservice -o delete -i esri_sde -d ORACLE

How to import csv files to oracle (11g):

See sqlloader FAQ
Note, the following is based on manually creating the tables and using sqlloader to load the data. Only at the end, I found out that I could have used http://sourceforge.net/projects/quickload/ to create the tables and load the data automatically from excel files. Although it only takes care of *.xls files, but I'm sure you can convert csv files to *.xls files.

  • Create a table for each csv file. Using the mdb file (if any) or csv file headers as column names. Using oracle UI drives me mad, because every time I pressed back to navigate to previous page, I'd lose everything I've typed, so I used SQLplus from the command line.

    • Go to cmd.
    • Type in sqlplus and log in as sde .
    • Type in:

Create table interpgeol_lut
(CODE varchar2(254), SORT number, UNITNAME varchar2(100), DESCRIPTN varchar2(254), PARENTCODE varchar2(50),
PARENTNAME varchar2(100),ROCKTYPE1 varchar2(100),LITHNAME1 varchar2(100),QUALIFIER1 varchar2(100),
QUALIFIER2 varchar2(100),MIXEDROCK varchar2(100),ROCKTYPE2 varchar2(100),LITHNAME2 varchar2(100),
MIXEDQUAL1 varchar2(100),MIXEDQUAL2 varchar2(100),REGOLITH varchar2(40),SUPERSUITE varchar2(100),
SUITE varchar2(100),SUPERGROUP varchar2(100),GROUP_ varchar2(100),SUBGROUP varchar2(100),
FORMATION varchar2(100),MEMBER varchar2(100),BED varchar2(100),STATE varchar2(100),
CRATON varchar2(40),SUPERTERRANE varchar2(45),TERRANE varchar2(40),DOMAIN varchar2(40),
GREENSTONE_GRANITIC_COMPLEX varchar2(40),SUPERBASIN varchar2(40),BASIN varchar2(40),
SUB_BASIN varchar2(40),OROGEN varchar2(40),PROVINCE varchar2(40),TECT_SUBDI varchar2(40),
OROG_FORE varchar2(40),EVENT_NAME varchar2(254),AGEFROM varchar2(50),AGETO varchar2(50));

When you're finished creating tables, exit sqlplus.

  • Create a ctl file for each csv file, e.g.
    load data
    infile 'c:\data.csv'
    into table geologic_unit
    fields terminated by "," optionally enclosed by '"'
    (objectid, name, description)

  • Ensure each csv file doesn't have the header on the first row. Or you can run the sqlloader command with skip=1 to skip the first row (the header row).

  • Use sqlloader to load the ctl files.
    Go to cmd.
    Change to the directory where the ctl file(s) are.
    Type in the command:

sqlldr username/password@server control=<name of ctl file> skip=<number of rows to skip>

sqlldr sde/sde@arcserver control=loader.ctl skip=1

  • Check if the data is loaded successfully, because sometimes it fails silently. Look in the log files.
  • Register the tables/layers, with row id column specified (otherwise GeoServer will not load the tables) with SDE as the column maintainer.
    If you want to use an existing column, it has to be a NUMBER(38) type. Otherwise, you can let SDE create a default one (objectid).

    • For non-spatial table:

sdetable o register t <table_name> 
[(c <row_id_column> C {SDE | USER| NONE}]
[M <minimum_row_id>] [S <table_description>]
[k <configuration_keyword>] [i <service>]
[s <server_name>] [D <database>] u <database_user>

sdetable -o register -t interpgeol_lut -u sde -p sde -c object_id -C SDE

    • For spatial layer:

sdelayer o register l <table_name,spatial_column_name> 
e <entity_mask>
{[C <row_id_column>[,{SDE | USER}[,<minimum_row_id>]]]}
[R <spatial_reference_id>] [k <configuration_keyword>] [i <service>]
[s <server_name>] [D <database>] u <database_user>

For more help: Registering ARCSDE tables.

  • Use shp2sde to load shape files.
Read: shp2sde.

e.g. to load 100k_interpgeol.shp to table interpgeol_lut, column SHAPE, do the following

shp2sde -o append -l interpgeol_lut,SHAPE
 -f "C:\Documents and Settings\Administrator\Desktop\arcsde data\ARCVIEW\SHAPEFILES\100k_interpgeol.shp"
 -g AUTOMATIC -u sde -p sde

Use create, instead of append, if the table doesn't exist yet.

Configure mapping file for GeoServer.

If you're not familiar with application schema, read the tutorial: app-schema tutorial. Special parameters for arcSDE:

     <value>eg. my.server.somewhere.au</value>
     <value>5151</value> (this is the default port)
     <value>your database name</value>

The last parameter above datastore.allowNonSpatialTables was needed to allow non spatial tables.
For more information: ArcSDE DataStore.

IMPORTANT: the table name in the sourceType tag has to be all capitals, eg. SDE.INTERPGEOL_LUT

Build the arcSDE plugin

Please refer to this page on building GeoTools with arcsde extension: ArcSDE plugin.

Below are my additional notes:

* Download the external jars from http://geoserver.org/display/GEOS/Stable for arcsde plugin.

* Move the sde jar files to WEB-INF/lib.

* Open up the command line, change to WEB-INF/lib directory and install the files:

mvn install:install-file -DgroupId=com.esri -DartifactId=jsde_sdk -Dversion=9.3
-Dfile=<jsde93_sdk.jar> -Dpackaging=jar -DgeneratePom=true

mvn install:install-file -DgroupId=com.esri -DartifactId=jpe_sdk -Dversion=9.3
-Dfile=<jpe93_sdk.jar> -Dpackaging=jar -DgeneratePom=true

* Even though not mentioned on the page, the build failed for me until I also did:

mvn install:install-file -DgroupId=com.esri -DartifactId=jsde_jpe_sdk -Dversion=9.3
-Dfile=<jsde_jpe_sdk.jar> -Dpackaging=jar -DgeneratePom=true

* Then run the command to build Geotools:
mvn -Parcsde -Dsde.version=9.3 clean install eclipse:eclipse

* Run eclipse:eclipse with arcsde and app-schema profiles in GeoServer:

mvn eclipse:clean eclipse:eclipse -Parcsde -Papp-schema

* Refresh your GeoServer workspace.

* GeoServer kept getting the sde dummy api despite my attempts at rebuilding. I ended up manually editing the Java build path for arcsde module, to add the sde jars and remove gt-sde-dummy jar in the library.

* More notes for ARCSDE installation for GeoServer:arcSDE installation.

Topic revision: r4 - 15 Oct 2010, UnknownUser

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).