Database generated columns⁽³⁾: GeoDjango & PostGIS

Show & Tell
1

I published my latest article in the series on database-generated columns, using PostgGIS, GeoDjango, and the GeneratedField added in Django 5.0 :robot::earth_africa:

Send feedback :point_down:
https://www.paulox.net/2023/12/11/database-generated-columns-part-3-geodjango-and-postgis/

1 Like
2

Hi Paulo, hope you’re well. I’ve been following your linked blogposts trying to get something working which I thought would be simple, but I don’t fully understand the “expression” syntax despite having read the docs and tried all sorts of different approaches.

Say I have a simple model like this:

from django.contrib.gis.db import models
from django.contrib.gis.geos import Point

class Place(models.Model):
    name = models.TextField()
    longitude = models.FloatField()
    latitude = models.FloatField()

What I would like is to create a field called lon_lat that uses the new GeneratedField feature to store the latitude and longitude in a PointField. I thought this would be as simple as something like this:

from django.contrib.gis.db import models
from django.contrib.gis.geos import Point

class Place(models.Model):
    name = models.TextField()
    longitude = models.FloatField()
    latitude = models.FloatField()
    lon_lat = models.GeneratedField(
        expression=Point("longitude", "latitude"),
        output_field=models.PointField(),
        db_persist=True,
    )

But this doesn’t work and it has sent me down a rabbit hole of Google searches that are still not help. I’ve tried many variations of that expression without luck.

I suspect I’m missing something fundamental about how the expression parameter works, but there are not many blog posts out there that really help in this context. Your posts have been the most helpful, but I still think I’m missing something.

Could you provide any advice?

thanks!
stuart

1 Like
3

My first general suggestion is to try to use your expression in an annotation, maybe in the Django shell, and check if it works or which errors you get.
Only when you are sure your expression will work, try to use in a Generated Field.
P.S. read the Django documentation for expression and for GeneratedField (in particular warning and note).

4

Thanks for the advice - this was helpful. I finally managed to get it to work, in a somewhat unusual way. I’m not sure if I’ve stumbled across a bug or it’s just my lack of in-depth knowledge, but I had to cast the expression to a GeometryField() to make this work, even though the output_field was set to PointField().

Ultimately this is the code that works with the GeneratedField model field:

from django.contrib.gis.db import models
from django.db.models.functions import Cast


class Place(models.Model):
    name = models.TextField()
    longitude = models.FloatField()
    latitude = models.FloatField()
    lon_lat = models.GeneratedField(
        output_field=models.PointField(),
        db_persist=True,
        expression=Cast(
            models.Func(
                models.F("longitude"),
                models.F("latitude"),
                function="Point",
                srid=4326,
            ),
            models.GeometryField(),
        ),
    )

If I remove the Cast function, then I get the following error when trying to migrate these changes: django.db.utils.ProgrammingError: column "lon_lat" is of type geometry but default expression is of type point

I might do some more troubleshooting tomorrow, but it’s late and I’m just glad it’s working!

Thanks - stuart.

1 Like