tiler

ADVANCED

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

tiler.CREATE_SIMPLE_TILESET(input, output_table, options)

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 format database.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°.

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 to 2^Z-1).

  • y: Y-index of the tile (0 to 2^Z-1).

  • data: contents of the tile in MVT format. It will contain the resulting features and their attributes (as defined by properties).

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 in Xmin, Ymin, Xmax, Ymax format.

  • center: center of the geographical extents as X, Y, Z, where the Z 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) and type, it contains min, max, average and sum.

Example

CALL carto.CREATE_SIMPLE_TILESET(
  'select * from MYDB.MYSCHEMA.carto_geography_usa_censustract',
  'MYDB.MYSCHEMA.carto_geography_usa_censustract_tileset',
  '{
    "zoom_min":0,
    "zoom_max":5,
    "metadata": {
      "name": "censustract_tileset",
      "description": "A description"
    },
    "properties":{
      "geoid":"String",
      "do_perimeter":"Number",
      "do_label":"String"
    }
  }'
);

CREATE_POINT_AGGREGATION_TILESET

tiler.CREATE_POINT_AGGREGATION_TILESET(input, output_table, options)

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 format database.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°.

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 to 2^Z-1).

  • y: Y-index of the tile (0 to 2^Z-1).

  • data: contents of the tile in MVT format. It will contain the resulting points or cell (location of the aggregated features according to aggregation_placement) and their attributes (as defined by properties).

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 in Xmin, Ymin, Xmax, Ymax format.

  • center: center of the geographical extents as X, Y, Z, where the Z 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) and type, it contains min, max, average and sum.

Example

CALL carto.CREATE_POINT_AGGREGATION_TILESET(
  'SELECT * FROM database.schema.cities_table',
  'database.schema.cities_tileset',
  '{
    "geom_column": "geom",
    "zoom_min": 0,
    "zoom_max": 12,
    "aggregation_resolution": 5,
    "aggregation_placement": "cell-centroid",
    "properties": {
      "num_cities": {
        "formula": "COUNT(*)",
        "type": "Number"
      },
      "population_sum": {
        "formula": "SUM(population)",
        "type": "Number"
      },
      "city_name": {
        "formula": "(CASE WHEN COUNT(*) <= 1 THEN MIN(city_name) ELSE NULL END)",
        "type": "String"
      }
    },
    "metadata": {
      "name": "Population",
      "description": "Population in the cities"
    }
  }'
);

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

CREATE_SPATIAL_INDEX_TILESET(source_table, target_table, options)

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 a TEXT of the form database.schema.tablename. The database can be omitted (in which case the current one will be used). The schema can also be omitted and the first on the search_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

CALL carto.CREATE_SPATIAL_INDEX_TILESET(
  'YOUR_DATABASE.YOUR_SCHEMA.INPUT_TABLE_QUADBIN_LEVEL14',
  'YOUR_DATABASE.YOUR_SCHEMA.OUTPUT_TILESET_QUADBIN_LEVEL14',
  '{
    "spatial_index_column": "quadbin:index",
    "resolution": 14,
    "resolution_min": 0,
    "resolution_max": 8,
    "aggregation_resolution": 6,
    "properties": {
      "population": {
        "formula": "SUM(population)",
        "type": "Number"
      }
    }
  }'
);
CALL carto.CREATE_SPATIAL_INDEX_TILESET(
  'SELECT * FROM YOUR_DATABASE.YOUR_SCHEMA.INPUT_TABLE_H3_LEVEL10',
  'YOUR_DATABASE.YOUR_SCHEMA.OUTPUT_TILESET_H3_LEVEL10',
  '{
    "spatial_index_column": "h3:index",
    "resolution": 10,
    "resolution_min": 0,
    "resolution_max": 6,
    "aggregation_resolution": 4,
    "properties": {
      "population": {
        "formula": "SUM(population)",
        "type": "Number"
      }
    }
  }'
);

Last updated