24

Prologue:

This is a question arising often in SO:

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

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:

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

Improve this question

1 Answer 1

Reset to default
24

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:

    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')
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Can you help me with this question? I struggle to call ST_FrechetDistance function from Django.
I love this 1 human QA. My only critique is that the GeoDjango Appendix adds nothing but initial confusion. From the beautifully linked source code for GeoFunc, it appears there is no difference between Func and GeoFunc. If that's the case, I think it would improve the answer to remove the GeoDjano Appendix section, since it only leads GeoDjango users (at least me) to believe there is a difference between the two classes. Since that's not the case, why include the appendix?... If I'm misunderstanding the difference between Func and GeoFunc, please advice. Cheers and thanks!
@vitale232 Although Func and GeoFunc seem quite similar, they are different in essence. Func cannot handle geo-functions that take as arguments Points, Polygons etc. (you can test that in a test project if you like). GeoFunc handles functions a bit differently and that can be seen here: github.com/django/django/blob/… (it is the GeoFuncMixin that separates GeoFunc from Func). Thank you too for appreciating my QA :D
Thanks, @JohnMoutafis. I got lazy when browsing the source code and failed to make my way to the GeoFuncMixin source. That clears things up :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.