SQL and Databases

Spatial databases

Spatial databases can be a valuable tool when working with big (geospatial) data. Tasks that may be impossible using R or Python due to memory constraints may be rapidly performed using a spatial database.

PostgreSQL (or just postgres) is a powerful, open source object-relational database system.

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

It is possible to set up a spatial database with a Conda virtual environment:

# create environment
conda create --name myenv

# enter the environment
conda activate myenv

# install postgresql and postGIS via conda
conda install -y -c conda-forge postgresql
conda install -c conda-forge postgis

# set a database name (can be anything)
DB="gedi"

# create a base database locally
initdb -D ${DB}_db

# start the server modus/instance of postgres
pg_ctl -D ${DB}_db -l logfile start

# create inner db
createdb $DB

# enable the postgis extension
psql -d $DB -c "CREATE EXTENSION postgis;"

# then populate with tables etc.

https://www.infoworld.com/article/3219795/what-is-sql-the-first-language-of-data-analysis.html