Saturday, 27 February 2021

How to use the PostGIS aggregate function ST_AsMVT with Django ORM

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.



from How to use the PostGIS aggregate function ST_AsMVT with Django ORM

No comments:

Post a Comment