Oracle¶

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: host:port:database, while a Service connection descriptor has the format host:port/database. GeoServer uses the SID form by default. To connect via a Service, prefix the 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 database ldap://[host]:[Port]/[db],cn=OracleContext,dc=[oracle_ldap_context].

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:

java.naming.referral=follow

Using loose bounding box¶

When the 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 MDSYS.USER_SDO* and MDSYS.ALL_SDO* views 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 MDSYS.ALL_SDO* views).

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,
   COORD_DIMENSION INTEGER,
   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.