Using Snowflake Spatial in FME for Geospatial Data

Introduction

FME versions 2020.1 and above support reading and writing geospatial data to Snowflake using the Snowflake Spatial Reader and Writer. Currently, the geography data type supports points, lines and polygons. The data can be stored in WKB, WKT, and GeoJSON. Learn more about the geography data type through Snowflake’s documentation. 

Read more about how to use FME and the Snowflake Database format.
 

Snowflake Coordinate System

The geography data type is stored in WGS 84 coordinate system, also known as EPSG 4326. So that’s easy enough for FME to take any of your geospatial data and transform it into this coordinate system. When stored in this coordinate system in Snowflake you can make use of the built-in geospatial functions of the database.
 

Reading Geospatial Data

As you know, FME makes it pretty easy to get data out of a database. No exception here. Simply create a database connection and select the geospatial tables you want to read into FME. Refer to our documentation to learn more about the Snowflake Spatial Reader. 
 

Writing Geospatial Data

Much like reading from a database, FME makes it just as easy to write your data into the Snowflake database. Refer to our documentation to learn more about the Snowflake Spatial Writer. 

When working with geospatial data you’ll want to ensure you are storing the data in a coordinate system that is common across your features. In this case, for Snowflake Database, that will be the Geography Coordinate system WGS 84. So ensure when writing geospatial data to Snowflake that the coordinate system is set on the Writer to LL-WGS84. Here’s a video that shows writing geospatial data to the Snowflake Database.
 

Video

Interacting with Snowflake's Geospatial Functions

As we can all appreciate - the Snowflake Database is meant for lots and lots of data. So you may want to brush up on ways to interact with this data inside the database. What I’m getting at here is the recommendation of “Let the Database do the work”. Instead of pulling all the data into FME and running geospatial functions on the data within FME, always consider what you might be able to do with the innate geospatial functions of the database. If you know you are going to be working with the Snowflake Database and Geospatial Data why not get familiar with what’s supported. Check out the Snowflake Documentation.

Inside of FME, you can access the Snowflake Geospatial Functions in a variety of ways. Using the SQLCreator and the SQLExecuter, on the Reader parameter Where Clause, and another way is through the FeatureReader where you can also set up a Where Clause to use a geospatial function. See some examples below.
 

SQLCreator or SQLExecutor 

And yes… this will return the geometry of the feature if it exists.
 

Reader Where Clause

The Where Clause parameter on the Reader can be made into a Published Parameter. This makes it more useful and allows a user to enter the latitude, longitude and buffer distance as seen in this example below.
 

Here I have made use of the other published parameters that will prompt the user for information and used this to build the Where Clause (jdbc_where_clause) and made it private so it isn’t prompting the user at runtime.
 

FeatureReader Where Clause

Here’s what the FeatureReader might look like. In this example I have 3 published parameters, looking for the longitude, latitude, and distance to buffer in metres. This allows me to build the where clause that I feed into the FeatureReader. The cool part of this is that it will apply to all the features of the FeatureReader.
 

The AttributeCreator ends up looking something like this:
 

And I pass the where clause (MyWhere) into the FeatureReader.