Files
Introduction
This tutorial will show you how to quickly translate an Oracle Spatial database into an Esri Shapefile (*.shp) format. We’ll see the steps used to connect to the database and how a WHERE clause can extract data for the output shapefile.
Requirements
- Read/Write privileges access to an Oracle Database
- Oracle Instant Client is installed on the same machine as FME Form.
- For more information about using Oracle with FME, please see Tutorial: Getting Started with Oracle
- Complete exercise in Converting to Oracle: Create, Drop or Truncate a Table article, if you want to follow along with the tutorial exactly.
Video
Step-By-Step Instructions
1. Access to Oracle Spatial Dataset
- If you already have a test dataset available, you can use that.
- If you want to follow along with the tutorial exactly, please first follow Converting to Oracle: Create, Drop or Truncate a Table to upload the DOWNTOWN_PUBLICART dataset into the Oracle database.
2. Add Oracle Spatial Object Reader
Start FME Workbench, and select New Workspace.
First, we need to add a source dataset. Click on the green plus button on the canvas. In the Add Reader dialog, enter the following:
- Format: Oracle Spatial Object
- Connection: <your database connection>
For information on how to connect to your own Oracle database instance, please see Viewing and Inspecting Oracle Data.
When the connection is established, click on the Parameters button. In the Oracle Spatial Object Parameters dialog:
- Click on the ellipsis next to Table List.
- Locate and select DOWNTOWN_PUBLICART in the Table List dialog
- Click OK to select the table.
- Click OK again to add the reader.
3. Inspect Data
Run the workspace with Feature Caching enabled. By doing this, it will cache the data from the database, so we don’t need to access the database every time we make a change to the data. This is good practice if you have multiple people using the same database at the same time, or you have a large dataset to query.
Click the green play button to run the workspace. When finished running, view the data by:
- Click “View Source Data”
- Select “Visual Preview”
- Toggle the icons to show/hide Display Control, Table, Graphics, and Features Information data
4. Create WHERE Clause
Typically, when we want to filter out data, we would use a Tester transformer, but since we are connecting to a database, we can use a WHERE clause to filter the data. Using a WHERE clause, we can filter the data when we read in the data, preventing large amounts of data from going through the workspace when we only need a tiny subsection.
To create the WHERE clause, open up the DOWNTOWN_PUBLICART reader parameters. Then, for the WHERE Clause, type in: TITLE like '%Untitled%'
To break this WHERE clause down, we are looking for the word 'Untitled' contained within the attribute 'TITLE'. The % symbols mean that Untitled can have any text on either side of it, and the WHERE clause will still find it. Once the WHERE clause is set, click OK.
Optional: If you want to test out the WHERE clause, rerun the translation and inspect the data.
5. Add an Esri Shapefile Writer
Click on the Add Writer, and in the Add Writer dialog box, set:
- Format: Esri Shapefile
- Dataset: <Output path>
- Coordinate System: LL84
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.
Now open the writer parameters, change the Shapefile Name to Untitled_PublicArt, and click OK.
6. Connect Writer and Run the Workspace
Connect the Untitled_PublicArt writer feature type to the DOWNTOWN_PUBLICART reader feature type.
Once connected, the workspace is ready to be run. To do so, click on the green play button.
7. View the Output Dataset
To view the output dataset, click on the Untitled_PublicArt, then the View Source Data button.
In Visual Preview, you should have 20 features that contain Untitled in their TITLE attribute.
Data Attribution
The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.