Django: serialize an annotated and aggregated queryset to GeoJSON

Question

I am trying to use Django ORM (or any other way using Django) to execute this query (PostgreSQL) AND send the result back to the front end in GeoJSON format. I am using Django 2.2.15

SELECT string_agg(name, '; '), geom
FROM appname_gis_observation
where sp_order = 'order1'
GROUP BY geom;

The model looks like this (models.py)

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

class gis_observation(models.Model): 
    name = models.CharField(max_length=100,null=True)
    sp_order = models.CharField(max_length=100,null=True)
    geom = models.MultiPointField(srid=4326)

So I thought this would work (views.py)

from django.core.serializers import serialize
from .models import *
from django.shortcuts import render
from django.contrib.postgres.aggregates.general import StringAgg

def show_observation(request):
  results = gis_observation.objects.values('geom').filter(sp_order='order1').annotate(newname=StringAgg('name', delimiter='; '))      
  data_geojson = serialize('geojson', results, geometry_field='geom', fields=('newname',))  
  return render(request, "visualize.html", {"obs" : data_geojson})

The ORM query works fine in the Django shell but Django complains at the serialize step: AttributeError: 'dict' object has no attribute '_meta'.

Even if the serialize step worked, I suspect it would skip my annotated field (by reading other posts)

Apparently I am not the only one who met that same problem but I could not find a solution for it.

Answer 1

This is the solution I came up with. Frankly I'd be glad to accept another answer, so any proposal still welcome! In the end, I built a Geojson array by looping though the result set. I guess I could as well have gone for a cursor sql query instead and skip the orm api entirely.

        queryset = gis_species_observation.objects.values('geom').filter(sp_order='order1').annotate(name=StringAgg('name', delimiter='; '))
        mydict = []
        results = list(queryset)
        for result in results: 
            rec = {}
            rec["type"] = "Feature"
            rec["geometry"] = json.loads(result["geom"].geojson)            
            rec["properties"] = {"name":result["name"]}
            mydict.append(rec)
        data_geojson = json.dumps(mydict)
        return render(request, "visualize_romania.html", {"mynames" :data_geojson})