Hands-On PostgreSQL Project: Spatial Data Science
1h 45mIntermediate2024-11-12
Authors
Maggie Ma
Course details
Geospatial data science is booming right now, and its appeal isn’t limited to just a few select fields. Its applications in the real world run the gamut, proving its utility in a wide range of industries, including aerospace, agriculture, urban planning, and tech. In this hands-on interactive course, instructor Maggie Ma provides an overview of how to perform advanced Spatial SQL operations in a local database. Maggie shows you how to set up a local DBeaver pgAdmin 4 database, import public datasets, and run PostGIS SQL queries to execute spatial joins. This course is ideal for database administrators and data scientists looking to learn more about the value of geospatial data.
Skills covered
DBeaverPostgreSQLGISDatabase AdministrationDatabase DevelopmentDatabase ManagementData AnalysisAECData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware DevelopmentOne-Off
Concepts
Introduction
- Discover the power of geospatial data
- What you should know
- Using Codespaces with this course
Geospatial Fundamentals
- Understanding the basics of geospatial data science
- Overview of current geospatial technology landscape
- Understanding coordinate systems in geospatial analysis
- Introduction to vector data for spatial analysis
- Introduction to raster data for spatial analysis
Building a Geospatial Project
- Overview of the geospatial project
- Exploring US census data and NYC trip data
- Technology stack - DBeaver, PostGIS, PostgreSQL
- Loading geospatial data in local database using DBeaver
Temporal Operations for Geospatial Analysis
- Data exploration with SQL
- Time-based analysis - Grouping trips by half-hour intervals
- Time-based analysis - Analyzing patterns by time of day
Spatial Operations in Geospatial Analysis
- Reproject census tract boundary geometry with PostGIS
- Creating geometric columns and defining projections
- Spatial ref sys table explained
- Spatial analysis - Analyzing patterns with spatial join
- Creating a choropleth map in QGIS
- Spatial analysis - Identify nearby stations with a buffer
Spatio-Temporal Analysis and Visualization
- Spatio-temporal analysis
- Visualize time series data in QGIS with choropleth map
Conclusion
- Next steps