Support for ST_AsMVT and ST_AsMVTGeom?

[mhkeller]

In PostGIS, you can serve vector tiles using a combination of ST_AsMVT and ST_AsMVTGeom. It would be really neat if you could do something similar with duckdb. Is there any plan to put these on the roadmap for duckdb_spatial?

Here are a few examples of how those functions can be strung together to create a vector-tile server:

1. minimal-mvt
2. mvt-server

[Maxxen]

Yes! I have a sketch for these functions in an old PR so will definitely be added in the future!

[mhkeller]

That's great to hear. I'm looking forward to seeing it. Thanks for your work!

[talllevy]

that is awesome @Maxxen. mind sharing the PR or branch? I was just looking for this type of functionality and would love to try to help make it happen

[Maxxen]

@talllevy Hmm, looks like I didnt push it after all and I can't find it in my git stash...

Anyway, the difficulty lies in writing the protobuf out incrementally in the aggregate function. An ideal implementation would just write out the bytes into a growable buffer similar to how e.g. most string aggregate functions already work, which would allow DuckDB to offload the aggregate states to disk. But a first step could also just be to keep a pointer in the aggregate state to a std::vector where we collect all the geometries and don't do the actual protobuf mvt encoding until the finalization step. I would use vtzero as we already use and bundle protozero, and duckdb core also provides a mini zip wrapper we can use to compress the final tile.

I should warn you though that this will probably only work well for individual/small collection of tiles and not for generating entire mbtile or pmtile archives as duckdbs current arbitrary grouped aggregate require every input to pass through every aggregate group, meaning you'll have to check intersection between every input geometry and every tile. There are smarter algorithms to do this by e.g. building a quad tree and/or recursively subdividing the tiles but that would probably require implementing a sorted/spatially aware aggregate operator a custom operator entirely (or multiple chained together), which I would like to look into at some point.

[talllevy]

thanks! I'll take a look and see if I can tackle this

[swdmike]

any update of ST_AsMVT, we really need this

[vincentsarago]

👋 We are also super interested about this feature.

Worth noting that this feature might need addition of ST_Segmentize and ST_MakeEnvelope

ref: https://github.com/developmentseed/tipg/blob/main/tipg/collections.py#L391-L401

[talllevy]

hey all. I've still been working on this, albeit very very slowly due to other obligations. I have been testing my implementation of ST_TileEnvelope and ST_MakeEnvelope first as a precursor to implementing the AsMVT functions. I will take a look at ST_Segmentize too. I'll dedicate more time to this over the next three weeks. Will give an update again then!

[pepjo]

We'd also love to have this feature, is there something we could help with?

[Maxxen]

I've revisited this again the last couple of days, now that we have fast spatial filtering through the R-Tree index I think using duckdb as a tile server backend could become somewhat feasible. In progress branch is here. Not the prettiest code I've ever written but the biggest issue so far seems to be getting ST_AsMVTGeom to work when GEOS rejects clipped geometries as invalid (which it created itself). Still also need to make ST_AsMVT handle non-geometry property columns and use the DuckDB memory allocator, but that shouldn't be that hard. I'll link to this issue once I get a PR up.

[majid-saeed]

@Maxxen any update here, anybody active on this?

[Maxxen]

@majid-saeed I haven't worked on this feature more since last update, no. But the plan is to pick it up again after next release is out. I have made some pretty big changes to the internals that should partly fix the issues I ran into last time.

[saltwick]

Hey there, I'm also interested in this feature - is there any updated estimate for when you'll be able to get back to it? Thanks!