Reading and Writing to Amazon Aurora Database

Introduction

FME supports writing to AWS Aurora, a fully managed relational database engine that's compatible with MySQL and PostgreSQL.

In this article series, you will learn how to write some spatial data (OpenStreetMap) into Aurora (MySQL) and read that spatial data back into a workspace.

The OSM data use in this example was obtained from Geofabrik Download server for British Columbia

The Admin Boundary dataset if from the Metro Vancouver Open Data Catalogue and is available as the Municipal Boundaries dataset.

Writing to Aurora

In this scenario, we are going to read in OpenStreetMap data for sport and leisure in the whole of BC, Canada and then filter out features that fall within the administrative boundaries for Metro Vancouver, and assign the municipality name to the feature.

1. Add OSM PBF Reader

Open FME Workbench, and add an OSM PBF reader to the workspace. Select the british-columbia-latest.osm.pbf dataset. Click on the Parameters button, and under Map Features select only leisure and sport. Click OK, until the two feature types are added to the canvas.

2. Add a GeometryFilter

For this example, we will load points and areas into Aurora in separate tables. Add a GeometryFilter transformer to the workspace and connect both feature types to it. Inside the GeometryFilter, choose Point and Area as the geometry types to filter.

3. Add PointOnAreaOverlayer and AreaOnAreaOverlayer

Next, add two transformers to the workspace, a PointOnAreaOverlayer and an AreaOnAreaOverlayer. These are the transformers that we’ll use to do a spatial join with the municipality names.
Connect the Point output from the GeometryFilter to the Point input on the PointOnAreaOverlayer. Connect the Area output from the GeometryFilter to the Area input on the AreaOnAreaOverlayer.

4. Read the AdminBoundary Shapefile

Now we need a dataset that includes the administrative boundaries for Metro Vancouver. Add another reader, this time for the Shapefile format, and select the AdminBoundary.shp file.

To perform a spatial transformation on both datasets together, we need to ensure that they both are in the same coordinate system. The AdminBoundary shapefile is in UTM83, so we will use a Reprojector transformer to convert it to WGS84.

Connect the output from the Reprojector to the Area input port on both Overlayer transformers.

Before we run the workspace to see what our data looks like so far, set both Overlayers to ‘Merge Incoming Attributes’.

Then we can run the workspace up until this point and inspect the data by clicking on the green magnifying glass icon on the output port.

5. Inspect the Data

Look at the data in the Visual Preview window - you’ll see some of the data features have an _overlaps value of more than one, and they will also have values in the FullName and ShortName columns. These are the data features we want, so we will add a Tester transformer to filter them out.

Inside the Tester, add test clauses so that there is either a ShortName or FullName, and either of the leisure, sport or name attributes have a value. Your Tester should like something like this, with a composite expression:

6. Add an Amazon Aurora Spatial Writer

Finally, add the Aurora writer. Add a writer, set the format to Amazon Aurora Spatial (MySQL compatible). You can add a new database connection if you will be connecting to this database again, or embed the connection details. You will need the hostname, port, database, username and password. Once you’ve enter the connection details, set the table definition to Automatic, and press OK.

Rename one feature type to recreation_point, and then go to the User Attributes tab.
Flip the attribute definition to Manual, and set the attributes to FullName, ShortName, sport, leisure, name.
Duplicate the recreation_point feature type and rename the new feature type to recreation_area and connect it to the output of the Tester for the areas.

7. Run Workspace and View Output

Run the Workspace. Now, we can verify that the data has been loaded successfully by using FME with an Aurora reader, or another database client.

In this case, DBeaver is the database client:

Reading from Aurora

To read in the entire dataset from Aurora, and then apply transformations to the data, add an Aurora reader. You can use the same database connection created earlier in this article, or embed the connection details. Open the reader parameters and select the tables that you wrote into Aurora previously. 
If you select both recreation_area and recreation point tables, then two feature types will be added to the canvas. You can then connect any transformers to those readers to process your data.



If you'd like to read in a subset of the data in a table, based on a SQL query, you can do this with a SQLCreator transformer.

Add a SQLCreator to the canvas, and set the database format to Amazon Aurora Spatial. For the SQL statement, enter the following statement:

SELECT FullName, ShortName, sport, leisure, name, geom
FROM VancouverOSM.recreation_area
WHERE ShortName LIKE "Coquitlam" AND (leisure LIKE "park" OR leisure LIKE "nature_reserve");

This will read all of the parks and nature reserves in Coquitlam into the workspace.
Before pressing ok, add the attributes you wish to expose in the workspace by typing them in on each row. You can copy the attribute names from the SQL statement. FME will automatically