Speed up "Find points in polygon"-query in geodjango

Question

I am working on a geodjango application where I want to find all the geogrpahic locations in a polygon. The idea is that I store all geographic information (cities, countries, POI, etc.) in a table and if I want to find everything inside a (multi)polygon that I query the database to find those points.

I have the following models:

class Location(models.Model):
    name = models.CharField(max_length=250, unique=True)
    geometry = models.GeometryField(null=True, blank=True)


class Project(models.Model):
    name = models.CharField(max_length=2000, blank=False, null=False, unique=True)
    location = models.ManyToManyField(Location, related_name='projects', blank=True)

In my view I have: class LocationDetailView(DetailView): model = Location

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        location_geometry = self.get_object().geometry

        location_projects = []
        if 'Polygon' in location_geometry.geom_type:
            # location is a Polygon or MultiPolygon
            for loc in Location.objects.prefetch_related('projects').all():
                if location_geometry.contains(loc.geometry):
                    for location_project in loc.projects.all():
                        location_projects.append({'name': location_project.name, 'pk': location_project.pk})

        # Add in a QuerySet of all the projects
        context['projects'] = location_projects
        return context

This is quite slow (and I do not have only a few hundred locations in the DB). As you can see I've tried speeding this up by prefetching the projects, but when I look at the SQL in the debug toolbar I see that (although it is a fast query) one query is duplicated 259 times:

SELECT
    "app_location"."id",
    "app_location"."name",
    "app_location"."geometry"::bytea,
FROM
    "app_location" WHERE "app_location"."id" = 979

Duplicated 259 times.

And this query is called once but is rather slow:

SELECT
    ("app_project_location"."location_id") AS "_prefetch_related_val_location_id",
    "app_project"."id",
    "app_project"."name"
FROM
    "app_project" INNER JOIN "app_project_location" ON
    ("app_project"."id" = "app_project_location"."project_id")
WHERE "app_project_location"."location_id" IN (780, ..., 1018, 1019, 1020, 1021, 1022, 1023)

How would I be able to do this more efficiently? I could ofcourse load all the objects and do the point in poly part in python but I assume that it can be done in the DB directly?

Answer 1

Thanks to @Jedndrusk his answer I looked around some more on the internet and found the solution which is much faster and in geodjango:

projects = []
if 'Polygon' in self.get_object().geometry.geom_type:
# location is a polygon
    for loc in Location.objects.filter(geometry__intersects=self.get_object().geometry).prefetch_related('projects').all():
        for project in loc.projects.all():
            projects.append({'name': project.name, 'pk': project.pk})