How to calculate Great circle distance at database level in django orm

Question

I'm trying to get users by a given filter. One of the filters is the distance between users. My problem is calculating the distance of a large circle between the participants. In the current implementation, I get:

TypeError: the float() argument should be a string or a real number, not "F"

The reason for the error is clear, but I can't find how to fix it, if at all possible. I could count everything in a loop, but that doesn't seem to be the right approach, so I'm thinking about using annotations. Before asking the question, I looked through the documentation, and one possible solution seems to be to write custom database functions, but I'm not sure if that would work. It's also possible that GeoDjango can help somehow, but I couldn't find any distance functions.

At the moment, I use geopy.distance.distance to calculate the distance.

As a beginner, this question is very confusing to me.

Therefore, I want to find out how to correctly determine the distance between the participants?

Models

class Participant(models.Model):
    GENDER_CHOICES = [
        ('M', ' Male'),
        ('F', 'Female'),
        ('O', 'Other'),
    ]
    user = models.OneToOneField(User, on_delete=models.CASCADE, related_name="participant",)
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    gender = models.CharField(max_length=1, choices=GENDER_CHOICES)
    avatar = models.ImageField(upload_to='avatars/')
    email = models.EmailField(unique=True)
    likes = models.ManyToManyField('self', related_name='liked_by', symmetrical=False, blank=True)
    latitude = models.DecimalField(max_digits=9, decimal_places=6)
    longitude = models.DecimalField(max_digits=9, decimal_places=6)

Views

class ParticipantListViewTest(generics.ListAPIView):
    queryset = Participant.objects.prefetch_related("likes")
    serializer_class = ParticipantSerializer
    filter_backends = [DjangoFilterBackend, filters.SearchFilter, filters.OrderingFilter]
    filterset_fields = ['gender', 'first_name', 'last_name']
    search_fields = ['first_name', 'last_name', 'gender']
    ordering_fields = ['first_name', 'last_name', 'gender']

    def get_queryset(self):
        queryset = super().get_queryset()
        max_distance = self.request.query_params.get('distance', None)

        if max_distance:
            user = User.objects.get(id=5).participant

            queryset = queryset.annotate(fact_distance=distance(
                lonlat(user.longitude, user.latitude),
                lonlat(F("longitude"), F("latitude"))).km
                                         ).filter(fact_distance__lt=float(max_distance))
        return queryset

Answer 1

It was quite difficult for me, but I was still able to do all the calculations on the database side.

from django.db.models import F, Func
from django.contrib.gis.geos import Point
from django.contrib.gis.db.models.functions import Distance

class ParticipantListView(generics.ListAPIView):
    queryset = Participant.objects.prefetch_related("likes")
    serializer_class = ParticipantSerializer
    filter_backends = [DjangoFilterBackend, filters.SearchFilter, filters.OrderingFilter]
    filterset_fields = ['gender', 'first_name', 'last_name']
    search_fields = ['first_name', 'last_name', 'gender']
    ordering_fields = ['first_name', 'last_name', 'gender']
    authentication_classes = [SessionAuthentication, BasicAuthentication]
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        queryset = super().get_queryset()
        max_distance = self.request.query_params.get('distance', None)

        if max_distance:
            auth_participant = self.request.user.participant

            queryset = queryset.annotate(fact_distance=Distance(
                Point(float(auth_participant.longitude), float(auth_participant.latitude), srid=4326),
                Func(F('longitude'), F('latitude'), 4326, function='ST_Point', output_field=PointField()))
            ).filter(fact_distance__lt=float(max_distance))

        return queryset

To make it all work, I had to use postgis as it supports django.contrib.gis.db.models.functions.Distance. I installed postgis on the next video. Also in settings.py for DATABASES and INSTALLED_APPS I have specified the following.

INSTALLED_APPS = [
    ...
    'rest_framework',
    'django.contrib.gis',
]

DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis',
        'NAME': 'datingsite_db', # the name of your database
        'USER': 'postgres', # database owner username, default postgres
        'PASSWORD': 'admin', # your database password
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

After that I had to install 3 libraries PROJ, GDAL and GEOS. I did not quite understand this step from the documentation, so I did the following: PROJ and GDAL I downloaded this site, where cp311 is the Python version, after which I installed them with pip.

pip install GDAL-3.4.3-cp311-cp311-win_amd64.whl
pip install pyproj-3.3.1-cp311-cp311-win_amd64.whl

The blessing for GEOS immediately worked.

pip install geos

After installing the libraries, you must specify the path to them in settings.py.

GDAL_LIBRARY_PATH = 'venv/Lib/site-packages/osgeo/gdal304.dll'
GEOS_LIBRARY_PATH = 'venv/Lib/site-packages/pygeos.libs/geos_c-6932ee63ecfb13ff3a77b9b9455a3f60.dll'

Also the documentation for django.contrib.gis.geos.Point and ST_Point