> For the complete documentation index, see [llms.txt](https://1spatial.gitbook.io/fme-form-beginner/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://1spatial.gitbook.io/fme-form-beginner/transformer-usage/data-joins/transformer-usage-exercise-3.md).

# Transformer Usage - Exercise 3

<table><thead><tr><th width="176">Exercise Details</th><th>Noise Control Laws Project (Data Joining)</th></tr></thead><tbody><tr><td><strong>Locality</strong></td><td>Vancouver, Canada</td></tr><tr><td><strong>Data</strong></td><td>Addresses (File Geodatabase)<br>Zoning (MapInfo TAB)<br>Roads (AutoCAD DWG)<br>Crime Statistics (CSV)</td></tr><tr><td><strong>Overall Goal</strong></td><td>Carry out a join between crime statistics and address features</td></tr><tr><td><strong>Demonstrates</strong></td><td>Attribute-Based Joins</td></tr><tr><td><strong>Start Workspace</strong></td><td>C:\FMETraining\Workspaces\Transformers-Ex3-Begin.fmw</td></tr><tr><td><strong>End Workspace</strong></td><td>C:\FMETraining\Workspaces\Transformers-Ex3-Complete.fmw</td></tr></tbody></table>

As you know, city councillors have voted to amend noise control laws and residents living in affected areas were informed of these changes.

In the first part of the project, you created a workspace to convert addresses from Geodatabase to CSV, mapping the schema at the same time. In the second part of the project, you continued the workspace to locate all single-family residences within 50 meters of a major highway and filter out all others from the stream of address data.

Now a data journalist with a national newspaper is concerned that the relaxation of noise control laws may lead to more crime in the city. They have therefore requested recent crime figures for each of the affected addresses. They intend to compare this against future data to see if their theory is correct.

This request is a significant test of the city's open data policy, and there's no question of not complying. However, a crisis arises as the current datasets for crime (CSV, table data) is not joined to the address database in any way.

So, for the final part of this project, you must take the existing noise control workspace and amend it to incorporate crime statistics. Pull this off, and you will be a data superhero!

***

{% stepper %}
{% step %}

## Start FME Workbench

Start FME Workbench (if necessary) and open the workspace from Exercise 2. Alternatively, you can open C:\FMETraining\Workspaces\Transformers-Ex3-Begin.fmw

<figure><img src="/files/uspQ91PCI2BNglnutgSD" alt=""><figcaption></figcaption></figure>
{% endstep %}

{% step %}

## Add a CSV Reader

Now let's start working with crime data. There is no benefit from using a FeatureReader, so add a reader to the workspace using Readers > Add Reader from the menu bar. Use the following parameters:

<table><thead><tr><th width="176">Parameter</th><th>Value</th></tr></thead><tbody><tr><td><strong>Reader Format</strong></td><td>Comma Separated Value (CSV)</td></tr><tr><td><strong>Reader Dataset</strong></td><td>C:\FMETraining\Data\Canada\Emergency\Crime\Crime.csv</td></tr><tr><td><strong>Reader Parameters</strong></td><td>Feature Type Name(s): From File Name(s)<br>Fields: Delimiter Character: , (Comma)<br>Fields: Field Names Line: 1</td></tr></tbody></table>

Click OK to add the reader.
{% endstep %}

{% step %}

## Inspect the Data

The next task is to familiarise yourself with the source data. Click on the Crime feature type to open the popup menu, then click on the View Source Data button.

<figure><img src="/files/m2IrCKW3A3LY1xLAtI2u" alt=""><figcaption></figcaption></figure>

The data will look like this in Visual Preview:

<figure><img src="/files/pxaEYj5ImFkzYMZoFMwx" alt=""><figcaption></figcaption></figure>

Notice how there is only data in the Table View, if you open the Graphics View it says that there is no geometry. We will need to use Block to spatially relate the crime data to our other data.

{% hint style="info" %}
**TIP**

*Since this is a crime dataset, the exact numbers are blocked out by Xs. Be aware that 7XX W Georgia Street means the seventh block on Georgia Street west of Ontario Street and covers building numbers 700-800. 7XX E Georgia Street would be 14 blocks away, the seventh block east of Ontario.*
{% endhint %}

You might have spotted that each address feature has a number (not a block ID like "7XX"), and that the road data is stored in Title case ("W Georgia St") in the roads dataset, whereas the crime dataset is upper case ("W GEORGIA ST").

Both of these will make it harder, but not impossible, to join the two sets of data together.
{% endstep %}

{% step %}

## Add a StringReplacer Transformer

To merge the data we need to reduce the address number to a block number that matches the crime data in structure; for example, we need 74XX instead of 7445.

So, add a StringReplacer transformer and connect it between the ListConcatenator and the PostalAddress feature type:

<figure><img src="/files/G2RNm3HufVpW6iAuPX9r" alt=""><figcaption></figcaption></figure>

Set the following parameters:

<table><thead><tr><th width="191">Parameter</th><th>Value</th></tr></thead><tbody><tr><td><strong>Attributes</strong></td><td>Number</td></tr><tr><td><strong>Mode</strong></td><td>Replace Regular Expression</td></tr><tr><td><strong>Text to Replace</strong></td><td>..$</td></tr><tr><td><strong>Replacement Text</strong></td><td>XX</td></tr></tbody></table>

The text to replace (two dots/periods and a dollar sign) means to replace the last two characters of the string, and they are replaced with XX to match the crime data.

<figure><img src="/files/088fXJrBpVfNecHVh6ol" alt=""><figcaption></figcaption></figure>

Run the workspace (using Run to This on the StringReplacer) and inspect the caches to ensure the transformer is working as expected. Each of the features in the Number column should have XX at the end.
{% endstep %}

{% step %}

## Add a StringCaseChanger Transformer

The other difference in crime/road data was in UPPER/Title case street names. This disparity can be fixed with a StringCaseChanger transformer.

Add a StringCaseChanger transformer after the StringReplacer and set the parameters to change the value of Street to upper case:

<figure><img src="/files/4ORRy9PyY4xT4qXmym2r" alt=""><figcaption></figcaption></figure>

Now address street names will correctly match street names in the crime dataset.
{% endstep %}

{% step %}

## Build Join Key

Having updated the attributes to match the crime data, we now have to construct a key out of them.

Add an AttributeCreator to the canvas after the StringCaseChanger. Create a new attribute called JoinKey. Open the Text Editor for the attribute and enter (select):

```
@Trim(@Value(Number) @Value(Street))
```

This will match the structure of the crime data (be sure to include a space character between the two attributes). The Trim function is there to ensure there are no excess spaces on those attributes.
{% endstep %}

{% step %}

## Add a FeatureJoiner Transformer

Now we've sorted out the structure of our join keys we can merge the data together with a FeatureJoiner.

Add a FeatureJoiner to the canvas.

Connect the address data (the AttributeCreator output) to the Left input port. Connect the crime data (the CSV reader feature type) to the Right input port.

Connect the FeatureJoiner:Joined output port to the PostalAddress writer feature type:

<figure><img src="/files/1adHxNXnMHoHrOtSHfhl" alt=""><figcaption></figcaption></figure>

Inspect the parameters for the FeatureJoiner.

For the Join Mode select Left. This means that we want all of the addresses to be output, whether they match to a crime record or not. In the Join On parameters select the JoinKey attribute for the Left value, and the Block attribute for the Right value.

<figure><img src="/files/nGlQkCV6sxauZkznZx28" alt=""><figcaption></figcaption></figure>

Run that part of the workspace to see what the results of this translation are.
{% endstep %}

{% step %}

## Add an Aggregator Transformer

Look at the FeatureJoiner to see the Joined feature counts. Interestingly, although 148 addresses enter the FeatureJoiner, 267 emerge from it:

<figure><img src="/files/saBjxAkUC6BrCqAuWwY4" alt=""><figcaption></figcaption></figure>

That's because there are multiple crimes per block and there were 267 matches with the data.

We can aggregate that data together using an Aggregator transformer. So place an Aggregator transformer after the FeatureJoiner:Joined port

Inspect the parameters. We need to set the group-by parameter by selecting attributes that will group matches back into the original addresses. There is no ID for each address because we removed them in a previous step, so return to the AttributeManager, undo the Remove option for OBJECTID by switching the Action to Do Nothing.

Then set the following in the aggregator:

* Group By as “OBJECTID” (after restoring it at the AttributeManager)
* Accumulation Mode as “User Attributes From On Feature” (We don’t want to drop attributes)
* Number of Aggregated Feature as “NumberCrimes” (This is where we set our attribute)

<figure><img src="/files/HG4h8pOu7V2l538pHH5d" alt=""><figcaption></figcaption></figure>
{% endstep %}

{% step %}

## Write Data

If you expand your attributes for the PostalAddress writer, you will notice that NumberCrimes doesn't appear.

To fix this simply add a new attribute called NumberCrimes here. Give it the type "uint16”. The data from the Aggregator will now have its attribute on the writer:

<figure><img src="/files/zeLPfCd7fujsBt0Zr3ab" alt=""><figcaption></figcaption></figure>

Close the dialog, turn off feature caching, and rerun the entire workspace. View the written data in Visual Preview. The data will include the number of crimes.
{% endstep %}
{% endstepper %}

***

{% hint style="success" %}
**CONGRATULATIONS**

By completing this exercise, you have learned how to:

* Pre-process data to get join keys with a matching structure
* Build a join key for use in a FeatureJoiner
* Join non-spatial data with a join key in the FeatureJoiner
* Use an Aggregator transformer to merge joins and count the number of joins
  {% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://1spatial.gitbook.io/fme-form-beginner/transformer-usage/data-joins/transformer-usage-exercise-3.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.