DEV Community: Sam

Need webhooks? Just use the Postgres HTTP extension

Sam — Thu, 18 Dec 2025 00:00:00 +0000

Reducing complexity

I previously wrote a blog aboutusing pg_notify and a Golang listenerto create a webhook based on Postgres events.

I no longer believe this is the best approach, after trying out thepgsql-http extensionfor this use case.

The http extension is super simple:

Ensure the extension is installed and enabled.
Create an SQL trigger for an event in your database.
Call a function from your trigger that does a http call (GET, POST, etc possible).
You can send a payload to an external webhook, including DB data, and even an X-API-Key header for authentication (or whatever you use in your API).

Now we no longer need an external service / listener running to wait for the events. Everything can be handled entirely in Postgres (a pattern that I am really loving recently, from cron jobs, webhooks, ML embeddings, etc).

How this works in practice

Adding the extension

A custom dockerfile is very simple:

FROM postgres:18 AS pg-18

RUN apt-get update \
    && apt-get install -y postgresql-18-http \
    && rm -rf /var/lib/apt/lists/*

You can use the same logic to add the extension to a Debian server install.
I actually build and publish pre-built images based on this exact logic - simply adding the extension into the official imagehere

For example: docker pull ghcr.io/hotosm/postgres:17-http

Adding a database trigger

If you manage you own database schema, you can add functions and triggers to your hearts content. Just add a http_request.

PERFORM http((
    'POST',
    'https://some.url.com/endpoint',
    http_headers('Content-Type', 'application/json'),
    'application/json',
    webhook_payload::text
)::http_request);

I was adding this to an existing database schema forODK Central, so instead opted to build a small Golang CLI for installing and uninstalling the trigger, based on certain events.

For the example implementation, seethis repo

How to add translations to the MapLibre UI?

Sam — Wed, 12 Nov 2025 00:00:00 +0000

How do translations work in MapLibre

MapLibre has very little built-in UI to speak of, as it’s mostly for displaying map data in whichever language the user decides.
However, there are a small number of control prompts, help texts, and tooltips to consider:

As of 2025-11, this consists of:

export const defaultLocale = {
  "AttributionControl.ToggleAttribution": "Toggle attribution",
  "AttributionControl.MapFeedback": "Map feedback",
  "FullscreenControl.Enter": "Enter fullscreen",
  "FullscreenControl.Exit": "Exit fullscreen",
  "GeolocateControl.FindMyLocation": "Find my location",
  "GeolocateControl.LocationNotAvailable": "Location not available",
  "LogoControl.Title": "MapLibre logo",
  "Map.Title": "Map",
  "Marker.Title": "Map marker",
  "NavigationControl.ResetBearing": "Reset bearing to north",
  "NavigationControl.ZoomIn": "Zoom in",
  "NavigationControl.ZoomOut": "Zoom out",
  "Popup.Close": "Close popup",
  "ScaleControl.Feet": "ft",
  "ScaleControl.Meters": "m",
  "ScaleControl.Kilometers": "km",
  "ScaleControl.Miles": "mi",
  "ScaleControl.NauticalMiles": "nm",
  "GlobeControl.Enable": "Enable globe",
  "GlobeControl.Disable": "Disable globe",
  "TerrainControl.Enable": "Enable terrain",
  "TerrainControl.Disable": "Disable terrain",
  "CooperativeGesturesHandler.WindowsHelpText":
    "Use Ctrl + scroll to zoom the map",
  "CooperativeGesturesHandler.MacHelpText": "Use ⌘ + scroll to zoom the map",
  "CooperativeGesturesHandler.MobileHelpText":
    "Use two fingers to move the map",
};

Currently, the user is required to create their own translation file, then import and use it during MapLibre object instantiation:

import maplibregl from "maplibre-gl";
// fr.ts contains the same content as above, translated
import { fr } from "./locales/fr.ts";

new maplibregl.Map({
  container: "map",
  style: "https://demotiles.maplibre.org/globe.json",
  center: [0, 0],
  zoom: 2,
  locale: fr, // Use the translation here
});

Introducing maplibre-ui-translations

In a web app I am developing, it is required for the user to change language dynamically, via a dropdown selector.
In order to simplify this, I decided to make a community plugin, maplibre-ui-translations.
The plugin is simple and has a single purpose: provide community-driven translations for the MapLibre UI elements in various languages, allowing the user to either: a. Change to a single different language. b. Support changing language via a dropdown selector or similar.
It handles the re-rendering of the MapLibre UI on language change via the helper function updateMaplibreLocale.

Example:

import maplibregl from "maplibre-gl";
import { defaultLocale } from "maplibre-gl/src/ui/default_locale";
import {
  updateMaplibreLocale,
  maplibreLocales,
} from "maplibre-ui-translations";

const map = new maplibregl.Map({
  container: "map",
  style: "https://demotiles.maplibre.org/globe.json",
  center: [0, 0],
  zoom: 2,
  locale: defaultLocale,
});

document.querySelector("#lang-switcher")?.addEventListener("change", (e) => {
  const selectedCode = (e.target as HTMLSelectElement).value;
  updateMaplibreLocale(map, selectedCode);
});

Your help is needed!

If you are bilingual and could assist with the community translations, I have made a Weblate project for it.
The current translations are mostly machine translations, so validation of those would also be appreciated!
The plugin can be found in the MapLibre Plugin docs

Hacking the Potensic Atom drone to fly pre-generated waypoint missions

Sam — Sun, 11 May 2025 00:00:00 +0000

1. Overview: Drone Tasking Manager (DroneTM)

About a year ago, a joint initiative between theHumanitarian OpenStreetMap Team (HOT) and NAXA began the development of the Drone Tasking Manager (DroneTM).

What is DroneTM?

DroneTM is a tool to collaboratively generate base imagery as an alternative to traditional satellite sources. It enables subdivision of an area into ‘tasks’ and then generation of flight plans for teams of local drone operators to fly simultaneously.
Niraj, the lead dev for DroneTM at NAXA already wrote agood blog explaining the idea, so there is no need for me to reiterate here.

Extra Links

Initial research for the tool here
Code repo here.

Hardware Support

The project initially focused on the DJI Mini 4 Pro—a highly capable, sub-250g drone that supports precise waypoint missions (~$1000).
Long-term, HOT aims to build an open-source cheap drone optimised for photo capture only, and using open-source ArduPilot as the flight controller.
In the short term, expanding drone support with similar cheap drones is critical for adoption.

2. The Potensic Atom Series

Potensic’s Atom 1 and Atom 2 offer affordable alternatives (~1/3 the cost of DJI), with promising hardware.

Here we have the Potensic Atom v1. You can clearly see the scuffed blades where I crashed it into a plant pot on landing 😂

Comparison

Atom 1 : Supports waypoint missions with the current firmware.
Atom 2 : Technically superior hardware (we suspect is uses the same photo sensor as the DJI Mini 4 Pro), but as of 2025-05-11, it does not yet support waypoint missions. Firmware update is expected later this year.

Note
Potensic currently imposes a 30 waypoint limit per mission in it’s apps. This needs further testing to assess if it’s a hard or soft limit.

3. Technical Exploration

Existing Attempts

An excellent community project by @koen-aerts reverse-engineered the proprietary flight log format for Potensic drones:

Can visualise flight logs via a Kivy app, replaying the flight parameters and drone location as an interactive movie.
Offers alpha support for waypoint mission loading/saving, primarily by clicking on a map, so it’s not overly sophisticated (yet).
See more details on his blog here

Waypoint Planning in DroneTM

DroneTM already contains aflight plan generation moduleflight plan generation module, that is used to generate waypoint and wayline missions.
The primary output candidate is DJI Waypoint Markup Language, however, the remainder of this blog will look into the potential for outputting to a format supported by the Potensic drones.

Storing Waypoints In The Atom

The `map.db` Waypoint File

The flow to consider here is: PotensicPro Android app (phone) --> Flight controller –> Atom Drone.
It looks like the PotensicPro stores the waypoint missions in a map.db SQLite database.
Upon further exploration, this is a pretty half-baked / unsophisticated approach, but it works! And you will see no complaints from me, because they could very well have added no waypoint support at all!
The file is located on the phone, reading an writing to it as needed:/data/data/com.ipotensic.potensicpro/databases/map.db

Note

Android File Access: A Quick Primer

An aside on how Android sanboxed filesystem works per-app since Android 14.

There are two storage options for apps:

Each app gets it own sandboxed storage to work with, to prevent privileged access to the entire filesystem: /data/data/<package.name>/.

Shared storage is also available if permission is granted, under:/storage/emulated/0/Android/data/<package.name>/.

However:

In order to copy the map.db file across to a place on the phone that the PotensicPro app can read, we need to copy to this sandboxed filesystem.

The sandboxed filesystem is only accessible directly from the app code , unless the device is rooted, or the app uses a debuggable flag in it’s manifest (see more info below).

We have two options:

Mod the PotensicPro app further, to read map.db from shared storage instead.
Use ADB to interact with the sandboxed storage, via ‘run-as’ the required user.

4. Implementing Waypoint Generation

An Existing Modded App

Option 1 above is probably not a maintainable solution into the future.
There are already modded versions of PotensicPro floating around, with one of the best coming from ‘e443mods’ telegram community:
- Changes the height, waypoint & distance limits to 10000m.
- Removes unnecessary trackers.
- Changes the transmission mode to the maximum possible.
I also modded the modded APK 😆 to add thedebuggable flag to AndroidManifest.xml , as described above.
- I’m not sure I can legally host and distribute this, but suffice to say it’s out there.

The process to make an existing APK ‘debuggable’:

# Gen a new signing key
docker run -it --rm -v $PWD:/home/makedebuggable -u makedebuggable ghcr.io/spwoodcock/make-debuggable:2025-05-11 /scripts/keygen.sh

# Mod the APK to be debuggable
docker run -it --rm -v $PWD:/home/makedebuggable -u makedebuggable ghcr.io/spwoodcock/make-debuggable:2025-05-11 /scripts/makeDebuggable.py apk PotensicPro-V6.6.1
-E443.apk PotensicPro-V6.6.1-E443-debug.apk debuggable.keystore debuggable pwpwpw

This allows us to access the sandboxed PotensicPro app files, including the waypoint database.
Android USB debugging must be enabled via the phone developer options.
This isn’t overly user friendly, so I will probably look intoADB In The Browser for DroneTM going forward.

Inspecting The Waypoint Database

The first step was to fly a waypoint mission manually via PotensicPro.
Some important info before we attempt to get the file:
- When using run-as com.ipotensic.potensicpro in ADB we cannot access anything other than the sandboxed filesystem (no shared storage access…).
- When using the standard user, we cannot access the sandboxed PotensicPro storage… a catch-22.
So, to copy the generated map.db file from the sandbox onto our filesystem, instead of copying to an intermediary location then using adb pull, we can pipe the file content directly to our machine via terminal:

# First ensure the db is not being accessed
./adb shell am force-stop com.ipotensic.potensicpro

# This approach doesn't work due to permission issues
#./adb shell run-as com.ipotensic.potensicpro cp /data/data/com.ipotensic.potensicpro/databases/map.db /sdcard/Android/data/com.ipotensic.potensicpro/files/

# This works (we use base64 encode to avoid encoding issues between shells, e.g. powershell vs bash, see below)
./adb exec-out run-as com.ipotensic.potensicpro base64 /data/data/com.ipotensic.potensicpro/databases/map.db > map.db.b64
# I then copied this across to WSL decoding base64
dos2unix map.db.b64
base64 -d map.db.b64 > map.db

# For a Linux-only workflow, it would be simpler to just:
cat map.db | ./adb exec-in run-as com.ipotensic.potensicpro /data/data/com.ipotensic.potensicpro/databases/map.db

A note about shell encoding… how I wasted 2hrs of my life.

Originally I was using:./adb exec-out run-as com.ipotensic.potensicpro cat /data/data/com.ipotensic.potensicpro/databases/map.db > map.db

This failed as I was testing using Powershell instead of bourne / bash. > After some hex inspection I noticed the encoding was wrong in the copied file headers.

In PowerShell, using redirection (>) causes the new files to be encoded in Windows UTF-16LE encoding, while Unix-like filesystems require UTF-8. > Making the file not a valid SQLite file.

So I swapped to the base64 encoding approach above, that should be cross compatible between different terminals!

Creating A Waypoint File

For now I implemented this as a test only, with hardcoded coordinates.
In the long run I will tie this into the existing code in drone-flightplan:
I made a start on this, replicating the SQLite database table structure, and inserting a dummy waypoint flight plus hardcoded coordinates for my flight in London today.

Out of interest, here is a crappy orthophoto I generated by simply flying around my area and taking some test photos. The output will no doubt be much better using a regular interval consistent bearing of a waypoint flight, but it gives you an idea!

See Stay Tuned below to follow up on the progress here.

Limitations

The SQLite format used isn’t particularly sophisticated. Basically we have:

A simple table to reference a mission, with a set height and other metadata.
A linked table storing lat/lon pairs for each waypoint in the mission.

As a result, three major limitations has been identified:

The mission altitude is set at a single level for the entire flight.
Photos must be taken using the interval timer feature, as taking a photo at each waypoint cannot be programmed.
The gimbal angle also cannot be adjusted automatically, meaning two runs of the mission would be required for both nadir (~85°) and oblique (~45°) imagery capture.

DJI obviously have an advantage here and clearly have a big team of software engineers at their disposal. The Waypoint Markup Language (WPML) is a very professional and infinitely configurable spec in comparison.

Importing The Generated File To A Phone

Copying back to the correct place on a phone:

# Cleanup old journal files
./adb shell run-as com.ipotensic.potensicpro rm -f databases/map.db-journal

# Overwrite db
base64 map.db > map.db.b64
# Linux
./adb shell run-as com.ipotensic.potensicpro sh -c "'base64 -d > databases/map.db'" < map.db.b64
# Powershell
Get-Content -Raw "map.db.b64" | ./adb shell run-as com.ipotensic.potensicpro sh -c "'base64 -d > databases/map.db'"

Next, PotensicPro should be opened, the waypoint map opened, then on the right there should be an option to view ‘previously flown’ waypoint missions.
Click on the generated flight, then opt to ‘fly it again’ (in reality, for the first time).
Note that before starting the mission, the interval timer should be set to an interval of ~2 - 3 second shots, else you will get no imagery!

ADB In The Browser

Requiring the user to install and use adb isn’t user friendly / accessible.
Asking users to also enable USB debugging also isn’t ideal… but I think this will still be required in any environment we use. It’s not a huge dealbreaker, as it’s a one time only action.
There is a very interesting ADB implementation in JavaScript calledTango.
I will definitely be exploring this further and implementing a simple workflow to:
- Generate flightplans in the DroneTM web app.
- Click ‘transfer to device’.
- Have the flightplan automatically copied to the necessary place to be picked up.
If Potensic released an SDK then this wouldn’t be necessary. But I would say that is highly unlikely.
DJI actually recently released an SDK for the Mini 4 Pro, that we will be integrating into our DroneTM workflow soon!

Stay Tuned

The creating a waypoint file section doesn’t include all the info for brevity, and because the work is ongoing 😄
Follow progress on the implementation in thislinked pull request
I will be testing the generated waypoint files work on probably the weekend 17th May.
From there, it will be a simple matter of hooking up the waypoint generation logic, and adding it as a drone option in DroneTM.

Programming languages in 2025: what we have now and future predictions

Sam — Tue, 22 Apr 2025 00:00:00 +0000

So What Languages Do We Have: Backend

Right off the bat:

I am coming at this with an extreme bias for web development.
Many people may disagree with my assessments, particularly if not focusing on web development.

Java

I won’t dig into this too much.
Java was a great language up to mid 2010’s.
Now there are many more objectively better alternatives, when it comes to anything web related at least.
For many modern web services, the Java Virtual Machine (JVM) feels like an unnecessary abstraction and overkill. It consumes a lot of memory, has higher startup times, and is generally a bit inefficient.
Other languages such as Golang could replace this easily, with easier syntax and better performance.
Usage is in decline, but Java still has a huge market and foothold however (it’s used lot in enterprise, high-performance computing, financial services).

Kotlin

Kotlin complicates this a bit - it’s sparked new interest in the JVM world.

It improves on Java with more concise syntax, better null safety, data classes, and more.

That said, it still runs on the JVM…

C, C++

C is the current standard for low-level performant critical code.
It can run anywhere: embedded systems, backend web API, etc.
It’s also dominant in systems programming, embedded development, and OS kernels.
We can also bind to C from any other language easily, making it a great candidate for low-level ‘write-once, bind-many’ libraries.
C++ is an extension of C with other advanced features and more capabilities like an Object-Oriented model, templates, and RAII.
Many low-level libraries are written in C++, for example the excellent GEOS library for processing geospatial vector data.

Rust

Rust is a C and C++ replacement, with key advantages being memory safety , no garbage collector, better concurrency, enforced type safety, and no runtime requirement (C++ needs a runtime, while Rust is entirely compiled to native machine code).
While we have a huge amount of performance critical libs written in C and C++, any future libraries should probably be written in Rust.
While it’s possible to bind to C/C++ libs from Rust, ideally existing libraries will be ported to Rust into the future.
- The georust community is hard at work replacing many low level geospatial libraries in Rust, such as GEOS.
- Maybe osgeo/GDAL one day?
- Replacing SQLite C library with Limbo

Golang

Rust is a “better” language when it comes to performance and code quality.
The key tradeoff here is simplicity: Golang goes most of the way to being an excellent performant modern programming language, while keeping concepts reasonably simple and syntax easy to read / write.
Go has a very good standard library built in, and is excellent at networking.
Huge tools like Kubernetes and the ecosystem around containers are all written in Go, so it’s no going anywhere soon!
If I had to write a small microservice / web API quickly and easily maintainable, I would use Golang. I did just that in a recent project.

Python

Python is a fantastic high-level language with a huge community.
It was the first language that I learned.
It’s continually evolving and improving, moving from its status as a simple scripting language, to running many much more serious applications these days:
- Web APIs with acceptable performance for most.
- The machine learning community has consolidated around Python.
- Anything else you can think of!
The introduction of type hinting and other improvements from other languages have ensured Pythons spot at the top of the easy-to-use / modern and maintainable tradeoff list.
For writing actual applications that leverage low-level languages underneath, or require building a community around them, Python is still my go-to language (for now).

Where Does Frontend Fit In?

There are two programming languages available in web browsers:

JavaScript / TypeScript : what the web is made of currently (alongside HTML/CSS).
WebAssembly : this is pretty much ‘backend code, in frontend’!

JavaScript… On The Backend??

NodeJS introduced JavaScript for backend programming.
We have seen the rise of many full integrated libraries covering both backend and frontend for web development, such as SvelteKit, Nest, Nuxt, etc.
We have also seen new JS engines, such as Deno and Bun , to fix some of the underlying issues with NodeJS.
But without going too deep, JavaScript just isn’t a great backend language. It’s single-threaded (aside from worker threads), uses prototype-based inheritance, and leans heavily on callbacks - all of which can complicate large codebases.
TypeScript solves the weak typing problem, but that only goes so far.
As an alternative, I would advocate for:
- Simple web apps: use Python or Golang to write a simple HTMX server.
- More complex web apps, requiring real-time or instant responsiveness / reactivity: use a local-first web development approach (future blog about this!), for offline-ready apps that sync directly with a database.

JavaScript vs Python

These days it’s a really tough call for which one to focus on.

Python is easy and has a large community. It also has many niche applications where it beats JS (like machine learning libraries).

However, JavaScript is equally capable, while being useful for development across the entire stack. JavaScript is also catching up on the usage of models directly in-browser, with potential for model generation done entirely in-browser too! Check out transformers.jsand onnxruntime if interested.

If you are focusing on local-first web development, then using JavaScript is probably your primary concern.

WebAssembly

Typically WebAssembly isn’t written directly, but instead one of the backend languages listed above are compiled into WebAssembly.
Out of all the awesome backend languages I listed above, these are the primary candidates:
- C/C++ : emscripten can do this, but we introduce two problems:
  1. Interfacing directly with the library from JavaScript means handling memory usage carefully and is not very user friendly.
  2. We write a user-friendly wrapper for the low-level API, that has to be maintained into the future.
- Rust : was early to adopt WebAssembly as a first-class target and provides excellent tooling for compiling to it. It handles the issues around memory safety, meaning we can write our library in Rust, compile to WebAssembly, and essentially start using it directly in the web.
- Golang : can also compile to WebAssembly and is a pretty decent, but less performant than Rust. The main problem is that it’s a garbage-collected language, meaning lots of superfluous code also needs to be compiled alongside your code (wasm_exec.js adds ~300kb). If doing this, I would recommend TinyGo

Summary

What to write (or learn) in 2025 and going forward:

Rust : low level systems programming, or performant library to bind from higher level languages, including WebAssembly to run in the web. Complex & requires skill to use.
Golang : willing to sacrifice some of the benefits of Rust for speed and ease of development. Ideal for small projects, startups, demos, microservices, etc.
Python : if writing simple websites (most of the web!) that could use the simplicity of HTMX. Or if working with machine learning / modelling. Or for writing scripts, where Python really shines.
JavaScript : if you are writing user-facing application, this is the language to learn. Particularly if you wish to make a web app that has a very modern feel and leverages the advantages of local-first web development.

As Promised, Some Prediction

C & C++ usage will eventually decline in favour of Rust, for low-level performant libraries. A good example could be the migration of GEOS (C) to GeoRust (Rust). I'm far from the first to point this out - it’s a growing trend (even the Linux kernel may have some Rust modules...).
The web ecosystem is diverging from the traditional 'backend' API-driven model toward local-first JavaScript approaches (compute and data in the browser).
Python usage will remain high, but JavaScript will dominate further in the web domain, as backend APIs (± ML / LLM) are swapped in favour of WASM compiled libraries directly in the frontend (Postgres in the browser anyone?).
Personally, I think a safe bet would be to double down on the JS / Rust combo.

Bonus: Other Languages

Elixir : tightly integrated with web development and particularly well-suited to high-concurrency applications like real-time collaboration tools due to it’s BEAM VM usage. Electric-SQL is built with this, and I love it!

I didn’t mention unrelated languages, such as Swift for iOS development, Kotlin for Android development, and other JVM-based languages like Scala or Clojure.

Bonus: A Note About Systems Programming

In an ideal world, much legacy C/C++ could be migrated to Rust. However, this isn’t a realistic goal, throwing away years of work on open-source projects and entire communities.

New projects should ideally be written in Rust, and migration efforts can be attempted.

There have been some emerging systems languages that help to improve C or C++ but building on existing tooling and code to help a gradual migration.

C --> Zig (simpler build system, better C interop, no hidden control flow).

C++ --> Carbon (Google-backed attempt to evolve C++ with modern tooling).

This is a slight oversimplification, but mostly covers it.

Building an open-source community at HOTOSM: a thank you

Sam — Sun, 09 Mar 2025 04:07:27 +0000

I’m deeply embedded in the open-source humanitarian software space by now.

This post is a retrospective of my time spent here so far, and a highlight of the successes from the open-source community at HOTOSM.

I apologise in advance if its overly long and bit rambly.

Where This All Began

I started working with HOTOSM as a volunteer in Feb 2022. They needed urgent assistance with developing a new tool called the Field Mapping Tasking Manager, in response to the Turkey / Syria earthquake crisis at the time.

Having worked on open-source tools for the preceding two years, I was pretty keen to volunteer my time.

After speaking with their senior humanitarian advisor (and now a great friend), Ivan Gayton, I was quickly convinced about the potential for the tool, and took a week off work to push forward developments.

Fast forward a few years and I am now the senior technical lead at HOTOSM, coordinating and assisting the development of our entire suite of tools.

Governance

HOT never fully formalised a governance model, but operates a do-ocracy for the most part.
The main contributors have the most input for the direction of a tool!

Of course, this doesn't always hold true, particularly when hiring contractors
to work on specific features with a pre-determined job spec.

But HOT does try to be extremely transparent with it's approach, working in the
open, and actively encouraging input from the community.

The Community

Community has always been HOT’s strongest asset. I am by no means the expert on this topic.

But I do know that the community largely formed around Tasking Manager, responding to humanitarian activations for disaster response and resiliance.

Sub-communities formed around procuring and processing base imagery, in-person mapathon events globally, country-wide communities deeply rooted in OpenStreetMap and other open communities such as OSGeo and YouthMappers.

The global reach of the community is truly astounding, with ~5000 users registered onSlack channels alone.

HOT also has a large network of software development collaborators, with names such as DevelopmentSeed and Kontur frequently stepping in to assist.

While our mapping and data volunteer network is strong, we still struggle to cultivate a sustainable open-source software development community. Why is that? Would a different governance model help?

Note

HOT recently joined the the Cloud Native Geospatial Foundation,
something I'm particularly excited about having followed their work for so long!

Software Development Skills Are Truly Global

A degree from a top university doesn’t automatically make someone a great developer. Real-world problem-solving, especially in humanitarian tech, demands a broader skill set — one that prioritises practicality over algorithmic challenges.

Sure you can produce exceptionally crafted algorithms, but are the end users needs best served by this? Having rarely lived through hardship or a humanitarian crisis, is our mindset suitably aligned to meet the multi-faceted requirements? (Of course, I realize the irony here — this probably exposes my own biases too). This is especially prescient in an often resource-constrained humanitarian sector.

I have worked with a mixed bag of developers, many of which don’t immediately grasp the bigger picture of what they are developing. When funneling $$$ into AWS isn’t always an option, thinking outside the box may be required. Sometimes simple (and low resource, low budget) is best.

It also often requires understanding the full stack implications of what you are developing: delivering tools from end to end, considering development time, ease of understanding and maintainability, plus hosting costs in the long term (factoring possible scaling too).

Where I am going with this is that the top coding talent need not be constrained to the talent pool from software engineer grads from the “Western world”. Hiring for projects in this sector has been an eye opening experience. The rest of the world is getting on fine with some excellent software developers. You just need to know where to look.

Case in point to this is the burgeoning geospatial software industry based in Kathmandu, Nepal. One of the most talented and ‘going-somewhere’ engineers I have met is my friend and colleagueKshitij Sharma, who graduated from the excellent Institute of Engineering at Tribhuvan University.

HOT’s biggest tech partner is the organisation NAXA, a group that is truly a pioneer of the geospatial development industry, working alongside many, many international partners to achieve theirs goals.

From this partnership, DroneTM was conceived, a tool for community driven drone imagery collection - with the vast majority of the technical challenges being solved by the team based in Nepal.

Notable Contributions

Since working with HOT, I have had some excellent community contribution experiences regarding software development.

Here are a couple of examples.

HOTOSM UI

Joe has been invaluable to the team in the past few years.
Not only did he hash out the design choices taken for the hotosm/ui project, a web component library that will be integrated into all of our tools into the future.
But Joe has also contributed many large patches to both Tasking Manager and FieldTM, overhauling our build tools, migrating the entire Tasking Manager codebase from JavaScript --> TypeScript, and more 🙌

Simple Reverse Geocoding

Emir worked on this fantastic self-contained project over a few months, with the outcome being a very professional and comprehensive solution.
The goal was simple: a basic reverse-geocoder based on PostGIS, that required no external dependencies.
The design pattern used for sync/async Python code can be carried across to future projects too!
Emir is currently working on the final extension of the project: internationalised city name output.

GeoJSON AOI Parsing

Luke, a volunteer who recently reached out, is working on a small idea I had a while ago calledgeojosn-aoi-parser.
Pretty much all of our tools do some sort of user-provided geojson parsing.
The plan with this module is to standardise the parsing to a consistent format, while again only relying on PostGIS as a dependency (all of our tools use this).

FMTM Test Cases

Azhar was an Outreachy intern who helped with creating many test cases for the FieldTM backend.
This has paid dividends for the stability of the API into the future.
She also assisted with the DroneTM flight plan generation module, adding support for arbitrary rotation.

Others

Charlie, who assisted the brainstorming for a new data conflation workflow in FieldTM (which we haven’t managed to see to completion yet, sorry!)
Ozgur, who has been assisting us with the development of a PowerBI dashboard for managers of field data collection campaigns.
On top of these notable contributions I have been present for, there have been many more (particularly for Tasking Manager), which I haven’t mentioned: productive discussions, filing bug reports, making PRs - it all counts!
All of the brainstorming and collaboration we have done with the ODK team and community to date! This was touched on inanother blog post

Call To Action

With a funding crisis in the humanitarian sector currently - from COVID, to global instability, to the downright unethical closing of USAID - the purse strings are continually tightening.

We need your contributions more than ever! Find our how you can best contribute here

Feel free to reach out to member of the Tech Team through any channel. Let’s grow HOT’s humanitarian tech community one new contributor at a time 💪

All About CORS Headers

Sam — Wed, 05 Mar 2025 15:56:42 +0000

There are already a lot of resources about CORS out there.

I’m going to try and keep this short and sweet — useful tips for web devs.

About CORS

CORS is a security mechanism based on HTTP headers.
CORS must be configured on the web server and consumed by the browser.
It is only relevant for web browsers, not requests from other software such as cURL or a Python script (which will ignore the configuration).

Why Does It Exist?

You are on a website with mydomain.com.
All web browsers have a Same-Origin-Policy (SOP), preventing access to domains such as sub.mydomain.comor otherdomain.com, as a security measure.
Cross-Origin Resource Sharing (CORS) is a mechanism to allow exceptions to this rule.

When Is It Triggered?

If the schema, hostname, or port do not match, this is considered cross-origin.
An example would be https://mysite.com and https://api.mysite.com, which are different domains and hence cross-origin.
To avoid CORS for backend APIs, a common approach is to host the API under a subpath instead https://mysite.com/api, and avoid CORS concerns entirely.

Configuring CORS Correctly

Pre-Flight Requests

Browsers send a “pre-flight” request under certain conditions before making the actual request (for cross-origin requests).
A pre-flight request is a special OPTIONS request that checks the browser is allowed to submit to the API before making the actual request.
As above, to avoid CORS and having to send two requests per API call, it’s possible to host an API under a subpath instead.

Access-Control Headers

These headers control how CORS is enforced by the browser.
They are set on the web server of the resource you are trying to access, e.g. your NGINX proxy or backend API.

Access-Control-Allow-Origins

Which origins in the browser can call the API / webserver endpoints.
Examples:
- Access-Control-Allow-Origin: * (allow all access)
- Access-Control-Allow-Origin: https://example.com(specific protocol (https), domain (example.com), and port (443)))

Access-Control-Allow-Credentials

If cookies or HTTP basic auth should be sent with cross-origin requests.
Example: Access-Control-Allow-Credentials: true (allow).

Access-Control-Allow-Methods

Specifies which HTTP methods are permitted for cross-origin requests.
Example: Access-Control-Allow-Methods: GET, POST, PUT, DELETE.

Access-Control-Expose-Headers

Specifies which headers the browser can access from the response.
Example: Access-Control-Expose-Headers: Content-Length,Content-Range.
A good example for this is the Content-Disposition which may contain the original filename of an attachment sent by the server:
- For the filename: Content-Disposition: attachment; filename="myphoto.jpg"
- Allow the header to be read: Access-Control-Expose-Headers: Content-Disposition.
- The browser can now download the file with the original filename.

Other Headers

Cross-Origin-Resource-Policy (CORP): controls how strict the CORS policy is:
- same-origin: Only allow requests from the exact same origin.
- same-site: Allow requests from subdomains (e.g. api.example.com for example.com).
- cross-origin: Allow all requests from any domain.
Cross-Origin-Opener-Policy (COOP) & Cross-Origin-Embedder-Policy (COEP): typically used for security and performance optimizations. A common example is usage with the Origin Private File System (OPFS) and SharedArrayBuffer.
Access-Control-Max-Age: defines how long the results of a pre-flight request can be cached before needing to run again.

Combining sync and async Python code: writing a DRY package

Sam — Tue, 18 Feb 2025 14:13:06 +0000

Async Python

Directly from the Python docs:

asyncio is a library to write concurrent code using the async/await syntax.

asyncio is used as a foundation for multiple Python asynchronous frameworks that provide high-performance network and web-servers, database connection libraries, distributed task queues, etc.

asyncio is often a perfect fit for IO-bound and high-level structured network code.

A typical use case for asynchronous code is web servers based on the ASGI-spec, such as FastAPI, LiteStar, etc.

However, sync and async code do not play nicely together.

Often, we need an async library, but there isn't one available, or vice versa.

This article will discuss a simple pattern for writing Python packages compatible with both Synchronous and Asynchronous code, while only having to write the functionality once! (no separate libraries)

Combining Sync and Async Code

First I will briefly describe the issues with mixing these two paradigms.

How Async Works

Each Python interpreter runs in a process on the system.
Each Python process has an event loop that can run async code.
An event loop can run multiple pieces of code (coroutines) concurrently, awaiting the return of something while another piece of code is executing.
This has huge benefits for IO-bound tasks, such as downloading multiple files or sending simultaneous database queries.

Sync From Async

Doing this will typically block the event loop for executing other async code.
This means that when you hit a piece of synchronous code, you essentially block everything below it from running until it completes, negating most of the benefits of async.
It also means that if async tasks were started prior to the sync code running, the execution of these tasks will not proceed until the sync code allows the event loop to run again. This problem is compounded by CPU-heavy tasks, which do not yield control back to the event loop.

Async From Sync

Synchronous code cannot use the async/await keywords, hence async code cannot be executed in it's normal manner.
To get around this, async code can be run in a newly spawned event loop (asyncio.run() or loop.run_until_complete()), a separate thread (ThreadpoolExecutor) or process (ProcessPoolExecutor).
Doing this significantly complicates codes (possibly introducing threading issues, multiple loops, and unexpected blocking), while providing none of the benefits of having async code in the first place.

How To Write A Package That Is Both Sync / Async

Always write async first!
It's much easier to remove the async specific tokens from code using a tokeniser script.
Once we have our asynchronous implementation, we can use a script to convert it into a synchronous equivalent.
This way you can release a package with both implementations available to your users: use the correct implementation for your use case.
There is a nice package called unasync that can do this for you, but the simplest and cleanest implementation I have found was in the encode/httpcore package:

#!venv/bin/python
import os
import re
import sys
from pprint import pprint

SUBS = [
    ('from .._backends.auto import AutoBackend', 'from .._backends.sync import SyncBackend'),
    ('import trio as concurrency', 'from tests import concurrency'),
    ('AsyncIterator', 'Iterator'),
    ('Async([A-Z][A-Za-z0-9_]*)', r'\2'),
    ('async def', 'def'),
    ('async with', 'with'),
    ('async for', 'for'),
    ('await ', ''),
    ('handle_async_request', 'handle_request'),
    ('aclose', 'close'),
    ('aiter_stream', 'iter_stream'),
    ('aread', 'read'),
    ('asynccontextmanager', 'contextmanager'),
    ('__aenter__', '__enter__'),
    ('__aexit__', '__exit__'),
    ('__aiter__', '__iter__'),
    ('@pytest.mark.anyio', ''),
    ('@pytest.mark.trio', ''),
    ('AutoBackend', 'SyncBackend'),
]
COMPILED_SUBS = [
    (re.compile(r'(^|\b)' + regex + r'($|\b)'), repl)
    for regex, repl in SUBS
]

USED_SUBS = set()

def unasync_line(line):
    for index, (regex, repl) in enumerate(COMPILED_SUBS):
        old_line = line
        line = re.sub(regex, repl, line)
        if old_line != line:
            USED_SUBS.add(index)
    return line


def unasync_file(in_path, out_path):
    with open(in_path, "r") as in_file:
        with open(out_path, "w", newline="") as out_file:
            for line in in_file.readlines():
                line = unasync_line(line)
                out_file.write(line)


def unasync_file_check(in_path, out_path):
    with open(in_path, "r") as in_file:
        with open(out_path, "r") as out_file:
            for in_line, out_line in zip(in_file.readlines(), out_file.readlines()):
                expected = unasync_line(in_line)
                if out_line != expected:
                    print(f'unasync mismatch between {in_path!r} and {out_path!r}')
                    print(f'Async code:         {in_line!r}')
                    print(f'Expected sync code: {expected!r}')
                    print(f'Actual sync code:   {out_line!r}')
                    sys.exit(1)


def unasync_dir(in_dir, out_dir, check_only=False):
    for dirpath, dirnames, filenames in os.walk(in_dir):
        for filename in filenames:
            if not filename.endswith('.py'):
                continue
            rel_dir = os.path.relpath(dirpath, in_dir)
            in_path = os.path.normpath(os.path.join(in_dir, rel_dir, filename))
            out_path = os.path.normpath(os.path.join(out_dir, rel_dir, filename))
            print(in_path, '->', out_path)
            if check_only:
                unasync_file_check(in_path, out_path)
            else:
                unasync_file(in_path, out_path)


def main():
    check_only = '--check' in sys.argv
    unasync_dir("httpcore/_async", "httpcore/_sync", check_only=check_only)
    unasync_dir("tests/_async", "tests/_sync", check_only=check_only)

    if len(USED_SUBS) != len(SUBS):
        unused_subs = [SUBS[i] for i in range(len(SUBS)) if i not in USED_SUBS]

        print("These patterns were not used:")
        pprint(unused_subs)
        exit(1)   


if __name__ == '__main__':
    main()

All credit to the devs at encode for the implementation.

Without digging into the code too deeply, it should be quite obvious from the SUBS param what this script does - converting async syntax to sync syntax.

Integrating Unasync.py

Add the above unasync.py code to your repo.
Place your async code in a _async directory.
Modify the SUBS param and unasync_dir usage in main() to match your project structure.
Run the script to generate the sync code equivalent in the _sync directory.

I followed this exact approach in a recent project I worked on with a volunteer at HOTOSM (Emir, an excellent dev!).

We were looking at a nice approach for making the package available for both sync and async (FastAPI) use cases, and the solution was surprisingly simple, but poorly documented online!

The full project an implementation can be viewed here

Optional: Use Via Pre-Commit Hook

In the linked project above, I set a pre-commit hook to trigger the unasync.py script on each commit.
This means the synchronous code never gets out of sync (ha!) with the asynchronous code.
The config for the hook was:

repos:
  # Unasync: Convert async --> sync
  - repo: local
    hooks:
      - id: unasync
        name: unasync-all
        language: system
        entry: python unasync.py
        always_run: true
        pass_filenames: false

How to Migrate Plex from Windows to Linux: What Most Guides Miss

Sam — Sun, 09 Feb 2025 16:10:13 +0000

Home Server Overhaul

I am in the process of migrating from a hybrid Windows / HyperV Ubuntu home server setup, to a Debian-based Kubernetes cluster.
The only app remaining on Windows was Plex, due to perceived difficult migrating all the metadata to Linux.
All of the guides I found online had partial information, but not everything I needed to get a fully migrated Plex server up and running on my Linux machine!

Note that I was migrating from a plain Windows install to a containerised Linux install, but for this guide, this info should not matter.

The Official Guide

This has most of everything you need: https://support.plex.tv/articles/201370363-move-an-install-to-another-system/
However, do note that it's a guide explicitly for migrating to a new machine using the same operating system.
There are two extra steps I will detail below, that should be carried out in the 'Copy Server Data From the Source System' section:
- Updating paths from Windows to Linux in the Plex SQLite database.
- Copying preferences from Windows Registry to Linux XML file.
After following the extra steps listed here, you should have an exact replica of the Plex installation you were migrating from!

Update SQLite File Paths

First backup your SQLite database for Plex, located here:

C:\Users\YOURUSER\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"

Enter the Plex SQLite shell

"C:\Program Files\Plex\Plex Media Server\Plex SQLite.exe" "C:\Users\YOURUSER\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"

Note that Plex uses a custom implementation of SQLite... standard browsers might corrupt your file, or just not work.

Replace Windows backslash with Linux forward slash

UPDATE media_parts SET file=REPLACE(file, '\', '/');
UPDATE section_locations SET root_path=REPLACE(root_path, '\', '/');
UPDATE media_streams SET url=REPLACE(url, '\', '/') WHERE url LIKE 'file://%';

Replace library root paths

// First get all of your library root locations
SELECT root_path FROM section_locations;

// For each location, replace with the correct path
UPDATE section_locations SET root_path=REPLACE(root_path, 'D:/Path/To/Library1', '/data/library1');
UPDATE section_locations SET root_path=REPLACE(root_path, 'D:/Path/To/Library2', '/data/library2');

Replace media part paths

// Inspect the format of the file paths
SELECT file FROM media_parts WHERE file != "" LIMIT 20;

// For each library above, ensure you correctly set the path here
UPDATE media_parts SET file=REPLACE(file, 'D:/Path/To/Library1', '/data/library1');
UPDATE media_parts SET file=REPLACE(file, 'D:/Path/To/Library2', '/data/library2');

Replace media stream paths

// Note here we possibly have double and triple slash prefixes that must be replaced
UPDATE media_streams SET url=REPLACE(url, 'file://D:/Path/To/Library1', 'file:///data/library1');
UPDATE media_streams SET url=REPLACE(url, 'file:///D:/Path/To/Library1', 'file:///data/library1');
UPDATE media_streams SET url=REPLACE(url, 'file://D:/Path/To/Library2', 'file:///data/library2');
UPDATE media_streams SET url=REPLACE(url, 'file:///D:/Path/To/Library2', 'file:///data/library2');

Replace metadata_item paths

UPDATE metadata_items SET guid=REPLACE(guid, 'file:///D:/Path/To/Library1', 'file:///data/library1');
UPDATE metadata_items SET guid=REPLACE(guid, 'file:///D:/Path/To/Library2', 'file:///data/library2');

Final checks

// Check for any corruption
PRAGMA integrity_check;
// If errors, try a reindex
REINDEX;

// Vacuum db
VACUUM;

// Exit
.quit

Migrating Preferences

Windows Registry

Windows keeps it Plex preferences in the registry.
To access the values, open tool regedit
Navigate to path: Computer\HKEY_CURRENT_USER\SOFTWARE\Plex, Inc.\Plex Media Server
Inspect the values to migrate. Some important ones are:
- PlexOnlineXXX values (for the PlexPass account)
- XXXMachineIdentifer values
- customConnections if used, say for a reverse proxy config
- allowedNetworks if used
Additional details can be found here: https://support.plex.tv/articles/201105343-advanced-hidden-server-settings/

Linux Preferences

Linux places all of these values into a Preferences.xml file.
First, start up and shut down your new Plex instance.
The Preferences.xml file will be created in the 'Plex Media Server' root directory.
Modify the preferences with the values from the Windows registry:

<?xml version="1.0" encoding="utf-8"?>
<Preferences
  allowedNetworks="127.0.0.1,192.168.90.11"
  autoEmptyTrash="0"
  secureConnections="1"
  sendCrashReports="1"
  customConnections="https:/your.custom.domain:443"
  AcceptedEULA="1" 
  AnonymousMachineIdentifier="xxx-xxx"
  CertificateUUID="xxx-xxx"
  CertificateVersion="3"
  CloudSyncNeedsUpdate="0"
  DatabaseCacheSize="4000"
  DisableTLSv1_0="1"
  DlnaEnabled="0"
  DvrIncrementalEpgLoader="0"
  EnableIPv6="0"
  FSEventLibraryPartialScanEnabled="0"
  FSEventLibraryUpdatesEnabled="0"
  FriendlyName="Name-Of-Your-Plex-Server"
  GlobalMusicVideoPathMigrated="1"
  LanNetworksBandwidth="192.168.90.11/24"
  LanguageInCloud="1"
  LastAutomaticMappedPort="0"
  LogVerbose="0"
  MachineIdentifier="xxx-xxx"
  ManualPortMappingMode="1"
  ManualPortMappingPort="32400"
  MergedRecentlyAdded="0"
  MetricsEpoch="1"
  MinutesAllowedPaused="30"
  OldestPreviousVersion="legacy"
  PlexOnlineHome="0"
  PlexOnlineMail="your@email.com"
  PlexOnlineToken="token_from_previous_install"
  PlexOnlineUsername="your_plex_username"
  PreferredNetworkInterface="eth0"
  ProcessedMachineIdentifier="xxx-xxx"
  PubSubServer="xxx"
  PubSubServerPing="xx"
  PubSubServerRegion="lhr"
  PublishServerOnPlexOnlineKey="1"
  PushNotificationsEnabled="1"
  RelayEnabled="0"
  ScannerLowPriority="1"
  ScheduledLibraryUpdateInterval="86400"
  ScheduledLibraryUpdatesEnabled="0"
  TranscodeCountLimit="3"
  TranscoderCanOnlyRemuxVideo="0"
  TranscoderH264BackgroundPreset="medium"
  TranscoderQuality="0"
  TranscoderTempDirectory="/tmp/transcode"
  TranscoderThrottleBuffer="1800"
  WanPerUserStreamCount="2"
  WanTotalMaxUploadRate="20000"
  WebHooksEnabled="0"
/>

Build a Simple Golang Webhook Service with Postgres Triggers and pg_notify

Sam — Thu, 06 Feb 2025 17:45:08 +0000

Context For The Problem

I work on a tool called the Field Tasking Manager (Field-TM) at the Humanitarian OpenStreetMap Team.

The goal is to coordinate field mapping activities, to both enrich OpenStreetMap data with field verified info, and collect field data for both development and humanitarian contexts.

Underneath, we use two tools from ODK.

Collect is used on mobile devices to send data to the Central server.

However, we need a way to be notified that a user has submitted new data from their phone, triggering an update from ODK Central --> Field-TM.

There are three obvious approaches for this type of behaviour:

Pull via polling: polling of the ODK APIs at an interval (simple, requires no change to ODK, but resource inefficient).
Push/pull via websocket: bi-directional communication, but potentially complex to implement, and requires full development control of both services (requires significant changes to ODK).
Push via webhook: a change in ODK Central triggers a call to a remote API, including POST data (simple and efficient, requiring no persistent connection).

Using a webhook could potentially involve changes to the ODK codebase, but we can work around that - with the solution I will describe below!

There are of course other application communication methods that are out of scope of this article: event driven messaging, Redis pub/sub, gRPC, etc.

Postgres LISTEN / NOTIFY

Having not previously used the LISTEN / NOTIFY notifications functionality in Postgres, I stumbled across this excellent article by @brojonat, which in turn references this article by @brandur.

They describe a 'notifier pattern' for Postgres, where a connection is made to establish a 'listener'. Events in Postgres (such as a data insert) can be configured to trigger sending a notification to this 'listener', including the data that was inserted, or other data in the database.

image credit to @brandur's post linked above.

This sounds perfect for a webhook service!

It could be achieved like this:

Create a connection to Postgres, with a LISTEN for events.
Create a TRIGGER in Postgres, to NOTIFY the listener and submit data.
Parse the data in our service, then create a POST request with the data on a remote API endpoint (webhook).

Ideally we could deploy this small service alongside ODK Central, scanning the Postgres database and triggering when new submission are made.

This would mean no changes are required to the codebase of the application we develop the webhook for - making this a performant, but non-intrusive method of implementing via middleware.

Why Golang?

I would say that I am far from proficient in Golang at this point, with Python being my preferred backend language.

Python has the excellent psycopg library for interfacing with Postgres, and has some great documentation for how to use LISTEN / NOTIFY in Python.

However, as a language, Golang has many advantages for this type of project:

High performance / resource usage ratio, with a low memory footprint.
Statically compiled binary that can be distributed anywhere. Simply download the binary / executable and run, without worrying about dependencies, versions, etc.
Has a very comprehensive standard library, meaning it should require little maintenance going forward (minimal dependency upgrades).
Reasonably simple to use. Of course, it has a few extra concepts then Python, such a pointers, composition, and goroutines, but not a huge lift to learn.

Alternative Approaches

It should be noted there are alternative approaches to achieve the goals, which may or may not be better depending on your requirements.

Three notable examples:

Instead of implementing a custom listener/notifier, it's possible to use existing solutions, such as this Express app that be configured by JSON to listen for events and trigger webhooks. See related blog.
Possibly you can call the webhook directly from a Postgres trigger using pgsql-http, as long as the API has no authentication requirements.
This very neat logical-replication based approach to do similar.

The Implementation

Right, now let's dig into the code.

I won't replicate much here, as it's all available on the linked code repo, but will attempt to explain how the tool works.

db/connection.go

Here we use the github.com/jackc/pgx/v5/pgxpool package (the only external dependency used) as the Postgres database driver. The lib/pq package might look more official at first glance, but it is community led and effectively in maintenance mode only.

db/listener.go
db/notifier.go

This is where we implement the LISTEN / NOTIFY functionality, as described in the previous section / blogs.

db/trigger.go

Here we have a reasonably complex TRIGGER function, with CASE statements to handle three different scenarios.
This is the core logic of what event we want to trigger the webhook. Your implementation could be significantly simpler.
We are using the ODK Central functionality called audit logs here, essentially an event stream we can hook into and trigger our notifications from.

CREATE OR REPLACE FUNCTION new_audit_log() RETURNS trigger AS
$$
DECLARE
    js jsonb;
    action_type text;
    result_data jsonb;
BEGIN
    -- Serialize the NEW row into JSONB
    SELECT to_jsonb(NEW.*) INTO js;

    -- Add the DML action (INSERT/UPDATE)
    js := jsonb_set(js, '{dml_action}', to_jsonb(TG_OP));

    -- Extract the action type from the NEW row
    action_type := NEW.action;

    -- Handle different action types with a CASE statement
    CASE action_type
        WHEN 'entity.update.version' THEN
            SELECT entity_defs.data
            INTO result_data
            FROM entity_defs
            WHERE entity_defs.id = (NEW.details->>'entityDefId')::int;

            -- Merge the entity details into the JSON data key
            js := jsonb_set(js, '{data}', result_data, true);

            -- Notify the odk-events queue
            PERFORM pg_notify('odk-events', js::text);

        WHEN 'submission.create' THEN
            SELECT jsonb_build_object('xml', submission_defs.xml)
            INTO result_data
            FROM submission_defs
            WHERE submission_defs.id = (NEW.details->>'submissionDefId')::int;

            -- Merge the submission XML into the JSON data key
            js := jsonb_set(js, '{data}', result_data, true);

            -- Notify the odk-events queue
            PERFORM pg_notify('odk-events', js::text);

        WHEN 'submission.update' THEN
            SELECT jsonb_build_object('instanceId', submission_defs."instanceId")
            INTO result_data
            FROM submission_defs
            WHERE submission_defs.id = (NEW.details->>'submissionDefId')::int;

            -- Extract 'reviewState' from 'details' and set it in 'data'
            js := jsonb_set(js, '{data}', jsonb_build_object('reviewState', js->'details'->>'reviewState'), true);

            -- Remove 'reviewState' from 'details'
            js := jsonb_set(js, '{details}', (js->'details')::jsonb - 'reviewState', true);

            -- Merge the instanceId into the existing 'details' key in JSON
            js := jsonb_set(js, '{details}', (js->'details') || result_data, true);

            -- Notify the odk-events queue
            PERFORM pg_notify('odk-events', js::text);

        ELSE
            -- Skip pg_notify for unsupported actions & insert as normal
            RETURN NEW;
    END CASE;

    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

parser/audit.go

The code to parse the TRIGGER output into a Go struct, the marshal a JSON for sending to the webhook.

webhook/request.go

Send a request with the given JSON payload, to the configured webhook URL.

webhook/auth.go

Handle authentication on the API by means of an X-API-Key request header.

The Outcome

The final code repository can be found here.

The final binary is ~10MB in size, 15MB when distributed in a minimal container image, and only consumes ~5MB of memory at idle.

Of course, this uses a connection to the Postgres database (that is cycled continually to remain alive), but overall I am extremely happy with this approach of a small lightweight service to run alongside ODK.

Forum discussion around the approach can be found here

The repo issue and linked PRs related to implementation with Field-TM can be found here

FastAPI, Pydantic, Psycopg3: The Ultimate Trio for Python Web APIs

Sam — Thu, 24 Oct 2024 23:12:43 +0000

Part 1: Discussion

Enter FastAPI

First of all, take the title with a pinch of salt.

If I was starting from scratch with Python web API development today, I would probably look more closely at LiteStar, which seems to me to be a better architected and with a better project governance structure.

But we have FastAPI and it's not going anywhere soon. I use it for a lot of personal and professional projects and still enjoy its simplicity.

For a guide on FastAPI design patterns, look no further than this page.

Retrieving Database Data

Despite FastAPI being great at the actual 'API' part, there has been one persistent uncertainty for me: how to best access the database, particularly if we need to also handle geospatial data types.

Let's review our options.

Note 1: we are only interested in async libraries here, as FastAPI is ASGI.

Note 2: I will only discuss connecting to PostgreSQL, although parts of the discussion are still relevant to other databases.

Simple To Code | Complex Design: ORMs

Handles your database connection and parsing of data from your database table into Python objects.

SQLAlchemy2: the biggest contender in the Python ORM world. Personally I really dislike the syntax, but each to their own.
TortoiseORM: I personally really like this Django-inspired async ORM; it's clean and nice to use.
Alternative ORMs: there are many such as peewee, PonyORM, etc.

The Middle Ground: Query Builders

No database connection. Simply output raw SQL from a Python-based query and pass it to the database driver.

SQLAlchemy Core: the core SQL query builder, without the mapping to objects part. There is also a higher level ORM built on this called databases that looks very nice. I do wonder how actively developed the project is however.
PyPika: I don't know much about this one.

Simple Design: Database Drivers

asyncpg: this was the gold standard async database driver for Postgres, being one of the first to market and most performant. While all other drivers use the C library libpq to interface with Postgres, MagicStack opted to rewrite their own custom implementation and also deviate from Python DBAPI spec. If performance is your main criteria here, then asyncpg is probably the best option.
psycopg3: well psycopg2 was clearly the king of the synchronous database driver world for Python/Postgres. psycopg3 (rebranded to simply psycopg) is the next, fully async, iteration of this library. This library has really come into it's own in recent years & I wish to discuss it further. See this interesting blog from the author about the early days of psycopg3.

Note that there is clearly a broader, more conceptual, discussion to be had here around ORMs vs query builders vs raw SQL. I won't cover that here.

Duplicated Models

Pydantic is bundled with FastAPI and is excellent for modelling, validating, and serialising API responses.

If we decide to use an ORM to retrieve data from our database, isn't it a bit inefficient keeping two sets of database models in sync? (one for the ORM, another for Pydantic)?

Wouldn't it be great if we could just use Pydantic to model the database?

This is exactly the problem the creator of FastAPI tried to solve with the library SQLModel.

While this could very well be a great solution to the problem, I have a few concerns:

Will this project suffer from the single-maintainer syndrome like FastAPI?
It's still a reasonably young project and concept, where documentation isn't fantastic.
It's intrinsically tied up with Pydantic and SQLAlchemy, meaning migration away would be extremely difficult.
For more complex queries, dropping down to SQLAlchemy underneath may be required.

Back To Basics

So many options! Analysis paralysis.

When there is uncertainty I would use the following precept: keep it simple.

SQL was invented 50yrs ago and is still a key skill for any developer to learn. It's syntax is consistently easy to grasp and uncomplicated to write for most use cases (for the die-hard ORM users out there, give it a try, you might be surprised).

Hell, we can even use open-source LLMs these days to generate (mostly working) SQL queries and save you the typing.

While ORMs and query builders may come and go, database drivers are likely more consistent. The original psycopg2 library was written nearly 20yrs ago now and is still actively used in production globally.

Using Psycopg with Pydantic Models

As discussed, while psycopg may not be as performant as asyncpg (the real world implications of this theoretical performance is debatable though), psycopg focuses on ease of use and a familiar API.

The killer feature for me is Row Factories.

This functionality allows you to map returned database data to any Python object, including standard lib dataclasses, models from the great attrs library, and yes, Pydantic models!

For me, this is the best compromise of approaches: the ultimate flexibility of raw SQL, with the validation / type safety capabilities of Pydantic to model the database. Psycopg also handles things like variable input sanitation to avoid SQL injection.

It should be noted that asyncpg can also handle mapping to Pydantic models, but as more of a workaround than a built-in feature. See this issue thread for details. I also don't know if this approach plays nicely with other modelling libraries.

As I mentioned above, I typically work with geospatial data: an area often neglected by ORMs and query builders. Dropping to the raw SQL gives me the ability to parse and unparse geospatial data as I need to more acceptable types in pure Python. See my related article on this topic.

Part 2: Example Usage

Create A Database Table

Here we create a simple database table called user in raw SQL.

I would also consider handling database creation and migrations using SQL only, but this is a topic for another article.

init_db.sql

CREATE TYPE public.userrole AS ENUM (
    'READ_ONLY',
    'STANDARD',
    'ADMIN'
);

CREATE TABLE public.users (
    id integer NOT NULL,
    username character varying,
    role public.userrole NOT NULL DEFAULT 'STANDARD',
    profile_img character varying,
    email_address character varying,
    is_email_verified boolean DEFAULT false,
    registered_at timestamp with time zone DEFAULT now()
);

Model Your Database With Pydantic

Here we create a model called DbUser:

db_models.py

from typing import Optional
from enum import Enum
from datetime import datetime
from pydantic import BaseModel
from pydantic.functional_validators import field_validator
from geojson_pydantic import Feature

class UserRole(str, Enum):
    """Types of user, mapped to database enum userrole."""

    READ_ONLY = "READ_ONLY"
    STANDARD = "STANDARD"
    ADMIN = "ADMIN"

class DbUser(BaseModel):
    """Table users."""

    id: int
    username: str
    role: Optional[UserRole] = UserRole.STANDARD
    profile_img: Optional[str] = None
    email_address: Optional[str] = None
    is_email_verified: bool = False
    registered_at: Optional[datetime]
    # This is a geospatial type I will handle in the SQL
    favourite_place: Optional[dict]

    # DB computed fields (handled in the SQL)
    total_users: Optional[int] = None

    # This example isn't very realistic, but you get the idea
    @field_validator("is_email_verified", mode="before")
    @classmethod
    def i_want_my_ints_as_bools(cls, value: int) -> bool:
        """Example of a validator to convert data type."""
        return bool(value)

Here we get the type safety and validation of Pydantic.

We can add any form of validation or data transformation to this model for when the data is extracted from the database.

Setting Up Psycopg With FastAPI

We use psycopg_pool to create a pooled database connection:

db.py

from typing import cast
from fastapi import Request
from psycopg import Connection
from psycopg_pool import AsyncConnectionPool

# You should be using environment variables in a settings file here
from app.config import settings


def get_db_connection_pool() -> AsyncConnectionPool:
    """Get the connection pool for psycopg.

    NOTE the pool connection is opened in the FastAPI server startup (lifespan).

    Also note this is also a sync `def`, as it only returns a context manager.
    """
    return AsyncConnectionPool(
        conninfo=settings.DB_URL.unicode_string(), open=False
    )


async def db_conn(request: Request) -> Connection:
    """Get a connection from the psycopg pool.

    Info on connections vs cursors:
    https://www.psycopg.org/psycopg3/docs/advanced/async.html

    Here we are getting a connection from the pool, which will be returned
    after the session ends / endpoint finishes processing.

    In summary:
    - Connection is created on endpoint call.
    - Cursors are used to execute commands throughout endpoint.
      Note it is possible to create multiple cursors from the connection,
      but all will be executed in the same db 'transaction'.
    - Connection is closed on endpoint finish.
    """
    db_pool = cast(AsyncConnectionPool, request.state.db_pool)
    async with db_pool.connection() as conn:
        yield conn

Next we open the connection pool in the FastAPI lifespan event:

main.py

from typing import AsyncIterator
from contextlib import asynccontextmanager
from fastapi import FastAPI

from .db import get_db_connection_pool

@asynccontextmanager
async def lifespan(app: FastAPI) -> AsyncIterator[None]:
    """FastAPI startup/shutdown event."""
    log.debug("Starting up FastAPI server.")

    # Create a pooled db connection and make available in lifespan state
    # https://asgi.readthedocs.io/en/latest/specs/lifespan.html#lifespan-state
    # NOTE to use within a request (this is wrapped in database.py already):
    # from typing import cast
    # db_pool = cast(AsyncConnectionPool, request.state.db_pool)
    # async with db_pool.connection() as conn:
    db_pool = get_db_connection_pool()
    await db_pool.open()

    yield

    # Shutdown events
    print("Shutting down FastAPI server.")
    # Here we make sure to close the connection pool
    await db_pool.close()

Now when you FastAPI app starts, you should have an open connection pool, ready to take connection from inside endpoints.

Helper Methods For The Pydantic Model

It would be useful to add a few methods to the Pydantic model for common functionality: getting one user, all users, creating a user, updating a user, deleting a user.

But first we should create some Pydantic models for input validation (to create a new user) and output serialisation (your JSON response via the API).

user_schemas.py

from typing import Annotated
from pydantic import BaseModel, Field
from pydantic.functional_validators import field_validator
from geojson_pydantic import FeatureCollection, Feature, MultiPolygon, Polygon
from .db_models import DbUser

class UserIn(DbUser):
    """User details for insert into DB."""

    # Exclude fields not required for input
    id: Annotated[int, Field(exclude=True)] = None
    favourite_place: Optional[Feature]

    @field_validator("favourite_place", mode="before")
    @classmethod
    def parse_input_geojson(
        cls,
        value: FeatureCollection | Feature | MultiPolygon | Polygon,
    ) -> Optional[Polygon]:
        """Parse any format geojson into a single Polygon."""
        if value is None:
            return None
        # NOTE I don't include this helper function for brevity
        featcol = normalise_to_single_geom_featcol(value)
        return featcol.get("features")[0].get("geometry")

class UserOut(DbUser):
    """User details for insert into DB."""

    # Ensure it's parsed as a Polygon geojson from db object
    favourite_place: Polygon

    # More logic to append computed values

Then we can define our helper methods: one, all, create:

db_models.py

...previous imports
from typing import Self, Optional
from fastapi.exceptions import HTTPException
from psycopg import Connection
from psycopg.rows import class_row

from .user_schemas import UserIn

class DbUser(BaseModel):
    """Table users."""

    ...the fields

    @classmethod
    async def one(cls, db: Connection, user_id: int) -> Self:
        """Get a user by ID.

        NOTE how the favourite_place field is converted in the db to geojson.
        """
        async with db.cursor(row_factory=class_row(cls)) as cur:
            sql = """
                SELECT
                    u.*,
                    ST_AsGeoJSON(favourite_place)::jsonb AS favourite_place,
                    (SELECT COUNT(*) FROM users) AS total_users
                FROM users u
                WHERE
                    u.id = %(user_id)s
                GROUP BY u.id;
            """

            await cur.execute(
                sql,
                {"user_id": user_id},
            )

            db_project = await cur.fetchone()
            if not db_project:
                raise KeyError(f"User ({user_identifier}) not found.")

            return db_project

    @classmethod
    async def all(
        cls, db: Connection, skip: int = 0, limit: int = 100
    ) -> Optional[list[Self]]:
        """Fetch all users."""
        async with db.cursor(row_factory=class_row(cls)) as cur:
            await cur.execute(
                """
                SELECT
                    *,
                    ST_AsGeoJSON(favourite_place)::jsonb
                FROM users
                OFFSET %(offset)s
                LIMIT %(limit)s;
                """,
                {"offset": skip, "limit": limit},
            )
            return await cur.fetchall()

    @classmethod
    async def create(
        cls,
        db: Connection,
        user_in: UserIn,
    ) -> Optional[Self]:
        """Create a new user."""

        # Omit defaults and empty values from the model
        model_dump = user_in.model_dump(exclude_none=True, exclude_default=True)
        columns = ", ".join(model_dump.keys())
        value_placeholders = ", ".join(f"%({key})s" for key in model_dump.keys())

        sql = f"""
            INSERT INTO users
                ({columns})
            VALUES
                ({value_placeholders})
            RETURNING *;
        """


        async with db.cursor(row_factory=class_row(cls)) as cur:
            await cur.execute(sql, model_dump)
            new_user = await cur.fetchone()

            if new_user is None:
                msg = f"Unknown SQL error for data: {model_dump}"
                print(f"Failed user creation: {model_dump}")
                raise HTTPException(status_code=500, detail=msg)

        return new_user

Usage

routes.py

from typing import Annotated
from fastapi import Depends, HTTPException
from psycopg import Connection

from .main import app
from .db import db_conn
from .models import DbUser
from .user_schemas import UserIn, UserOut

@app.post("/", response_model=UserOut)
async def create_user(
    user_info: UserIn,
    db: Annotated[Connection, Depends(db_conn)],
):
    """Create a new user.

    Here the input is parsed and validated by UserIn
    then the output is parsed and validated by UserOut
    returning the user json data.
    """

    new_user = await DbUser.create(db, user_info)
    if not new_user:
        raise HTTPException(
            status_code=422,
            detail="User creation failed.",
        )

    return new_user

    # NOTE within an endpoint we can also use
    # DbUser.one(db, user_id) and DbUser.all(db)

This is the approach I have started to use in a project I maintain, the FMTM, a tool to collect field data for communities around the world.

See the full codebase here.
And ⭐ if you found this useful!

That's all for now! I hope this helps someone out there 🚀

Contributing to ODK: the go-to mobile form data collection tool

Sam — Thu, 13 Jun 2024 21:36:16 +0000

This is a post with very little code, but instead a higher level retrospective on contribution to a mature open-source project.

Intro to ODK

ODK is a collection of open-source tools that are used to collect data via forms designed for a mobile phone.

It is at the forefront of tools available for this purpose, commonly used in sectors such as public health, global development, crisis response, environmental research, and more.

The key tools to be aware of are:

ODK Collect: the Android application written in Java/Kotlin, used to collect data.
ODK Central: the Node.js web server written in JavaScript, used to receive the data from mobile devices. There is a companion web frontend written in Vue.

Additional requirements from field mappers

At the Humanitarian OpenStreetMap team (HOT), we use ODK to underpin one of our core tools, the Field Mapping Tasking Manager (FMTM).

Mappers in the field must use ODK Collect to gather information about features on the ground (buildings, roads, etc).

However, there are two major features that would significantly improve the workflow for mappers:

To have an easy way to load base satellite imagery in the background of ODK Collect.
To open ODK Collect from the click of a button in FMTM, with information pre-filled, such as a selected building.

Luckily, the ODK community and developers, are both very welcoming, and also very receptive to feedback and new ideas.

Implementing easier MBTile usage

ODK has supported loading basemaps in MBTile format for a while now, however adding them to a project requires connecting a computer and manually copying the files over.

After fruitful discussion around this task on the ODK forums, a plan was made by the ODK devs to improve the implementation.

HOT works closely with NAXA for development on FMTM, and NAXA's current Chief Operating Officer is incidentally one of their most senior Android devs!

Nishon managed to put in a big shift to implement most of the requirements for loading the MBTiles file directly from a device here.

Unfortunately due to lack of capacity from our side, the final touches to the PR were not able to be made 😅 Grzegorz from the ODK team picked up the feature and finalised it for their most recent beta release (2024.2.0-beta.2).

However, overall it was a very successful collaboration & we are extremely grateful to the ODK team for being so responsive and receptive to contribution.

Loading ODK Collect by intent

More recently we have been focusing on our second requirement: loading ODK Collect from an external app, with data pre-filled.

We started discussion on this a while ago, gathering community input and requirements.

Ping, an very experienced dev who used to be part of ODK, helped us to create a proof of concept. After further bug fixing by Samir at NAXA, we finally have a built dev APK with all of our requirements.

Obviously this proof of concept would not pass ODK's very strict code quality requirements, including extensive testing and covering a well researched combination of user requirements.

However, we plan to continue the discussion and development (primarily by Samir) for hopefully another successful collaboration with the ODK team.

Other contributions

During this process I have also been providing additional contributions to documentation and build configs (as my experience does not lie in Android development), to help build goodwill and aid the long term success of ODK.

At the organisation level, and individual level, we are very keen to see the suite of ODK tools grow and the team to continue the exemplary job they are doing managing an open-source project!

Some minor contributions:

Helping debug the ODK Central deployment on ARM-based systems.
Upgrading the Python version for pyxform-http.
Tweaking the containerised deployment of Central.
Proof reading and adding content to docs.
Testing all the tools in various scenarios.
Early adoption of new features and providing feedback.

Retrospective

Contributing to an established application with many complex user requirements is no easy task.

Thankfully the developers at ODK are extremely professional about this process, and while expect a very thorough and professional contribution, they are open to assist and provide guidance throughout the entire process.

It may take some extra time to gather user requirements, provide comprehensive tests, and review the code more thoroughly than normally accustomed, however in the end it has been very rewarding. ODK is, and remains to be, the most reliable and complete suite of tools for form-based data collection, and hopefully with the support of the open-source community will continue to be long into the future!

Using PostGIS to Read and Write FlatGeobuf Files (With Examples)

Sam — Thu, 13 Jun 2024 13:41:29 +0000

Post adapted from https://www.openstreetmap.org/user/spwoodcock/diary/402948

Flatgeobuf in Python

Flatgeobuf is an excellent replacement for shapefile, particularly for geospatial data on the web.

With web/javascript being the main target, currently there is no official implementation in Python to read/write data.

Instead devs should most likely use the GDAL Python bindings.

To GDAL or not to GDAL

GDAL is an incredible geospatial library and underpins so much of what we do, including our databases (PostGIS).

However, sometimes it might be a bit heavyweight for what we are trying to achieve.

Installing it as a base system dependency inevitably installs everything - there are no options.

Install size is especially important when building container images, that we want to be as small as possible for distribution.

GDAL in PostGIS

PostGIS uses GDAL for most of it's geospatial processing, including reading and writing various geospatial file formats.

When developing a web application in Python, including a database, there is a good chance you are using Postgis already as part of your software stack.

So today I thought: why not just use the geospatial processing built into PostGIS for reading and writing flatgeobuf data?

This would save having to install GDAL alongside my Python API and reduce container image size significantly.

The solution wasn't super simple, but works quite nicely for my use case!

Database Access

First we need a way to access the database.

An example using SQLAlchemy could be:

from sqlalchemy.engine import create_engine
from sqlalchemy.orm import DeclarativeBase, Session

def get_engine(db: Union[str, Session]):
    """Get engine from existing Session, or connection string.
    If `db` is a connection string, a new engine is generated.
    """
    if isinstance(db, Session):
        return db.get_bind()
    elif isinstance(db, str):
        return create_engine(db)
    else:
        msg = "The `db` variable is not a valid string or Session"
        log.error(msg)
        raise ValueError(msg)

This example allows for an existing connection to be re-used from an endpoint, for example a FastAPI dependency.

The nitty-gritty SQL


from geojson import FeatureCollection
from sqlalchemy.orm import Session

def geojson_to_flatgeobuf(db: Session, geojson: FeatureCollection):
    """From a given FeatureCollection, return a memory flatgeobuf obj."""
    sql = f"""
        DROP TABLE IF EXISTS public.temp_features CASCADE;
        CREATE TABLE IF NOT EXISTS public.temp_features(
            id serial PRIMARY KEY,
            geom geometry
        );
        WITH data AS (SELECT '{geojson}'::json AS fc)
        INSERT INTO public.temp_features (geom)
        SELECT
            ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom
        FROM (
            SELECT json_array_elements(fc->'features') AS feat
            FROM data
        ) AS f;
        WITH thegeom AS
        (SELECT * FROM public.temp_features)
        SELECT ST_AsFlatGeobuf(thegeom.*)
        FROM thegeom;
    """
    # Run the SQL
    result = db.execute(text(sql))
    # Get a memoryview object, then extract to Bytes
    flatgeobuf = result.fetchone()[0].tobytes()
    # Cleanup table
    db.execute(text("DROP TABLE IF EXISTS public.temp_features CASCADE;"))
    return flatgeobuf

The function requires a FeatureCollection geojson.

Now I'm sure this is a much more efficient way to write this by nesting SQL SELECTs, but I was too lazy to debug and I find this approach quite readable, albeit slightly less efficient.

Using the code

An example of using in FastAPI:

import geojson
from app.db.postgis_utils import geojson_to_flatgeobuf
from io import BytesIO

@router.post("/something")
async def something(
    upload_geojson: UploadFile = File(...),
    db: Session = Depends(database.get_db),
):
    json_obj = await upload_geojson.read()
    parsed_geojson = geojson.loads(json_obj)
    flatgeobuf = BytesIO(geojson_to_flatgeobuf(db, parsed_geojson))
    *do something with flatgeobuf file*

Limitations

There is one glaringly obvious limitation of this approach: if reading the FlatGeobuf is implemented in the same way then we lose the benefit of it's 'cloud native' encoding.

Reading requires downloading the entire file, passing to PostGIS, and returning a GeoJSON.

However, that was not the intended purpose of this workaround.

FlatGeobuf is primarily a format meant for browser consumption. With excellent support via the npm package.

So while the backend API can write data to FlatGeobuf without requiring dependencies, the frontend can then read the data if it's hosted somewhere online (i.e. an S3 bucket).

Reading The Data Again in Python

In some cases you may wish to access all of the data, say to convert to a different format.

This is also possible directly in the database.
I ended up writing the reverse query flatgeobuf --> geojson:

async def flatgeobuf_to_geojson( db: Session, flatgeobuf: bytes ) -> Optional[geojson.FeatureCollection]:
    """Converts FlatGeobuf data to GeoJSON.

    Args:
        db (Session): SQLAlchemy db session.
        flatgeobuf (bytes): FlatGeobuf data in bytes format.

    Returns:
        geojson.FeatureCollection: A FeatureCollection object.
    """
    sql = text(
        """
        DROP TABLE IF EXISTS public.temp_fgb CASCADE;

        SELECT ST_FromFlatGeobufToTable('public', 'temp_fgb', :fgb_bytes);

        SELECT jsonb_build_object(
            'type', 'FeatureCollection',
            'features', jsonb_agg(feature)
        ) AS feature_collection
        FROM (
            SELECT jsonb_build_object(
                'type', 'Feature',
                'geometry', ST_AsGeoJSON(fgb_data.geom)::jsonb,
                'properties', fgb_data.properties::jsonb
            ) AS feature
            FROM (
            SELECT *,
                NULL as properties
                FROM ST_FromFlatGeobuf(null::temp_fgb, :fgb_bytes)
            ) AS fgb_data
        ) AS features;
    """
    )

    try:
        result = db.execute(sql, {"fgb_bytes": flatgeobuf})
        feature_collection = result.first()
    except ProgrammingError as e:
        log.error(e)
        log.error(
            "Attempted flatgeobuf --> geojson conversion, but duplicate column found"
        )
        return None

    if feature_collection:
        return geojson.loads(json.dumps(feature_collection[0]))

    return None

There are two steps required.

First a table must be created with fields representing the field types in the flatgeobuf.

Then the data is extracted from the file, using the table type as reference.

This wasn’t very intuitive to me & the PostGIS docs are really lacking here, so I hope this helps someone!

DEV Community: Sam

Need webhooks? Just use the Postgres HTTP extension

Reducing complexity

How this works in practice

Adding the extension

Adding a database trigger

How to add translations to the MapLibre UI?

How do translations work in MapLibre

Introducing maplibre-ui-translations

Your help is needed!

Hacking the Potensic Atom drone to fly pre-generated waypoint missions

1. Overview: Drone Tasking Manager (DroneTM)

What is DroneTM?

Extra Links

Hardware Support

2. The Potensic Atom Series

Comparison

3. Technical Exploration

Existing Attempts

Waypoint Planning in DroneTM

Storing Waypoints In The Atom

The map.db Waypoint File

4. Implementing Waypoint Generation

An Existing Modded App

Inspecting The Waypoint Database

Creating A Waypoint File

Limitations

Importing The Generated File To A Phone

ADB In The Browser

Stay Tuned

Programming languages in 2025: what we have now and future predictions

So What Languages Do We Have: Backend

Java

C, C++

Rust

Golang

Python

Where Does Frontend Fit In?

JavaScript… On The Backend??

WebAssembly

Summary

As Promised, Some Prediction

Building an open-source community at HOTOSM: a thank you

Where This All Began

The Community

Software Development Skills Are Truly Global

Notable Contributions

HOTOSM UI

Simple Reverse Geocoding

GeoJSON AOI Parsing

FMTM Test Cases

Others

Call To Action

All About CORS Headers

About CORS

Why Does It Exist?

When Is It Triggered?

Configuring CORS Correctly

Pre-Flight Requests

Access-Control Headers

Access-Control-Allow-Origins

Access-Control-Allow-Credentials

Access-Control-Allow-Methods

Access-Control-Expose-Headers

Other Headers

Combining sync and async Python code: writing a DRY package

Async Python

Combining Sync and Async Code

How Async Works

Sync From Async

Async From Sync

How To Write A Package That Is Both Sync / Async

Integrating Unasync.py

Optional: Use Via Pre-Commit Hook

How to Migrate Plex from Windows to Linux: What Most Guides Miss

Home Server Overhaul

The Official Guide

Update SQLite File Paths

Migrating Preferences

Windows Registry

The `map.db` Waypoint File