Netezza Spatial readers and writers rely on the presence of two metadata tables to decipher which tables (and columns) are spatial, and what 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.
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 a single geometry column per table. When adding geometry values to an existing column, the geometry types must be compatible with the geometry column type or expect translation failure. Compatibility testing considers a variety of factors such as dimensionality, the coordinate system, and whether measures are present, amongst others.
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.
Geometry Conversion 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.
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.
When writing a spatial table, a SRID value corresponding to a coordinate system should be supplied. For spatial tables in databases with Esri spatial packages, if no coordinate system is supplied, or if the coordinate system supplied does not match a known Esri coordinate system, a default SRID of 4326 is assigned. For spatial tables in databases with non-Esri spatial packages, if no coordinate system is supplied by the user, a default SRID of 0 is assigned. Note that a SRID of 0 will cause many spatial functions, such as ST_LENGTH(), to err.
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 LineJoiner 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 around 3990 (2D, 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 LineJoiner 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 Geometry 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.