pg_tileserv

pg_tileserv

A PostGIS-only tile server in Go. Strip away all the other requirements, it just has to take in HTTP tile requests and form and execute SQL. In a sincere act of flattery, the API looks a lot like that of the Martin tile server.

• https://access.crunchydata.com/documentation/pg_tileserv/latest/

Setup and Installation

Download

Builds of the latest code:

• Linux
• Windows
• MacOS
• Docker

Basic Operation

The executable will read user/connection information from the DATABASE_URL and connect to the database, exposing all functions and tables the database user has read and execute permissions on.

For production deployment, place an HTTP proxy caching layer (eg Varnish) in between the tile server and clients to reduce database load and increase application performance.

Linux/MacOS

export DATABASE_URL=postgresql://username:password@host/dbname
./pg_tileserv

Windows

SET DATABASE_URL=postgresql://username:password@host/dbname
pg_tileserv.exe

PostgreSQL not on 5432

If your PostgreSQL is not running on unix socket or 5432 port, you can specify the port as part of the URL

DATABASE_URL=postgresql://username:password@host:port/dbname

Docker

Use Dockerfile.alpine to build a lightweight (18MB expanded) Docker Image. See also a full example with Docker Compose.

Trouble-shooting

To get more information about what is going on behind the scenes, run with the --debug commandline parameter on, or turn on debugging in the configuration file:

./pg_tileserv --debug

Configuration File

The configuration file will be automatically read from the following locations, if it exists:

• Relative to the directory from which the program is run, ./config/pg_tileserv.toml
• In a root volume at /config/pg_tileserv.toml
• In the system configuration directory, at /etc/pg_tileserv.toml

If you want to pass a path directly to the configuration file, use the --config commandline parameter to pass in a pull path to configuration file. When using the --config option, configuration files in other locations will be ignored.

./pg_tileserv --config /opt/pg_tileserv/pg_tileserv.toml

In general the defaults are fine, and the program autodetects things like the server name. If you are not running PostgreSQL on 5432 port, you may need to add the port to the DbConnection parameter

user=you host=localhost dbname=yourdb port=yourdbport

…

For SSL support, you will need both a server private key and an authority certificate. For testing purposes you can generate a self-signed key/cert pair using openssl:

openssl req  -nodes -new -x509  -keyout server.key -out server.crt

…

You can use the CoordinateSystem block to output files in a system other than the default Web Mercator projection. In order to view a map with multiple layers in a non-standard projection, you will have to ensure that all layers share the same projection, otherwise the layers will not line up.

Configuration Using Environment Variables

Any parameter in the configuration file can be over-ridden at run-time in the environment. Prepend the upper-cased parameter name with TS_ to set the value. For example, to change the HTTP port using the environment:

export TS_HTTPPORT=8889

Operation

The purpose of pg_tileserv is to turn a set of spatial records into tiles, on the fly. The tile server reads two different layers of data:

• Table layers are what they sound like: tables in the database that have a spatial column with a spatial reference system defined on it.
• Function layers hide the source of data from the server, and allow the HTTP client to send in optional parameters to allow more complex SQL functionality. Any function of the form function(z integer, x integer, y integer, ...) that returns an MVT bytea result can serve as a function layer.

Web Interface

After start-up you can connect to the server and explore the published tables and functions in the database via a web interface at:

• http://localhost:7800

To disable the web interface, supply the run time flag --no-preview

Health Check Endpoint

In order to run the server in an orchestrated environment, like Docker, it can be useful to have a health check endpoint. This is /health by default, and returns a 200 OK if the server is responding to requests. To use a custom URL for the health check endpoint, supply the run time flag -e and your path. This setting will respect the base path setting, so if you choose a base path of /example and a health check endpoint of /foo, your health check URL becomes /example/foo.

Layers List

A list of layers is available in JSON at:

• http://localhost:7800/index.json

The index JSON just returns the minimum information about each layer.

{
    "public.ne_50m_admin_0_countries" : {
        "name" : "ne_50m_admin_0_countries",
        "schema" : "public",
        "type" : "table",
        "id" : "public.ne_50m_admin_0_countries",
        "description" : "Natural Earth country data",
        "detailurl" : "http://localhost:7800/public.ne_50m_admin_0_countries.json"
    }
}

The detailurl provides more detailed metadata for table and function layers.

The description field is read from the comment value of the table. To set a comment on a table, use the COMMENT command.

COMMENT ON TABLE ne_50m_admin_0_countries IS 'This is my comment';

Table Layers

By default, pg_tileserv will provide access to only those spatial tables:

• that your database connection has SELECT privileges for;
• that include a geometry column;
• that declare a geometry type; and,
• that declare an SRID (spatial reference ID)

For example:

CREATE TABLE mytable (
    geom Geometry(Polygon, 4326),
    pid text,
    address text
);
GRANT SELECT ON mytable TO myuser;

To restrict access to a certain set of tables, use database security principles:

• Create a role with limited privileges
• Only grant SELECT to that role for tables you want to publish
• Only grant EXECUTE to that role for functions you want to publish
• Connect pg_tileserv to the database using that role

If your table contains a geometry column that appears valid, but it is not available within pg_tileserv, you may need to specifically set a geometry type or SRID.

To determine if a table is compatible, make sure that it is returned by the following query:

SELECT
	nspname AS SCHEMA,
	relname AS TABLE,
	attname AS geometry_column,
	postgis_typmod_srid (atttypmod) AS srid,
	postgis_typmod_type (atttypmod) AS geometry_type
FROM
	pg_class c
	JOIN pg_namespace n ON (c.relnamespace = n.oid)
	JOIN pg_attribute a ON (a.attrelid = c.oid)
	JOIN pg_type t ON (a.atttypid = t.oid)
WHERE
	relkind IN('r', 'v', 'm')
	AND typname = 'geometry'
    AND postgis_typmod_srid (atttypmod) != 0
	AND relname = '';

If not, make sure that the geometry column has a valid SRID defined in the table metadata. You may need to specifically assign a geometry type, especially if the table was created using a SELECT query from another geometry table.

For example, to set the geometry as a Point type:

ALTER TABLE mytable ALTER COLUMN geom TYPE geometry (Point, 4326);

Views can also be used provided it exposes the geometry type and srid.

For example:

CREATE VIEW myview AS
SELECT ST_Buffer(geom)::Geometry(Polygon, 4326) AS geom, pk
FROM mytable;

Table Layer Detail JSON

In the detail JSON, each layer declares information relevant to setting up a map interface for the layer.

…

• id, name and schema are the fully qualified, table and schema name of the database table.
• bounds and center give the extent and middle of the data collection, in geographic coordinates. The order of coordinates in bounds is [minlon, minlat, maxlon, maxlat]. The order of coordinates in center is [lon, lat].
• tileurl is the standard substitution pattern URL consumed by map clients like Mapbox GL JS and OpenLayers.
• properties is a list of columns in the table, with their data types and descriptions. The column description field can be set using the COMMENT SQL command, for example:
  sql复制

  COMMENT ON COLUMN ne_50m_admin_0_countries.name_long IS 'This is the long name';

Feature id

The vector tile specification allows for an optional id field for each feature. This field should be unique within the parent layer.

By default, pg_tileserv will generate this id if:

• the PostGIS version is >= 3.0 and;
• the table has a primary key and;
• the primary key field is one of 'int2', 'int4', 'int8'

A feature id will not be generated for Views since these do not have a primary key. In cases where an id is not generated, depending on the map renderer, it may be possible to generate a feature id at runtime. See https://docs.mapbox.com/mapbox-gl-js/style-spec/sources/#vector-promoteId for an example in Mapbox GL JS.

Table Tile Request Customization

Most developers will just use the tileurl as is, but it possible to add some parameters to the URL to customize behaviour at run time:

• limit controls the number of features to write to a tile, the default is 50000.
• resolution controls the resolution of a tile, the default is 4096 units per side for a tile.
• buffer controls the size of the extra data buffer for a tile, the default is 256 units.
• properties is a comma-separated list of properties to include in the tile. For wide tables with large numbers of columns, this allows a slimmer tile to be composed.
• filter is a CQL logical expression which specifies the features to be included in the tile. See the CQL documentation.

For example:

http://localhost:7800/public.ne_50m_admin_0_countries/{z}/{x}/{y}.pbf?limit=100000&properties=name,long_name

For property names that include commas (why did you do that?) URL encode the comma in the name string before composing the comma-separated string of all names.

Multi-Layer Tile Requests

For more complex applications, multi-layer tiles can be useful to cut down on the amount of HTTP requests to pull in vector tiles. Doing this with pg_tileserv is easy, just add additional tables to your request. You can add as many tables as you like to your request, just separate them with a comma.

For example:

http://localhost:7800/public.ne_50m_admin_0_countries,public.ne_50m_airports/{z}/{x}/{y}.pbf

When you style those combined layers with Maplibre GL JS ("Layers" Docs) or Mapbox GL JS, use the original layer id as source-layer.

Function Layers

By default, pg_tileserv will provide access to only those functions:

• that have z integer, x integer, y integer as the first three parameters;
• that return a bytea, and
• that your database connection has EXECUTE privileges for.

In addition, hopefully obviously, for the function to actually be useful it does actually have to return an MVT inside the bytea return.

Functions can also have additional parameters to control the generation of tiles: in fact, the whole reason for function layers is to allow novel dynamic behaviour.

Function Layer