Oracle Spatial ¶

Author:: Bart van den Eijnden
Author:: Jeff McKenna
Contact:: jmckenna at gatewaygeomatics.com
Last Updated:: 2021-05-05

Oracle Spatial is a spatial component 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 that this document was based on.

Binaries ¶

MapServer Windows plugins with Oracle spatial support are included in MS4W. But you need Oracle client software installed on the server on which you are running MapServer. Oracle client software can be obtained for development purposes from the Oracle website. 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 that your MapServer is compiled against the same version as your Oracle client.

Installation ¶

See Oracle Installation for more configuration and installation information for MapServer’s native Oracle support.

Bemerkung

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: GDAL/OGR is not compiled with Oracle Spatial support by default, 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]
]"

Note variable binding is supported by using BINDVALS and name value pairs.

Examples ¶

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 not use any geometry function or spatial operator. So, the internal SQL doesn’t restrict, based on the extent, the data from source. All the data from source will be returned to MapServer. The NONE token is very useful when the source of the data doesn’t contain any spatial index. It usually occurs 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 PROJECTION to your 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"