DEV Community: Camptocamp Geospatial Solutions

Incremental Backup with PostgreSQL 17

Marion Baumgartner — Fri, 20 Mar 2026 08:09:17 +0000

With PostgreSQL 17 incremental backups wer introduced in a built in way. This article describes the result of a workshop dedicated to study this new feature.

The Setup

We used the following docker composition to simulate the a cluster with traffic that we want backed up. For this we created the following docker-compose.yml with several containers:

services:
  postgres_main:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_USER: postgres
      POSTGRES_DB: testdb
      PGDATABASE: testdb
    volumes:
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - pg_data:/var/lib/postgresql/data
      - wal_archive:/mnt/wal_archive
      - full_backup:/mnt/full_backup
      - incremental_backup:/mnt/incremental_backup
    command: >
      postgres -c archive_mode=on
               -c archive_command='cp %p /mnt/wal_archive/%f'
               -c summarize_wal=on

  postgres_restore:
    image: postgres:17
    profiles:
      - restore
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_USER: postgres
      POSTGRES_DB: testdb
    volumes:
      - pg_data_restore:/var/lib/postgresql/data
      - wal_archive:/mnt/wal_archive
      - full_backup:/mnt/full_backup
      - incremental_backup:/mnt/incremental_backup
    command: >
      postgres -c restore_command='cp /mnt/wal_archive/%f %p'

  cli:
    image: cli
    build: .
    stop_grace_period: 1s
    environment:
      PGPASSWORD: postgres
      PGUSER: postgres
      PGDATABASE: testdb
      PGHOST: postgres_main
    volumes:
      - pg_data_restore:/mnt/data/restore
      - pg_data:/var/lib/postgresql/data
      - wal_archive:/mnt/wal_archive
      - full_backup:/mnt/full_backup
      - incremental_backup:/mnt/incremental_backup
      - ./checksum.py:/usr/local/bin/checksum.py
    entrypoint: ["/bin/sh", "-c"]
    command:
      - |
        chown -R 999 /mnt/wal_archive
        chown -R 999 /mnt/full_backup
        chown -R 999 /mnt/incremental_backup
        chown -R 999 /mnt/data/restore
        sleep infinity

volumes:
  pg_data:
  pg_data_restore:
  wal_archive:
  full_backup:
  incremental_backup:

Notes about the docker compose file

postgres_main contains the database we wish to backup
postgres_restore is the database where we want to restore the database
cli is to prepare the data for restoration, it enables us to connect to all clusters.

⚠️ Make sure to use PG17!

The main idea:

Take a full backup as a starting point
Take an incremental backup
Take another incremental backup
Repeat step 3

1. Initial full Backup

The first step, consists of creating an initial full backup with the following command:

pg_basebackup --pgdata=/mnt/full_backup

Some info about the `pg_basebackup` command

The target directory is a mount point for the full backup, it’s also shared between containers (via the volumes).
By default pgdata specifies the target directory where the backup will be stored. In this case the output will be written to /mnt/full_backup

Output of the command

pg_basebackup will create a bunch of files, we will focus on the 2 important ones:

backup_label: this is a legacy description of the backup and here is an extract

START WAL LOCATION: 0/4000028 (file 000000010000000000000004)
CHECKPOINT LOCATION: 0/4000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2025-04-24 09:08:31 UTC
LABEL: pg_basebackup base backup
START TIMELINE: 1

This file indicates the location in the WAL where the backup starts, and so is the checkpoint location. The other information speak of themselves.

backup_manifest: (available since pg 13) is linked to the feature we are currently talking about - the incremental backup.

This file will serve as a reference to determiner which files should be included in the incremental backup. Don’t hesitate to order some training and/or feel free to checkout the documentation [https://www.postgresql.org/docs/current/backup-manifest-files.html]

Here is an extract of this new backup_manifest:

{ 
 "Path": "base/16384/3766",
 "Size": 16384,
 "Last-Modified": "2025-04-24 08:56:45 GMT", 
 "Checksum-Algorithm": "CRC32C",
 "Checksum": "3c0ea625"
},

For this specific “extract” of the file ‘base/16384/3766’ we have:

The checksum, that is the fingerprint 3c0ea625
The last modification date 2025-04-24 08:56:45 GMT

Understanding the Checksum

We can check this fingerprint by ourselves with a short snippet:

Prerequisites:

python3
pip install crc32c (you may need –fix-broken-packages)

#!/usr/bin/env python3

import sys
import crc32c
def main():
    if len(sys.argv) != 2:
        print(f"Usage: {sys.argv[0]} <filename>")
        sys.exit(1)

    filename = sys.argv[1]

    try:
        with open(filename, 'rb') as f:
            data = f.read()
    except Exception as e:
        print(f"Failed to read file: {e}")
        sys.exit(1)

    checksum = crc32c.crc32c(data)

    print(f"CRC32C (normal) : 0x{checksum:08x}")

    le_bytes = checksum.to_bytes(4, byteorder='big')[::-1]
    print(f"CRC32C (little-endian) : 0x{le_bytes.hex()}")

if __name__ == "__main__":
    main()

While no traffic is recorded on the pg cluster, the data will remain the same, as shown here.

Execute the checksum python script (file is in the path) with the parameter (file)

NOTE: You can find the file where the data of a specific table is stored with the following query:

SELECT
  relname,
  'base/' || pg_database.oid || '/' || relfilenode AS filename,
  pg_database.oid AS db_oid,
  pg_database.datname AS database,
  nspname AS schema
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN pg_database ON pg_database.oid = pg_database.oid
WHERE relfilenode IS NOT NULL
  AND relname LIKE 'pgbench%';

Which in this case returns the output:

relname	filename	database	schema
pgbench_accounts	base/16384/16397	testdb	public
pgbench_accounts_pkey	base/16384/16405	testdb	public
pgbench_branches	base/16384/16398	testdb	public
pgbench_branches_pkey	base/16384/16401	testdb	public
pgbench_history	base/16384/16399	testdb	public
pgbench_tellers	base/16384/16400	testdb	public
pgbench_tellers_pkey	base/16384/16403	testdb	public

So based on this we need to take a look at the file base/16384/16397 to determine checksum for the pgbench_accounts relation

Now execute the python script:

./checksum.py /var/lib/docker/volumes/demo-postgres-backup-incremental_pg_data/_data/base/16384/16397

>>> 0x57679e47  # CRC32C 

>>>> { "Path": "base/16384/16397", "Size": 536870912, "Last-Modified": "2025-04-24 11:44:14 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "57679e47"},

To se a change on the checksum let’s make some changes: We update a column in the pgbench_account table

UPDATE pgbench_accounts SET abalance = abalance + 10;

Lets, check the fingerprint again:

./checksum.py /var/lib/docker/volumes/demo-postgres-backup-incremental_pg_data/_data/base/16384/16397

>>> 0x06cc374f  # CRC32C

>>>> { "Path": "base/16384/16397", "Size": 1073741824, "Last-Modified": "2025-04-24 12:37:28 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "06cc374f" },

‼️When using a long update query, we can inspect the datadir and notice that the file is modified (checksum will be different), even if the transaction is not yet committed.

If we cancel the query, even if the data is not modified from the logical point (transaction rollback), the data on the disk will contain the uncommitted modifications. Due to the visibility map those modifications are not visible by any transaction but the file is modified, and the checksum will be different

⚠️ Wal summarize need to be activated (see our docker-compose)

2. Now Let’s Increment!

The first increment contains the diff from the full backup. Then the second increment references the previous one and should only contain the diff from the first incremental.

To set this up we will use the following command

pg_basebackup --checkpoint=fast --incremental=/mnt/full_backup/backup_manifest --pgdata=/mnt/incremental_backup/0/

--checkpoint=fast is set in order not wait for the next checkpoint
--incremental is where the magic appears, it must point to the backup_manifest of the last increment or full backup and is the origin of the diff
-- pgdata specifies the destination directory where the incremental backup will be stored

The creation of a new increment can be repeated multiple times. Where each increment contains only a copy of the blocks/pages that have changed since the last given increment.

Integrating Incremental Backups for Restoration

Now let’s put the incremental backup back together with the full backup and begin the database restoration process.
In order to do this we will use pg_combinebackup to merge the full backup with the incremental backups.
This command accepts multiple arguments, allowing us to specify as many incremental backups as we need. The first argument must be a full backup followed by the increments in a chronological order. If this order is not respected we will run into issues.

pg_combinebackup -d -o /mnt/data/restore /mnt/full_backup /mnt/incremental_backup/0 /mnt/incremental_backup/1

This command will give us a full backup in /mnt/data/restore. So now we can start a restored database from the combined backup.

In terms of the size of the back up we can see, that the full backup is much larger than the the increment:

Size	Item
1.6G	full_backup
25M	incremental_backup

And the more changes there are in the database the larger is the incremental backup:

Size	Changes	Comment
25M	0
25M	1	change of 2 records in a partitioned table
28M	2	change of 1000 records in a partitioned table

RTO (Recovery Time Objective) and RPO (Recovery Point Objective):

Lets talk about the impact of the recovery time objective (RTO). This is the time that is needed to rebuild a full backup with all the increments and recreate a recovery database.

It is possible to to have different scenarios:

One full backup once a week and one incremental backup every day.
One nightly full backup and incremental backups on an hourly rate.

Note: The second scenery is only relevant for a database with a lot of traffic.

The most consuming part is the reply of the WAL file, this means that we need to reduce the number of WAL to replay. One way to do this is to make the last incremental backup as close as possible to the recovery target. The second scenario is a good candidate to cover this. With incremental backups it is possible to handle this in a way so that not all of the data in the Database is transferred every hour but just the tables that contain modification in comparison to the last incremental backup.

Another independent scenery that could be imagined is that we make use of the increments and the full backup to create a new füll backup once in a while. Then on the next increment will be based on this new full backup. In this way backup traffic on the cluster can be reduced.

Summary:

In the blog pst we have analysed how to use incremental backups and how to set them back together to create a new folder so that we can start a restoration of the original database.

On the side the checksum and the manifest files that are used for incremental backups are explained and analysed.

As an out come we see is that by using incremental backups the subsequent backup gets smaller since only what changed since the last incremental run is saved.

FOSDEM 2026

Emilien Devos — Tue, 10 Mar 2026 12:52:59 +0000

Feedback from FOSDEM in Brussels (Belgium) in 2026: https://fosdem.org/2026/ and https://fosdem.org/2026/about/.

Vibe

Like last year, I focused more on the social aspect and discussions than trying to cram as many presentations as possible into two days.

There were more people than last year. FOSDEM is becoming increasingly popular. This year was the first time I was unable to attend the closing presentation of FOSDEM because there were too many participants in a room that can accommodate 1,500 people!

In particular, gyptazy, a member of the community, shared the feeling that this year has been a turning point, with a sharp increase in attendance:

But in 2026, it felt like something had shifted.

Interesting discussions

This year, I wasn't able to participate in a BoF (Birds Of a Feather) session, and unfortunately there weren't any organized around geospatial topics.

But I did get to chat with a few people from the community, including:

Vates, a virtualization solution that positions itself as an open source alternative to Proxmox and VMWare but based on Xen technology.
François Lacombe, who shared his Podoma project with me, which I will discuss in the next section.

Talks

Geospatial

This is the second year that the Geospatial track has returned to FOSDEM. And the Geospatial community at FOSDEM seems to be growing, as this year the room could accommodate 190 people compared to 75 last year!

The only presentation I managed to attend was Podoma, by François Lacombe, who presented a tool for visualizing OpenStreetMap contributions. This tool was created out of a need to better understand the evolution of contributions to OSM. It includes a podium system to highlight the biggest contributors in specific areas.

Other Geospatial presentations are available here: https://fosdem.org/2026/schedule/track/geospatial/

Infrastructure

Garage Object Storage: 2.0 update and best practices

The Garage team presents its second major release. Garage is software for hosting a self-hosted S3 API.

The enthusiasm for the project is much greater than when I attended its launch presentation two years ago. This is mainly because Minio is no longer open source, so the community has turned to Garage.

I noted quite a few tips on how to use Garage, particularly regarding hosting. This could be very useful for an S3 deployment on GeoServer.

PostgreSQL and MySQL, Two Databases, Three Perspectives

A great talk that clearly explains the internal differences between PostgreSQL and MySQL. It covers areas such as replication, the database engine, client connections, and more.

This talk highlights the value of drawing inspiration from MySQL's strengths in order to improve PostgreSQL accordingly.

Building ISOs from OCI containers

I was intrigued by this talk because at Camptocamp, many of us write Dockerfiles, but to deploy an application in a container.

The idea of being able to deploy this container as a virtual machine or even physically via a USB key is interesting!

This talk discussed the Containerfile format, a variant of Dockerfile, for generating an ISO file from a syntax familiar to that of a Dockerfile.

Other very interesting talks

The Servo project and its impact on the web platform ecosystem

Servo is a rendering engine for web pages. This talk presents the impact that Servo has on the web ecosystem.

It was accompanied by a demo where the presenter showed the rendering of complex web pages such as GitHub, Wikipedia, playing on Chess.com, and even his presentation was in Servo!

This is super exciting, because only three browser rendering engines dominate the web, and one more is really welcome!

Who Pays Your Bills? Sustainability, Community, and Business: The Open Source Triangle

Closely related to geospatial technology because the presenter is part of the QGIS team.

This presentation explains how to find the financial balance between the three pillars of an open source project: community, product, and business.

To view the other 1,073 talks

All talks have been recorded and can be viewed by selecting the desired theme and then the specific talk: https://fosdem.org/2026/schedule/rooms/

Adding SonarQube to an AI-Assisted Development Workflow

Florent Gravin — Mon, 02 Mar 2026 11:11:40 +0000

We use AI agents as coding assistants (here Claude Code) in our daily work. Not in a "vibe coding" way where you throw a prompt and hope for the best. We practice spec-driven development: detailed specifications, structured plans, context engineering through CLAUDE.md files, custom instructions, and carefully scoped tasks. The goal is to give the AI enough context to produce good code from the start.

And it mostly works. The code we get is solid. But "mostly" isn't enough when you're shipping to production. Even with good context and clear specs, an AI assistant can still produce code with security issues, excessive complexity, or subtle smells that you'd catch in a thorough review — if you had the time.

So we added SonarQube to the loop, and we let the AI fix what Sonar finds.

The setup

If you want to rely on CI based Sonar scans and reports, you need to have non-free options :

Sonar cloud subscription

SonarQube sever, developer edition (the community edition does not help much).

We wanted something that runs locally, requires no manual steps, and integrates directly into the AI agent's workflow.

A local SonarQube instance via Docker Compose:

services:
  sonarqube:
    image: sonarqube:lts-community
    ports:
      - "9000:9000"
    volumes:
      - sonar_data:/opt/sonarqube/data

A scan script (scripts/sonar-scan.sh) that handles the full lifecycle:

checks that SonarQube is up
auto-provisions an auth token on first run
creates the project if needed
runs the scanner via Docker
waits for analysis to finish
fetches all issues
and writes them to a structured sonar-report.json file.

Each issue comes with a file path, line number, severity, rule ID, and a description. This structured output is what makes the automation possible — the AI agent can parse it and act on each issue directly.

A Claude Code slash command (.claude/commands/sonar.md) that ties it together. When you run /sonar, the agent:

Runs bash scripts/sonar-scan.sh
Reads the report — issues sorted by severity (BLOCKER, CRITICAL, MAJOR...)
For each issue: reads the file, understands the rule violation, applies a minimal fix
Skips generated code (API clients, etc.)
Runs npm run type-check to make sure fixes don't break anything
Re-scans. If new issues appeared, fixes those too.
Reports what it changed and how many iterations it took.

CI enforcement via GitHub Actions on every PR:

- name: SonarQube Scan
  uses: SonarSource/sonarqube-scan-action@v5
  env:
    SONAR_TOKEN: ${{ secrets.SONAR_TOKEN }}
    SONAR_HOST_URL: ${{ secrets.SONAR_HOST_URL }}

- name: Quality Gate
  uses: SonarSource/sonarqube-quality-gate-action@v1

How the loop works in practice

We recently built a feature with several Lit web components, services, and types — version history with diffs, draft management, conflict resolution dialogs. The AI generated it from detailed specs. The code worked, tests passed, TypeScript was happy.

Then we ran /sonar. Sonar found things like:

Cognitive complexity too high in some diff rendering logic
Unused imports left over from refactoring iterations
Duplicated string literals across components
Missing type narrowing where null dereferences were possible
Missing ARIA attributes on interactive elements

None of these were show-stoppers. All of them were things you'd want fixed before merging. The agent resolved them in 2-3 scan cycles without intervention.

The point isn't that the AI wrote bad code. The point is that static analysis catches a class of issues that are easy to miss during generation — and the AI is perfectly capable of fixing them when told what's wrong.

Why Sonar and not just a linter

ESLint catches a lot, but SonarQube covers ground that linters don't:

Security vulnerability detection (injections, hardcoded secrets, insecure patterns)
Cross-file duplication detection
Cognitive complexity scoring
A quality gate — a binary pass/fail, not just a list of warnings

Combined with TypeScript's type checker and your test suite, you get three independent verification layers, each catching different categories of problems.

What this looks like day-to-day

Our workflow before a PR:

Write specs and plan the feature
AI implements it with full context (CLAUDE.md, specs, existing code)
Run /sonar — the agent scans, fixes, re-scans until clean
Verify that typecheck and tests still pass
Review the diff and open the PR

The Sonar step typically adds a few minutes. It's not something we run after every edit — just before we consider a feature done. Think of it as an automated pre-review pass.

Closing thoughts

If you use AI assistants for coding, you already have good reasons to invest in context engineering — giving the AI the right specs, the right constraints, the right examples. SonarQube is another form of that same idea: giving the AI structured feedback about what's not right, and letting it iterate.

The scan script, the slash command, and the CI workflow are all straightforward to set up. SonarQube Community Edition is free. The scripts are plain bash. This works with any AI coding tool that can run shell commands and read structured output.

The code is on GitHub if you want to look at the implementation.

Link to the repository

🧠 Lessons Learned: Multi-Platform Intelligence Suite

Emmanuel Belo — Sat, 21 Feb 2026 10:31:48 +0000

This retrospective documents the development of a Multi-Platform Intelligence Suite — an autonomous OSINT python tool designed to transform high-noise social feeds into structured, geopolitical-style briefings, released on GitHub.

The application currently aggregates timelines from X (Twitter) and Bluesky, standardizing engagement metrics via Z-score normalization to ensure high-signal content is identified regardless of the host platform's user density.

The tool supports two AI backends:

Gemini (Google Cloud): Fast, high-quality, but costs API credits.
Ollama (Local): Runs entirely on your own hardware, free to use.

This document captures the real challenges I ran into, the decisions I made, and the insights I'd apply if starting over. It is written for someone who wants to build something similar.

1. API Costs: Discover, Measure, Then Mitigate

What happened: The X API is priced pay-per-request. $25 was loaded as credits and the tool was run for the first time on a real 24-hour timeline. The result: fetching ~800 posts cost approximately $4 in a single run.

That's a concrete, measurable signal. At that rate, iterating on the AI pipeline — running it 5-10 times to test different prompts, models, and architectures — would have burned through the entire $25 budget in API calls alone, producing nothing useful.

The immediate response was to ask the agent to implement a skip flag: --from-summary. Once a summary is fetched and saved to disk, all subsequent runs reuse it. The X API is never called again until you explicitly want fresh data.

A second flag, --intel-limit N, was added to cap the number of posts sent to the LLM for fast, cheap iteration on the AI components.

The lesson is not "plan for costs upfront." It's: measure your first real run, notice where money is being burned, and immediately build the escape hatch. On a pay-per-request API, the fetch layer and the intelligence layer should be independently executable from the start.

Takeaway: Two-stage pipelines (fetch → process) should always support running each stage independently. The moment you see a real cost, you can respond in minutes — if the architecture allows it.

2. The Value of Agent-Delegated Complexity

Several genuinely hard technical problems appeared during this project: OAuth 1.0a authentication (a 4-key scheme used by the X API), environment variable loading bugs, and API credential validation errors that produced opaque error messages. These are exactly the kinds of problems that would traditionally stop a non-developer cold.

They didn't stop the project. The agent diagnosed each issue, reprompted itself, tried fixes, validated them, and moved on — autonomously. From the user's perspective, these problems happened and were resolved without any manual intervention. No terminal commands to write. No .env syntax to understand. No OAuth documentation to read.

The real lesson here is not technical — it is about trust and scope.

Working with an agentic AI partner means you can delegate an entire category of problem — low-level debugging, configuration, environment management — and direct your own attention toward higher-level decisions: what to build, which model to use, what the output should look like.

This is a different kind of collaboration than using a search engine or a tutorial. The agent doesn't just give you the answer — it implements it, tests it, and moves on. The release of being able to trust that process was significant: it meant the pace of the project was set by decisions, not by debugging capacity.

Takeaway: The ceiling of what a non-developer can build is dramatically higher with an agentic AI partner — not because the technical problems go away, but because they no longer have to stop you.

3. Architecture Decision: Map-Reduce for Local Models

Context: A "context window" is the maximum amount of text a language model can process at once. A typical LLM context window holds ~8,000–32,000 tokens. An 800-post timeline easily exceeds that limit.

The problem: Feeding all 800+ posts to a local model in one prompt caused hallucinations, dropped posts, and incoherent summaries.

Solution — Map-Reduce:

Map: Split posts into small batches of 10. Ask the model to classify each post into one of 6 predefined categories.
Select: From each category, take only the top 15 posts by engagement score.
Draft: Generate a section summary for each category (90 posts total, well within any context window).
Reduce (Reflective Pass): Feed all 6 drafted sections back to the model and ask it to synthesize a single-paragraph Executive Summary — the model reflecting on its own work.

The "reflective pass" is a powerful general pattern: generate drafts, then ask the model to step back and synthesize. It consistently produces better summaries than one-shot attempts.

Takeaway: For large datasets, never attempt a single prompt. Classify first, then synthesize progressively.

4. Cloud-to-Local: Architectures Don't Scale Down

This is perhaps the most counterintuitive lesson of the entire project.

The natural assumption when adding local model support is: "I already have a Gemini prompt that works well — I'll reuse it with Ollama and swap the endpoint." This is wrong, and the failure is not gradual — it is qualitative.

In naval engineering, this problem has a name: similitude failure. When you build a scale model of a ship to test in a tank, the forces that dominate behavior are different at small scale. A full-size ship is dominated by wave drag and inertia. A small model in a tub is dominated by surface tension and viscosity. The same equations, the same shape — completely different governing physics. You can't just shrink the design.

The same principle applies to LLMs:

Failure Mode	Large Cloud Model (Gemini)	Small Local Model (Llama 3.2)
Context window	Effectively unlimited (1M+ tokens)	Very limited (~8K tokens)
Instruction fidelity	Reliably follows complex prompts	Drifts, invents, simplifies
Structured output	Stable with a well-written prompt	Needs strict enumeration of every valid output
Hallucination	Rare in classification tasks	Common if categories are not explicitly constrained
Single-pass summary	Works for 800+ posts	Fails; context is truncated or ignored

The dominant failure modes are qualitatively different, not just quantitatively worse. A single Gemini prompt breaks in a completely different way on Llama 3.2 than you'd expect from "a smaller, dumber version."

What this means in practice: Local model support is not a feature — it is a separate architecture. It requires:

Breaking large prompts into small, constrained steps (Map-Reduce)
Strict enumeration of all valid outputs in the prompt
A reflective synthesis pass instead of one-shot generation
Validation and fallback logic at every stage

Corollary — Define the Output Schema First: This lesson has an immediate practical implication. With two backends (Gemini and Ollama), each had its own system prompt written independently. The result: different category names, different heading structures, and incompatible report formats. The Gemini prompt used free-form topics; the Ollama pipeline used a fixed list of 6 categories. They couldn't be compared or combined.

The fix was to define the 6 categories centrally and update both prompts to reference them. In a multi-backend system, the output schema is the contract — treat it like an API. Writing prompts before agreeing on the schema causes messy retrofits.

5. Model Selection: VRAM is the Real Constraint

Context: VRAM (Video RAM) is the dedicated memory on your GPU. If a model fits entirely in VRAM, inference is extremely fast. If it doesn't, the system "spills" to regular RAM, which is 10-20x slower.

Model	Size	Fits in 4GB VRAM?	Time for 839 posts
Mistral 7B	4.4 GB	❌ (RAM spill)	~6.5 hours (estimated)
Llama 3.2 3B	2.0 GB	✅	~25 minutes
Gemini Flash	Cloud	N/A	~45 seconds

The hardware used for this benchmark: Intel 11th Gen i7, 16GB RAM, NVIDIA T500 (4GB VRAM) — a mobile workstation laptop several years old. Despite its age, Llama 3.2 ran entirely in its VRAM and processed the full 839-post dataset comfortably.

Trap: "Mistral-Small" sounds like a small model — it is actually 24B parameters (~14GB). The "Small" in Mistral's naming refers to it being small relative to their 123B flagship, not relative to a 7B base model.

Takeaway: For batch-heavy local pipelines, the largest model that fits completely in VRAM will outperform a "smarter" model that doesn't — often by an order of magnitude.

6. Prompt Engineering: Strictness Over Creativity

Challenge: Local models are prone to "category hallucination" — inventing category names not in the provided list (e.g. returning "Finance & Economy" instead of the defined "Economics & Markets"). This corrupts the Map-Reduce grouping step.

What worked:

Providing the exact list of 6 category strings in the prompt with explicit instruction to use only these strings.
One-shot formatting: showing the model one example of the expected input/output pair.
Strict matching in the classification parser with a fallback to "unrecognised" rather than accepting approximate matches.

Takeaway: For structured classification tasks, suppress model creativity. Enumerate all valid outputs explicitly. Any ambiguity will be exploited.

7. Code Quality: SonarCloud as Your External Auditor

As a non-developer, you cannot assess the security or quality of code you didn't write. You can't spot a deeply nested function that will become impossible to maintain, or know when exception handling is done in a way that suppresses critical signals. You have to trust that the code is correct.

SonarCloud provides an independent, automated answer to that trust problem. Every time code was pushed to GitHub, SonarCloud ran a quality pass and flagged any issues — from structural complexity to unsafe exception handling patterns. No developer judgment required; the findings are plain-language, specific, and actionable.

The workflow that emerged was trivially simple:

Check the SonarCloud dashboard after a push.
Copy the flagged issue description.
Hand it directly to the coding agent: "Fix this SonarCloud warning."
The agent implements the fix, explains what changed, and pushes.

No major security issues were found. The warnings were structural — functions too complex to maintain easily, commented-out dividers mistaken for dead code, a try/except pattern that could suppress system signals. All were resolved quickly.

Takeaway: For non-developer-led projects, a CI/CD quality gate like SonarCloud is not optional — it is your independent code reviewer. It closes the gap between "the agent wrote code" and "the code is trustworthy."

8. Git Strategy: Branches for Architectural Changes

Challenge: During the shift to Map-Reduce, the codebase changed significantly — new modules, new data flow, new prompts. The temptation is to keep going until it "works" before committing. The result: a sprawling unstaged delta that's hard to reason about or partially revert.
The psychological trap: "I don't want to commit broken code." This causes delayed commits, which causes large messy commits, which causes reverting damage to be much worse.
What to do instead:

git checkout -b feature/map-reduce — protect main from the work-in-progress.
Commit each stable sub-component individually: the classifier function, the category selector, the section generator, then the assembler.
End-to-end doesn't need to work for each commit — only the piece you just wrote needs to be internally correct.
Merge to main only when the full pipeline is tested green.

Takeaway: A broken commit on a branch isn't broken code — it's a checkpoint. Use branches to make partial commits feel safe.

9. Beyond Vibe Coding: Agentic Technical Direction

When you build software entirely through conversational AI, the popular term is "vibe coding" — a process often characterized by low-context requests ("make an app that does X") and the blind acceptance of AI-generated code. This frequently results in unmaintainable architecture because the human doesn't actually understand the system.
This project demonstrated something fundamentally different: Agentic Technical Direction.
In this model, I acted as a Tech Lead or Technical Product Manager, not a typist:

Defining Architecture: I made the hard structural decisions ("Use Ollama for local inference," "Rank by engagement instead of X's proprietary algorithm," "Implement Map-Reduce to handle context windows").
Auditing & Verifying: I didn't accept the first draft. When presented with failure modes (hallucinations, VRAM spills, API costs) by the agentic AI, I defined the mitigation strategy (e.g., "Implement a --from-summary skip flag").
Enforcing Standards: I demanded external quality gates (SonarCloud), insisted on 90%+ test coverage, and enforced open-source standards (MIT licenses, CONTRIBUTING.md).
Documenting the Why: I framed the project as a cohesive product with a distinct point of view (Local Privacy, Deep Reading), rather than just a script. The AI agent wrote the code, but I designed the system. That is the future of software engineering.

10. Leveling Up: Skills for Agentic Technical Direction

Acting as a Technical Director for an AI agent requires a specific skill set that differs from traditional coding. Based on my workflow in this project, here are the key areas I need to develop for future agentic projects:

What to Learn: Git and Version Control

The Challenge: The fastest way out of bad AI-generated code is not asking the AI to fix it, but simply reverting to the last known good state. If I don't control the version timeline, the AI controls me.
The Skill: Master the difference between the working directory, staging area, and commits. Learn to ruthlessly use feature branches (git checkout -b new-experiment) so the main branch stays pristine until I explicitly approve the agent's work.

What to Improve: The Skill of "Scaffolding"

The Challenge: AI models struggle with "big bang" integration where everything is built at once, but they are incredible at filling in the blanks.
The Skill: Practice "Data-First" building. Before asking the agent to write a processing pipeline, manually define exactly what the input data and desired output data look like. Write empty function signatures (stubs) with clear docstrings, and let the AI fill in the logic.

What to Study: Fundamentals of Software Architecture

The Challenge: Without structural guidance, an AI will take the path of least resistance, resulting in giant, monolithic files holding thousands of lines of spaghetti code.
The Skill: Learn the vocabulary of system design to direct the agent on how files should interact. Understand "Separation of Concerns" (e.g., splitting fetching, ranking, and rendering) and "Dependency Injection" (which allowed us to easily swap between Gemini and Ollama).

The Superpower to Cultivate: The "Stop and Inspect" Reflex

The Strength: My best moments in this project occurred when development was paused to inspect failures. When SonarCloud flagged complexity, I had the agent refactor it. When a $4 API cost was observed, I had the agent build a bypass flag.
The Lesson: Many developers let AI tools generate code until the system collapses under its own weight. The reflex to inspect output, measure costs, and refactor messes before adding the next feature is exactly what makes a great Technical Director.

11. Conclusion: Learning by Doing with an AI Partner

This project started as a focused utility and became a hands-on education in local AI orchestration, Python engineering, API management, CI/CD quality gates, and system design.

The most important meta-lesson: complex, real work is the best teacher. Passively reading about Map-Reduce, VRAM constraints, and OAuth authentication doesn't produce the same understanding as hitting those walls directly and solving through them.

Working with an agentic AI partner compressed that learning dramatically — not by removing the problems, but by making it possible to actually encounter and solve them faster than you could alone.

The barriers to running your own local AI pipeline, writing production-quality Python, and shipping to GitHub with automated tests — are lower than they appear. You just need to start.

And the trend is clear: the models that ran efficiently today on older laptop hardware would have required a high-end server just a few years ago. As quantization and architecture efficiency improve, the "VRAM constraint" will keep relaxing. A 3B model in 2026 already does things a 13B model from 2023 struggled with. The intelligence-per-gigabyte ratio is rising fast — and it will keep rising.

Multi-Line Editing in edittrack: Work with Multiple Routes Simultaneously

Frédéric Junod — Mon, 09 Feb 2026 09:05:10 +0000

Planning complex routes often requires more than a single track. Whether you're mapping a multi-day trek, comparing alternative paths, or managing a network of interconnected trails, keeping all your routes in one workspace lets you see the full picture and maintain context while editing each part independently.

Starting with version 2.0-beta, edittrack introduces multi-line editing—the ability to create, edit, and manage multiple independent tracks within the same TrackManager. This post walks through the feature, its real-world applications, and how to work with it.

What multi-line editing offers

Multi-line editing lets you work with multiple independent line strings—called "parts"—in a single edittrack workspace. Each part is a complete track with its own control points, segments, POIs, and routing configuration. You can:

Create as many parts as you need
Switch between parts to edit them individually
See all parts simultaneously for context
Style active and inactive parts differently for visual clarity
Delete parts you no longer need

When you draw, move points, or add POIs, only the currently active part is affected. Other parts remain untouched, letting you refine one route without disrupting others.

Real-world use cases

Multi-day trips

Break a long journey into daily segments. Each day becomes its own part, so you can edit day 3 without accidentally moving points from day 1. All days remain visible on the map, giving you a sense of the full itinerary while you tweak individual stages.

Alternative routes

Present options to clients or collaborators by drawing multiple variations side-by-side:

Scenic route vs. fastest route
Technical mountain bike trail vs. beginner-friendly path
Main highway vs. scenic backroads

Compare distances, elevation profiles, and surface types across alternatives before committing to one.

Route networks

Build interconnected trail systems where a main route has optional side trips:

A primary hiking trail with viewpoint spurs
Different starting points converging at a common destination
Loop routes with shortcut options

Each branch is its own part, making it easy to modify without tangling the entire network.

Activity segments

Separate parts based on how you'll travel:

Walk to the trailhead (part 0), bike the main trail (part 1), drive home (part 2)
Approach routes vs. main climbing routes
Paved access roads vs. off-road trails

Each part can use different routing profiles or snapping configurations if needed.

Version control and experimentation

Keep your original route as part 0, then create part 1 for an optimized version. Compare them directly on the map. If the experiment doesn't work, simply delete the new part or switch back to the original.

Working with multiple parts: the API

If you're familiar with edittrack from the first blog post, working with multiple parts builds naturally on what you already know. The key difference: instead of managing one track, you now manage multiple parts and switch between them.

Creating new parts

Start with an existing TrackManager instance. Call createNewPart() to add a new empty part:

const partIndex = trackManager.createNewPart();
// Returns the index (0, 1, 2, etc.) and automatically switches to that part

The new part becomes the active part, and you can immediately start drawing on it. The first part you create is always part 0.

Switching between parts

To edit a different part, use workOnPart():

trackManager.workOnPart(1); // Switch to part 1

After switching, all drawing, point manipulation, and POI operations affect only that part. To check which part is currently active:

const currentPart = trackManager.activePart();
console.log(`Editing part ${currentPart}`);

Managing parts

Find out how many parts you have:

const total = trackManager.partsCount();

Delete a part you no longer need:

trackManager.deletePart(2); // Remove part 2

Deleting a part removes all its features—control points, segments, and POIs—and renumbers subsequent parts to maintain a sequential index. If you delete the active part, edittrack automatically switches to another available part.

Automatic switching on drag

By default, edittrack lets you grab features from any part and automatically switches to that part when you start dragging. This makes quick edits intuitive: just grab a point from part 2, and edittrack switches context so you can move it.

To disable this behavior (e.g., to prevent accidental edits to inactive parts):

trackManager.switchPartOnDrag = false;

With this set to false, dragging features from inactive parts is blocked entirely, enforcing strict isolation between parts.

Getting data from all parts

The familiar methods—getSegments(), getControlPoints(), and getPOIs()—return data only from the currently active part. If you need data from all parts at once, use the "all" variants:

const allSegments = trackManager.getAllSegments(); // Returns Feature[][]
const allControlPoints = trackManager.getAllControlPoints();
const allPOIs = trackManager.getAllPOIs();

Each of these returns a nested array: one sub-array per part. For example, allSegments[0] contains segments from part 0, allSegments[1] contains segments from part 1, and so on.

Iterating through parts

To process each part individually without manually switching back and forth, use the partsGenerator() method:

for (const {index, trackData} of trackManager.partsGenerator()) {
  console.log(`Part ${index} has ${trackData.segments.length} segments`);
  // Process trackData.segments, trackData.controlPoints, trackData.pois
}

The generator automatically restores the original active part when the loop completes, so you don't have to track state manually.

History management

Undo and redo now track both features and the active part. When you undo, edittrack restores not just the previous geometry and points, but also which part you were editing:

await trackManager.undo();
await trackManager.redo();

This means you can switch parts, make edits, switch again, and undo your way back through the entire sequence—edittrack remembers which part was active at each step.

Visual feedback and user experience

To help users distinguish between parts, edittrack sets an active property on every feature. This boolean indicates whether the feature belongs to the currently active part. You can use it in your styles to make active parts stand out:

const trackLineStyle = {
  "stroke-width": 6,
  "stroke-color": ["case",
    ["==", ["get", "active"], false],
    "#00883c80",  // Semi-transparent green for inactive parts
    "#00883cff"   // Opaque green for active parts
  ],
  "text-value": ["concat", "", ["get", "part"]], // Display part number as text
  "text-fill-color": "#fff",
};

Similarly, control points can use conditional styling:

const controlPointStyle = {
  "circle-fill-color": ["case",
    ["==", ["get", "active"], false],
    "#0071ec80",  // Semi-transparent blue for inactive
    "#0071ecff"   // Opaque blue for active
  ],
};

The part property stores the numeric index (0, 1, 2, etc.) of each feature's part. Displaying this number as a label on the map helps users keep track of which part is which, especially when working with many parts.

Try it yourself

The live demo now includes three new controls:

Add a new line string: creates a new empty part
Change active line string: cycles through existing parts
Delete active line string: removes the currently active part

To experiment:

Draw a route on part 0 by clicking points on the map
Click "Add a new line string" to create part 1
Draw a second route—notice the first route becomes semi-transparent
Click "Change active line string" to switch back to part 0 and modify it
Try dragging points from inactive parts to see automatic switching in action

The demo uses the conditional styling shown above, so you'll see clear visual distinction between active and inactive parts.

License

BSD-3-Clause

[PostGIS/PgRouting] With french routable data

jwaddle — Tue, 27 Jan 2026 12:10:14 +0000

Road-network isochrones with PostGIS & pgRouting (Docker)

This post documents a complete workflow to compute road-network isochrones using PostGIS and pgRouting, running inside Docker.
It used Data (BDTopo Dataset) from the IGN, France’s national mapping agency.
It simulates the time taken by fire trucks to reach different locations from a set of origin points (fire stations).

⚠️ Important disclaimer (read first or don't)

speed information in BD TOPO is not always correct, and optimistic, work is being done to make it more accurate.
intersections are not always modelled correctly (see pgr_separateTouching and/or use pgr_separateCrossing)
- https://docs.pgrouting.org/latest/en/pgRouting-concepts.html#id65
snapping is approximate (For the starting point, we get the closest point on the graph, it can be far away, or not truly be accessible in reality)
roads are sometimes included entirely even if only partially reachable

The result should be understood as a network reach envelope, not a real travel-time model.

1. PostgreSQL + pgRouting in Docker

docker run -d \
  --name pgrouting \
  -p 5432:5432 \
  -e POSTGRES_DB=gis \
  -e POSTGRES_USER=gis \
  -e POSTGRES_PASSWORD=gis \
  -v $(pwd)/pgdata:/var/lib/postgresql \
  pgrouting/pgrouting:18-3.6-4.0

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

2. Preparing the Road Network

Upload road network to the database (gdal, qgis). The data used here was downloaded from the french IGN https://geoservices.ign.fr/bdtopo

ST_Force2D is used to drop Z/M values if present.

CREATE TABLE bdtopo.edges AS
SELECT
  id::bigint AS id,
  ST_Force2D(geom)::geometry(LineString, 2154) AS geom,
  vitesse_moyenne_vl
FROM bdtopo.troncon_de_route
WHERE geom IS NOT NULL;

Always add an index on the geometry column for performance.

CREATE INDEX edges_gix ON bdtopo.edges USING GIST (geom);

Add required columns for pgRouting.

ALTER TABLE bdtopo.edges
  ADD COLUMN source bigint,
  ADD COLUMN target bigint,
  ADD COLUMN cost double precision,
  ADD COLUMN reverse_cost double precision;

3. Travel Time (Length × Speed)

Replace NULL speeds with 0 (unusable edges).

UPDATE bdtopo.edges
SET vitesse_moyenne_vl = 0
WHERE vitesse_moyenne_vl IS NULL;

Compute costs based on length and speed (in km/h).
With BD TOPO, I exaggerated the cost by 15% to get more optimistic results.
Nullif is used to avoid division by zero; 1000.0 / 3600.0 converts km/h to m/s, I hope

UPDATE bdtopo.edges
SET cost = 1.15 *ST_Length(geom)
           / NULLIF(vitesse_moyenne_vl::double precision * 1000.0 / 3600.0, 0);

UPDATE bdtopo.edges
SET reverse_cost = 1.15 * ST_Length(geom)
           / NULLIF(vitesse_moyenne_vl::double precision * 1000.0 / 3600.0, 0);

Costs are expressed in seconds.

Intersections are free (if a node exists ofc), speeds are approximate, results are optimistic.

4. Graph Vertices

This is the point layer representing all start/end points of edges.

CREATE TABLE bdtopo.edges_vertices AS
SELECT *
FROM pgr_extractVertices(
  $$SELECT id, geom FROM bdtopo.edges ORDER BY id$$
);

Always add indexes for performance, well not always but when it makes sense.

CREATE INDEX edges_vertices_gix ON bdtopo.edges_vertices USING GIST (geom);
CREATE INDEX edges_vertices_id  ON bdtopo.edges_vertices (id);

For each edge (line), find the start and end point and link to the corresponding vertex id (calculated previously).

UPDATE bdtopo.edges e
SET source = v.id
FROM bdtopo.edges_vertices v
WHERE ST_StartPoint(e.geom) = v.geom;

UPDATE bdtopo.edges e
SET target = v.id
FROM bdtopo.edges_vertices v
WHERE ST_EndPoint(e.geom) = v.geom;

The result is visible in QGIS.

cost and reverse_cost are in seconds.

5. Snapping Origin Points

In the example we use fire stations as origin points (these are not the real locations).
You only need a table with points and an id column.

ALTER TABLE bdtopo.fire_stations
  ADD COLUMN IF NOT EXISTS start_vid bigint,
  ADD COLUMN IF NOT EXISTS snap_dist_m double precision;

We need to snap them to the closest vertex in the road network.
This is an approximation; ideally we would snap to the closest point on the closest edge.

UPDATE bdtopo.fire_stations p
SET
  start_vid = (
    SELECT v.id
    FROM bdtopo.edges_vertices v
    ORDER BY v.geom <-> p.geom
    LIMIT 1
  ),
  snap_dist_m = (
    SELECT ST_Distance(p.geom, v.geom)
    FROM bdtopo.edges_vertices v
    ORDER BY v.geom <-> p.geom
    LIMIT 1
  );

As we can see, the closest vertex is not very representative of the actual road network location.

6. Isochrone Computation

The aim here is to compute polygons representing areas reachable within a set of time thresholds (5, 10, 15, 20 minutes).

First create the output table.

CREATE TABLE bdtopo.isochrones (
  fire_stations_id bigint,
  minutes int,
  geom geometry(Polygon, 2154)
);

And here is the complete query to compute the isochrones.

TRUNCATE bdtopo.isochrones;

WITH dd20 AS (
    SELECT
        p.id AS fire_station_id,
        d.node,
        d.agg_cost
    FROM bdtopo.sis p
     JOIN LATERAL pgr_drivingDistance(
        $$SELECT id, source, target, cost, reverse_cost FROM bdtopo.edges$$,
        p.start_vid,
        20 * 60,
                                              directed := false
        ) d ON TRUE
WHERE p.start_vid IS NOT NULL -- and p.id = 7 TEST ONLY
  AND d.agg_cost <= 20*60
    ),
    bucketed AS (
SELECT
    fire_station_id,
    node,
    CASE
    WHEN agg_cost <  5*60 THEN 5
    WHEN agg_cost < 10*60 THEN 10
    WHEN agg_cost < 15*60 THEN 15
    ELSE 20
    END AS minutes
FROM dd20
    ),
    reach_pts AS (
SELECT
    b.fire_station_id,
    b.minutes,
    v.geom
FROM bucketed b
    JOIN bdtopo.edges_vertices v ON v.id = b.node
    )
INSERT INTO bdtopo.isochrones (fire_station_id, minutes, geom)
SELECT
    fire_station_id,
    minutes,
    ST_Buffer(
            ST_ConcaveHull(ST_Collect(geom), 0.10),
            5
    )::geometry(Polygon,2154) AS geom
FROM reach_pts
GROUP BY fire_station_id, minutes;

Quick tip: As polygons are not donuts.

You can still use QGIS to make sure the minutes taken are visible:

click on layer properties → Symbology → Categorized
check Control feature rendering order

Here is what it looks like in QGIS :)

And vs OSM isochrone tool (ORS tools), which handles a lot more (turns, roundabouts, etc.)

For 5 and 10 minutes both sets of data are quite close, but this gets worse the further the distance.

Some explanation about the above query

For each point in bdtopo.fire_stations, compute travel-time isochrones for:

5 minutes
10 minutes
15 minutes
20 minutes

Using a road network stored in bdtopo.edges.

Costs are expressed in seconds.

SQL Workflow

-- TRUNCATE bdtopo.isochrones;

WITH dd20 AS (
    SELECT
        p.id AS fire_station_id,
        d.node,
        d.agg_cost
    FROM bdtopo.sis p
     JOIN LATERAL pgr_drivingDistance(
        $$SELECT id, source, target, cost, reverse_cost FROM bdtopo.edges$$,
        p.start_vid,
        20 * 60,
        directed := false
        ) d ON TRUE
    WHERE p.start_vid IS NOT NULL
      AND d.agg_cost <= 20*60
),
bucketed AS (
    SELECT
        fire_station_id,
        node,
        CASE
            WHEN agg_cost <  5*60 THEN 5
            WHEN agg_cost < 10*60 THEN 10
            WHEN agg_cost < 15*60 THEN 15
            ELSE 20
        END AS minutes
    FROM dd20
),
reach_pts AS (
    SELECT
        b.fire_station_id,
        b.minutes,
        v.geom
    FROM bucketed b
    JOIN bdtopo.edges_vertices v ON v.id = b.node
)
INSERT INTO bdtopo.isochrones (fire_station_id, minutes, geom)
SELECT
    fire_station_id,
    minutes,
    ST_Buffer(
        ST_ConcaveHull(ST_Collect(geom), 0.10),
        5
    )::geometry(Polygon,2154) AS geom
FROM reach_pts
GROUP BY fire_station_id, minutes;

Step-by-Step Explanation

1. Compute reachable vertices within 20 minutes (`dd20`)

pgr_drivingDistance is executed once per SIS point
Maximum distance: 20 minutes (1200 seconds)
Output nodes are filtered to agg_cost <= 20*60

Result:

(fire_station_id, node_id, travel_time_seconds)

2. Assign vertices to time bands (`bucketed`)

Vertices are classified into exclusive time ranges:

Travel time	Bucket
0–4:59	5 min
5–9:59	10 min
10–14:59	15 min
15–20:00	20 min

3. Convert vertices to points (`reach_pts`)

Each reachable node is joined to bdtopo.edges_vertices
to retrieve its POINT geometry.

4. Build polygons from points

For each (fire_station_id, minutes) group:

Points are collected
A tight concave hull is computed using alpha 0.10
A small buffer (5 m) smooths the geometry

Interpretation

These polygons represent:

Approximate envelopes of reachable network vertices

They are suitable for:

exploratory analysis
accessibility screening
visual communication

They are not precise travel-time surfaces.

Tip

If you want cumulative isochrones (0–5, 0–10, 0–15, 0–20),
And make sur the smaller isochrones are drawn on top of the larger ones.

7. Visualization with points

Here is a small snippet to extract isochrone points for visualization,
and see for each point which category (5, 10, 15, 20 minutes) it belongs to.

DROP TABLE IF EXISTS bdtopo.isochrone_points;
CREATE TABLE bdtopo.isochrone_points (
  fire_station_id   bigint,
  minutes  int,
  vid      bigint,
  agg_cost double precision,
  geom     geometry(Point, 2154)
);

CREATE INDEX isochrone_points_gix ON bdtopo.isochrone_points USING GIST (geom);
CREATE INDEX isochrone_points_idx ON bdtopo.isochrone_points (fire_station_id, minutes);

INSERT INTO bdtopo.isochrone_points (fire_station_id, minutes, vid, agg_cost, geom)
WITH dd20 AS (
  SELECT
    p.id AS fire_station_id,
    d.node,
    d.agg_cost
  FROM bdtopo.sis p
  JOIN LATERAL pgr_drivingDistance(
    $$SELECT id, source, target, cost, reverse_cost FROM bdtopo.edges$$,
    p.start_vid,
    20 * 60,
    directed := false
  ) d ON TRUE
  WHERE p.start_vid IS NOT NULL -- and p.id = 7 TEST ONLY
),
bucketed AS (
  SELECT
    fire_station_id,
    node,
    agg_cost,
    CASE
      WHEN agg_cost <=  5 * 60 THEN  5
      WHEN agg_cost <= 10 * 60 THEN 10
      WHEN agg_cost <= 15 * 60 THEN 15
      ELSE 20
    END AS minutes
  FROM dd20
)
SELECT
  b.fire_station_id,
  b.minutes,
  b.node AS vid,
  b.agg_cost,
  v.geom
FROM bucketed b
JOIN bdtopo.edges_vertices v
  ON v.id = b.node;

You can visualize the result in QGIS.
This helps you understand what can be wrong/explained with/by the network data.

Conclusion

These isochrones are approximations.
They are useful for exploration, not for precision routing—unless you have a very high-quality road network. :)

I did not work on the base data itself, it would need to be preprocessed for better results (e.g. filtering bike lanes, stairs, etc.).

In order to be used for real routing / isochrone computation, we need to:

improve snapping to edges (closest point, not vertex)
split the edges at intersections (pgr_separateTouching / pgr_separateCrossing)
handle turn restrictions
split the road into smaller segments to have better speed representation

All can be achieved with pgRouting/PostGIS functions, a good dataset and time.

links

https://docs.pgrouting.org/3.8/en
https://qgis.org/

GreHack 2025

Emilien Devos — Mon, 01 Dec 2025 14:37:09 +0000

Conference presentation

GreHack is a conference on IT security held in Grenoble. The first day is dedicated to talks in English by various speakers, as well as workshops at the end of the day. The second day is specifically devoted to CTF, which brings together several hundred people.

This year, the following topics were presented:

Network security / Hardware security
Enterprise application security
Physical security / Red team
Reverse engineering / Program analysis
IoT security
Web security / Browsers
Protocol security / Databases / Authentication
DevOps security / CI-CD / Supply chain
Advanced network analysis / Network forensics
Radio security / SDR

All of the talks are available for replay here: https://www.youtube.com/live/X-ZJH4d2tuE and the talk schedule is here: https://grehack.fr/program/

Interesting presentations

One does not simply walk into a building... or do they?

PDF of the presentation: https://blog.volkercarstein.com/grehack_2025_one_does_not_simply_walk_into_a_building.pdf

The presenter recounts a week spent physically infiltrating a company's premises without being caught or seen. The aim was to test the physical security of the company's premises.

This is quite interesting, as it highlights the fact that physical security depends heavily on the conditioning of employees, who are often the weakest link.

Exploring Browser Permissions and Exploiting Permission Hijacking

PDF of the presentation: https://albertofdr.github.io/web-security-class/browser/browser.permissions

Presentation on managing permissions (e.g. camera) in the browser, which also apply to malicious components that may be found on the site, such as an iframe. This could be the case if the iframe was injected following a website hack or a poorly configured website.

The presentation highlights the importance of defining HTTP headers in order to more finely manage the browser permissions authorised on a website. Similar to a Content Security Policy (CSP) but for browser permissions.

From YAML to Root: CI/CD Pipeline Attacks and Countermeasures

Video of the presentation: https://www.youtube.com/watch?v=YUbN6MuiuFM

The presenter explains the potential exploitation and recovery of secrets via compromised CI/CD. With different types of access, how it is possible to exfiltrate secrets each time. Focused mainly on Azure DevOps but also applies to GitHub actions.

Workshops and CTF

I was unable to participate in the workshops or the CTF because I bought my tickets at the last minute.

There were 12 workshops where participants had to solve exercises while being guided by the organiser. The exercises ranged from application exploitation to hardware.

List here: https://grehack.fr/workshops/

The CTF took place in a large room at ENSIMAG, where participants formed groups of up to eight people. The groups had to solve challenges in the following categories:

Cryptography
Reverse Engineering
Exploit
Web
Forensics
Hardware

Mood

The atmosphere was very student-oriented, and it was nice to discuss security issues with students and young workers.

Many French companies were present, including Synacktiv (a branch in Lyon), Orange, DGSE, EDF and many others: https://grehack.fr/sponsors/.

Conclusion

I was personally very pleased to have participated in this conference.

Although many of the talks did not apply to Camptocamp's field of activity, some of them still provided me with additional knowledge that I can apply in my work (see the list of presentations above).

Bonus

At the end of the day, participants in the audience can give short presentations, in the style of ‘Lightning talks’. It was almost exclusively students, and the atmosphere was rather light-hearted, with everyone laughing about the intrusions they had each managed to pull off.

These talks are called ‘Rump session’.

The one that made me laugh the most was a student who began his presentation with:

I need to eat and I don't have any money. So it's either work at Burger King or hack Burger King.

He then explained how he abused the Burger King app's coupon system to get free burgers (in very large quantities).

Securing AI in Software Development: the Lethal Trifecta

Emmanuel Belo — Wed, 05 Nov 2025 09:46:05 +0000

AI assistants are transforming how software is developed. They help automate tasks, accelerate coding, and improve quality, but they also introduce new security challenges. Understanding and managing these risks is essential to ensure AI systems operate safely. Secure AI development involves identifying where vulnerabilities arise, limiting exposure to sensitive data, and ensuring AI tools cannot be misused or manipulated.

What is the Lethal Trifecta?

A concept developed by Simon Willison that identifies three conditions that make AI systems particularly risky when they occur together. When all three conditions are present, an AI system becomes significantly more dangerous.

The Three Conditions

Exposure to untrusted content: Including hidden malicious instructions or incorrect information.
Access to private data: Including secret keys, tokens, passwords, and proprietary information.
Ability to externally communicate: The capability to send data outside a closed system.

The Risk

When all three conditions exist simultaneously, an AI assistant can:

Receive malicious commands from untrusted content
Extract or use private data
Send that data elsewhere or use it to attack systems

Risk Mitigation Strategies

The primary recommendation is to remove at least one of the three conditions.
If removal isn't possible, constrain what you can control.
Implement additional risk reduction measures when the trifecta cannot be fully broken.

Draw and edit network‑snapped tracks in OpenLayers with edittrack

Frédéric Junod — Fri, 17 Oct 2025 12:11:29 +0000

Planning a hike, a bike ride, or any route that should follow a real‑world network? edittrack lets users draw and edit tracks that snap to routing services , enrich them with elevation profiles, and manage POIs—all directly on an OpenLayers map.

This post gives you a quick tour, shows how to wire it up, and highlights tips from the built‑in demos.

What edittrack does

edittrack is a lightweight UI library focused on interactive track editing:

Snapped segments: draw lines that follow a network via routers (GraphHopper or OSRM)
Control points: add/move/delete points that define and modify segments
POIs: add metadata‑bearing points anywhere along your track
Elevation profiles: compute per‑segment XYZM profiles via profilers (Swisstopo, extract from geometry, or a fallback chain)
History: undo/redo across all edits
Densification: optional point insertion to improve geometry quality between router samples
Shadow track and mask: visualize original track state while editing and constrain drawing to an extent

The core class you’ll interact with is TrackManager.

API docs: geoblocks.github.io/edittrack/api
Live demos: simple and schm

Install

npm install @geoblocks/edittrack ol

Edittrack is ESM and ships TypeScript types
ol (OpenLayers) is a peer dependency

Quickstart

Below is a minimal setup using OSRM for routing and a profiler chain that tries to reuse segment Z values first, then falls back to Swisstopo for high‑quality elevation (if you work in Switzerland and have EPSG:2056 registered).

import Map from 'ol/Map';
import View from 'ol/View';
import TileLayer from 'ol/layer/Tile';
import OSM from 'ol/source/OSM';
import VectorLayer from 'ol/layer/Vector';
import VectorSource from 'ol/source/Vector';
import {Style, Stroke, Circle as CircleStyle, Fill} from 'ol/style';

import {
  TrackManager,
  OSRMRouter,
  ExtractFromSegmentProfiler,
  FallbackProfiler,
  SwisstopoProfiler,
  SnappedDensifier,
} from '@geoblocks/edittrack';

// Basic map and layers
const trackLayer = new VectorLayer({ source: new VectorSource() });
const shadowTrackLayer = new VectorLayer({ source: new VectorSource() });
const map = new Map({
  target: 'map',
  view: new View({ center: [0, 0], zoom: 2 }),
  layers: [
    new TileLayer({ source: new OSM() }),
    shadowTrackLayer,
    trackLayer
  ],
});

// Router (provide your own OSRM/GraphHopper URL)
const router = new OSRMRouter({
  map,
  url: 'https://router.project-osrm.org/route/v1/driving',
  // optional:
  // extraParams: 'annotations=true',
  // radius: 10000,
});

// Profiler chain (first try existing Z, then Swisstopo)
const profiler = new FallbackProfiler({
  profilers: [
    new ExtractFromSegmentProfiler({ projection: map.getView().getProjection() }),
    new SwisstopoProfiler({ projection: map.getView().getProjection() }),
  ],
});

// Optional densifier to insert points between router samples
const densifier = new SnappedDensifier({
  optimalPointDistance: 10, // meters
  maxPointDistance: 80,
  maxPoints: 8000,
});

// Style (use your own StyleLike/FlatStyleLike)
const style = [
  new Style({ stroke: new Stroke({ color: '#2563eb', width: 3 }) }),
  new Style({ image: new CircleStyle({ radius: 5, fill: new Fill({ color: '#111827' }) }) }),
];

// Track manager
const tm = new TrackManager({
  map,
  trackLayer,
  shadowTrackLayer,
  router,
  profiler,
  densifier,
  style,
  hitTolerance: 10, // px
  // deleteCondition, addLastPointCondition, addControlPointCondition are optional
  // drawExtent, drawMaskColor are optional
});

// Start editing
tm.mode = 'edit';

// Listen to changes (update a profile or UI)
tm.addTrackChangeEventListener(() => {
  // e.g., recompute a merged elevation profile using tm.getSegments()
});

// Add an initial point by letting users click on the map (TrackInteraction handles it)

Working with the track

Add/move points: in edit mode, click to add control points; drag points or segments to update routing
Toggle snapping: tm.snapping = true | false (if false, segments become straight lines)
Undo/Redo: await tm.undo() / await tm.redo()
Reverse: await tm.reverse(true) to re‑route, or await tm.reverse(false) to just flip geometry and refresh profiles
POIs:
- Add: tm.addPOI([x, y], {name: 'Café'})
- Update meta: tm.updatePOIMeta(index, meta)
- Delete: tm.deletePOI(index)
Save/restore state:

  const snapshot = [
    ...tm.getControlPoints(),
    ...tm.getSegments(),
    ...tm.getPOIs(),
  ];
  await tm.restoreFeatures(snapshot);

Hover feedback: subscribe and map distance to your UI (e.g., highlight point on a chart)

  tm.addTrackHoverEventListener((distanceFromStart) => {
    // distanceFromStart in meters along the full track (if available)
  });

Constraining drawing and visual aids

Draw extent and mask: pass drawExtent and optionally drawMaskColor to restrict editing to a region and render a mask overlay
Shadow track: when entering edit mode, the current track is cloned into shadowTrackLayer so you can see what changed

Routers and profilers

Routers:
- GraphHopper (GraphHopperRouter): snaps segments to a GraphHopper backend
- OSRM (OSRMRouter): snaps via OSRM; supports radius, extraParams, and pixel‑based maxRoutingTolerance inherited from RouterBase
Profilers:
- SwisstopoProfiler: high‑quality elevation profile for CH (register EPSG:2056)
- ExtractFromSegmentProfiler: reuse geometry Z values if present
- FallbackProfiler: try multiple profilers in order and use the first that succeeds

Each routed segment stores:

segment.get('snapped'): boolean
segment.get('profile'): Coordinate[] with [x, y, z, m], where m is cumulative distance from the segment start
segment.get('surfaces'): optional array of surface ranges (when supported by the router)

Tips from the demos

Densification improves elevation smoothness and downstream analytics by adding intermediate points while keeping a hard cap (maxPoints) to avoid oversized geometries
Keep hitTolerance between 10–20 px for easier selection on touch devices
If you want to require a modifier key to delete, pass a deleteCondition that checks the event (see demos/simple/demo.js)

TypeScript and modules

Full type definitions are published under lib/types
Package is ESM‑only; use modern bundlers (Vite, Parcel, Webpack 5+, etc.)

License

BSD‑3‑Clause

Postgres psql on Windows: SSL SYSCALL error: EOF detected expected authentication request from server, but received H

Oliver Christen — Wed, 30 Jul 2025 13:24:33 +0000

Some recent (as of july 2025) Windows update has apparently broken old version of Postgres psql binaries, when trying to connect to an external database with SSL enabled, you may get the following kind of error:

pg_restore: error: connection to database "xxx" failed: SSL SYSCALL error: EOF detected
expected authentication request from server, but received H"

The solution is "simply" to use the latest version of the psql binary.
Version 17 has been tested ok.

This of course also apply to pg_dump/pg_restore.

Querying Overture Maps GeoParquet Directly in the Browser with DuckDB WASM

Florent Gravin — Fri, 16 May 2025 12:32:04 +0000

Overture Maps provides a rich, open and collaborative dataset of geospatial features that’s designed to power everything from routing to visualization. These datasets are distributed in GeoParquet format : cloud-native, efficient, columnar, and increasingly becoming a standard for geospatial data at scale.

But what if you could explore and query these massive datasets directly in your browser - without any server-side processing or backend setup? Thanks to DuckDB-WASM, you can.

Demo
Code

In this post, we’ll walk through how to:

Load an Overture Maps GeoParquet file into the browser
Query it using SQL, right from the front-end
Visualize features on a map

Let’s dive in. 🦆

Tools and Technologies

DuckDB-WASM: A full DuckDB engine compiled to WebAssembly, enabling in-browser analytics.
GeoParquet: Parquet format with additional metadata to support geometry and spatial operations.
Overture Maps: A collaborative open map dataset from companies like Meta, Microsoft, Amazon, and TomTom.
MapLibre GL JS: For rendering geospatial features.

Step 1: Load DuckDB WASM

Include DuckDB-WASM in your HTML or install it via npm:

npm install @duckdb/duckdb-wasm

And initialize it:

import * as duckdb from '@duckdb/duckdb-wasm'
import duckdb_wasm from '@duckdb/duckdb-wasm/dist/duckdb-mvp.wasm?url'
import mvp_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-mvp.worker.js?url'
import duckdb_wasm_next from '@duckdb/duckdb-wasm/dist/duckdb-eh.wasm?url'
import eh_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-eh.worker.js?url'

const MANUAL_BUNDLES = {
  mvp: {
    mainModule: duckdb_wasm,
    mainWorker: mvp_worker,
  },
  eh: {
    mainModule: duckdb_wasm_next,
    mainWorker: eh_worker,
  },
}

const bundle = await duckdb.selectBundle(MANUAL_BUNDLES)
const worker = new Worker(bundle.mainWorker)
const db = new duckdb.AsyncDuckDB(logger, worker)
await db.instantiate(bundle.mainModule, bundle.pthreadWorker)

Step 2: Fetch and Load the GeoParquet File

Disclaimer : As DuckDB WASM does not support HTTPFS extension you can't directly hint the official Overture Maps GeoParquet releases, which provide root level S3 file system entry.
You should extract a subset of the datasets and host them in your web application, you could use the Python CLI for that.

Then, load the spatial extension to process the GeoParquet, and init your DuckDB connection.

conn.query('INSTALL spatial;LOAD spatial;')
conn.query('INSTALL h3 FROM community;LOAD h3;')
const conn = await db.connect()

Step 3: Query Your Data

Run SQL queries directly in the browser:

const res = await conn.query(`
WITH areas AS (
  SELECT names.primary as name,
         geometry as area_geom
  FROM read_parquet('https://your.parquet.path/division_area.parquet', filename=true, hive_partitioning=1) 
  WHERE subtype = 'locality'
    AND region = 'US-UT'
),
schools AS (SELECT geometry as place_geom
  FROM read_parquet('https://your.parquet.path/places.parquet', filename=true, hive_partitioning=1) 
  WHERE categories.main = 'hotel'
)
SELECT name, ST_AsGeoJSON(area_geom) as geometry,
       CAST(count(place_geom) as INT) as count
FROM areas
       LEFT JOIN schools ON ST_Contains(area_geom, place_geom)
GROUP BY area_geom, name
`)

This query fetches Overture division areas of Utah, and performs a spatial aggregation, it counts every hotel within each locality.

Step 4: Visualize on a Map

Convert geometries to GeoJSON and render them:

const data = {
  type: 'FeatureCollection',
  features: res.toArray().map((d: DuckResponseObject) => {
    const { geometry, ...properties } = d
    return {
      type: 'Feature',
      geometry: JSON.parse(geometry) as Polygon,
      properties,
    }
  }),
}

map.addSource('places-area-sources', {
  type: 'geojson',
  data,
})

map.addLayer({
  id: 'places-area',
  type: 'fill',
  source: 'places-area-sources',
  paint: {
    'fill-color': ['interpolate', ['linear'], ['get', 'count'], ...colorGradient],
    'fill-outline-color': 'grey',
    'fill-opacity': 0.8,
  },
})

Where colorGradient is a computed classification depending on count distribution.

Et voila ! You have a beautiful choropleth map, from Overture Maps datasets, with no backend implied 👏

Cesium Guess

Frédéric Junod — Mon, 28 Apr 2025 11:39:07 +0000

Introduction

Welcome to Cesium Guess, an interactive web application that challenges you to guess locations on a map. This project leverages powerful mapping technologies to provide an engaging and educational experience. In this blog post, we'll explore what Cesium Guess does, the technologies it uses, and how you can get started with it.

What is Cesium Guess?

Cesium Guess is a web-based game where players are presented with a random location in Switzerland and must guess where it is on the map. The game provides a fun way to test and improve your geographical knowledge of Switzerland. After making a guess, the game shows the actual location and the distance between your guess and the correct location.

Key Features

Random Location Generation: The game uses a random position generator to place the camera at a random location within Switzerland.
Interactive Map: Players can interact with the map to make their guesses.
Distance Calculation: After guessing, the game calculates and displays the distance between the guessed location and the actual location.
Responsive Design: The game is designed to work seamlessly on various devices, including desktops and mobile devices.

Technologies Used

Cesium Guess is built using a combination of modern web technologies and powerful mapping libraries. Here are the key technologies used in this project:

CesiumJS

CesiumJS is a JavaScript library for creating 3D globes and 2D maps in a web browser without a plugin. It provides the core functionality for rendering the 3D globe and handling interactions.

CesiumWidget: The main component for rendering the 3D globe.
ImageryLayer: Used to display map imagery.
CesiumTerrainProvider: Provides terrain data for the globe.

OpenLayers

OpenLayers is a high-performance, feature-packed library for creating interactive maps. It is used in this project to handle the 2D map interactions.

Map: The main component for rendering the 2D map.
TileLayer: Used to display map tiles.
VectorLayer: Used to display vector data, such as points and lines.

Turf.js

Turf.js is a JavaScript library for spatial analysis. It is used in this project to generate random positions and check if they are within Switzerland's boundaries.

randomPosition: Generates random geographical positions.
booleanPointInPolygon: Checks if a point is within a polygon.
bbox: Calculates the bounding box of a polygon.

Vite

Vite is a build tool that provides a fast development environment and optimized build process. It is used to bundle and serve the application.

Web Components

The project uses Web Components for creating reusable UI elements, such as the compass bar and dialog boxes.

Getting Started

To get started with Cesium Guess, follow these steps:

Clone the Repository: Clone the project repository from GitHub.

   git clone https://github.com/fredj/cesium-guess.git
   cd cesium-guess

Install Dependencies: Install the required dependencies using npm.

   npm install

Run the Development Server: Start the development server using Vite.

   npm run start

Open the Application: Open your web browser and navigate to http://localhost:3000 to start playing the game.

Conclusion

Cesium Guess is a fun and educational project that combines modern web technologies with powerful mapping libraries. Whether you're a geography enthusiast or just looking for a fun way to pass the time, Cesium Guess offers an engaging experience. We hope you enjoy playing the game and exploring the beautiful landscapes of Switzerland!

For more information and to try the online demo, visit the Cesium Guess GitHub page.

Happy guessing!

DEV Community: Camptocamp Geospatial Solutions

Incremental Backup with PostgreSQL 17

The Setup

The main idea:

1. Initial full Backup

Some info about the pg_basebackup command

Output of the command

Understanding the Checksum

2. Now Let’s Increment!

Integrating Incremental Backups for Restoration

RTO (Recovery Time Objective) and RPO (Recovery Point Objective):

Summary:

FOSDEM 2026

Vibe

Interesting discussions

Talks

Geospatial

Infrastructure

Garage Object Storage: 2.0 update and best practices

PostgreSQL and MySQL, Two Databases, Three Perspectives

Building ISOs from OCI containers

Other very interesting talks

The Servo project and its impact on the web platform ecosystem

Who Pays Your Bills? Sustainability, Community, and Business: The Open Source Triangle

To view the other 1,073 talks

Adding SonarQube to an AI-Assisted Development Workflow

The setup

How the loop works in practice

Why Sonar and not just a linter

What this looks like day-to-day

Closing thoughts

🧠 Lessons Learned: Multi-Platform Intelligence Suite

1. API Costs: Discover, Measure, Then Mitigate

2. The Value of Agent-Delegated Complexity

3. Architecture Decision: Map-Reduce for Local Models

4. Cloud-to-Local: Architectures Don't Scale Down

5. Model Selection: VRAM is the Real Constraint

6. Prompt Engineering: Strictness Over Creativity

7. Code Quality: SonarCloud as Your External Auditor

8. Git Strategy: Branches for Architectural Changes

9. Beyond Vibe Coding: Agentic Technical Direction

10. Leveling Up: Skills for Agentic Technical Direction

What to Learn: Git and Version Control

What to Improve: The Skill of "Scaffolding"

What to Study: Fundamentals of Software Architecture

The Superpower to Cultivate: The "Stop and Inspect" Reflex

11. Conclusion: Learning by Doing with an AI Partner

Multi-Line Editing in edittrack: Work with Multiple Routes Simultaneously

What multi-line editing offers

Real-world use cases

Multi-day trips

Alternative routes

Route networks

Activity segments

Version control and experimentation

Working with multiple parts: the API

Creating new parts

Switching between parts

Managing parts

Automatic switching on drag

Getting data from all parts

Iterating through parts

History management

Visual feedback and user experience

Try it yourself

Links

License

[PostGIS/PgRouting] With french routable data

Road-network isochrones with PostGIS & pgRouting (Docker)

1. PostgreSQL + pgRouting in Docker

2. Preparing the Road Network

3. Travel Time (Length × Speed)

4. Graph Vertices

5. Snapping Origin Points

6. Isochrone Computation

Some explanation about the above query

SQL Workflow

Step-by-Step Explanation

1. Compute reachable vertices within 20 minutes (dd20)

2. Assign vertices to time bands (bucketed)

Some info about the `pg_basebackup` command

1. Compute reachable vertices within 20 minutes (`dd20`)

2. Assign vertices to time bands (`bucketed`)

3. Convert vertices to points (`reach_pts`)