Great progress so far on setting up the subquery part! The tricky thing about ST_AsMVT is that it operates on a row set (PostGIS's anyelement type) rather than a single column—something standard Django aggregates like MAX or COUNT don't handle out of the box. Let's build a custom aggregate class tailored to this PostGIS function.

Step 1: Define the Custom AsMVT Aggregate #

We need an Aggregate subclass that generates the correct SQL syntax for ST_AsMVT, which expects a row alias as its input. We'll use BinaryField as the output field since the function returns binary bytea data (the raw MVT tile):

from django.db import models
from django.db.models import Aggregate

class AsMVT(Aggregate):
    function = "ST_AsMVT"
    # Template ensures we reference the row set alias (default: 'tile')
    template = "%(function)s(%(alias)s)"
    output_field = models.BinaryField()

    def __init__(self, alias="tile", **kwargs):
        self.alias = alias
        super().__init__(**kwargs)

    def as_sql(self, compiler, connection):
        # Pass the row alias into the SQL template
        sql, params = super().as_sql(compiler, connection)
        return sql.replace("%(alias)s", self.alias), params

Step 2: Chain the Aggregate to Your Subquery #

Now you can attach this aggregate to your prepared subquery. The key is to alias your subquery result set so ST_AsMVT can reference it properly:

# Your existing code to prepare tile-ready geometries
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)
).values("id", "mvt_geom")

# Alias the subquery and run the ST_AsMVT aggregate
mvt_result = tile_geometries_mvt.alias(tile=tile_geometries_mvt).aggregate(
    mvt_data=AsMVT()
)

# Extract the binary MVT tile data
mvt_tile_bytes = mvt_result["mvt_data"]

Step 3: Serve the MVT Tile in a View #

To deliver this tile to clients, return it as an HTTP response with the correct content type:

from django.http import HttpResponse

def serve_mvt_tile(request, zoom, x, y):
    zoom = int(zoom)
    x = int(x)
    y = int(y)
    
    # Prepare the tile geometries
    tile_envelope = TileEnvelope(zoom, x, y)
    tile_geometries = GeoModel.objects.filter(geometry__intersects=tile_envelope)
    tile_geometries_mvt = tile_geometries.annotate(
        mvt_geom=AsMVTGeom("geometry", tile_envelope)
    ).values("id", "mvt_geom")
    
    # Generate the MVT tile
    mvt_result = tile_geometries_mvt.alias(tile=tile_geometries_mvt).aggregate(
        mvt_data=AsMVT()
    )
    
    # Return the binary tile data
    return HttpResponse(
        mvt_result["mvt_data"],
        content_type="application/vnd.mapbox-vector-tile"
    )

How This Works #

• The custom AsMVT aggregate uses a SQL template that targets the entire row set (via the alias tile) instead of a single column.
• The alias() method tells Django to name our subquery result set tile, which matches exactly what ST_AsMVT expects as input.
• BinaryField ensures Django correctly handles the binary bytea data returned by PostGIS, so you can directly serve it to clients.

This approach keeps everything within the Django ORM—no raw SQL required!

内容的提问来源于stack exchange，提问作者MarcelCode