.. _oci:

*****************************************************************************
 Oracle Spatial
*****************************************************************************

:Author: Bart van den Eijnden
:Last Updated: 2005/12/12

.. contents:: Table of Contents
    :depth: 2
    :backlinks: top


.. index:: Oracle

Oracle Spatial is a spatial cartridge for the Oracle database. Remember
that all Oracle databases come with Locator, which has less features than
Oracle Spatial. The differences between Locator and Spatial can be found in
the `Oracle Spatial FAQ`_. 

You can also see the original `OracleSpatial wiki page <https://github.com/mapserver/mapserver/wiki/OracleSpatial>`__
that this document was based on.

What MapServer 5.2 with Oracle Spatial
------------------------------------------------------------------------------

* mode=map 
* query modes: query, nquery, itemnquery 
* :ref:`mapscript` query functions such as querybyattributes 
* :ref:`OGC:WMS <wms_server>`: GetCapabilities, GetMap, GetFeatureInfo, DescribeLayer 
* :ref:`OGC:WFS <wfs_server>`, GetCapabilities, DescribeFeatureType, GetFeature 


Binaries
------------------------------------------------------------------------------

MapServer Windows plugins with Oracle spatial support can be downloaded from
:ref:`MS4W <windows>`. But you need Oracle client software in the server on which you
are running MapServer. Oracle client software can be obtained for development
purposes from the Oracle website, but you need to register, which by the way
is free. The most recent version is Oracle Database 10g Release 1 Client. The
ORACLE TECHNOLOGY NETWORK DEVELOPMENT LICENSE AGREEMENT applies to this
software. The instant client will be satisfactory, and you can download the
`instant client`_. Make sure though your MapServer is compiled against the
same version as your Oracle client, for compiling you need a full client
install, not just the instant client.

Installation
------------------------------------------------------------------------------

See :ref:`oci_install` for more configuration and installation information 
for MapServer's native Oracle support

.. note::
    If you receive error messages like "Error: .". It's likely related to
    MapServer being unable access or locate the ORACLE_HOME.

Two options for using Oracle Spatial with MapServer
------------------------------------------------------------------------------

Oracle Spatial layers in MapServer can be used through 2 interfaces:

* The native built-in support through maporaclespatial.c
* OGR, but watch out: OGR is not compiled with Oracle Spatial support so it
  won't work without compiling in OCI (Oracle client) yourself. This requires
  both recompiling GDAL/OGR as well as recompiling MapServer itself against
  the new GDAL/OGR !!!!

Mapfile syntax for native Oracle Spatial support
------------------------------------------------------------------------------

The DATA statement for a LAYER of CONNECTIONTYPE oraclespatial can now have 4
options. This change is backwards compatible, i.e. the old ways of specifying
DATA still work. The new options are an extension to the old DATA statements,
as they needed to include identification of the primary key to be used for the
query modes (UNIQUE).

The following options are valid DATA statements:

::

    "[geom_column]
    FROM 
    [table]| [( 
                SELECT [...] 
                FROM [table]|[Spatial Operator] 
                [WHERE condition] )] 
    [USING [UNIQUE column] 
            | [SRID #srid] 
            | [FUNCTION] 
            | [VERSION #version] 
    ]"


Example 1
..............................................................................

The most simple DATA statement, in this case you only need to define one
geometry column and one table. This option assumes you do not have an SRID
defined.

::

    LAYER 
        ... 
        CONNECTIONTYPE oraclespatial 
        DATA "MYGEOMETRY FROM MYTABLE" 
        ... 
    END 
    

Example 2
..............................................................................

It's composed of the first option plus the USING UNIQUE parameter. These new
features are necessary when you want to use any query function. When it is
used you must pass a numeric column type. This option assumes you do not have
an SRID defined.

::
    
    LAYER 
        ... 
        CONNECTIONTYPE oraclespatial 
        DATA "MYGEOMETRY FROM MYTABLE USING UNIQUE MYTABLE_ID" 
        ... 
    END 
    
Example 3
..............................................................................

This option is an extension to the first option. In this
mode you must define the USING SRID parameter when the SRID value in your data
is different from NULL. 

::

    LAYER 
        ... 
        CONNECTIONTYPE oraclespatial 
        DATA "MYGEOMETRY FROM MYTABLE USING SRID 90112" 
        ... 
    END


Example 4
..............................................................................

This option is a combination of examples 2 and 3.

::

    LAYER 
        ... 
        CONNECTIONTYPE oraclespatial 
        DATA "MYGEOMETRY FROM MYTABLE USING UNIQUE MYTABLE_ID SRID 90112" 
        ... 
    END



Using subselects in the DATA statement
------------------------------------------------------------------------------

It is possible to define the source of the date as a subselect and not only as
a table. As source of data, used in FROM token, you can define any SQL, table,
function, or operator that returns a SDO_GEOMETRY. For example:

::

    DATA "[geom_column] FROM (SELECT [columns] FROM [table]|[Spatial function])" 

If the LAYER definition contains a CLASSITEM, LABELITEM or FILTER, it is
necessary that the fields used are returned by the query. When you define
CLASSITEM you can use an expression without any problems.



Additional keywords - [FUNCTION]
------------------------------------------------------------------------------

You can add three keywords to the DATA statement for [FUNCTION] option that
influence the query which will be executed in Oracle:

USING FILTER
..............................................................................

::

    "[geom_column] FROM [table]|([Subselect]) USING FILTER" 

Using this keyword triggers MapServer to use the Oracle Spatial SDO_FILTER
operator. This operator executes only the Oracle Spatial primary filter over
your query data. In the Oracle User guide they explain: The primary filter
compares geometric approximations, it returns a superset of exact result. The
primary filter therefore should be as efficient (that is, selective yet fast)
as possible. This operator uses the spatial index, so you need to define your
spatial index correctly to retrieve an exact result. If the result of the
query is not exact you can try the next option.


USING RELATE
..............................................................................

::

    "[geom_column] FROM [table]|([Subselect]) USING RELATE"

Using this keyword triggers MapServer to use the Oracle Spatial SDO_RELATE
operator. This operator applies the primary and secondary Oracle Spatial
filters. It's performance can be slightly slow but the result is extremely
correct. You can use this mode when you want a perfect result or when you
can't readjust the spatial index.


USING GEOMRELATE
..............................................................................

::

    "[geom_column] FROM [table]|([Subselect]) USING GEOMRELATE" 

Using this keyword triggers MapServer to use the geometry function
SDO_GEOM.RELATE, a function that searches the relations between geometries.
SDO_GEOM.RELATE does not use the spatial index and your performance is more
slow than operators but it's very accurate. You can use this mode when you
can't use the spatial index or when it doesn't exist.

USING NONE
..............................................................................

::

    "[geom_column] FROM [table]|([Subselect]) USING NONE"

Using this keyword triggers MapServer to don't use any geometry function or
spatial operator. So, the internal SQL don't retrict, bases in the extent, the
data from source. All the data from source will be returned for MapServer. The
NONE token is very useful when the source of the data don't contains any
spatial index. It's usually occur when the source is a function like
SDO_BUFFER, SDO_XOR, SDO_INTERSECTION...... So this mode is recommended when
you can't use the spatial index or when it doesn't exist. 

Additional keywords - [VERSION]
------------------------------------------------------------------------------

You can define what version of database you are using to improve the internal
sql. This is very useful when using geodetic SRIDs and MapServer functions
that retrieve the extent from data.

USING VERSION 8i
..............................................................................

::

    "[geom_column] FROM [table]|([Subselect]) USING VERSION 8i" 

This indicates MapServer to use a internal SQL that it's compatible with
Oracle 8i version.

USING VERSION 9i
..............................................................................

::

    "[geom_column] FROM [table]|([Subselect]) USING VERSION 9i"

The second indicates MapServer to use 9i version, is recommended to use this
parameter if you are using 9i version because the internal SQL will use
specific spatial functions that is need to retrieve data correctly from 9i
Oracle Spatial versions. 

USING VERSION 10g
..............................................................................

::
    
    "[geom_column] FROM [table]|([Subselect]) USING VERSION 10g"

This indicates MapServer to use a internal SQL that it's compatible with
Oracle 10g version.

More information
------------------------------------------------------------------------------

* You can define any :ref:`PROJECTION` to your :ref:`LAYER` without problem,
  can be used for data with or without an SRID in Oracle.

* The native support for Oracle Spatial supports the defaults definition for
  SDO_GEOMETRY in database, the Oracle Spatial SDO package.

* Information about the primary and secondary Oracle Spatial filters can be
  found in the Oracle Spatial User Guide (the "Query Model" section).
  Information about the SDO_FILTER and SDO_RELATE operators can be found in
  the "Spatial Operators" section, and information about the SDO_GEOM.RELATE
  function can be found in the "Geometry Function" section of the Oracle
  Spatial User Guide.

Example of a LAYER
------------------------------------------------------------------------------

::

    LAYER 
        NAME kwadranten 
        TYPE POLYGON
        CONNECTIONTYPE oraclespatial 
        CONNECTION "user/pwd" 
        DATA "GEOMETRIE FROM KWADRANTEN USING SRID 90112" 
        CLASS 
            STYLE 
                OUTLINECOLOR 0 0 0 
                COLOR 0 128 128 
            END 
        END 
    END 

You can specify the SID for your database, the SID alias needs to be supplied
in the tnsnames.ora file of the Oracle client, e.g.

Example for tnsnames.ora:

::

    MYDB =
        (DESCRIPTION =
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = server_ip)(PORT = 1521))
            )
            (CONNECT_DATA =
                (SERVICE_NAME = DB1)
            )   
        )

So after this you can define you layer connection as:

::

    CONNECTION "user/pwd@MYDB"

Mapfile syntax for OGR Oracle Spatial support
------------------------------------------------------------------------------

Syntax for your MAP file: 

::

    CONNECTION "OCI:user/pwd@service" 
    CONNECTIONTYPE OGR 
    DATA "Tablename" 

.. note::
    Make sure you set the wms_extent METADATA for the LAYER, as otherwise the
    "Getcapabilities" request takes a lot of time.


.. _`Oracle Spatial FAQ`: http://www.orafaq.com/faqsdo.htm
.. _`instant client`: http://www.oracle.com/technology/software/tech/oci/instantclient/index.html
