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.

Vistula

django-vectortiles (disclaimer: I have contributed some PRs) has implemented the first part, but the question itself is not solved. github.com/submarcos/django-vectortiles/blob/… – Mugwump

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

Tann

Could you give examples of code? I think it would describe better what you have tried. – Mugwump

I add examples in my original answer – Tann

I am very probably misunderstanding this. My AsMVT template is "%(function)s(subquery.*)". I was getting col1, col2, etc, but added .order_by() to remove any defaults, and used .annotate(friendly_name=F('path__to__field'), and now get friendly names in my MVT. Did this by tracing the compilation code you highlighted and finding when it renames to col1, etc. Probably utterly missing the point though - ignore if so. – Castellanos

I have also successfully used .annotate(friendly_name=Concat('my_field', 'another_field')) etc. I have not managed to set the feature_id_name parameter of the ST_AsMVT aggregate, or control how the shape is labelled in the MVT. – Castellanos

you can add an annotate to an aggregate ? and it add it in properties !? great ! – Tann

Answer 2

There is already a solution for serving mapbox vector tiles within Django, djangorestframework-mvt. It is quite useful according to my experience. You can filter by field values via url queries.

https://github.com/corteva/djangorestframework-mvt

A small example for using served tiles in Deck.gl:

getCityTileData = () => (
  new TileLayer({
    stroked: true,
    getLineColor: [0, 0, 192],
    getFillColor: [140, 170, 180],
    filled: false,
    getLineWidth: 1,
    lineWidthMinPixels: 1,
    getTileData: ({ x, y, z }) => {
      const mapSource = `${API_URL}/mvt/city?tile=${z}/${x}/${y}&name=ANKARA`;
      return fetch(mapSource)
        .then(response => response.arrayBuffer())
        .then(buffer => {
          const tile = new VectorTile(new Protobuf(buffer));
          const features = [];
          for (const layerName in tile.layers) {
            const vectorTileLayer = tile.layers[layerName];
            for (let i = 0; i < vectorTileLayer.length; i++) {
              const vectorTileFeature = vectorTileLayer.feature(i);
              const feature = vectorTileFeature.toGeoJSON(x, y, z);
              features.push(feature);
            }
          }
          return features;
        });
    }
  })

Crossover

Thanks for your suggestion. I already saw this library and it is very useful. I miss some additional custom filtering. I will have a look into the source code. Hopefully I can fit it to my needs. – Vistula

You're welcome. I am not sure what you mean by custom filtering but you can use generic django query filters in you request like: city=Ankara or avg__gt=5 – Crossover

djangorestframework-mvt simply builds an SQL string and executes it. It does not make use of Django's custom functions. – Mugwump

I couldn't catch what you mean by Django's custom functions. One of the main properties of Django is building and executing SQL strings with its powerful ORM. – Crossover

Ah, I meant inheriting from Func or GeoFunc, like OP did. djangorestframework-mvt does not make use of Django's ORM, but instead stitches together SQL strings. – Mugwump