Database Operations in Smallworld

Introduction

Welcome to the FME for Smallworld 5 tutorial. This article covers importing data into your Smallworld 5 database, including how to handle incremental updates to the Smallworld database.

Once information is stored in a database, its source is unlikely to stay static: Changes will occur. This article discusses how you can use FME to undertake incremental updates to your Smallworld database. Keeping the database up-to-date based on changes in the real world. In FME Workbench, this is accomplished using the format attribute: fme_db_operation. A bit more detail on using fme_db_operation can be found in the article: Updating Databases.

You have used the ‘fme_db_operation’ format attribute in the previous article. FME uses the ‘fme_db_operation’ attribute on the Smallworld writer to specify the database operation INSERT, UPDATE, or DELETE for each feature.

When the operation is UPDATE or DELETE, then you must also supply the join key attribute that matches the Smallworld ‘sys_id’ on the writer feature type. FME will automatically identify the record to write using this join key attribute.

When the operation is INSERT, the join key attribute should not be included on the feature. For inserted records, Smallworld automatically creates an internal ‘sys_id’, and the insert operation may fail if the writer attempts to supply a join key.

Step-by-step Instructions

Update Electric Network

In the previous article, electrical supply points and cables were proposed for new customers. These proposed changes have now be Accepted. Some new connections are now required and also some existing connections need to be updated or removed. This example will update the Smallworld database with these changes to the electrical system.

In this example, you’ll use a GML file that has been created by a change management system. Changes are tracked by the update_type attribute as either D, U, I (delete, update or insert).
 

1. Inspect Source Dataset

Inspect the source dataset at Cambridge_Electricity_Updates.gml

Some features have no geometry. All features have an update_type attribute that indicates the type of database operation to perform: I (Insert), U (Update) & D (Delete).

2. Add Smallworld Alternative

In the Smallworld Version Management application, select the alternative fme_updates or add this new alternative if it doesn’t already exist:

3. Generate Workspace

Start FME Workbench. On the Start tab, choose the option to Generate Workspace.

Place a check mark on the Import feature type definitions checkbox.

Be sure to use the | symbol as the first character for Alternative

Click OK to generate the workspace.

4. Specify Feature Types

When the Import Writer Feature Types dialog appears, click on the Parameters button.

Set these parameters:

Service: FMENOFACTORY

Table List:

• electricity.cable
• electricity.customer
• electricity.supply_point

When the GML Select Feature Types dialog appears, select these feature types:

• electricity_cable
• electricity_customer
• electricity_supply_point.

5. Tidy Workspace

The feature types on the reader may be in a different order than on the writer. To make the feature type names easily visible, you can select the feature types and resize them, or add an annotation can be attached to each feature type.

To add an annotations, select all the feature types. Right-click and choose Attach Annotation.

Tidy the workspace so that each reader feature type is horizontally aligned with its matching writer feature type; but don’t connect just them yet.

6. Set Database Operation

Add an AttributeValueMapper transformer connected to a reader feature type. The update_type attribute identifies which type of operation applies to each feature.

Right-click on the transformer and use Duplicate to make two copies of the transformer and connect the duplicates to the remaining feature types as shown below:

7. Set Geometry Name: centerline & location

In previous sections you set the geometry name for Cables and Supply Points. Connect a GeometrPropertySetter transformer to the AttributeValueMapper for supply points and cables and set the geometry names to 'location' and 'centreline', respectively.

Why don’t you need a GeometryPropertySetter for the 'customer' feature type?

Note: The FME Smallworld 5 writer handles Smallworld geometries in a completely different way to Smallworld 4. The Smallworld 4 versions of the workspaces are all attached at the bottom of this article for reference. Geometry types are no longer exposed as attributes and no longer use the sworld_geometry{} list attributes. Old workspaces are still compatible in Smallworld 5.

8. Connect to the Writer Feature Type

Connect the AttributeValueMapper or AttributeCreator output ports to its associated writer feature type.

Your workspace will look something like the one below: smallworldex6-complete.fmwt

How does FME make the join to the Smallworld objects for UPDATE and DELETE? FME uses the sys_id (or the equivalent) to make the join to the Smallworld object for the update and delete. In this example this is the id attribute (cable & supply_point) or customer_id (customers).

9. Inspect Output

Click on Writers > Redirect to FME Data Inspector to turn off the Smallworld writer and output to Inspector instead.

Save the workspace and then run it.

Inspect the output in the FME Data Inspector. Use the inspection tool to select cables and supply points and then verify their attributes. Note that for the INSERT’s the id (or customer_id for customers) is missing. This is because it’s a new feature and does not yet have a ‘sys_id” created yet.

Here’s what’s going to happen:

10. Write the Output

In FME Workbench, click on Writers > Redirect to FME Data Inspector to direct the output to the Smallworld writer.

Pause: are you writing to the correct Smallworld alternative?

Run the workspace.

Open the "Smallworld Professional (core) - Smallworld Core" application.

Use the “Electric Network” bookmark to zoom in to see the area of interest around at Oak Tree Avenue.

Inspect the output in Smallworld. In the Smallworld Version Manager double click "fme_updates" to make it the current alternative. You may need to refresh the GIS view.

Locate the inserted cables and supply_points.

Compare the fme_updates alternative to the top to detect the records that were deleted.

Use the object Explorer to locate the "customer" records that were inserted, since without geometry they won’t appear in the view.

There is a relationship between the Supply Points and the Customer table that uses an intermediate join table. The Smallworld 5 writer cannot create joins through an intermediate join table, so the supply_points have not been joined to their customers. You would have to accomplish this in the Smallworld GIS Object Editor.

FME can be used to import objects that have a direct join (parent/child) relationships. These can be a simple 1:0..n simple joins, 1:0..n heterogeneous joins or 1:0..n multiple joins. Handling joins is described in the Smallworld FME Translator Documentation - Translating fields of Smallworld RWOs - Join Fields (you need to obtain the login from GE SupportCentral). You're going to work with join fields in a later article.

Advanced Task

The supply points that were inserted have labels, with the label location derived from the geometry of the supply point location.

In the Smallworld Version Management dialog double click on the "begin" checkpoint to rollback to the original data.

Back in FME Workbench, modify the workspace to add a label geometry to the new supply points. Hint: use an Offsetter transformer. The changes to your workspace will be similar to the previous section where you added labels to the supply points.

Run the workspace and in Smallworld check that the re-inserted supply points now have new label locations.

Smallworld 4 Examples

There are differences in the way that FME creates the geometry features for the Smallworld 5 writer, compared to Smallworld 4. It's quite a lot easier in Smallworld 5. The example below shows how to accomplish this for Smallworld 4. The Smallworld 5 writer still supports this old approach, so if you have existing workspaces, they will be compatible with Smallworld 5 and should run without any need to change them.