Querying Postgres with Python Fastapi Backend and Leaflet & Geoman Fronted - Applying Geometry Filters
How to query a database with a user-defined geometry drawn on a Leaflet frontend?
A very common use case for map applications are custom filters. Either thematic, temporal or value-based filters play a significant role but probably in a geographical context the most dominant one might be spatial filtering. In this blog post, I describe a simple boilerplate setup based on postgres, fastapi and geoman.
1. A simple yet powerful and scalable fullstack
The whole setup is split up in a frontend and a backend part.
In modern UI design, you obviously don’t enter lat long anymore but rely on free drawing by clicking in a map. Personally for leaflet I prefer Leaflet-Geoman as it’s fast, intuitive, production-ready and easy to use. Check out the demo!
With geoman you can either decide whether you go for a simple bounding box or polygon drawing. In both cases, you can easily draw as many shapes on a map canvas and get the geometry with one click.
Find the jsfiddle here.
In case you want to get the
self_drawn layer as GeoJSON, simply type
self_drawn.toGeoJSON(). It will return a nicely formatted feature collection.
If you drew a simple bounding box the JSON will look like this.
If you made it to this point, half of the work is already done! 💪
Whenever a polygon is drawn on our webpage, a simple GET request should be fired and return our result.
Isn't postgres enough of a backend?
Unfortunately not. You cannot query a database directly from a frontend. It’s a pity as one could set up a simple static page with hard coded SQL commands, host it anywhere (even for free) and setup a read-only user in postgres for this purpose. However, using a proper web framework has many advantages.
2. The server side
Having an instance in between the frontend and postgres backend has some advantages of i.e. simple authentication methods, full control, addtional data transformation and the power of all the available python libraries around there.
As intermediate instance or rather web framework Postgrest tried to bridge this gap but has certain tradeoffs. Personally - in particular in the context of (geo-) data science - I really like pandas and hence choose python as powerful language. As a web famework Fastapi delivers the best speeds around there thanks to starlette.
3. Setting up fastapi
Fastapi is easy to learn and most importantly: is worth it!
Our python code and fastapi need to do five things now.
- Receive the geometries
- Wrap the geometries in a SQL statement
- Query the database
- Receive the database result
- Return the database result to our frontend
Our frontend will get the geometry and request the data via ajax. Fastapi receives this data on an endpoint, let’s say
and wraps the
geometry_string in an SQL statement. Python will query the database with this statement and hand over the result to fastapi.
4. Postgres sample table
Let’s assume a very simple table (localhost:5432) called
mytable to be queried containing coordinates and some data:
The SQL pseudocode will work like this
Eventually, after python queried the database, fastapi returns the data to our frontend where it can be further processed.
5. Putting it all together!
Below you find some code snippets taken from a real-world application I just finished.
We need to add something to our frontend as we currently have an object returned by our simple statement:
As we cannot pass an object to the URL, we need to stringify the GeoJSON! Note: for simplicity we assume that you just want to pass one polygon to the SQL query.
The code below just gets the first feature of our self drawn layer (our polygon) and unparses the object to a string.
The result is a plain string, ready to be passed to fastapi!
Let’s say that we want to display the result in a popup of our polygon as soon as the user draws it. Thanks to leaflet, it’s not too complicated. Below, we perform an asynchronous request. So as soon, as the result is ready it will be bound as a popup to our polygon. If your database isn’t too big, thanks to the amazing speeds of postgres and fastapi, it should happen quite instantly.
With this code, the frontend is not only ready to send but also to receive data!
Flexibility with web frameworks
Note that the code below is just the crucial part for our workflow. In any webapp, there is always more code involved delivering at least the
index.html. This html-file could contain the code snippet from the jsfiddle above, providing the basic leaflet application. Read here how to do it with fastapi.
The routing routine here would work the same way with flask, django and all their siblings! So feel free to choose any other web framework.
You could also test the endpoint by simply typing in your browser:
If everything works fine, you will receive the answer, i.e. as new-line
\n-formatted csv, ready to be parsed by your frontend:
Proudly click the popup and pat your shoulder! 🙌
If you did everything right, you should be able to see a nice popup displaying your data! Of course, you should format the response in an appropriate way by parsing correctly but that’s another story.
In case you want to get familiar with fastapi and need a minimal example, simply serving your
index.html containing the code from the jsfiddle above, find my answer on stackoverflow.
Otherwise always keep up your motivation and don’t let small errors stop you!