How to use the PostGIS aggregate function ST_AsMVT with Django ORM

Question

Problem

I would like to create a Mapbox vector tile (MVT) in Django, using the ORM. In SQL (PostgreSQL, PostGIS) the SQL query looks like this for the tile with zoom=8, x=137, y=83:

SELECT ST_AsMVT(tile)
FROM (SELECT id, ST_AsMVTGeom(geometry, ST_TileEnvelope(8, 137, 83)) AS "mvt_geom"
      FROM geomodel
      WHERE ST_Intersects(geometry, ST_TileEnvelope(8, 137, 83))
     ) AS tile;

ST_AsMVT aggregates all rows and the output is a binary Field (bytea) which can be sent as response.

As GeoDjango does not include the specific PostGIS functions I created custom functions for them:

class TileEnvelope(Func):
    function = "ST_TileEnvelope"
    arity = 3
    output_field = models.GeometryField()


class AsMVTGeom(GeoFunc):
    function = "ST_AsMVTGeom"
    arity = 2
    output_field = models.GeometryField()

I managed to create the inner subquery and it works:

tile_envelope = TileEnvelope(8, 137, 83)
tile_geometries = GeoModel.objects.filter(geometry__intersects=tile_envelope)
tile_geometries_mvt = tile_geometries.annotate(mvt_geom=AsMVTGeom("geometry", tile_envelope))
tile_geometries_mvt = tile_geometries_mvt.values("id", "mvt_geom")

print(tile_geometries_mvt)
>> <QuerySet [{'id': 165, 'mvt_geom': <Point object at 0x7f552f9d3490>}, {'id': 166, 'mvt_geom': <Point object at 0x7f552f9d3590>},...>

Now the last part is missing. I would like run ST_AsMVT on tile_geometries_mvt:

SELECT ST_AsMVT(tile)
FROM 'tile_geometries_mvt' AS tile;

Question

I tried to create a custom Aggregate function for ST_AsMVT, but was not successful. Normally aggregate functions like MAX, for example, expect one column as input, whereas ST_AsMVT expects an anyelement set row.

How can I turn ST_AsMVT into a Django Aggregate (similar to this SO question)?

I know, that I can use raw_sql queries in Django, but this question is explicitly about solving it with the Django ORM.

Answer 1

I have already try to make AsMVT aggregate but it seems not possible to achieve that (for the moment).

• ST_ASMVT design should have subquery.*, and not (geom, column_1, ...), whithout it works but ST_ASMVT don't keep column name (renamed as f1, f2, f3 etc)
• if we use subquery.* in aggregate template, it's ok, ST_ASMVT keep properties name.. but django orm rename columns in subqueries.. so properties are named __col1, __col2 etc . this mechanism is defined in SQLAggregateCompiler which can not be override

examples :

class AsMVT(Aggregate):
    name = "AsMVT"
    function = "ST_ASMVT"
    template = (
        "%(function)s((%(distinct)s%(expressions)s), '%(layer_name)s', %(extent)s)"
    )


features.aggregate(
    tile=AsMVT(
        F("geom_prepared"),
        F("name"),
        extent=self.vector_tile_extent,
        layer_name=self.get_vector_tile_layer_name(),
    )
)

generate a vector tile but property name is renamed f1 by ST_ASMVT. ST_ASMVT required a real rowset instead of subquery list field

class AsMVT(Aggregate):
    name = "AsMVT"
    function = "ST_ASMVT"
    template = "%(function)s(subquery.*, '%(layer_name)s', %(extent)s)"


features.aggregate(
    tile=AsMVT(
        F("geom_prepared"),
        F("name"),
        extent=self.vector_tile_extent,
        layer_name=self.get_vector_tile_layer_name(),
    )
)

generate a vector tile but property name is renamed __col1 by django ORM in aggregate join