ArcSDE¶

Spatial Database Engine (SDE) is one of ESRI‘s products which enables spatial data to be stored, managed, and quickly retrieved from leading commercial database management systems like Oracle, Microsoft SQL Server, Sybase, IBM DB2, and Informix.

Supported ArcSDE Operations¶

Versioned queries (query geometry and attributes from a specified version)
queryByAttributes (select geometry and attributes based on the values of an attribute)
Limited join support for within-database tables</li>
queryByRect (select geometry based on an extent)
Projection on the fly
SDE for Coverages (a read-only type of SDE for coverage, shapefile, and ArcStorm/ArcLibrarian repositories)
SDE 8.1, 8.2, 8.3, 9.0, 9.1, and 9.2
Linux, Windows, and Solaris (platforms that have SDE C API support)

Unsupported ArcSDE Operations¶

queryByShape (pass in a shape with MapScript and use it for queries)
Direct Connect (bypass SDE to connect directly to the database with the SDE C API)

How to make a connection to SDE:¶

Install the SDE C API client libraries for your platform (preferably matched to the server version you are using, ie 8.2 client -> 8.2 server, 8.3 client -> 8.3 server)
Compile MapServer with SDE support MapServer Unix Compilation Howto for specific details)
Define a LAYER block in a MapFile that uses SDE as the CONNECTIONTYPE

LAYER
  NAME         states
  TYPE         POLYGON
  CONNECTION "sdemachine.iastate.edu,port:5151,sde,username,password"
  CONNECTIONTYPE SDE
  DATA "HOBU.STATES_LAYER,SHAPE,SDE.DEFAULT"
  FILTER "where MYCOLUMN is not NULL"
  PROCESSING "QUERYORDER=ATTRIBUTE" # <-- MapServer 4.10 and above

  # Within database one-to-one join support

  # MapServer 5.0 and above
  PROCESSING "JOINTABLE=SDE_MASTER.GEOSERVWRITE.JOINTABLE"

  # MapServer 5.0 and above
  CLASSITEM "SDE_MASTER.GEOSERVWRITE.JOINTABLE.VAL"

  # MapServer 5.0 and above
  FILTER "SDE_MASTER.GEOSERVWRITE.JOINTABLE.AQ_TAG=SDE_MASTER.GEOSERVWRITE.JOINTESTLAYER.AQ_TAG"

  # ObjectID column manipulation
  # MapServer 5.0 and above
  PROCESSING "OBJECTID=OBJECTID"

  TEMPLATE '/where/the/template/file/is/located'
  CLASS
      STYLE
              SYMBOL 'circle'
              SIZE 3
              COLOR -1 -1 -1
              OUTLINECOLOR 0 0 0
      END
  END
END

CONNECTION - Order is important!¶

sdemachine.iastate.edu - The name of the machine you are connecting to. In some instances, this may need to be the IP address of the machine rather than the name if the server running MapServer is not configured to cascade DNS lookups
port:5151 - The port number of SDE. The port: is important as SDE expects you to define the service in this slot, and it can be other names like sde:oracle (for direct connect) or esri_sde (for systems with port 5151 defined as esri_sde in /etc/services)
sde - The database username that the SDE server is using to connect to your database. It is often only important for SDE setups that are connecting to Oracle (and even then, not so important). Just leave it as sde if you don’t know what it should be.
username - The username that will be connecting to SDE. This user must have been granted rights to select the layer that you will be specifying in the DATA directive. You can use ArcCatalog or the SDE command-line utilities to grant the appropriate rights to layers.
password - Password of the user connecting to SDE. Case Sensitive.

DATA - Order is important!¶

HOBU.STATES_LAYER - The layer name you are querying. This the full name of the table in which the layer resides. If you are using Oracle or Microsoft SQL Server as the DB for SDE, the schema name must also be supplied.
SHAPE - The column that contains the geometry. SDE technically allows for storage of multiple geometry types in the same layer, but in practice this isn’t desirable. Also, expect to have problems if there are invalid or null geometries in the layer (or versions of the layer).
SDE.DEFAULT - As of MapServer 4.2, you can query against a specific version of the layer. SDE supports multi-user editing with versions. If a layer has been Registered with the GeoDatabase and Registered as Versioned (ArcGIS terms), MapServer can query against specified versions of those edits. If not specified, SDE.DEFAULT will be used for all queries. Case Sensitive.

Note

The version parameter is located in a different spot than MapServer 4.2, which had it on the CONNECTION string.

TEMPLATE¶

/where/the/template/file/is/located - A template directive must be specified (can point to a dummy file) in order for MapServer to be able to query attributes from SDE. If you are only going to be drawing layers, this directive is unnecessary and will slow down the query operations of SDE (especially for layers with lots of attribute columns).

PROCESSING¶

PROCESSING “QUERYORDER=ATTRIBUTE” - Allows you to force SDE to use the WHERE clause that was defined in your FILTER statement first, without attempting to hit the spatial index. Only in very special cases will you want to do this.
PROCESSING “OBJECTID=OBJECTID” - If you are having trouble with the SDE driver detecting your unique ID column, you can override it with this processing parameter. Doing so will also have a slight performance benefit because it will save a couple of extra queries to the database.
PROCESSING “ATTRIBUTE_QUALIFIED=TRUE” - User can set this option to always use fully qualified attribute names.

Within-database Join Support¶

MapServer’s SDE driver, as of MapServer 5.0, allows you to join a single attribute table that has no geometries to the layer that you are rendering. This feature allows you to use the data in the joined table much as you would in a composite query that was made with something like PostGIS or Oracle Spatial. That is, the columns in the right table of the join are available for CLASSITEM, LABELITEM and so on. The biggest constraint, however, is that fully qualified names must be used or it most likely will not work. The join support is activated through PROCESSING options.

PROCESSING “JOINTABLE=SDE_MASTER.GEOSERVWRITE.JOINTABLE” - The JOINTABLE processing option tells the driver which table you are joining the current layer to.
CLASSITEM “SDE_MASTER.GEOSERVWRITE.JOINTABLE.VAL” - A CLASSITEM or LABELITEM for a joined table using this mechanism must be fully qualified.
FILTER “SDE_MASTER.GEOSERVWRITE.JOINTABLE.AQ_TAG=SDE_MASTER.GEOSERVWRITE.JOINTESTLAYER.AQ_TAG” - An important part of the join is defining how the join is to be made. Use a FILTER to do so.

Navigation