IBM Netezza Spatial Architecture

Netezza Spatial readers and writers rely on the presence of two metadata tables to decipher which tables (and columns) are spatial, and which coordinate systems are associated with them. These metadata tables are named GEOMETRY_COLUMNS and SPATIAL_REF_SYS.

When writing spatial data, if the current schema to which the user is connecting does not contain the two required metadata tables, the metadata tables will be cloned and truncated, one time, from one of the following locations:

For the single schema configuration:

  • the INZA database.

For multiple schema configurations:

  • the INZA schema in the current database, and if not there, then
  • the ADMIN schema in the current database, and if not there, then
  • the INZA database.

If the metadata tables are not found, translation fails. If an INZA package that does not contain these metadata tables is installed, see Supported Versions and Configurations on how to manually create these metadata tables.

The F_TABLE_NAME column in the GEOMETRY_COLUMNS table records the names of spatial tables in the current schema. If a recorded spatial table name does not exist in the current schema, it will not show up in the Table List picker of the Netezza Spatial writer. If creating spatial tables outside of FME, please ensure that a mechanism exists that will correctly update the GEOMETRY_COLUMNS and SPATIAL_REF_SYS tables. Please pay attention to the case-sensitivity of table names and column names.

When writing spatial data to a database that either has an Esri spatial analytics package registered, or which falls back onto the INZA database which has an Esri spatial analytics package, please note the following caveats.

Writing non-homogenous collections results in a database error. Writing geometries outside the bounds of the specified coordinate system results in a database error. Similarly, when writing geometries whose coordinates collapse onto the discrete grid defined by the specified coordinate system, and where the collapsing results in duplicate consecutive coordinates or self-intersections, expect database errors.

The database errors can result in feature rejections or translation failures, depending on the setting for Abort Translation on Bad Data. It is highly recommended that Abort Translation on Bad Data be set to Yes, as database errors result in the rollback of the current transaction, including any good data that had been successfully written.

FME supports writing to a single geometry column per table.

Column Descriptions for GEOMETRY_COLUMNS

The schema for GEOMETRY_COLUMNS:

Column

Type

Modifier

F_TABLE_CATALOG

nvarchar(256)

NOT NULL

F_TABLE_SCHEMA

nvarchar(256)

NOT NULL

F_TABLE_NAME

nvarchar(256)

NOT NULL

F_GEOMETRY_COLUMN

nvarchar(256)

NOT NULL

GEOMETRY_TYPE

integer

 

COORD_DIMENSION

integer

 

SRID

integer

 

DISSOLVE_COLUMN

nvarchar(256)

 

 

Column

Description

F_TABLE_CATALOG

The name of a database. FME does not currently use this column.

F_TABLE_SCHEMA

The name of a database schema. FME does not currently use this column.

F_TABLE_NAME

The name of a spatial table that contains geometry data.

F_GEOMETRY_COLUMN

The name of a geometry column in the table stored under
F_TABLE_NAME.

GEOMETRY_TYPE

The WKB data type of the spatial data contained in the geometry column.

COORD_DIMENSION

The dimensionality of the geometry column (XY = 2, XYZ = 3, XYM = 3, XYZM = 4, where M is for measures).

SRID

the spatial reference ID, and corresponds to the same named column in the SPATIAL_REF_SYS table. It specifies the coordinate system, or lack thereof, for the spatial table

DISSOLVE_COLUMN

FME does not currently use this column.

SPATIAL_REF_SYS

The table SPATIAL_REF_SYS conforms to the OpenGIS standard Implementation Specification for Geographic information - Simple feature access - Part 2- ยง7.1.2.

Spatial Storage and Coordinate Systems

When writing spatial data, geometry is first converted into WKB, before being converted into ST_GEOMETRY via ST_GEOMFROMWKB(). If writing to a database with an Esri spatial analytics package, the geometry further undergoes a transformation so its coordinates, if they are within the bounds of the specified coordinate system, are snapped onto the discrete grid that underlies the coordinate system.

When writing a spatial table, a single coordinate system should be supplied. There are three cases:

  • Coordinate system is not supplied: if a coordinate system is not specified, the default coordinate system of LL-WGS84 (SRID=4326) will be attached to spatial features being written.
  • Coordinate system is supplied and is known to the destination database: the spatial feature will be written with a SRID corresponding to the known coordinate system.
  • Coordinate system is supplied and is unknown to the destination database: if writing to an Esri database, the spatial feature will first be reprojected to LL-WGS84 (SRID=4326) and then written if reprojection succeeds; if writing to a non-Esri database, the unknown coordinate system will be added to the non-Esri database, and then the SRID assigned to that coordinate system will be attached to the spatial feature being written.

Geometry Size

The maximum geometry size is the lesser of:

  • 64,000 bytes, or
  • 65,535 bytes minus the size of the non-geometry columns.

To split up an area or line feature that is larger than the maximum geometry size, use a Chopper transformer. Line parts can be glued back together using a LineCombiner transformer, and areal parts can be glued back together using a Dissolver transformer. IBM documentation states that the maximum number of coordinates a line or area can have is approximately 3990 (assuming 2D, and no measures).

To glue the geometry parts back together, it will be necessary to create a feature ID column in the destination table. The feature ID needs to be assigned to features before they are chopped up. A Dissolver or LineCombiner transformer can be used to glue the parts back together by setting Group By to the feature ID column.

If a geometry being written has a size greater than the geometry size specified by the table schema, an attempt will be made to increase the geometry column size. If the incoming geometry has a size that is too large to be accommodated, then expect feature rejection or translation failure. If you know the maximum geometry size a priori, set it using the Spatial Column Width parameter under the Format Parameters tab in a Netezza Spatial writer feature type. If geometry column resizing occurs at run-time, expect slowdowns.