1

I am trying to set up a web server in Node.js that serves vector tiles to be displayed in a browser using MapboxGL JS. The data for the vector tiles is stored in a Postgressql database with PostGIS plugin.

In server, I receive a geojson file and process it createing a database with next sentence:

CREATE TABLE IF NOT EXISTS countries (   
  table_id SERIAL,   
  properties jsonb not null,   
  geom geometry(GeometryZ,4326),   
  primary key (table_id) 
);

So with data stored I can add a layer with mapbox with tiles on source: http://localhost:8090/tiles/namelayer/{z}/{x}/{y}.pbf

source: {
   type: "vector",
   tiles["http://localhost:8090/tile/coropletas/{z}/{x}/{y}.pbf"]
}

How can pass properties inside tile to work with data properties??

I read that I can use a sql on DataSource table and I do this:

/** CONSTANTS **/
const TILE_SIZE = 256;
const PROJECTION_STRING = '+init=epsg:3857';

/** LIBRARIES **/
var zlib = require('zlib');
var express = require('express');
var mapnik = require('mapnik');
var Promise = require('promise');
var SphericalMercator = require('sphericalmercator');

const { pool } = require('../postgressql/config');

var mercator = new SphericalMercator({
    size: TILE_SIZE
});

mapnik.register_default_input_plugins();

var app = express();

app.get('/:namelayer/:z/:x/:y.pbf', (req, res, next) => {
    var options = {
        x: parseInt(req.params.x),
        y: parseInt(req.params.y),
        z: parseInt(req.params.z),
        layerName: req.params.namelayer
    };

   const sql = `SELECT ARRAY_AGG(f) as keys FROM (SELECT jsonb_object_keys(properties) f FROM ${options.layerName} group by f) u`;
    try {
        pool.query(sql, (error, results) => {
            if (error) {
                return res.status(500).json({
                    ok: false,
                    message: error
                });
            }

            const keys = (results && results.rows && results.rows.length > 0 && results.rows[0].keys && results.rows[0].keys.length >0) ? results.rows[0].keys.slice() : [];
            const sql = generateSQL(options, keys);

            makeVectorTile(options, sql).then( (vectorTile) => {
                zlib.deflate(vectorTile, (err, data) => {
                    if (err) {
                        return res.status(500).send(err.message);
                    }

                    res.setHeader('Content-Encoding', 'deflate');
                    res.setHeader('Content-Type', 'application/x-protobuf');
                    res.setHeader('Access-Control-Allow-Origin', '*');
                    return res.send(data);
                });
            });
        });
    } catch (e) {
        res.status(404).send({
            error: e.toString(),
        });
    }
});

function generateSQL(options, keys) {

    if (keys.length === 0) {
        return `select table_id, geom from ${options.layerName}`;
    } else {
        let sql = "";
        keys.forEach( key => {
            sql = sql + `(properties->>'${key}') as ${key},`;
        });
        sql = `select table_id, ${sql} geom from ${options.layerName}`
        return sql;
    }
};

function makeVectorTile(options, sql) {

    var extent = mercator.bbox(options.x, options.y, options.z, false, '3857');
    var map = new mapnik.Map(TILE_SIZE, TILE_SIZE, PROJECTION_STRING);
    map.extent = extent;

    var layer = new mapnik.Layer(options.layerName);
    layer.datasource = new mapnik.Datasource({
        type: process.env.DB_TYPE,
        dbname: process.env.DB_DATABASE,
        // table: options.layerName,
        table: `(${sql}) as tile`,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD
    });

    layer.styles = ['default'];
    map.add_layer(layer);

    return new Promise( (resolve, reject) => {
        var vtile = new mapnik.VectorTile(parseInt(options.z), parseInt(options.x), parseInt(options.y));
        map.render(vtile, function (err, vtile) {
            if (err) {
                return reject(err);
            }
            console.log(`${vtile.getData().length} KB`);
            resolve(vtile.getData());
        });
    });
};

module.exports = app;

With previuos code If I click on feature a receive properties of each feature but I can't work with them if I want to create a choropleth layer.

And with this code If I click on feature a receive all properties of each feature and I can create a choropleth layer but I dont know why render it incorrectly.

var express = require('express');
var SphericalMercator = require('sphericalmercator');
var mercator = new SphericalMercator({
    size: 256 //tile size
});

const { pool } = require('../postgressql/config');

var app = express();

app.get('/:namelayer/:z/:x/:y.pbf', (req, res, next) => {
    var options = {
        x: parseInt(req.params.x),
        y: parseInt(req.params.y),
        z: parseInt(req.params.z),
        layerName: req.params.namelayer
    };
    const bbox = mercator.bbox(options.x, options.y, options.z, false, '3857');

    const sql = `
        SELECT ST_AsMVT(q, '${options.layerName}', 4096, 'geom')
        FROM (
            SELECT
                table_id, properties,
                ST_AsMVTGeom(
                    geom,
                    ST_MakeEnvelope(${bbox[0]}, ${bbox[1]}, ${bbox[2]}, ${bbox[3]}, 4326),
                    4096,
                    256,
                    false
                ) AS geom
            FROM ${'public.'+options.layerName} c
        ) q;
    `;

    try {
        pool.query(sql, (error, results) => {
            if (error) {
                return res.status(500).json({
                    ok: false,
                    message: error
                });
            }

            const tile = results.rows[0];
            // 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(),
        });
    }
});


module.exports = app;

enter image description here

1

I solved adding properties stored in database as jsonb to tile.

/** CONSTANTS **/
const TILE_SIZE = 256;
const PROJECTION_STRING = '+init=epsg:3857';

/** LIBRARIES **/
var zlib = require('zlib');
var express = require('express');
var mapnik = require('mapnik');
var Promise = require('promise');
var SphericalMercator = require('sphericalmercator');

const { pool } = require('../postgressql/config');

var mercator = new SphericalMercator({
    size: TILE_SIZE
});

mapnik.register_default_input_plugins();

var app = express();

app.get('/:namelayer/:z/:x/:y.pbf', (req, res, next) => {
    var options = {
        x: parseInt(req.params.x),
        y: parseInt(req.params.y),
        z: parseInt(req.params.z),
        layerName: req.params.namelayer
    };

    const sql = `select json_data.key, jsonb_typeof(json_data.value) from ${options.layerName} x, jsonb_each(x.properties) as json_data group by key, jsonb_typeof order by key, jsonb_typeof;`
    try {
        pool.query(sql, (error, results) => {
            if (error) {
                return res.status(500).json({
                    ok: false,
                    message: error
                });
            }
            const response = (results && results.rows && results.rows.length > 0 && results.rows[0]) ? results.rows.slice() : [];
            const sql = generateSQL(options, response);

            makeVectorTile(options, sql).then( (vectorTile) => {
                zlib.deflate(vectorTile, (err, data) => {
                    if (err) {
                        return res.status(500).send(err.message);
                    }

                    res.setHeader('Content-Encoding', 'deflate');
                    res.setHeader('Content-Type', 'application/x-protobuf');
                    res.setHeader('Access-Control-Allow-Origin', '*');
                    return res.send(data);
                });
            });
        });
    } catch (e) {
        res.status(404).send({
            error: e.toString(),
        });
    }
});



function generateSQL(options, response) {

    if (response.length === 0) {
        return `select table_id, geom from ${options.layerName}`;
    } else {
        let sql = "";
        response.forEach( data => {
            sql += (data.jsonb_typeof === 'null' || data.jsonb_typeof === null) ? 
                ` properties->>'${data.key}' as ${data.key},` : 
                ` cast (properties->>'${data.key}' as ${getDataBaseType(data.jsonb_typeof)}) as ${data.key},`;
        });
        sql = `select table_id, ${sql} geom from ${options.layerName}`;
        return sql;
    }
};

function getDataBaseType(typedb) {

    switch(typedb) {
        case 'string': return 'text';
        case 'number': return 'decimal';
        default: return;
    }

};

function makeVectorTile(options, sql) {

    var extent = mercator.bbox(options.x, options.y, options.z, false, '3857');
    var map = new mapnik.Map(TILE_SIZE, TILE_SIZE, PROJECTION_STRING);
    map.extent = extent;

    var layer = new mapnik.Layer(options.layerName);
    layer.datasource = new mapnik.Datasource({
        type: process.env.DB_TYPE,
        dbname: process.env.DB_DATABASE,
        table: `(${sql}) as tile`,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD
    });

    layer.styles = ['default'];
    map.add_layer(layer);

    return new Promise( (resolve, reject) => {
        var vtile = new mapnik.VectorTile(parseInt(options.z), parseInt(options.x), parseInt(options.y));
        map.render(vtile, function (err, vtile) {
            if (err) {
                return reject(err);
            }
            resolve(vtile.getData());
        });
    });
};

module.exports = app;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.