Oracle Spatial support

[Fernando S.]

Hi users,
    I fixed the bug with layer close and added the support for  NONE
token. I added t the how to documentation for Oracle Spatial. I updated
the source code with the fix this morning.
   Thanks Valik for the hints with layer close and thanks Francois for
the hints about NONE token.
   Thanks for all.

----------------------------------------------------------------
Fernando Simon - simon at inf.univali.br
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada
http://g10.cttmar.univali.br
----------------------------------------------------------------

-------------- next part --------------
HOWTO: Using mapserver with native Mapserver Oracle Spatial support

This HOWTO assumes you are familiar with the Mapserver Mapfile reference. The native Oracle Spatial support in Mapserver deals with LAYERs of CONNECTIONTYPE oraclespatial.

New features were added in maporaclespatial which will be released in Mapserver 4.4. This version supports cgi mapserv query modes: query, nquery, itemnquery and mapscript query functions as querybyattributes.

The DATA statement for a LAYER of CONNECTIONTYPE oraclespatial now have more options. This change is backwards compatible, i.e. the old ways (option 1 and option 3) of specifying DATA still work. The new options are an extension to the old DATA statements, as they needed to include identification of the column that store your unique identifier for your table, used for the query modes (UNIQUE) and SPATIAL function.

The basic options for DATA statements:

1 - "<geom_column> from <table>"
2 - "<geom_column> from <table> USING UNIQUE <column>"
3 - "<geom_column> from <table> USING SRID <srid#>"
4 - "<geom_column> from <table> USING UNIQUE <column> SRID <srid#>"

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

Example:

LAYER
  ..
  CONNECTIONTYPE oraclespatial
  DATA "mygeometry FROM mytable"
  ..
END

------------
| Option 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 as a parameter. This example assumes you do not have an SRID defined.

Example:

LAYER
  ..
  CONNECTIONTYPE oraclespatial
  DATA "mygeometry FROM mytable USING UNIQUE unique_column"
  ..
END

------------
| Option 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.

Example:

LAYER
  ..
  CONNECTIONTYPE oraclespatial
  DATA "mygeometry FROM mytable USING SRID 90112"
  ..
END

------------
| Option 4 |
------------
This option is a simple combination of option 2 and 3.

Example:

LAYER
  ..
  CONNECTIONTYPE oraclespatial
  DATA "mygeometry FROM mytable USING UNIQUE unique_column SRID 90112"
  ..
END


------------------
| Another Option |
------------------
  It is possible to define your table as a subselect and not only as a
simple table. You can use the lastest options with this option. The sql
inside of "( )" will be interpreted how a table.
  IMPORTANT, 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.

Data Example:

DATA "mygeometry FROM (SELECT mygeometry, <fields> FROM <table>)"


--------------------
| SPATIAL FUNTIONS |
--------------------
You can add four keywords to the DATA statement to influence the sql which will be executed in Oracle. You can use this four options with the lastest options. The options:

1. USING FILTER

Example: "<geom_column> from <table> 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. With Oracle 10g one problem can appear when the index was defined with SDO_LEVEL parameter, the return of your sql can be more tahn you want, in 10g you don't need to use (the Oracle don't reccomend) to use this parameter when you create the index.

2. USING RELATE

Example: "<geom_column> from <table> 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. Its performance can be slightly slow but the result is extremely correct. You can to use this mode when you want a perfect result or when you can't readjust the spatial index.

3. USING GEOMRELATE

Example: "<geom_column> from <table> 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 any 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.

4. USING NONE

Example: "<geom_column> from <table> USING NONE"

Using this keyword triggers Mapserver to don't use any the geometry function os spatial function. In this case the internal sql don't use any Oracle function.

-------------
| IMPORTANT |
-------------
  By default when you don't specify any spatial functions the internal
sql will use the FILTER operator. When you use an Oracle Spatial function
(for instance SDO_AGGR_UNION) as a source for your query and you are
experiencing a problem with USING FILTER or USING RELATE you can use
USING GEROMRELATE or USING NONE.
  More 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.
  You can define any PROJECTION in your LAYER without any problem, both
for data with or without an SRID in Oracle.