Data Analytics

Loading geospatial data into BigQuery just got easier with FME

With all of the geographical data available today, we made sure that Google Cloud’s BigQuery data warehouse includes first-class support for geospatial data types and functions. With this unique capability, you can process and analyze geospatial data at scale. To accelerate the workflows of our geospatial customers, we’re announcing our partnership with Safe Software, the maker of FME.

FME is a data integration platform designed to support spatial data worldwide, and 2020.0 brings the ability to ingest data from more than 450 geo formats and applications and materialize them as BigQuery tables.

FME is ideal to use when you need to ingest and transform one of the myriad geospatial file and data formats and land that data in BigQuery. FME is designed to help you overcome common data integration challenges. Using a visual interface, you can build workflows to extract, transform, load, integrate, validate, and share data. Plus, you can build event-based workflows to automate your data integration tasks, create notification services, and take advantage of real-time processing.

Using FME to connect to BigQuery GIS

There are hundreds of GIS file types and projections. Loading them into a data warehouse requires transforming the data type and its projection into the native projection of the data warehouse. In this case, BigQuery GIS uses the WGS84 coordinate system. Workflows are scalable: When you build a data integration workflow in FME, you can ingest a single file or hundreds at a time, transform them, and load them directly into BigQuery tables, all within FME. 

Here’s a look at the FME Workbench interface, the authoring environment for data integration workflows.

FME Workbench interface.jpg

FME supports hundreds of formats, applications, and systems, and includes 493 transformers to help data manipulation tasks like geometry and geography creation, validation, generalization, and coordinate system reprojections. With data coming from different sources, data validation and quality control is a critical step in your workflow. The transformers are accessible through the GUI and let you create consistent and repeatable spatial data pipelines. That way you can make sure the data migrated to data warehouses like Google BigQuery is valid and meets all requirements. 

Using geospatial data in production

We’ve heard from customers using FME and BigQuery that they can move and transform data more quickly to focus on innovation.

"We've been using FME to transform shp, GeoJson, CAD and various other data types for several years,” says Adam Radel, IT director for GISP, State of Utah Department of Transportation. “We've imported over 1,000 files to BigQuery using FME in just a few weeks. The addition of the BQ writer to FME is game-changing for us. I'm excited that my team has such a powerful tool available to them."  

How to load geospatial data from FME to BigQuery

Check out this detailed look at how the loading process works. The example starts with the assumption that the user has data to load, a shape file for an example, and a licensed or trial version of FME Desktop installed on a virtual machine or on their local machine. (Note: FME has example files for loading.)

If you don’t have FME running already, get started with a trial straight from the Google Cloud Marketplace, and check out these instructions on how to deploy FME in Google Cloud.

To use the data that you load with FME, you can also check out examples of how to query data in BigQuery GIS, like plotting hurricane paths in BQ GIS or k-means clustering of spatial with BQML. We modeled the BQ GIS syntax on PostGIS, so you’ll find the queries easy to compose as well. And click on Explore in GeoViz from the user interface to get a quick styleable visualization of your results. For a more scalable, cloud-native GIS visualization solution that can use BigQuery as a scalable spatial backend, take a look at our partner CARTO.