Using the SQLExecutor to do a SQL Join

Introduction

In this example, the SQLExecutor transformer will be used to join records from a database table that relate to source features from a GML file. We have a dataset of Fire Halls in GML format, and a database table called PostalAddress. Both datasets have a common address field.

FME will be used to read in the Fire Halls, then select matching records in the PostalAddress table by using a SQLExecutor to do a database join based on the common address field.

Note: Though this example focuses on the PostGIS format, it is also applicable to other relational database formats such as Oracle and SQL Server.

Step-by-step Instructions

The download sqlexecutor-join-201.fmwt is the complete workspace. If you would like to create the workspace yourself, please download sourcegmldata.zip, and follow the steps below to create it.

1. Inspect Source Data

FireHalls GML

Add a GML Reader and set it to read the FireHalls.gml. Use the Data Inspector to inspect FireHalls.gml. Use the Table View window to view the text records. Notice that each Fire Hall facility has an Address field. This field will be used to match to the PostalAddress table's PostAddress field.

PostalAddress Table

Inspect the source PostalAddress table in Data Inspector by using the foll

owing parameters after setting the Dataset to Embed Connection Parameters:

• Host: postgis.train.safe.com
• Port: 5432
• Database: fmedata
• Username: fmedata
• Password: fmedata
• Set Schemas for Table Listing to public, then pick the table PostalAddress.

The PostalAddress field will be used to match the Fire Hall GML's Address field.

2. Set Up the SQLExecutor

The SQLExecutor will be used to match the address record from the PostalAddress table in the database for each GML Fire Hall facility.

The following SQL Statement will be used to join the GML record to the database record:

select * from public."PostalAddress" where "PostalAddress" ='@Value(Address)' 

The SQL statement can be created most easily using the SQL Editor tool. Be sure to include the quote characters around the final @Value() part!

Use the Atributes to Expose parameter to make any attributes added by the SQL join visible in FME Workbench, if you want.

3. Copy Attribute Values

An AttributeManager transformer may be used to copy or rename attributes. For example, rename the Name attribute from the Fire Halls GML feature to OwnerName.

4. Examine the Output In FME Data Inspector

SQLExecutor results in Data Inspector.

5. Edit the SQL Statement

The SQLExecutor has merged the data from an SQL SELECT statement onto the Firehall features. You can also undertake an SQL join with the SQL statement.

The Firehall data is missing the CivicNo attribute. This is in the AddressPoints table. Use the AddressID to join the CivicNo attribute onto the Filehall records. You could either use a second SQLExecutor, or use a SQL join. Something like:

SELECT a.* , b."CivicNo"  FROM  public."PostalAddress" a, "public"."AddressPoints" b WHERE "a"."PostalAddress" = '@Value(Address)'  AND a."AddressId" = b."AddressId";

Whenever possible "let the database do the work"! Do as much as you can in a single SQL call.