How to make/use a custom database function in Django

Prologue:

This is a question arising often in SO:

• Equivalent of PostGIS ST_MakeValid in Django GEOS
• Geodjango: How to Buffer From Point
• Get random point from django PolygonField
• Django custom for complex Func (sql function)

and can be applied to the above as well as in the following:

• Django F expression on datetime objects

I wanted to compose an example on SO Documentation but since it got shut down on August 8, 2017, I will follow the suggestion of this widely upvoted and discussed meta answer and write my example as a self-answered post.

Of course, I would be more than happy to see any different approach as well!!

---

Question:

Django/GeoDjango has some database functions like Lower() or MakeValid() which can be used like this:

Author.objects.create(name='Margaret Smith')
author = Author.objects.annotate(name_lower=Lower('name')).get()
print(author.name_lower)

Is there any way to use and/or create my own custom database function based on existing database functions like:

• Position() (MySQL)
• TRIM() (SQLite)
• ST_MakePoint() (PostgreSQL with PostGIS)

How can I apply/use those functions in Django/GeoDjango ORM?

Django provides the Func() expression to facilitate the calling of database functions in a queryset:

Func() expressions are the base type of all expressions that involve database functions like COALESCE and LOWER, or aggregates like SUM.

There are 2 options on how to use a database function in Django/GeoDjango ORM:

For convenience, let us assume that the model is named MyModel and that the substring is stored in a variable named subst:

from django.contrib.gis.db import models as gis_models

class MyModel(models.Model):
    name = models.CharField()
    the_geom = gis_models.PolygonField()

1. Use Func()to call the function directly:

   We will also need the following to make our queries work:

   • Aggregation to add a field to each entry in our database.
   • F() which allows the execution of arithmetic operations on and between model fields.
   • Value() which will sanitize any given value (why is this important?)

   The query:

   MyModel.objects.aggregate(
       pos=Func(F('name'), Value(subst), function='POSITION')
   )
   

2. Create your own database function extending Func:

   We can extend Func class to create our own database functions:

   class Position(Func):
       function = 'POSITION'
   

   and use it in a query:

   MyModel.objects.aggregate(pos=Position('name', Value(subst)))
   

---

GeoDjango Appendix:

In GeoDjango, in order to import a GIS related function (like PostGIS's Transform function) the Func() method must be replaced by GeoFunc(), but it is essentially used under the same principles:

class Transform(GeoFunc):
    function='ST_Transform'

There are more complex cases of GeoFunc usage and an interesting use case has emerged here: How to calculate Frechet Distance in Django?

---

Generalize custom database function Appendix:

In case that you want to create a custom database function (Option 2) and you want to be able to use it with any database without knowing it beforehand, you can use Func's as_<database-name> method, provided that the function you want to use exists in every database:

class Position(Func):
    function = 'POSITION' # MySQL method

    def as_sqlite(self, compiler, connection):
        #SQLite method
        return self.as_sql(compiler, connection, function='INSTR')

    def as_postgresql(self, compiler, connection):
        # PostgreSQL method
        return self.as_sql(compiler, connection, function='STRPOS')