Introduction to SQL and Geospatial Data Processing: Reference

Key Points

Initial Exercise
  • There are many different ways to explore the same dataset

Introduction to Databases
  • Databases offer a highly structured framework for storing and rapidly manipulating data

  • Setting up this structure takes some time

  • Advantages include the ability to investigate complex relationships between data using a simple query language

Database Connection
  • dBeaver provides a simple interface for viewing database tables and testing SQL queries

SQL Queries
  • SQL provides a simple yet powerful set of tools for performing anaylis on your data

Geospatial Queries
  • All geospatial analysis requires a knowledge of reference frames and coordinate systems

  • Be sure to use the correct coordinate system to match your analysis, otherwise you can get unexpected results

  • many databases have extensions that encode geospatial information (e.g. PostGIS)

  • PostGIS functions provide a wide range of tools to incorporate spatial analysis into your workflow

  • GIS software provides a graphical user interface for running geospatial queries and making maps of the results

  • QGIS is a particularly good software for interfacing with PostGIS

Scripting database connections in Python/R
  • All database functionality can be accessed through Python or R scripts

  • This is an important way to ensure reproducibility and transferability of our work

Advanced Geospatial Queries

FIXME: more reference material.