.. _arcsde:

*****************************************************************************
 ArcSDE
*****************************************************************************

.. WARNING::
   MapServer's native SDE driver was removed for the MapServer 7.0 release 
   (see `discussion <http://lists.osgeo.org/pipermail/mapserver-dev/2015-February/014349.html>`__).
   SDE support can still be accessed through the `OGR driver <http://www.gdal.org/drv_sde.html>`__.

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 
  :ref:`MapServer Unix Compilation Howto <unix>` 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.

.. _ESRI: http://www.esri.com
