How to use the PostGIS aggregate function ST_AsMVT with Django ORM

user_11852262 0
Viewed 1146

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.

vector-tiles postgis postgresql django python
2 Answers

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

user_22355660

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;
        });
    }
  })
user_140535390
Related