PostGIS: Free Open Source Spatial Database

Quick Overview

The video demonstrates how to use Elestio to deploy a fully managed PostGIS spatial database instance on PostgreSQL, which is then connected to QGIS for visualizing and querying the spatial data, showing the entire workflow from cloud deployment to spatial analysis.

Key Points: The demonstration begins by deploying a PostGIS 3.6.1 instance via the Elestio platform, selecting the Netcup provider and a MEDIUM-2C-4G service plan (0:07-1:08). After deployment, Elestio sends an email confirming the service is ready and provides access credentials for both the Admin UI (PGAdmin) and the database root user (1:21). The user connects to the deployed PostgreSQL database using PGAdmin (2:10) and restores the provided 'nycdata.backup' file into the 'postgres' database (3:11-3:30). Once data is restored, the user connects QGIS to the new PostGIS instance using the credentials obtained from Elestio (4:09-4:34). After connecting QGIS, the user visualizes multiple layers, including NYC neighborhoods, census blocks, streets, and homicide locations, demonstrating the successful integration of the spatial database with the GIS client (4:46-5:27). The video concludes by running a spatial SQL query in PGAdmin to count homicides per neighborhood, showcasing PostGIS's capability to handle spatial operations directly within the database (5:57-6:53).

Context: This tutorial guides users through setting up and utilizing PostGIS, an open-source extension for PostgreSQL that adds support for storing, indexing, and querying geospatial data. The demonstration uses the Elestio managed DevOps platform to quickly provision the database server, followed by using PGAdmin to load sample NYC crime and geography data, and finally connecting QGIS to visualize and analyze the spatial information.

Detailed Analysis

The video provides a complete walkthrough of deploying a PostGIS spatial database using the Elestio platform and subsequently visualizing the loaded data in QGIS. The process starts at 0:00 with the presenter introducing the need for spatial database capabilities. At 0:08, the features of PostGIS are briefly shown via documentation. The deployment phase begins at 0:38, where the user navigates to Elestio, chooses PostGIS, selects a cloud provider (Netcup), region (Nuremberg), and a MEDIUM-2C-4G plan, finalizing the service creation at 1:20. Upon completion, an email confirms readiness, providing both the PGAdmin URL and database credentials (1:21). The user then logs into the Elestio dashboard to retrieve the database root credentials (1:35). Next, the user connects to the remote PostgreSQL server via PGAdmin (2:10) using the host, port, username ('root'), and password provided by Elestio (2:29). After successfully registering the server, the user follows the PostGIS documentation (3:03) to restore the sample data from an 'nycdata.backup' file into the 'postgres' database (3:11). Following the successful restoration (3:37), the user opens QGIS (4:06) and establishes a new PostgreSQL connection using the same credentials (4:12). Once connected, the user loads several layers from the restored database, including homicide points, census blocks, streets, and neighborhoods (4:46). The visualization step shows the spatial data overlaid on the NYC map (5:07). Finally, the video demonstrates leveraging PostGIS spatial SQL functions within PGAdmin by executing a query to count the number of homicides per neighborhood geometry, proving the spatial data is accessible and queryable (5:57-6:53).