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: |
|
|
For multiple schema configurations: |
|
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 |
|
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.