Using the new MVT function in PostGIS使用PostGIS中的MVT函数ST_AsMVT()

We ❤️ vector tiles. They’re a key part of our modern open source spatial stack, and we’ve played around with several ways to generate them over the y̵e̵a̵r̵s̵
months. We’ve pulled them out of Carto’s Maps API, even before they were a documented feature. We’ve built simple tools cut them from geojson, and used tilestrata to create them from shapefiles. We host our own openmaptiles server to serve up vector tiles with openstreetmap data.

我们❤️ 矢量平铺。它们是我们现代开源空间堆栈的关键部分，我们过去几个月已经研究了几种生成它们的方法。我们已经把它们从Carto的maps api中拉了出来，甚至在它们成为一个文档化的特性之前。我们构建了从geojson中剪切它们的简单工具，并使用tilestrata从shapefile中创建它们。我们托管我们自己的openmappiles服务器来提供带有openstreetmap数据的向量块。

We recently found ourselves setting up a new PostGIS-based data service, and trying to figure out the best way to serve vector tiles from it. In the past, vector tiles have involved some other layer of code to process and compress the raw spatial data that comes from the database.

我们最近发现自己正在建立一个新的基于PostGIS的数据服务，并试图找出从中提供矢量切片服务的最佳方法。在过去，矢量图块涉及到一些其他的代码层来处理和压缩来自数据库的原始空间数据。

 

 Prior to ST_AsMVT(), you needed middleware or some other package to clip, build, and compress a vector tile from raw data

在ST_AsMVT()函数出现之前，你需要中间件或者其它的包来裁剪、生成和压缩从原始数据来制作矢量切片

As of PostGIS 2.4.0, ST_AsMVT() is a thing! 🎉 Now you can get a ready-to-consume vector tile right from the database, without another layer on top. What’s also great is that this works seamlessly with express, our technology of choice for building web APIs. This means we can build out a custom vector tile endpoint with just a few lines of JavaScript! (We found several great tutorials on using the new features, but none that specifically paired them with express and pg-promise, so here’s our contribution for others who may be using this stack). The new PostGIS feature cuts out the middle man!

而从PostGIS2.4.0开始，ST_AsMVT()出现了！现在你可以直接从数据库中获取一个矢量切片，而无需在上面再包裹一层。还有一点很好，那就是它可以与express无缝结合，express是我们构建webapi的首选技术。这意味着我们可以用几行JavaScript构建一个定制的矢量切片端点(我们找到了一些关于使用新特性的很好的教程，但是没有一个专门将它们与express和pg-promise结合使用，因此下面是我们对其他可能使用此堆栈的人的贡献）。新的PostGIS功能去掉了中间人！

 

 With ST_AsMVT(), you can get ready-to-consume vector tiles right out of PostGIS!

使用ST_AsMVT()，你可以从PostGIS中直接获取矢量切片！

Here’s a dirt-simple vector tile route. You hit the endpoint with your z/x/y tile ids, and get back a tile.

这是一个非常简单的矢量切片route。您使用z/x/y切片ID命中端点，然后得到一个切片。

/* GET /tiles/:z/:x/:y.mvt */
/* Retreive a vector tile by tileid 通过切片id获取一个矢量切片*/
router.get('/tiles/:z/:x/:y.mvt', async (req, res) => {
  const { z, x, y } = req.params;

  // calculate the bounding polygon for this tile 
  const bbox = mercator.bbox(x, y, z, false);

  // Query the database, using ST_AsMVTGeom() to clip the geometries
  // Wrap the whole query with ST_AsMVT(), which will create a protocol buffer
  const SQL = `
    SELECT ST_AsMVT(q, 'internal-layer-name', 4096, 'geom')
    FROM (
      SELECT
          somecolumn,
          ST_AsMVTGeom(
              geom,
              ST_MakeEnvelope(${bbox[0]}, ${bbox[1]}, ${bbox[2]}, ${bbox[3]}, 4326),
              4096,
              256,
              false
          ) geom
      FROM sometable c
    ) q
  `;

  try {
    const tile = await db.one(SQL);

    // set the response header content type
    res.setHeader('Content-Type', 'application/x-protobuf');

    // trigger catch if the vector tile has no data, (return a 204)
    if (tile.st_asmvt.length === 0) {
      res.status(204);
    }    
    
    // send the tile!
    res.send(tile.st_asmvt);
  } catch (e) {
    res.status(404).send({
      error: e.toString(),
    });
  }
});

A few things to note:

• ST_AsMVT() works hand-in-hand with ST_AsMVTGeom(), which clips the geometries at the tile edge—plus a tile buffer in the same units as the extent (see below).
• The subquery above gets us multiple rows of tile-ready geometries and their properties (or attributes for the GIS-minded), the the wrapping query uses ST_AsMVT(), which bundles it all up in a nice compressed tile in protocol buffer format.
• We must get the corners of the tile before we can call ST_AsMVTGeom(); this is done in node using the @mapbox/sphericalmercator package. The resulting coordinates are added to the SQL query as a bounding polygon using ST_MakeEnvelope();
• The 4096 you see in both ST_AsMVT() and ST_AsMVTGeom() is the tile’s extent, or the internal coordinate system of tile. For more on why 4096 is the default for this, here’s a github issue thread about it.
• We’re using pg-promise and async-await to run the query. If all goes well, we get a nice vector tile blob back, and can send it right out the door with res.send() All that’s necessary is to set the response Content-Type header to application/x-protobuf
• If the query yields no results because there are no geometries within the bounds of the requested tile, we return an HTTP 204 (no data). This prevents console warnings/errors in the client that’s consuming the vector tiles.

We were surprised at how quickly this approach “just worked”, and that the data returned from the database could just be sent back in the express response without any additional work. We had mapboxGL consuming our new tile endpoint in minutes!

Some things to keep tinkering with:

• So far we’ve only used this method to produce vector tiles with a single internal layer. Our next step will be to pack several internal layers in to the same tile.

          到目前为止，我们只使用这种方法来生成具有单个内层的矢量图块。我们的下一步将是把几个内部层打包到同一个瓷砖中。

• There may be some efficiency gained if we can pipe/stream the data from the database into the response, especially for larger multi-layer tiles.

         如果我们能够将数据库中的数据通过管道/流传输到响应中，可能会获得一些效率，特别是对于更大的多层图块。

Thanks for reading! Have you used ST_AsMVT()? If you have pointers, pitfalls, or general comments, let us know on twitter at @nycplanninglabs.

Happy mapping!

https://medium.com/nyc-planning-digital/using-the-new-mvt-function-in-postgis-75f8addc1d68