djangodjango-modelsgeodjango

Database constraint on latitude and longitude of a PointField with MySQL backend


I have a following Django model:

class Place(models.Model):
    location = models.PointField(geography=True)

The location field seems to happily accept any arbitrary number for latitude and longitude. Even past the limits of +-180 and +-90. My research says this is due to SRID not being set in the database column, even though the database was generated by Django. I do belive the MySQL backend just does not support properly setting the SRID on database level.

To prevent any issues from arising, I'm trying to write constraints on the field. However, I do not seem to be able to generate a working constraint object. Perfect outcome would be checking the lat & lng are in range of the PointField object's extent field, but I'd be happy to settle for a hardcoded limits also.

Alternatively, a solution to make the database respect the proper lat & lng limitations without any extra Django constraints would be greatly appreciated.

I've tried many iterations of something like this. I have not found a combination that would make both python and MySQL happy.

class GeometryPointFunc(Func):
    template = "%(function)s(%(expressions)s::geometry)"

    def __init__(self, expression: any) -> None:
        super().__init__(expression, output_field=FloatField())


class Latitude(GeometryPointFunc):
    function = "ST_Y"


class Longitude(GeometryPointFunc):
    function = "ST_X"

...

class Meta:
    constraints = [models.CheckConstraint(condition=models.Q(Latitude("location")__lte=90), name="lat_lte_extent_lat")]
class Meta:
    constraints = [models.CheckConstraint(condition=models.Q(Latitude("location")<=90), name="lat_lte_extent_lat")]
class Meta:
    constraints = [models.CheckConstraint(condition=models.Q(90__gte=Latitude("location"), name="lat_lte_extent_lat")]

Solution

  • If you write Q(foo__lte=bar), then the Django ORM compiler will eventually rewrite this to LessThanOrEqual(F('foo'), bar), so we can use this to work with constraints that can not (easily) be expressed with Q objects [Django-doc], we thus can use this to rewrite the constraint to:

    from django.db.models.lookups import LessThanOrEqual
    
    
    class Place(models.Model):
        # …
    
        class Meta:
            constraints = [
                models.CheckConstraint(
                    condition=LessThanOrEqual(Latitude('location'), 90),
                    name='lat_lte_extent_lat',
                )
            ]