Writing to Database Tables that contain Multiple Geometry Columns

Introduction

There are two multiple-geometry writing scenarios:

• Reading AND Writing multiple geometries
• Reading single geometry features and converting them to multiple geometries

Reading and Writing Multiple Geometries

In a Multiple -> Multiple translation FME handles the reading and writing automatically.

Reading Single and Writing Multiple Geometries

When converting single geometries to multiple, the key is in how to identify two features that are related, and how to assign each of them to the appropriate geometry column.

Because FME doesn’t yet handle Single -> Multiple translations automatically within Workbench, the setup for each database record to be written must be defined manually. It will be composed of two or more features, each of which contributes its geometry to the final record.

The functionality used to do this involves geometry names and aggregates.

1) A geometry name is applied to each feature with a GeometryNameSetter and this geometry name identifies the geometry columns to write to.

2) The features are grouped together as an aggregate—usually with an Aggregator transformer—and this identifies which features form a particular database record.

Creating Multiple Geometry Tables

FME isn’t yet able to create tables with multiple geometry columns. You may either use an existing table with multiple geometry columns, or you can use a SQL script within the FME translation to create one.

The goals of the SQL script are to:

• Check if the table already exists and, if so, drop (delete) it
• Delete any existing metadata records for the table
• Create the new table
• Insert new metadata records for the table

A sample script Oracle_SQL.txt for using with Oracle is attached.

Notes:

The script is divided into four sections:

• An FME keyword to define the SQL delimiter
• A PL/SQL script to drop an existing table and delete its metadata
• A SQL command to create the CITY_PARKS table
• Two insert statements to create metadata entries
• The FME_SQL_DELIMITER keyword specifies a character to separate multiple SQL statements. Usually you would use a semi-colon (;) but here we have a section of PL/SQL that already uses semi-colon delimiters within itself, and so this script uses the exclamation mark (!) instead.
• The PL/SQL section is used in order to check if the table exists before dropping it. It is equivalent to – though not as elegant as – the SQL Server “IF EXISTS” function.
• In the line that deletes existing metadata records, CITY_PARKS is bracketed by two single quote characters (''CITY_PARKS''). This is a requirement of the FME parser.
• There are two lines to create metadata (one for each geometry column). Without these records FME would not be able to find the table to read it back!

A second sample script Oracle_SimpleSQL.txt is also attached.

For working with a SQLServer database the sample script SQLServer_SimpleSQL.txt is also attached.

Notes:

The difference here is, rather than trap errors in the script, a specific FME device is used to ignore them. Notice the hyphen character that precedes the DROP and DELETE commands. This prompts FME to ignore any errors from these commands, such as would occur when trying to drop a table that does not exist.

Pros A shorter, simpler script

Cons Non-standard SQL

Commits are performed automatically, and do not need to be included

Workspaces

Either of these scripts can be used in the Writer Advanced Parameter > SQL Statement to Execute Before Translation. This ensures that the table is built correctly before the workspace is to be run. Then it is important NOT to specify "Drop and Create" in the Table Handling Parameters on the Feature Type.

The attached workspace for Writing Multiple Geometry columns performs the following steps:

- Create the Oracle table. FME cannot create multiple geometry tables

- make sure the database writer has the parameter "Handle Multiple Spatial Columns"=Yes

1 - Set the name of the geometry column for each set of features

2 - Turn the features into an aggregate

3 - Flag the aggregates such that they are setup where each part is independent of the others and it's own complete geometry.

4 - Write to the table - truncate the table and don't set a geom column name

SQL Server

If working with SQL Server it is possible to have two geometry columns with differing geometry types. e.g. one column that uses the geometry model and one column that uses the geography model. In the SQL Server sample workspace, the table has been created such that the Polygons will be stored as GEOGRAPHY data. We have added a Reprojector to reproject the polygon features into a Lat/Long coordinate system and then they will be written correctly.

Updating Spatial Data

You can update tables that have multiple spatial columns. First, here's how updates are supposed to work when working with multiple spatial columns. There are three update scenarios.

1. update both spatial columns

2 update both spatial columns, but set one to a <null> geometry

3. only update one of the spatial columns leaving the other one unchanged.

Prerequesites for writing multiple spatial columns are discussed above.

When writing updates to multiple spatial column tables (fme_db_operation = UPDATE) that have multiple geometries then you can expect the following behaviour:
Say we have named spatial columns G1 & G2

1. update both geometries: G1 & G1
Create an aggregate. Use MultipleGeometrySetter to flag the aggregate as a multiple geometry. G1 & G2 are on the feature as an aggregate. G1 & G2 are both updated

2. update both geometries and set G2 to <null> geometry: G2 needs to be on the feature bust as a <null> geometry. You can use VertexRemover to remove the coordinates but keep the geometry (i.e. G2 = null) Create the aggregate of G1 & G2 . G1 & G2 are both updated & G2 = <null>

3. Update G1, do NOT update G2. Create an aggregate of G1 and MultipleGeometrySetter. G2 is missing from the feature. G1 is updated and G2 should be left unchanged.