Data Engineering Podcast Scale Your Spatial Analysis By Building It In SQL With Syntax Extensions
Feb 7, 2022
59:54
Normalize Formats With GDAL
- Use GDAL/OGR (the GDAL library) to normalize and transform incoming spatial file formats and projections.
- Automate reprojection and vector/raster conversions before ingesting into the database.
Separate Geometry From Attributes
- Store geometries separately from tabular attributes and join by a stable geo ID to reduce storage and simplify updates.
- Keep one geometry record and join it to changing attribute tables to avoid duplicating large geometries.
Projection Choice Changes Measurements
- Projection and spatial type (geometry vs geography) determine whether you measure on a flat plane or the Earth's curve.
- Choose the spatial type consciously because distance and analysis accuracy change with scale.
Get the Snipd Podcast app to discover more snips from this episode
Get the app 1 chevron_right 2 chevron_right 3 chevron_right 4 chevron_right 5 chevron_right 6 chevron_right 7 chevron_right 8 chevron_right 9 chevron_right 10 chevron_right 11 chevron_right 12 chevron_right 13 chevron_right 14 chevron_right 15 chevron_right 16 chevron_right 17 chevron_right 18 chevron_right 19 chevron_right
Introduction
00:00 • 2min
The Best Data Bases for Geo Spatial Analysis
01:57 • 2min
Is It a Good Idea to Use Spatial Relationships in the Real World?
03:47 • 3min
Post G I S, and Other Implementations of Post S
07:16 • 3min
The Difference Between Intersex and Within Versus Overlaps
10:03 • 3min
Geospatial Data Engineering - Is There a Common Representation?
13:09 • 4min
The Challenges of Segmenting Geometries
16:46 • 3min
How Much Background Knowledge Is Necessary?
19:30 • 4min
Is There a Difference Between a Projection and an Analysis?
23:01 • 2min
Is There a Difference Between a Data Scientist and a Statistical Analyst?
25:27 • 4min
Scaling for Multiple Geographies or Cities or Something Like That
29:02 • 3min
Geospatial Data Engineer - Is There a Way to Integrate Geometric Data Checks?
32:07 • 4min
Data Pipelines - A Data Integration Platform Built for Constant Change
36:02 • 4min
Is There a Way to Convert a Vector Into a Geometry?
40:29 • 4min
What Are Some of the Coolest Use Cases for Geo Spatial Data?
44:57 • 4min
The Challenges of Working With Spatial Data in a Sequel Environment
48:53 • 3min
The Best Workflow for Go Data?
51:32 • 4min
Geospatial Data - What's Next?
55:16 • 2min
The Biggest Gap in Data Management
57:04 • 3min
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
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 hosts@dataengineeringpodcast.com) 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