GeoServer does not come built-in with support for Oracle; it must be installed through an extension. Proceed to Installing the Oracle extension for installation details.
Oracle Spatial and Locator are the spatial components of Oracle. Locator is provided with all Oracle versions, but has limited spatial functions. Spatial is Oracle’s full-featured spatial offering, but requires a specific license to use.
Installing the Oracle extension¶
Ensure to match plugin (example 2.25-SNAPSHOT above) version to the version of the GeoServer instance.
Extract the contents of the archive into the
WEB-INF/libdirectory of the GeoServer installation.
Adding an Oracle datastore¶
Once the extension is properly installed Oracle appears as an option in the Vector Data Sources list when creating a new data store.
Configuring an Oracle datastore¶
The Oracle server host name or IP address.
The port on which the Oracle server is accepting connections (often this is port 1521).
The name of the database to connect to.
By default this is interpreted as a SID name. To connect to a Service, prefix the name with a
The database schema to access tables from. Setting this value greatly increases the speed at which the data store displays its publishable tables and views, so it is advisable to set this.
The name of the user to use when connecting to the database.
The password to use when connecting to the database. Leave blank for no password.
Connection pool configuration parameters. See Database Connection Pooling for details.
Controls how bounding box filters are made against geometries in the database. See the Using loose bounding box section below.
Flag controlling the use of MDSYS.USER_SDO_GEOM_METADATA or MDSYS.ALL_SDO_GEOM_METADATA table for bounding box calculations, this brings a better performance if the views access is fast and the bounds are configured right in the tables default is false
Boolean flag specifies whether REMARKS metadata will be returned.
Connecting to an Oracle cluster¶
In order to connect to an Oracle RAC one can use an almost full JDBC url as the
database, provided it starts with
( it will be used verbatim and options “host” and “port” will be ignored. Here is an example “database” value used to connect to an Oracle RAC:
(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service)))
More information about this syntax can be found in the Oracle documentation.
Connecting to a SID or a Service¶
Recent versions of Oracle support connecting to a database via either a SID name or a Service name.
A SID connection descriptor has the form:
while a Service connection descriptor has the format
GeoServer uses the SID form by default. To connect via a Service,
database name configuration entry with a
Connecting to database through LDAP¶
For instance if you want to establish a connection with the jdbc thin driver through LDAP, you can use following connect string for the input field
If you are using referrals, enable it by placing a jndi.properties file in geoserver’s CLASSPATH, which is in geoserver/WEB-INF/classes. This property file contains:
Using loose bounding box¶
Loose bbox option is set, only the bounding box of database geometries is used in spatial queries. This results in a significant performance gain. The downside is that some geometries may be reported as intersecting a BBOX when they actually do not.
If the primary use of the database is through the Web Map Service (WMS) this flag can be set safely, since querying more geometries does not have any visible effect. However, if using the Web Feature Service (WFS) and making use of BBOX filtering capabilities, this flag should not be set.
Using the geometry metadata table¶
The Oracle data store by default looks at the
to determine the geometry type and native SRID of each geometry column.
Those views are automatically populated with information about the geometry columns stored in tables that the current
user owns (for the
MDSYS.USER_SDO* views) or can otherwise access (for the
There are a few issues with this strategy:
if the connection pool user cannot access the tables (because impersonation is used) the MDSYS views will be empty, making it impossible to determine both the geometry type and the native SRID
the geometry type can be specified only while building the spatial indexes, as an index constraint. However such information is often not included when creating the indexes
the views are populated dynamically based on the current user. If the database has thousands of tables and users the views can become very slow
Starting with GeoServer 2.1.4 the administrator can address the above issues by manually creating a geometry metadata table describing each geometry column. Its presence is indicated via the Oracle datastore connection parameter named Geometry metadata table (which may be a simple table name or a schema-qualified one). The table has the following structure (the table name is flexible, just specify the one chosen in the data store connection parameter):
CREATE TABLE GEOMETRY_COLUMNS(
F_TABLE_SCHEMA VARCHAR(30) NOT NULL,
F_TABLE_NAME VARCHAR(30) NOT NULL,
F_GEOMETRY_COLUMN VARCHAR(30) NOT NULL,
SRID INTEGER NOT NULL,
TYPE VARCHAR(30) NOT NULL,
UNIQUE(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN),
CHECK(TYPE IN ('POINT','LINE', 'POLYGON', 'COLLECTION', 'MULTIPOINT', 'MULTILINE', 'MULTIPOLYGON', 'GEOMETRY') ));
When the table is present the store first searches it for information about each geometry column to be classified, and falls back on the MDSYS views only if the table does not contain any information.
Configuring an Oracle database with JNDI¶
See Setting up a JNDI connection pool with Tomcat for a guide on setting up an Oracle connection using JNDI.