Django Filter by Distance using only Sqlite (No GeoDjango, PostGres, MySQL, GIS)

Question

I have a Django application that needs to filter users by location (show users who are within a given distance of the logged in user).

Right now I am able to capture the logged in users latitude and longitude coordinates using HTML5 and save them into the Location table in my SQLite backend.

I have the GeoIP2 lists with city and country in a folder in my project, I'm not sure how I can use them but they seem to be a good option as they take in lat and lon coordinates and generate out a location.

I know there are a few options here, for example making a point value from the lat lon coordinates, which would then perhaps be able to filter by radius? Etc, 5km, 10km, 15km?

I'm relatively new to all of this which is why I am asking for a clean minimal solution.

I do not want to use PostGres, GIS, GeoDjango.

If someone has a simple solution for doing this using Sqlite and possibly the geoip2 lists, I would very much appreciate you sharing it!

models.py

class Location(models.Model):
latitude    = models.DecimalField(max_digits=19, decimal_places=16)
longitude   = models.DecimalField(max_digits=19, decimal_places=16)
user        = models.OneToOneField(User, on_delete=models.CASCADE, null=False, related_name='loc')

def __str__(self):
    return f'{self.user}\'s location'


@login_required
def insert(request):
    location = Location(latitude=request.POST['latitude'], 
longitude=request.POST['longitude'], user = request.user)
    location.save()
    return JsonResponse({'message': 'success'})

def location_view(request):
    queryset = User.objects.annotate(
        radius_sqr=pow(models.F('loc__latitude') - 
request.user.loc.latitude, 2) + pow(models.F('loc__longitude') - 
request.user.loc.longitude, 2)
    ).filter(
        radius_sqr__lte=pow(radius_km / 9, 2)
    )
    return django.shortcuts.render_to_response(request, context, 'connect/home.html')

location page.html example

{% for user in location %}
<h5><b>{{ user.first_name }} {{ user.last_name }}</b></h5>
{% endfor %}

Damir Boltachev · Accepted Answer · 2019-02-08 12:01:01Z

5

First of all, if your user have only one position, there's more likely to use OneToOneField instead of Foreign key. It's also a good idea to place 'related_name' parameter in any relation you use to enhance queries building. Like so:

    class Location(models.Model):
    lat = models.DecimalField(max_digits=19, decimal_places=16)
    lon = models.DecimalField(max_digits=19, decimal_places=16)
    user = models.OneToOneField('User', on_delete=models.CASCADE, related_name='loc')

    def __str__(self):
        return f'{self.user}\'s location'

Note, that null=False is default value for related fields. Now we need to calculate square of radius as sum of squares of differences between latitudes and longitudes. For calculations on each row we may use django.models.F class to use in annotate or filter method. Finally, we use calculated value in filter lte method (it important to filter by square of radius). For request.user it will be looked like:

from django.db.models import F
from django.shortcuts import render_to_response

def location_view(request):
    radius_km = request.data.get('radius', 0)
    queryset = User.objects.annotate(
        radius_sqr=pow(models.F('loc__latitude') - 
request.user.loc.latitude, 2) + pow(models.F('loc__longitude') - 
request.user.loc.longitude, 2)
    ).filter(
        radius_sqr__lte=pow(radius_km / 9, 2)
    )
    context = dict(location=queryset)
    return render_to_response('connect/home.html', context)

Place radius as number in request POST payload in javascript function, that will request POST to location_view.

Note, that this calculation will be inaccurate on the big distances and more inaccurate in near-polar area. Catch this 9 coefficient near radius? It's a number of kilometers in one degree on the equator). For more accuracy we need to go deeper in math and use a calculated coefficient, according to user's latitude.

Need more accuracy? Just use GeoDjango, or side API like google geocoding.

edited Feb 8, 2019 at 12:01

answered Feb 7, 2019 at 11:26

Damir Boltachev

1011 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Trilla Over a year ago

Hi! Thank you very much for your help and suggestions, I have taken them on board. I still have a few questions. So I have added the queryset in the def location_view (I have updated my question so you can see). What would be the django templating syntax to render out the queryset? Ex. {% for user in qs %}? Also, one last question, is it possible to edit the javascript function to render the queryset view when the save location button is pressed? Right now it calls the def insert_view which saves the lat and lon. Thank you again.

Damir Boltachev Over a year ago

First, yes, it's a good practice to iterate over queryset in templates.

Damir Boltachev Over a year ago

According to second question, from save_location view you may return ‘django.shortcuts.render_to_response()‘ with given template name, request object and context, containing givens queryset.

Trilla Over a year ago

What the actual syntax be to return the queryset in location_view? I have tried {% for user in queryset %} but nothing is rendering. I want to make sure I am understanding this correctly. I have path('location', connect_views.location_view, name='location_filter') which calls location_view with queryset = User.objects.annotate etc Is that correct?

Damir Boltachev Over a year ago

{% for user in queryset %} is part of templates syntax. Use it in your template. You need to add new template to your project. Let it be your connect/home.html. If there's many kinds of responses, you need a base template and expand it with your locations template. See how. Anyway, you also need to add templates support to your project.

|

Collectives™ on Stack Overflow

Django Filter by Distance using only Sqlite (No GeoDjango, PostGres, MySQL, GIS)

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related