Vector Tiles Markers Generator

Tasks pipeline to generate MBtiles (vector tiles) from a PostGIS server using channel notification, og2ogr and tippecanoe. This was especially made for simple tasks like creating a layer with a lot of markers/points.

When a new notification from PG_notify is received, a GeoJSON is created with a specific SQL query for the given task and then processed with Tippecanoe to create a MBTiles file. The file can be then served for example with maptiler/tileserver-gl or consbio/mbtileserver.

Getting started with Node.js

To run the application on your machine, you will need:

• ogr2ogr (from GDAL)
• tippecanoe

Installation of ogr2ogr on ubuntu

sudo add-apt-repository ppa:ubuntugis/ppa
sudo apt-get update
sudo apt-get install gdal-bin

Installation of Tippecanoe on ubuntu

git clone https://github.com/mapbox/tippecanoe.git
cd tippecanoe
make -j
make install

### Running the application

```bash
# Install dependencies.
npm ci

# Run the application.
npm run start

Getting started with Docker

To run the development environment in Docker containers, you will need:

• Docker 19+
• Docker Compose 1.25+

Then simply build and run the application:

docker build . --tag smapshot-points-vt-generate:X.X

Using the vector tiles

Be aware that current Tippecanoe configuration guess what the best max zoom is. Vector tiles use the principle of "overzoom". When max zoom is reached, the displayed vector tiles are still shown at greater zoom. If you load the map at a higher zoom of the max zoom, you'll see the tiles only if the max zoom parameter is the same as the one set by Tippecanoe on the configuration. So it means you should avoid loading tiles with .pbf directly in your front-end library, but use the TileJSON URL (generated by your vector tiling server) instead which contains the generated max zoom value.

Configuration

Tilesets are stored at /usr/src/app/output use a volume to share it with other containers using docker-compose:

volumes:
    - 'vt_tilesets:/usr/src/app/output'

Tasks file

Tasks are defined in a yaml file as ./tasks.yml. Copy a local file to the container within a DockerFile or share a volume to allow the container to access it:

volumes:
    - yourLocalRelativePath/tasks.yml:/usr/src/app/tasks.yml

Each task is defined with the name of the task under the tasks attributes:

tasks:
    nameOfTheTask:
        channelName: "myChannelName" # Channel name is use in Postgresl to trigger a new task with for example 'NOTIFY myChannelName;'
        sql: "SELECT id, location FROM images" # SQL query used to export data to GeoJSON. Geometry is automagically discovered. Other attributes are stored in the properties of each feature
        sqlColumNameRef: "images.id" # Column reference which are used in the payload and the update query. Tips: avoid ambiguity by proving table name
        vtParams: # Command parameters to generate vector tiles with Tippecanoe. Default are '--force', '--quiet' and export-input paths.
        - "-z9" # -zg auto is not recommended. Tile-join support only merging mbtiles with same max zoom level.
        - "--drop-densest-as-needed"
        - "--extend-zooms-if-still-dropping"

Trigger and Notify PSQL function

Example of a trigger and a way to notify for change on a table:

CREATE OR REPLACE FUNCTION update_points_vt() RETURNS trigger AS $$
BEGIN
 -- When state changed, update vt
 IF old.state <> new.state
 THEN
    -- To update an item (single mode)
    PERFORM pg_notify(
        'myChannelName'::text,
        json_build_object(
            'action', 'add', -- add/remove are supported
            'ref', new.id
        )::text
    );
    -- To update all items (bulk mode)
    PERFORM pg_notify('myChannelName'::text, json_build_object()::text);
 END IF;

 RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_points_vt_trigger ON images;

CREATE TRIGGER update_points_vt_trigger AFTER UPDATE on images
FOR EACH ROW EXECUTE PROCEDURE update_points_vt();

Environment

Moreover, the following environment variables need to be set at build time:

Variable	Default value	Description
DB_USER	-	The username to connect to the database
DB_PASS	-	The password to connect to the database
DB_HOST	-	The hostname to connect to the database
DB_PORT	-	The port to connect to the database
DB_NAME	-	The database name to connect to the database

Optional variables can also be set for extra features:

Variable	Default value	Description
KILL_IMAGE_NAME	-	Image name corresponding to a running container to send kill signal (for example to gracefully restart it when new tilesets are generated)
KILL_SIGNAL	-	Type of kill signal send to KILL_IMAGE_NAME container
TMP_PATH	-	Temporary working folder inside the container. Be aware that TMP_PATH and OUTPUT_PATH need to be on the same filesystem.
OUTPUT_PATH	-	Output mbtiles folder inside the container. Be aware that TMP_PATH and OUTPUT_PATH need to be on the same filesystem.

Reload the vector tiles server

A fork of consbio/mbtileserver has been created which add a file watcher functionality. Every minute a cron job is initiated to check if mbtiles inside a given folder have been changed and send a kill signal inside the container to gracefully restart the tilesets generation of mbtileserver. If you need a faster solution, you should check the next chapter.

Using kill signal

⚠ Be aware that sharing the docker socket could comprise the security of your docker instance and especially your host

The pipeline can send a kill signal to another Docker container sharing the docker socket. It can be useful to gracefully restart the vector tiles server when new tilesets are generated.

To use this feature, the docker socket needs to be mounted as a volume when using docker-compose:

volumes:
    - '/var/run/docker.sock:/var/run/docker.sock'

And docker container user smapshot_vt need to be granted write permission on the docker.sock file of the host. This might be done by giving access to docker user group.

Using mediacomem/mbtileserver fork

A safer option is to use a tileserver with a cron job like the mediacomem/mbtileserver fork.

The setup is imple: the volumes containing the tilesets need to be shared with the container. The disadvantage is that the cron job is watching only every minute for changes.

Code improvements

Some directions of how the project could be enhanced:

• Queue seem to be limited currenlty. After a certain amount of PSQL Notification, new notification aren't added to the queue. This might be a limitation of better-queue which use by default in-memory management.
• Functions use too much arguments. It could be better to use object to share arguments across function in index.ts.