Multi-layer PostGIS MVT

In case you’re wondering what that prototype DIRT code looks like, it’s like this:

mvt2.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
// route query
const sql = (params, query) => {
const tables = params.table.split(',')
let mvtSQL = ``
let unionSQL = ``

tables.forEach((table, idx) => {
mvtSQL += `
,
${table}_mvt as (
SELECT ST_AsMVTGeom(ST_Transform(t.${query.geom_column}, 3857), bounds.geom) AS geom
FROM ${table} t, bounds
WHERE t.${query.geom_column} && ST_transform(bounds.geom, ${query.srid})
)
`
unionSQL += `
${idx > 0 ? 'UNION' : ''}
SELECT ST_AsMVT(${table}_mvt, '${table}') AS mvt
FROM
${table}_mvt
`
})


return `
WITH
bounds(geom) AS (SELECT ST_TileEnvelope(${params.z}, ${params.x}, ${params.y}))

${mvtSQL}

SELECT string_agg(mvt, '') as mvt
FROM (
${unionSQL}
) sub;
`
}

// route schema
const schema = {
description:
'Return table as Mapbox Vector Tile (MVT). The layer names returned are the names of the tables.',
tags: ['feature'],
summary: 'return MVT',
params: {
table: {
type: 'string',
description: 'The name of the table(s) or view(s), separated by a comma.'
},
z: {
type: 'integer',
description: 'Z value of ZXY tile.'
},
x: {
type: 'integer',
description: 'X value of ZXY tile.'
},
y: {
type: 'integer',
description: 'Y value of ZXY tile.'
}
},
querystring: {
geom_column: {
type: 'string',
description: 'Optional geometry column of the table(s). The default is geom.',
default: 'geom'
},
srid: {
type: 'string',
description: 'EPSG code of the data. Default is 3857',
default: '3857'
}
}
}

// create route
module.exports = function(fastify, opts, next) {
fastify.route({
method: 'GET',
url: '/mvt2/:table/:z/:x/:y',
schema: schema,
handler: function(request, reply) {
fastify.pg.connect(onConnect)

function onConnect(err, client, release) {
if (err)
return reply.send({
statusCode: 500,
error: 'Internal Server Error',
message: 'unable to connect to database server'
})
client.query(sql(request.params, request.query), function onResult(
err,
result
) {
release()
if (err) {
reply.send(err)
} else {
const mvt = result.rows[0].mvt
if (mvt.length === 0) {
reply.code(204)
}
reply.header('Content-Type', 'application/x-protobuf').send(mvt)
}
})
}
}
})
next()
}

module.exports.autoPrefix = '/v1'