There are a ton of different ways to work with spatial SQL, but there are three different elements of every spatial SQL setup:
The database or data warehouse
The access method
The visualization layer
This video will take you through the different options for your spatial SQL setup and help you get started.
The database or data warehouse
Below are some of the options that I covered in the video to create your spatial SQL set up:
PostGIS on Docker
Set Up a PostGIS Database With Docker
Setting up a PostGIS database is easy! We will use Docker, a popular containerization platform to install and set up a PostGIS database, then connect to it using QGIS and ArcGIS Pro.
Error | Docker Hub
Postgres.app
Postgres.app – the easiest way to get started with PostgreSQL on the Mac
Postgres.app is a full featured PostgreSQL installation packaged as a standard Mac app.
Data Warehouses
Cloud Data Warehouse – Amazon Redshift – Amazon Web Services
Amazon Redshift uses SQL to analyze structured and semi-structured data across data warehouses, operational databases, and data lakes, using AWS-designed hardware and machine learning to deliver the best price performance at any scale.
BigQuery: Enterprise Data Warehouse | BigQuery: Cloud Data Warehouse | Google Cloud
BigQuery is a serverless, cost-effective and multicloud data warehouse designed to help you turn big data into valuable business insights. Start free.
The Data Cloud | Snowflake
Discover how the Snowflake Data Cloud can unite data and smash silos to solve your biggest challenges.
Data Lakehouse Architecture and AI Company – Databricks
Databricks combines data warehouses & data lakes into a lakehouse architecture. Collaborate on all of your data, analytics & AI workloads using one platform.
The access method
Next you need some method to query your database or data warehouse. All of the data warehouses listed above have some tools to query your data in a UI. My recommended method is actually setting up PostGIS with QGIS as it allows you to import, query, and visualize your data all in a desktop environment. Check out the tutorial here:
Another quick way to do this is with Geopandas and PostGIS:
I would also check out DBeaver if you want a totally open source query interface with some light mapping capabilities too!
DBeaver Community | Free Universal Database Tool
Free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.
The visualization layer
The last step is of course a layer to visualize your data on a map. The options above all have some different methods to do this but CARTO provides a great toolkit to query and visualize your data too. You can sign up for a trial and if you are a student you get free access.
FAQs | CARTO Documentation
CARTO for Education How can I get a Student account in CARTO?
How can I get an Educator acccount?
We need Enterprise capabilities for our institution or academic research, can you help?
What is the process for getting a CARTO Student account?
I am an educator and my course materials use the previous…
And here are some Python libraries to take a look at as well:
