Skip to content

Latest commit

 

History

History

postgis

README.md

PostGIS

The PostGIS provider manages querying for tile requests against a Postgres database with the PostGIS extension installed. The connection between tegola and Postgis is configured in a tegola.toml file. An example minimum connection config:

[[providers]]
# provider name is referenced from map layers (required)
name = "test_postgis"

# the type of data provider must be "postgis" for this data provider (required)
type = "postgis"

# PostGIS connection string (required)
uri = "postgres://tegola:supersecret@localhost:5432/tegola?sslmode=prefer" #

# PostGIS connection config run time parameter to label
# the origin of a connection
# The default is "tegola"
# (optional)
application_name = "tegola"

# PostGIS connection config run time parameter (optional)
# A read-only SQL transaction cannot alter non-temporary tables.
# This parameter controls the default read-only status of each new transaction.
# The default is OFF (read/write).
# (optional)
default_transaction_read_only = "off"

Connection Properties

Establishing a connection via connection string (uri) will become the default connection method as of v0.16.0. Connecting via host/port/database is deprecated.

  • uri (string): [Required] PostGIS connection string
  • name (string): [Required] provider name is referenced from map layers
  • type (string): [Required] the type of data provider. must be "postgis" to use this data provider
  • srid (int): [Optional] The default SRID for the provider. Defaults to WebMercator (3857) but also supports WGS84 (4326)

Connection string properties

Example

# {protocol}://{user}:{password}@{host}:{port}/{database}?{options}=
postgres://tegola:supersecret@localhost:5432/tegola?sslmode=prefer&pool_max_conns=10

Options

Tegola uses pgx to manage PostgresSQL connections that allows the following configurations to be passed as parameters.

  • sslmode: [Optional] PostGIS SSL mode. Default: "prefer"
  • pool_min_conns: [Optional] The min connections to maintain in the connection pool. Defaults to 100. 0 means no max.
  • pool_max_conns: [Optional] The max connections to maintain in the connection pool. Defaults to 100. 0 means no max.
  • pool_max_conn_idle_time: [Optional] The maximum time an idle connection is kept alive. Defaults to "30m".
  • pool_max_connection_lifetime [Optional] The maximum time a connection lives before it is terminated and recreated. Defaults to "1h".
  • pool_max_conn_lifetime_jitter [Optional] Duration after max_conn_lifetime to randomly decide to close a connection.
  • pool_health_check_period [Optional] Is the duration between checks of the health of idle connections. Defaults to 1m

Provider Layers

In addition to the connection configuration above, Provider Layers need to be configured. A Provider Layer tells tegola how to query PostGIS for a certain layer. An example minimum config:

[[providers.layers]]
name = "landuse"
# this table uses "geom" for the geometry_fieldname and "gid" for the
# id_fieldname so they don't need to be configured
tablename = "gis.zoning_base_3857"

Provider Layers Properties

  • name (string): [Required] the name of the layer. This is used to reference this layer from map layers.
  • tablename (string): [*Required] the name of the database table to query against. Required if sql is not defined.
  • geometry_fieldname (string): [Optional] the name of the filed which contains the geometry for the feature. defaults to geom.
  • id_fieldname (string): [Optional] the name of the feature id field. defaults to gid.
  • fields ([]string): [Optional] a list of fields to include alongside the feature. Can be used if sql is not defined.
  • srid (int): [Optional] the SRID of the layer. Supports 3857 (WebMercator) or 4326 (WGS84).
  • geometry_type (string): [Optional] the layer geometry type. If not set, the table will be inspected at startup to try and infer the gemetry type. Valid values are: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection.
  • sql (string): [*Required] custom SQL to use use. Required if tablename is not defined. Supports the following tokens:
    • !BBOX! - [Required] will be replaced with the bounding box of the tile before the query is sent to the database. !bbox! and!BOX! are supported as well for compatibilitiy with queries from Mapnik and MapServer styles.
    • !ZOOM! - [Optional] will be replaced with the "Z" (zoom) value of the requested tile.
    • !X! - [Optional] will be replaced with the "X" value of the requested tile.
    • !Y! - [Optional] will be replaced with the "Y" value of the requested tile.
    • !Z! - [Optional] will be replaced with the "Z" value of the requested tile.
    • !SCALE_DENOMINATOR! - [Optional] scale denominator, assuming 90.7 DPI (i.e. 0.28mm pixel size)
    • !PIXEL_WIDTH! - [Optional] the pixel width in meters, assuming 256x256 tiles
    • !PIXEL_HEIGHT! - [Optional] the pixel height in meters, assuming 256x256 tiles
    • !ID_FIELD! - [Optional] the id field name
    • !GEOM_FIELD! - [Optional] the geom field name
    • !GEOM_TYPE! - [Optional] the geom type field name

*Required: either the tablename or sql must be defined, but not both.

Example minimum custom SQL config

[[providers.layers]]
name = "rivers"
# custom SQL to be used for this layer. Note: that the geometery field is wrapped
# in ST_AsBinary() and a !BBOX! token is supplied for querying the table with the tile bounds
sql = "SELECT gid, ST_AsBinary(geom) AS geom FROM gis.rivers WHERE geom && !BBOX!"

Environment Variable support

Helpful debugging environment variables:

  • TEGOLA_SQL_DEBUG: specify the type of SQL debug information to output. Supports the following values:
    • LAYER_SQL: print layer SQL as they’re parsed from the config file.
    • EXECUTE_SQL: print SQL that is executed for each tile request and the number of items it returns or an error.
    • LAYER_SQL:EXECUTE_SQL: print LAYER_SQL and EXECUTE_SQL.

Example:

$ TEGOLA_SQL_DEBUG=LAYER_SQL tegola serve --config=/path/to/conf.toml

Testing

Testing is designed to work against a live PostGIS database. To see how to set up a database check this github actions script. To run the PostGIS tests, the following environment variables need to be set:

$ export RUN_POSTGIS_TESTS=yes
$ export PGURI="postgres://postgres:postgres@localhost:5432/tegola"
$ export PGURI_NO_ACCESS="postgres://tegola_no_access:@localhost:5432/tegola" # used for testing errors when user does not have read permissions on a table
$ export PGPASSWORD=""
$ export PGSSLMODE="disable"

If you're testing SSL, the following additional env vars can be set:

$ export PGSSLMODE="" // disable, allow, prefer, require, verify-ca, verify-full
$ export PGSSLKEY=""
$ export PGSSLCERT=""
$ export PGSSLROOTCERT=""