Querying Postgres with Python Fastapi Backend and Leaflet-Geoman Frontend - Applying Geometry Filters

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.

Frontend

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.

The javascript is straight forward: initialize leaflet and a map instance, add geoman controls and add a geoman listener to whenever a polygon is created on canvas.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

var osmUrl = 'http://{s}.tile.osm.org/{z}/{x}/{y}.png',
  osmAttrib = '&copy; <a href="http://openstreetmap.org/copyright">OpenStreetMap</a> contributors',
  osm = L.tileLayer(osmUrl, {
    maxZoom: 18,
    attribution: osmAttrib
  });

// initialize the map on the "map" div with a given center and zoom
var map = L.map('map').setView([50, 8], 6).addLayer(osm);

// add leaflet.pm controls to the map
map.pm.addControls();

map.on('pm:create', ({ workingLayer	}) => {
  self_drawn = map.pm.getGeomanDrawLayers(true)
  console.log(self_drawn)
});

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

{
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "properties": {},
            "geometry": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [
                            7.049103,
                            50.722112
                        ],
                        [
                            7.049103,
                            50.733413
                        ],
                        [
                            7.077255,
                            50.733413
                        ],
                        [
                            7.077255,
                            50.722112
                        ],
                        [
                            7.049103,
                            50.722112
                        ]
                    ]
                ]
            }
        }
    ]
}

If you made it to this point, half of the work is already done! 💪

Backend

Whenever a polygon is drawn on our webpage, a simple GET request should be fired and return our result.

2. The server side

Server perks

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.

Server options

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.

1. Receive the geometries
2. Wrap the geometries in a SQL statement
3. Query the database
4. Receive the database result
5. 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

1

../query/<geometry_string>

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

1

SELECT * FROM mytable WHERE geometry_of(lat,long) IN transform_string_to_geometry(geometry_string)

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.

Frontend

We need to add something to our frontend as we currently have an object returned by our simple statement:

1
2

self_drawn = map.pm.getGeomanDrawLayers(true)
self_drawn.toGeoJSON()

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.

1

JSON.stringify(self_drawn.toGeoJSON().features[0])

The result is a plain string, ready to be passed to fastapi!

1

"{"type":"Feature","properties":{},"geometry":{"type":"Polygon","coordinates":[[[7.049103,50.722112],[7.049103,50.733413],[7.077255,50.733413],[7.077255,50.722112],[7.049103,50.722112]]]}}"

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

// ...
map.on('pm:create', ({ workingLayer	}) => {
  self_drawn = map.pm.getGeomanDrawLayers(true)
  $.get("../query/" + JSON.stringify(self_drawn.toGeoJSON().features[0])).done(function (data) {

      self_drawn.bindPopup(
          data.toString(),
          // add some popup css if you like
           {
              maxHeight: 500,
              minWidth: 180,
              maxWidth: 220
          })
  });
});

With this code, the frontend is not only ready to send but also to receive data!

Backend

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# skipping other imports, basic routes
# ...

# 1. Database connection
import psycopg2
db_connection = psycopg2.connect(
    host="localhost",
    port="5432",
    dbname="testdb",
    user="postgres",
    password="samplepassword"
)
db_connection.set_session(readonly=True)  # read only!
cur = db_connection.cursor() # db cursor

# 2. Fastapi Route
@app.get("/query/{geometry_string}") # corresponds to the ajax request above $.get("../query/" + <geometry_string>)...
async def geometry_filter(geometry_string: str = None):

    # Note: for testing purposes f-strings or string concatenation are fine but never to be used in production!
    # Always use parametrized queries instead!

    db_query = """
    SELECT * FROM mytable
    WHERE ST_Intersects(ST_SetSRID(ST_MakePoint(lat, long),4326), -- creating a point from lat long in EPSG 4326
                        ST_SetSRID(ST_GeomFromGeoJSON('%(geometry_string)s'),4326)) -- creating the geometry from string EPSG 4326
        """
    cur.execute(db_query,{"geometry_string":geometry_string}) # execute the query safely
    return cur.fetchall()[0] # comes back as array, hence to be indexed with [0], return to frontend

1

localhost:8000/query/<geometry_string>

1

lat,long,value\n50.1,8,130\n49.3,7.56,133

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!