February 07, 2022

Scale Your Spatial Analysis By Building It In SQL With Syntax Extensions

59 minutes

Summary

Along with globalization of our societies comes the need to analyze the geospatial and geotemporal data that is needed to manage the growth in commerce, communications, and other activities. In order to make geospatial analytics more maintainable and scalable there has been an increase in the number of database engines that provide extensions to their SQL syntax that supports manipulation of spatial data. In this episode Matthew Forrest shares his experiences of working in the domain of geospatial analytics and the application of SQL dialects to his analysis.

Announcements

Hello and welcome to the Data Engineering Podcast, the show about modern data management

When you’re ready to build your next pipeline, or want to test out the projects you hear about on the show, you’ll need somewhere to deploy it, so check out our friends at Linode. With their managed Kubernetes platform it’s now even easier to deploy and scale your workflows, or try out the latest Helm charts from tools like Pulsar and Pachyderm. With simple pricing, fast networking, object storage, and worldwide data centers, you’ve got everything you need to run a bulletproof data platform. Go to dataengineeringpodcast.com/linode today and get a $100 credit to try out a Kubernetes cluster of your own. And don’t forget to thank them for their continued support of this show!

Atlan is a collaborative workspace for data-driven teams, like Github for engineering or Figma for design teams. By acting as a virtual hub for data assets ranging from tables and dashboards to SQL snippets & code, Atlan enables teams to create a single source of truth for all their data assets, and collaborate across the modern data stack through deep integrations with tools like Snowflake, Slack, Looker and more. Go to dataengineeringpodcast.com/atlan today and sign up for a free trial. If you’re a data engineering podcast listener, you get credits worth $3000 on an annual subscription

StreamSets DataOps Platform is the world’s first single platform for building smart data pipelines across hybrid and multi-cloud architectures. Build, run, monitor and manage data pipelines confidently with an end-to-end data integration platform that’s built for constant change. Amp up your productivity with an easy-to-navigate interface and 100s of pre-built connectors. And, get pipelines and new hires up and running quickly with powerful, reusable components that work across batch and streaming. Once you’re up and running, your smart data pipelines are resilient to data drift. Those ongoing and unexpected changes in schema, semantics, and infrastructure. Finally, one single pane of glass for operating and monitoring all your data pipelines. The full transparency and control you desire for your data operations. Get started building pipelines in minutes for free at dataengineeringpodcast.com/streamsets. The first 10 listeners of the podcast that subscribe to StreamSets’ Professional Tier, receive 2 months free after their first month.

Your host is Tobias Macey and today I’m interviewing Matthew Forrest about doing spatial analysis in SQL

Interview

Introduction

How did you get involved in the area of data management?

Can you describe what spatial SQL is and some of the use cases that it is relevant for?

compatibility with/comparison to syntax from PostGIS

What is involved in implementation of spatial logic in database engines

mapping geospatial concepts into declarative syntax

foundational data types

data modeling

workflow for analyzing spatial data sets outside of database engines

translating from e.g. geopandas to SQL

level of support in database engines for spatial data types

What are the most interesting, innovative, or unexpected ways that you have seen spatial SQL used?

What are the most interesting, unexpected, or challenging lessons that you have learned while working with spatial SQL?

When is SQL the wrong choice for spatial analysis?

What do you have planned for the future of spatial analytics support in SQL for the Carto platform?

Contact Info

Website

@mbforr on Twitter

Parting Question

From your perspective, what is the biggest gap in the tooling or technology for data management today?

Closing Announcements

Thank you for listening! Don’t forget to check out our other show, Podcast.__init__ to learn about the Python language, its community, and the innovative ways it is being used.

Visit the site to subscribe to the show, sign up for the mailing list, and read the show notes.

If you’ve learned something or tried out a project from the show then tell us about it! Email [email protected]) with your story.

To help other people find the show please leave a review on iTunes and tell your friends and co-workers

Links

Carto

Spatial SQL Blog Post

Spatial Analysis

PostGIS

QGIS

KML

Shapefile

GeoJSON

Paul Ramsey’s Blog

Norwegian SOSI

GDAL

Google Cloud Dataflow

GeoBEAM

Carto Data Observatory

WGS84 Projection

EPSG Code

PySAL

GeoMesa

Uber H3 Spatial Indexing

PGRouting

Spatialite

The intro and outro music is from The Hug by The Freak Fandango Orchestra / CC BY-SA

Support Data Engineering Podcast

...more

View all episodes

By Tobias Macey

4.6

135135 ratings