bluegreencity-ki

PostGIS and QGIS User Guide

This guide explains how project members access the PostGIS database from their preferred tools. It is written for reproducibility and publication, so all connection examples use placeholders rather than live infrastructure values.

Connection Template

Parameter	Value Pattern
Host
Port	5432
Database
User
Password	stored locally in .env or provided out of band
SSL	according to your private network setup

Before connecting:

Ensure you are on the private network or VPN used by the project.
Copy .env.example to .env if you need Python-based access.
Never commit populated credentials.

Recommended Tooling

DBeaver for database browsing and SQL work
VS Code PostgreSQL extension for lightweight development queries
Python for scripted reads and writes inside the pipeline
QGIS for spatial inspection and cartography
pgAdmin only as a fallback web interface

Method 1: DBeaver

Install DBeaver Community Edition.
Create a new PostgreSQL connection.
Enter the connection template values.
Test the connection and save it.

Typical daily tasks:

browse schemas and tables
run ad hoc SQL queries
inspect spatial tables and indexes
export result sets for analysis

For a focused setup walkthrough, see DBeaver_Setup_Guide.md.

Method 2: VS Code PostgreSQL Extension

Install the Microsoft PostgreSQL extension.
Add a new connection from the command palette.
Use the same host, port, database, and user values as above.
Store credentials locally only if your workstation policy allows it.

This method is suitable for small queries during development, but it is less convenient than DBeaver for schema exploration.

Method 3: Python Pipeline Access

Create a local .env file:

Copy-Item .env.example .env

Minimal .env structure:

POSTGIS_HOST=<database-hostname>
POSTGIS_PORT=5432
POSTGIS_DB=<database-name>
POSTGIS_USER=<database-user>
POSTGIS_PASSWORD=<database-password>

DATABASE_URL=postgresql://<database-user>:<url-encoded-password>@<database-hostname>:5432/<database-name>

Read data from PostGIS:

import os

import geopandas as gpd
from dotenv import load_dotenv
from sqlalchemy import create_engine

load_dotenv()
engine = create_engine(os.getenv("DATABASE_URL"))

trees = gpd.read_postgis(
    "SELECT * FROM bgcki.trees_inventory",
    engine,
    geom_col="geom",
)

Write data to PostGIS:

import os

import geopandas as gpd
from dotenv import load_dotenv
from sqlalchemy import create_engine

load_dotenv()
engine = create_engine(os.getenv("DATABASE_URL"))

result_gdf = gpd.read_file("data/derived/example.gpkg")
result_gdf.to_postgis(
    name="example_table",
    con=engine,
    schema="analysis",
    if_exists="replace",
    index=False,
)

Method 4: QGIS

Open QGIS and create a new PostgreSQL connection.
Reuse the same database host, port, name, and user.
Load the relevant layers from the trees, context, analysis, or climate schemas.
Use QGIS primarily for visualization and manual spatial checks.

For publication workflows, export maps or GeoPackages from QGIS rather than editing the operational database interactively.

Method 5: pgAdmin Fallback

If pgAdmin is enabled in docker-compose.yml, open it through the internal service URL configured for your environment. Keep that URL in internal operational notes rather than public-facing documentation.

Use pgAdmin only when:

DBeaver is unavailable
browser-based access is required
an administrator needs the bundled database UI

Security Notes

Do not paste passwords into shared markdown documents.
Prefer hostnames over raw IP addresses in user-facing docs.
Keep .env files local and excluded from version control.
Distribute credentials through an approved internal channel.