| Author: | Bart van den Eijnden |
|---|---|
| Last Updated: | 2005/12/12 |
Table of Contents
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 that this document was based on.
MapServer Windows plugins with Oracle spatial support can be downloaded from MS4W. 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.
See Oracle Installation 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.
Oracle Spatial layers in MapServer can be used through 2 interfaces:
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]
]"
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
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
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
This option is a combination of examples 2 and 3.
LAYER
...
CONNECTIONTYPE oraclespatial
DATA "MYGEOMETRY FROM MYTABLE USING UNIQUE MYTABLE_ID SRID 90112"
...
END
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.
You can add three keywords to the DATA statement for [FUNCTION] option that influence the query which will be executed in Oracle:
"[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.
"[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.
"[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.
"[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 recomended when you can’t use the spatial index or when it doesn’t exist.
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.
"[geom_column] FROM [table]|([Subselect]) USING VERSION 8i"
This indicates MapServer to use a internal SQL that it’s compatible with Oracle 8i version.
"[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.
"[geom_column] FROM [table]|([Subselect]) USING VERSION 10g"
This indicates MapServer to use a internal SQL that it’s compatible with Oracle 10g version.
LAYER
NAME kwadranten
TYPE POLYGON
CONNECTIONTYPE oraclespatial
CONNECTION "user/pwd"
DATA "GEOMETRIE FROM KWADRANTEN USING SRID 90112"
DUMP TRUE
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"