Database Tips & Tricks In FME

Introduction

When working with databases in FME, there are some tips and tricks the can enhance your workspaces while improving performance and reducing the likelihood of failure. This article is part of a tutorial series exploring how to let the database do the work and will cover common pitfalls related to using databases with FME.

Tips & Tricks

Auto Connect

When writing to databases such as Oracle or PostgreSQL, FME may not automatically match attributes due to case differences. For example, in the image below is a PostGIS Reader and an Oracle Writer: the PostGIS lowercase attributes are not connected to the uppercase Oracle attributes.

Right-click on the connection between the two and select “Auto Connect Attributes”. The attributes are now connected. As a second step, right-click on the connection again and select “Replace Link with AttributeManager”. This will import an AttributeManager that documents the case change and can be used to preserve the change in the workspace.

PostGIS and Oracle default to using lower and upper case, respectively. This default can be disabled on the Oracle Writer and PostGIS Writer parameters.

Import Feature Types

Import Feature Types  allows a user to bring in attributes and data types from an existing dataset. This is useful when writing to an existing table in the database, or when trying to use the schema of an existing table as a template for a new table. You can also update feature types if the table you are writing to has changed since the feature type was last added to the canvas (i.e.- a column has been added or removed).

Writers and the FeatureWriter  are capable of importing feature types. Working with Foreign Keys: Writing Database Tables  shows an example of importing feature types using the FeatureWriter.

Exposing Attributes in the SQLCreator or SQLExecutor

When reading in a table using either the SQLCreator or SQLExecutor, FME does not automatically expose the attributes. You’ll notice this if you inspect the output in Visual Preview or the FME Data Inspector and see the features have <no schema>. To prevent this, you’ll need to expose the attributes. This can be done by manually typing in the attributes in the “Attributes to Expose” dialog. Alternatively, use the “Populate from SQL Query” from that dialog to send an independent SQL query to the database to retrieve attributes from the table being read. The query defaults to the statement set in the SQL Statement and can be used provided there is no WHERE clause.

Use this trick with Import Features Types on your Writer to ensure that attributes and their types are correctly matched and written to your output dataset.

Indexes

Indexes are extremely useful when reading from a database. By creating an index on your table, you’ll see an improvement in performance and time spent reading. See: Let the Database Do the Work: Reading  for an example of reading using an index.

Conversely, when writing to a table with an index, the translation can be slower due to rebuilding the index on the table. If possible, drop your index before writing to the database, and re-create it after loading the data. This can be done by using an SQLExecutor to drop the index; a FeatureWriter to write the data; and, an SQLExecutor to re-create the index. Most database Writers also have the ability to execute SQL statement before or after writing which can be applied to attributes or spatial indices.

Spatial Data

Both PostGIS and SQL Server can store either Geometry or Geography. Ensure that when you are writing to either of these formats, the Spatial Column Type is set correctly. Geometry is used for planar coordinates such as UTM while Geography is used to ellipsoidal data such as lat/long. A mismatch between what is stored in the database and what is set on the Feature Type can cause errors.

Oracle does not require that Geometry or Geography is set, but if you are writing to an existing spatial table, check that the SRID in the metadata and on your Feature Type match.

Ïf writing to a table with multiple spatial columns in Oracle or SQL Server, set “Handle Multiple Spatial Columns” in the parameters in the Navigator to ‘Yes’. FME is capable of writing different geometries (i.e.- points and polygons) with different SRID values or spatial column types (geometry, geography) to the same table. See Writing to Database Tables that contain Multiple Geometry Columns  for more information.