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.
To run the application on your machine, you will need:
- ogr2ogr (from GDAL)
- tippecanoe
sudo add-apt-repository ppa:ubuntugis/ppa
sudo apt-get update
sudo apt-get install gdal-bin
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
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
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.
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 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"
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();
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. |
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.
⚠ 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.
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.
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.