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

create a base database locally

initdb -D mylocal_db

start the server modus/instance of postgres

pg_ctl -D mylocal_db -l logfile start

create inner db

createdb myinner_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