Join Fields in Smallworld

Introduction

Welcome to the FME for Smallworld 5 tutorial. This article describes how to work with Smallworld 5 join fields in FME. FME can read different types of join fields. FME can also read intermediate join tables, for example, electricity.int!supply_point_customer. So when exporting from Smallworld you can reconstruct more complex joins.

Some fields are not always made visible in the Smallworld GIS, and they must be visible for FME to be able to read the fields. Join fields are very often not visible. Use the Smallworld ACE application to make the fields you need to access visible to FME.

The FME Smallworld 5 writer can import join fields for text-based joins fields and foreign key fields. Other types of join fields are not supported, refer to the GE Smallworld documentation: the Smallworld FME Translator Documentation - Translating fields of Smallworld RWOs - Join Fields (you need to obtain the login from GE SupportCentral). You may have to make some fields visible using the Administration ACE application so that the FME writer can access them.

FME can load simple joins. There are three types of joins that can be represented:

• Simple parent–child, 1:0..n relationship, i.e. Town - Roundabout
• Heterogeneous relationships. These are parent–child 1:0..n relationships, where there can be different parent objects. i.e. Office or Point of Interest may join to a Road Works object.
• Multiple child object joins. These parent–child relationships have one parent but multiple child objects.

Step-by-step Instructions

Viewing Intermediate Join Tables

You’ll use FME Data Inspector to view more complex join tables. Let’s look at the following tables and their related join tables:

The image above comes from the Smallworld CASE for the Electricity Network. There is a join table for Customer and Supply Point.
 

1. FME Data Inspector

Start FME Data

Select File > Open Dataset.

Enter the reader format parameters

2. Enter Database Connection Parameters

Click on the Parameters button and enter the Smallworld database connection parameters:

3. Select Constraints

Still in the Parameters dialog, check Expose Internal Tables.

Click on the Table List browse button.

Select each of these feature types:

• electricity.cable
• electricity.joint
• electricity.substation
• electricity.supply_point
• electricity.int!supply_point_customer

The int! objects are the intermediate join tables.

Click OK to close the Select Feature Types dialog.

Uncheck Use Search Envelope.
 

4. Inspect Results

In the Data Inspector Table View, you should see all of the selected objects. The electricity.int!supply_point_customer is the join table for Customers & Supply Points.

You can use the same steps in Workbench to create a workspace that reads and preserves the relationships between the Customer and Supply Point objects through the int! tables, to load into, say, an Esri Geodatabase relationship feature class.

Writing Simple Joins

In this example, you’ll load some new features for Company and Office and build the relationship. This is a 1:n relationship with Company as the parent and Office as the child table.

As mentioned above, to export join fields, you may have to make the fields visible in the Administration ACE application. In the following example, the Company object has had the ‘id’ field made visible and the Office object has had the office_id and company_id fields made visible. These fields are not exposed in the default Cambridge database.

To do this in Smallworld: you would use the Administration application to open the ‘ACE Configuration’ tool. Use the Edit – Object Properties menu to change the appropriate properties, including the field visibility.

First, we’ll add a new Company record to the Smallworld database:

1. Open Workspace

Start FME Workbench and open the workspace: smallworld9a-complete.fmwt. The workspace reads company information from a MapInfo MIF file and writes to the office object in the '|fme_offices' alternative.

2. Run Workspace

Run the workspace. It will add a new record into the Company object in the |fme_offices alternative.

There is no geometry on the Company object so check the results in the Smallworld Explorer (Tools menu -> Explorer…). Navigate to Gis > Non Topological Objects > Company and browse all features – don’t forget to switch to the GIS version |fme_offices

You can see there are no offices linked to the "Safe Software Inc." company.

3. Open Workspace

Open the second workspace for this exercise: smallworld9b-complete.fmwt

This is a little more interesting. This workspace adds a new Office record and makes the join to the Company object.

To make the join between the Office and the Company, you have to add the company primary key to the Office object – as the foreign key. The foreign key on the Office table is ‘company_id’ which must match the primary key on the Company table which is ‘id’.

In the workspace:

The transformers in the Bookmark “Office location/annotation geometries” creates the Office feature with location and annotation geometries, in the same way as the previous exercises.

Smallworld 5 reader: This workspace uses the Smallworld reader to read back the Company records. You could also add a predicate to the reader to limit which Company records are read back.

FeatureMerger: Join the Office object to the related Company. We can make the join using the company name. The office object now has the Company ‘id’ which can be used as the foreign key.

AttributeCreator: Rename the ‘id’ field (which is the Company primary key) to ‘company_id’ which is the foreign key on the Office child object.

4. Run Workspace

Run the workspace. It will add a new record into the Office table in the |fme_offices alternative

Check the results in the Smallworld Explorer – don’t forget to switch to the GIS version |fme_offices.

For the Company table, you should see the original Safe Software Inc company, but it should now show the number of related offices:

As mentioned before, you may have to use the Administration ACE tool to make some of the join fields visible.

In summary, to build a simple join relationship you need to set the foreign key on the child table to match the primary key of the parent table.