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.

The completed workspace for Smallworld 5 is attached: Smallworld6-complete.fmw

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 been 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:

To make it easier to keep track of where we are starting in case we need to rollback, we will create a new checkpoint here by going to File > New > Checkpoint and give the checkpoint name "begin" 

You also want to ensure that this alternative is writable 

3. Generate Workspace

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

As of FME 2025.2, the Coordinate System parameter is now configured within the Parameters dialog of each reader/writer format. For more information, including details about the change and affected transformers, please see Coordinate System Parameter Location Change.

• Reader: 
  • Format: OGC GML (Geography Markup Language)
  • Dataset: Cambridge_Electricity_Updates.gml
  • Parameters:
    • Group Entities By: Attribute Schema
    • Expand Blocks into Entities: Disabled
    • Coordinate System: OSGB-GPS-2015-OSTN15
• Writer:
  • Format: Smallworld 4/5
  • Server Host: localhost
  • Dataset: localhost:30000
  • Service: FME
  • Alternative: |fme_updates
  • Coordinate System: OSGB-GPS-2015-OSTN15

Enable 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
• Coordinate System: OSGB-GPS-2015-OSTN15

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 that can be attached to each feature type.

To add an annotation, 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 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.

• Input Attribute: update_type
• Output Attribute: fme_db_operation
• Value Map:
  • Value 1:
    • Input Value: I
    • Output Value: INSERT
  • Value 2:
    • Input Value: U
    • Output Value: UPDATE
  • Value 3:
    • Input Value: D
    • Output Value: DELETE

Right-click on the transformer and select 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 label for Cables and Supply Points. Connect a GeometryPropertySetter transformer to the AttributeValueMapper for supply points and cables. Set the geometry labels to 'location' and 'centreline', respectively.

Supply Point: 

• Property to Set: Geometry Label
• Geometry Label: location

Cable: 

• Property to Set: Geometry Label
• Geometry Label: centreline

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: Smallworld6-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 Workspace Parameters > Order and Redirect > Writer Redirect > Redirect to Data Inspector to turn off the Smallworld writer and output to Data Preview 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, 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 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 inserted supply points have labels, with the label location derived from the geometry of the supply point's 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.