= OracleSpatial Support =

----
The official document is at: http://www.mapserver.org/input/vector/oracle.html
----

Author: Bart van den Eijnden, The Netherlands, bartvde@xs4all.nl

== BINARIES ==
Mapserver binaries with Oracle spatial support can be download at the !MapServer download site. 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 http://otn.oracle.com, but you need to register, which by the way is free. The most recent version is Oracle 9i client. The ORACLE TECHNOLOGY NETWORK DEVELOPMENT LICENSE AGREEMENT applies to this software. Download from: http://otn.oracle.com/software/htdocs/devlic.html?/software/products/oracle9i/htdocs/winsoft.html

== MAPFILE SYNTAX ==
Oracle Spatial layers in !MapServer can be used through 2 interfaces:

1) the native built-in support through maporaclespatial.c 2) 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 !!!!

=== OPTION 1 NATIVE ORACLE SPATIAL SUPPORT ===

There are two possible syntaxes in the mapfile for the DATA statement:

   1. DATA "GEOMETRIE FROM KWADRANTEN USING SRID 90112"
   2. DATA "GEOMETRIE FROM (SELECT GEOMETRIE FROM KWADRANTEN)" 

When used with the WMS interface, syntax 1. takes in the bbox into the request which is sent to Oracle. Syntax 2 does not do this and gets in the example above all the records from the table, although only displaying a few with my example URL. When leaving out the USING SRID I get an error: msDrawMap?(): Image handling error. Failed to draw layer named 'kwadranten'.

Example URL: http://myserver/cgi-bin/mapserv/mapserv_gif.exe?map=C:\mapfiles\kwadranten.map&request=map&layers=kwadranten&bbox=171760,449736,178257,456421&version=1.1.0&format=gif&width=500&height=500

Complete layer syntax:
{{{
  LAYER
    NAME kwadranten
    TYPE POLYGON
    CONNECTIONTYPE oraclespatial
    CONNECTION "user/pwd@service"
    DATA "GEOMETRIE FROM KWADRANTEN USING SRID 90112"
    DUMP TRUE
    CLASS 
     OUTLINECOLOR	0 0 0
     COLOR 0 128 128
    END 
  END 
}}}
Where service is the alias (in this example MYDB) supplied in the tnsnames.ora file of the Oracle client, e.g.
{{{
  MYDB =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = www.mydomain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB1)
    )
  )
}}}
BTW with Oracle Spatial layers it is also possible to put labels, like for normal mapserver layers, e.g.:
{{{
  LAYER
    NAME kwadranten
    TYPE POLYGON
    CONNECTIONTYPE oraclespatial
    CONNECTION "user/pwd@service"
    DATA "GEOMETRIE FROM KWADRANTEN USING SRID 90112"
    LABELITEM "KWADRANTNR"
    CLASS 
      OUTLINECOLOR	0 0 0
      LABEL
        COLOR 0 0 0
        POSITION CC
      END
    END 
  END
}}}
An example of the query which will be run against Oracle is:
{{{
SELECT rownum, KWADRANTNR, GEOMETRIE FROM KWADRANTEN WHERE SDO_FILTER( KWADRANTEN.GEOMETRIE, MDSYS.SDO_GEOMETRY(2003, 90112, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(171673.094,449742.685,178343.906,456414.315) ),'querytype=window') = 'TRUE'
}}}
=== OPTION 2 ORACLE SPATIAL SUPPORT THROUGH OGR === 

Mapserver 4.0.1 Windows binary with OGR Oracle Spatial support (GDAL version 1.2) downloadable from: http://www.vz.geodan.nl/users/bart/mapserv4.0.1-OGR-Oracle.zip

Syntax for your MAP file:
{{{
  CONNECTION "OCI:user/pwd@service"
  CONNECTIONTYPE OGR
  DATA "Tablename"
}}}
Make sure you set the wms_extent METADATA for the LAYER, as otherwise the "Getcapabilities" request takes a lot of time.

http://www.xlmhx.com.cn

== ORACLE SPATIAL FAQ ==

The Oracle Spatial FAQ is located at http://www.orafaq.com/faqsdo.htm