Add ability to specify COMMENT ON PostgreSQL information

[tordans]

PostgreSQL has this feature to add text comments to tables and columns.

• https://www.postgresql.org/docs/current/sql-comment.html
• COMMENT ON TABLE foo_name IS 'foo data for some purpose.';
• COMMENT ON COLUMN foo_name.minzoom IS 'Used by tile rendering functions to filter data based on zoom.';

This can be useful to share documentation on the data.
Some tile servers like Martin use this information when they generate their TileJSON.

• https://maplibre.org/martin/sources-pg-tables.html#tilejson-in-sql-comments

It would be nice if I could specify those comments right in my osm2pgsql define_table section.
Maybe like…

local db_table = osm2pgsql.define_table({
  name = 'foo_name',
  description = 'foo data for some purpose.',
  ids = { type = 'any', id_column = 'osm_id', type_column = 'osm_type' },
  columns = {
    { column = 'id',   type = 'text', not_null = true },
    { column = 'tags', type = 'jsonb' },
    { column = 'geom', type = 'point' },
    { 
      column = 'minzoom', 
      type = 'integer', 
      description = 'Used by tile rendering functions to filter data based on zoom.' 
    },
  }
})

And maybe even a automatic JSON transformation for Martin…

local db_table = osm2pgsql.define_table({
  name = 'foo_name',
  description = {
    version = "1.2.3",
    attribution = "osm",
    description = "a description from table comment",
    foo = { bar = "foo"}
  },
  ids = { type = 'any', id_column = 'osm_id', type_column = 'osm_type' },
  columns = {
    { column = 'id',   type = 'text', not_null = true },
    { column = 'tags', type = 'jsonb' },
    { column = 'geom', type = 'point' },
    { 
      column = 'minzoom', 
      type = 'integer', 
      description = 'Used by tile rendering functions to filter data based on zoom.' 
    },
  }
})

[pnorman]

This is also something I've thought of. In the past I've distributed a separate SQL file which contains all the COMMENTs but it's much better to include it in the lua

[tordans]

@pnorman did you use a string as comment or JSON? – I wonder which tile servers support the JSON solution that Martin uses and if this is kind of standardised.

[pnorman]

I use strings. This was well before Martin, but I briefly glanced over the Martin solution and don't like it.

Assuming you're dealing with a style which is just selecting everything from a table, one table corresponds to one of the vector_layers elements, and the columns correspond to the fields of the vector_layer element.

Stuff like attribution, description, version, etc, doesn't belong to any one table. Those are properties of the tileset as a whole.