The official document is at: http://www.mapserver.org/input/vector/oracle.html
Author: Bart van den Eijnden, The Netherlands, bartvde@…
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
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:
- DATA "GEOMETRIE FROM KWADRANTEN USING SRID 90112"
- 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.
ORACLE SPATIAL FAQ
The Oracle Spatial FAQ is located at http://www.orafaq.com/faqsdo.htm