How to Convert Oracle Spatial to Shapefile

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 be used to extract data for the output shapefile. We’ll also see how to map attribute values using a transformer, and how to change geometry_type.

Requirements

• Access to an Oracle Database
• Oracle Instant Client
• FME Desktop Database Level Licensing or higher
• Converting to Oracle: Create, Drop or Truncate a Table article completed

To complete this tutorial, you will need to have read/write privileges for an Oracle database. Additionally, you will need to have the Oracle Instant Client installed on the same machine as FME Desktop. For more information about using Oracle with FME, please see Tutorial: Getting Started with Oracle article.

Video

Note that this video was recorded using FME 2016 and uses different data. Even though the data is different, the concepts are still the same.

Step-By-Step Instructions

1. Complete How to Convert Microsoft Excel to Oracle article

This tutorial relies on data that is available in the Oracle database. 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 Oracle. Once the data is uploaded, return to this tutorial.

2. Add Oracle Spatial Object Reader

Open a blank FME Workspace and add a new reader. In the reader dialog, type in Oracle Spatial Object. If the format does not appear, it means that Oracle Instant Client isn’t installed or FME doesn’t can’t find the installation. To troubleshoot this issue, see the Why are my Oracle Formats Greyed Out in the Formats Gallery article.

Once the Oracle Spatial Object format has been selected, click the drop-down next to Connection and select the Oracle Database connection that contains the Downtown_PublicArt table. If the connection isn’t already set up, use the Embed Connection option and set up your connection details provided by your database administrator.

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. Then in the Select Table List dialog, find the DOWNTOWN_PUBLICART table and click OK. Note that your user will be specific to your database. Click OK two more times to add the reader.

3. Inspect Data

Now let’s see what our data looks like. In FME 2018 and newer, 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.

When the workspace is finished running, there will be a green cached feature icon, click on that to view your data in either Visual Preview.

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. By using a WHERE clause, we can filter the data when we read in the data, this prevents large amounts of data 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 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 there 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. Set the Output (Writer) Format to Esri Shapefile

Next, we need to add a writer to the canvas. Click on the Add Writer and in the Add Writer dialog box, for the Format, select Esri Shapefile.

Then for Dataset, browse to a folder to save the shapefile. We will need to add a coordinate system. In the Coord. System box enter LL84. For the Shapefile Definition, leave it set to Copy from Reader. Click OK.

Now open the writer parameters and change the Shapefile Name to Untitled_PublicArt and change the Geometry to shapefile_point. 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 now ready to be run. Run the workspace by clicking on the green play button.

7. View the Output Dataset

To view the output dataset, click on the Untitled_PublicArt writer feature type to open the popup menu, then click on the View Written 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.