Teradata

Note

Teradata database support is not enabled by default and requires the Teradata extension to be installed prior to use. Please see the section on Installing the Teradata extension for details.

The Teradata Database is a commercial relational database (RDBMS) that specializes in parallel processing and scalability. From version 12.0, Teradata has added geospatial support, closely following the SQL/MM standard (SQL Multimedia and Applications Packages). Geospatial support was available through an add-on in version 12.0 and became standard in version 13.0.

GeoServer connects to a Teradata database via JDBC.

For more information on Teradata and the Teradata Database system, please go to http://www.teradata.com.

Compatibility

The GeoServer Teradata extension is compatible with GeoServer 2.1.1 and higher. GeoServer can connect to Teradata databases version 12.0 or higher. Version 12.0 of the Teradata Database requires the optional geospatial extension to be installed.

Read/write access

The Teradata datastore in GeoServer supports full transactional capabilities, including feature creation, editing, and deleting.

To support editing, a table must have one of the following:

  • a primary key
  • a unique primary index
  • an identity (sequential) column

Note

It is not recommended to solely use an identity column, as spatial index triggers are not supported when referencing an identity column. See the section on Spatial Indexes for more details.

Query Banding

The GeoServer Teradata extension supports Query Banding. Query Banding is a feature which allows any application to associate context information with each query it issues to the database. In practice this can be used for purposes of workload management (i.e. request prioritization), debugging, and logging.

GeoServer sends the following information as part of a standard request:

  • Name of application (i.e. GeoServer)
  • Authenticated username (if set up)
  • Hostname (if available)
  • Type of statement (i.e. “SELECT”, “INSERT”, “DELETE”)

It is not possible to modify this information from within GeoServer.

Spatial indexes

GeoServer will read from a spatial index if its exists. The convention for a spatial index table name is:

[TABLENAME]_[GEOMETRYCOLUMN]_idx

So for a layer called “STATES” with a geometry column called “GEOM”, the index table should be called STATES_GEOM_idx.

Warning

Make sure to match the case of all tables and columns. If the geometry column is called “GEOM” (upper case) and the index created is called STATES_geom_idx (lower case), the index will not be properly linked to the table.

This index table should contain two columns:

  • A column that maps to the primary key of the spatial data table
  • The tessellation cell ID (cellid)

The tessellation cell ID is the ID of the cell where that feature is contained.

Geometry column

As per the SQL/MM standard, in order to make a Teradata table spatially enabled, an entry needs to be created for that table in the geometry_columns table. This table is stored, like all other spatially-related tables, in the SYSSPATIAL database.

Tessellation

Tessellation is the name of Teradata’s spatial index. In order to activate tessellation for a given layer, an entry (row) needs to be placed in the SYSSPATIAL.tessellation table. This table should have the following schema:

Table name Type Description
F_TABLE_SCHEMA varchar Name of the spatial database/schema containing the table
F_TABLE_NAME varchar Name of the spatial table
F_GEOMETRY_COLUMN varchar Column that contains the spatial data
U_XMIN float Minimum X value for the tessellation universe
U_YMIN float Minimum Y value for the tessellation universe
U_XMAX float Maximum X value for the tessellation universe
U_YMAX float Maximum Y value for the tessellation universe
G_NX integer Number of X grids
G_NY integer Number of Y grids
LEVELS integer Number of levels in the grid
SCALE float Scale value for the grid
SHIFT float Shift value for the grid

Warning

The tessellation table values are case sensitive and so must match the case of the tables and columns.

Installing the Teradata extension

Teradata database support is not enabled by default and requires the GeoServer Teradata extension to be installed prior to use. In addition to this extension, an additional artifact will need to be downloaded from the Teradata website.

GeoServer artifacts

  1. Download the Teradata extension from the download page that matches your version of GeoServer. The extension is listed at the bottom of the download page under Extensions.

    Warning

    Make sure to match the version of the extension to the version of GeoServer!

  2. Extract the contents of the archive into the WEB-INF/lib directory of the GeoServer installation.

Teradata artifacts

In addition to the GeoServer artifacts, it is also necessary to download the Teradata JDBC driver. This file cannot be redistributed and so must be downloaded directly from the Teradata website.

  1. Download the Teradata JDBC driver at https://downloads.teradata.com/download/connectivity/jdbc-driver.

    Note

    You will need to log in to Teradata’s site in order to download this artifact.

  2. Extract the contents of the archive into the WEB-INF/lib directory of the GeoServer installation.

When all files have been downloaded and extracted, restart GeoServer. To verify that the installation was successful, see the section on Adding a Teradata datastore.

Note

The full list of files required are:

  • gt-jdbc-teradata-<version>.jar
  • tdgssconfig.jar
  • terajdbc4.jar

Adding a Teradata datastore

Once the extension has been added, it will now be possible to load an existing Teradata database as a store in GeoServer. In the Web Administration Interface, click on Stores then go to Add a new Store. You will see a option, under Vector Data Stores, for Teradata. Select this option.

../../_images/teradata_addnewstore.png

Teradata in the list of readable stores

Note

If you don’t Teradata in this list, the extension has not been installed properly. Please ensure that the steps in the Installing the Teradata extension have been followed correctly.

On the next screen, enter in the details on how to connect to the Teradata database. You will need to include the following information:

Option Description
Workspace Name of the workspace to contain the database. This will also be the prefix of any layers server from tables in the database.
Data Source Name Name of the database in GeoServer. This can be different from the name of the Teradata database, if desired.
Description Description of the database/store.
Enabled Enables the store. If disabled, no layers from the database will be served.
host Host name where the database exists. Can be a URL or IP address.
port Port number on which to connect to the above host.
database Name of the Teradata database.
user User name to connect to use to connect to the database.
passwd Password associated with the above user.
namespace Namespace to be associated with the database. This field is altered automatically by the above Workspace field.
Expose primary keys Exposes primary key as a standard attribute.
max connections Maximum amount of open/pooled connections to the database.
min connections Minimum number of open/pooled connections.
fetch size Number of records read with each interaction with the database.
Connection timeout Time (in seconds) the connection pool will wait before timing out.
validate connections Checks the connection is alive before using it.
Primary key metadata table Name of primary key metadata table to use if unable to determine the primary key of a table.
Loose bbox If checked, performs only the primary filter on the bounding box.
tessellationTable The name of the database table that contains the tessellations
estimatedBounds Enables using the geometry_columns/tessellation table bounds as an estimation instead of manual calculation.
Max open prepared statements The maximum number of prepared statements.

When finished, click Save.

../../_images/teradata_store1.png
../../_images/teradata_store2.png

Adding a Teradata store

Using JNDI

GeoServer can also connect to a Teradata database using JNDI (Java Naming and Directory Interface).

To begin, in the Web Administration Interface, click on Stores then go to Add a new Store. You will see a option, under Vector Data Stores, for Teradata (JNDI). Select this option.

../../_images/teradata_selectionjndi.png

Teradata (JNDI) in the list of readable stores

On the next screen, enter in the details on how to connect to the Teradata database. You will need to include the following information:

Option Description
Workspace Name of the workspace to contain the database. This will also be the prefix of any layers server from tables in the database.
Data Source Name Name of the database in GeoServer. This can be different from the name of the Teradata database, if desired.
Description Description of the database/store.
Enabled Enables the store. If disabled, no layers from the database will be served.
jndiReferenceName JNDI path to the database.
schema Schema for the above database.
namespace Namespace to be associated with the database. This field is altered by changing the workspace name.
Expose primary keys Exposes primary key as a standard attribute.
Primary key metadata table Name of primary key metadata table to use if unable to determine the primary key of a table.
Loose bbox If checked, performs only the primary filter on the bounding box.

When finished, click Save.

../../_images/teradata_storejndi.png

Adding a Teradata store with JNDI

Adding layers

One the store has been loaded into GeoServer, the process for loading data layers from database tables is the same as any other database source. Please see the Layers section for more information.

Note

Only those database tables that have spatial information and an entry in the SYSSPATIAL.geometry_columns table can be served through GeoServer.