tiler
We currently provide procedures to create the following kind of tilesets:
Spatial index tiles (aggregates spatial indexes into tiles at specific resolutions)
Geometry-based MVT tiles of two types:
simple tilesets to visualize features individually
aggregation tilesets to generate aggregated point visualizations
CREATE_SIMPLE_TILESET
Description
Create a simple tileset from a table, with feature dropping.
input
:TEXT
that can either contain a table name (e.g.database.schema.tablename
) or a full query (e.g.(SELECT * FROM database.schema.tablename)
).output_table
:TEXT
of the formatdatabase.schema.tablename
where the resulting tileset will be stored. The database and schema must exist and the caller needs to have permissions to create a new table in it. The process will fail if the table already exists.options
:TEXT
containing a valid JSON with the different options. Valid options are described in the table below.
warning
If a query is passed in input
, it might be evaluated multiple times to generate the tileset. Thus, non-deterministic functions, such as [ROW_NUMBER
] should be avoided. If such a function is needed, the query should be saved into a table first and then passed as input
, to avoid inconsistent results.
warning
In case of receiving the next error: ERROR: transform: tolerance condition error (-20) (SQLSTATE XX000)
. The geom used as geom_column
inside the input
query or table might be containing latitudes around ±90°. This procedure performs reprojections to the Web Mercator Projection (SRID 3857) which is not able to show data near poles. If this is your case you might have to discard those geoms or clip them to latitudes around ±85°.
For the map viewer, tile_resolution
is really a way of using an offset zoom level to load 'large' but few tiles , or 'small' but many tiles. For example, at zoom level 2 we might have to load 16 tiles to fill our screen. By increasing tile_resolution
one step (eg. 0.5
to 1
), we artificially use one z-level less (zoom level of 1) to load 4 (larger) tiles. Or we could increase tile_resolution
two steps (eg. from 0.5 to 2), to artificially use two z-levels less (zoom level of 0) and thus load just one (even larger) tile. Here is a table which illustrates the real requested Z levels based on a tileset's tile_resolution
.
As shown, tile_resolution
of 0.5
is where the tileset zoom level and map zoom levels match, so we use 0.5
as our baseline even though the default tile_resolution
is 1
. Other tile_resolution
values (eg, 1, 2, 4) will use offset z-levels when loaded on the map.
Relationship between tile\_resolution
and max\_tile\_features
/max\_tile\_size\_kb
tile\_resolution
and max\_tile\_features
/max\_tile\_size\_kb
In the web map viewer, tile_resolution
is really a way of using an offset zoom level to load 'larger' but less tiles. For example, at zoom level 3 we might have to load 16 tiles to fill our screen. By increasing tile_resolution
one step (eg. 1
to 2
), we artificially use one z-level less (zoom level of 2) to load 4 (larger) tiles. Or we could increase tile_resolution
two steps (eg. from 1 to 4), we artificially use two z-levels less (zoom level of 1) to load just one (even larger) tile. Here is a table which illustrates the real requested Z levels based on a tilesets tile_resolution
.
As shown, tile_resolution
of 0.5
is where the tileset zoom level and map zoom levels match. Other tile_resolution
values will need offset z-levels.
At the default tile_resolution
of 1
, any value set for max_tile_features
or max_tile_size_kb
will be multiplied by 4. For example, at tile_resolution
of 1
, a specified value for max_tile_features
of 10000 x 4 = 40000. This factor increases to 16 for tile_resolution
of 2
and 64 for tile_resolution
of 4
. Likewise, it decreases to 1 (ie. no change) at tile_resolution
0.5, and 0.25 at tile_resolution
of 0.25
(ie. divide by 4).
Although this is somewhat unintuitive, the offset ensures that tilesets generated using the same options (but different tile_resolutions) will always appear the same on the map.
Result
The generated tileset consists of a table with the following columns, where each row represents a tile:
z
: zoom level of the tile.x
: X-index of the tile (0
to2^Z-1
).y
: Y-index of the tile (0
to2^Z-1
).data
: contents of the tile in MVT format. It will contain the resulting features and their attributes (as defined byproperties
).
Additionally, there is a row in the tileset, identified by Z=-1
, in which the data
column contains metadata about the tileset in JSON format. It includes the following properties:
bounds
: geographical extents of the source as a string inXmin, Ymin, Xmax, Ymax
format.center
: center of the geographical extents asX, Y, Z
, where theZ
represents the zoom level where a single tile spans the whole extents size.zmin
: minimum zoom level in the tileset.zmax
: maximum zoom level in the tileset.tilestats
: stats about the feature's properties. In addition to its name (attribute
) andtype
, it containsmin
,max
,average
andsum
.
Example
CREATE_POINT_AGGREGATION_TILESET
Description
Generates a point aggregation tileset.
input
:TEXT
that can either contain a table name (e.g.database.schema.tablename
) or a full query (e.g.(SELECT * FROM database.schema.tablename)
).output_table
:TEXT
of the formatdatabase.schema.tablename
where the resulting tileset will be stored. The database and schema must exist and the caller needs to have permissions to create a new table in it. The process will fail if the table already exists.options
:TEXT
containing a valid JSON with the different options. Valid options are described in the table below.
warning
If a query is passed in input
, it might be evaluated multiple times to generate the tileset. Thus, non-deterministic functions, such as [ROW_NUMBER
] should be avoided. If such a function is needed, the query should be saved into a table first and then passed as input
, to avoid inconsistent results.
warning
In case of receiving the next error: ERROR: transform: tolerance condition error (-20) (SQLSTATE XX000)
. The geom used as geom_column
inside the input
query or table might be containing latitudes around ±90°. This procedure performs reprojections to the Web Mercator Projection (SRID 3857) which is not able to show data near poles. If this is your case you might have to discard those geoms or clip them to latitudes around ±85°.
In web map tilesets, each additional zoom level has 4 times the amount of tiles as the previous zoom. Level 0 has 1 tile, level 1 has 4, level 2 has 16, etc.
For the map viewer, tile_resolution
is really a way of using an offset zoom level to load 'large' but few tiles , or 'small' but many tiles. For example, at zoom level 2 we might have to load 16 tiles to fill our screen. By increasing tile_resolution
one step (eg. 0.5
to 1
), we artificially use one z-level less (zoom level of 1) to load 4 (larger) tiles. Or we could increase tile_resolution
two steps (eg. from 0.5 to 2), to artificially use two z-levels less (zoom level of 0) and thus load just one (even larger) tile. Here is a table which illustrates the real requested Z levels based on a tilesets tile_resolution
.
As shown, tile_resolution
of 0.5
is where the tileset zoom level and map zoom levels match, so we use 0.5
as our baseline even thoug the default tile_resolution
is 1
. Other tile_resolution
values (eg, 1, 2, 4) will use offset z-levels when loaded on the map.
Relationship between tile\_resolution
and aggregation\_resolution
tile\_resolution
and aggregation\_resolution
The value of aggregation_resolution
will be adjusted based on tile_resolution
. Although the default tile_resolution
is 1, we use 0.5
as the baseline. So by default, aggregation_resolution
gets adjusted. Its value (whether default or user-specified) will be decreased/increased as outlined below:
Such that, 7, the default aggregation_level
@ tile_resolution
of 1
, is better thought of as 6 + 1 = 7. Likewise, if a user specified an aggregation_resolution
of 8, and tile_resolution
of 4, the generated tile will actually use 8 + 3 = 11. But, when rendered on the map, the z-levels are offset by -3 for tile_resolution
4 so the tiles are geographically larger but otherwise look the same as those generated with another tile_resolution
value.
Relationship between tile\_resolution
and max\_tile\_features
/max\_tile\_size\_kb
tile\_resolution
and max\_tile\_features
/max\_tile\_size\_kb
In the web map viewer, tile_resolution
is really a way of using an offset zoom level to load 'larger' but less tiles. For example, at zoom level 3 we might have to load 16 tiles to fill our screen. By increasing tile_resolution
one step (eg. 1
to 2
), we artificially use one z-level less (zoom level of 2) to load 4 (larger) tiles. Or we could increase tile_resolution
two steps (eg. from 1 to 4), we artificially use two z-levels less (zoom level of 1) to load just one (even larger) tile. Here is a table which illustrates the real requested Z levels based on a tilesets tile_resolution
.
As shown, tile_resolution
of 0.5
is where the tileset zoom level and map zoom levels match. Other tile_resolution
values will need offset z-levels.
At the default tile_resolution
of 1
, any value set for max_tile_features
or max_tile_size_kb
will be multiplied by 4. For example, at tile_resolution
of 1
, a specified value for max_tile_features
of 10000 x 4 = 40000. This factor increases to 16 for tile_resolution
of 2
and 64 for tile_resolution
of 4
. Likewise, it decreases to 1 (ie. no change) at tile_resolution
0.5, and 0.25 at tile_resolution
of 0.25
(ie. divide by 4).
Result
The generated tileset consists of a table with the following columns, where each row represents a tile:
z
: zoom level of the tile.x
: X-index of the tile (0
to2^Z-1
).y
: Y-index of the tile (0
to2^Z-1
).data
: contents of the tile in MVT format. It will contain the resulting points or cell (location of the aggregated features according toaggregation_placement
) and their attributes (as defined byproperties
).
Additionally, there is a row in the tileset, identified by Z=-1
, in which the data
column contains metadata about the tileset in JSON format. It includes the following properties:
bounds
: geographical extents of the source as a string inXmin, Ymin, Xmax, Ymax
format.center
: center of the geographical extents asX, Y, Z
, where theZ
represents the zoom level where a single tile spans the whole extents size.zmin
: minimum zoom level in the tileset.zmax
: maximum zoom level in the tileset.tilestats
: stats about the feature's properties. In addition to its name (attribute
) andtype
, it containsmin
,max
,average
andsum
.
Example
In the example above, for all features we would get a property "num_cities"
with the number of points that fall in it and "population_sum"
with the sum of the population in those cities. In addition to this, when there is only one point that belongs to this property (and only in that case) we will also get the column values from the source data in "city_name"
.
CREATE_SPATIAL_INDEX_TILESET
Description
Creates a tileset that uses a spatial index (H3 and QUADBIN are currently supported), aggregating data from an input table that uses that same spatial index.
Aggregated data is computed for all levels between resolution_min
and resolution_max
. For each resolution level, all tiles for the area covered by the source table are added, with data aggregated at level resolution + aggregation_resolution
.
source_table
:TEXT
that can either be a table name (e.g.schema.tablename
or a full query (e.g.SELECT * FROM database.schema.tablename
).target_table
: Where the resulting table will be stored. It must be aTEXT
of the formdatabase.schema.tablename
. Thedatabase
can be omitted (in which case the current one will be used). Theschema
can also be omitted and the first on thesearch_path
will be used. The schema must exist and the caller needs to have permissions to create a new table on it. The process will fail if the target table already exists.options
:TEXT
containing a valid JSON with the different options. Valid options are described the table below.
warning
If a query is passed in input
, it might be evaluated multiple times to generate the tileset. Thus, non-deterministic functions, such as [ROW_NUMBER
] should be avoided. If such a function is needed, the query should be saved into a table first and then passed as input
, to avoid inconsistent results.
tip
Any option left as NULL
will take its default value if available.
Examples
Last updated