Specifying an id column with auto_publish

[nyurik]

Auto-discovered PG tables may have some columns that should be exposed as a feature ID rather than just being a regular feature property. I propose to add an id_column param as shown below.

postgres:
  connection_string: ${DATABASE_URL}
  auto_publish:
      tables:
        # Set a custom source ID based on the table name
        id_format: 'table.{schema}.{table}.{column}'
        # A table column to use as the feature ID
        # If a table has no column with this name, `id_column` will not be set for that table.
        # We could even allow a list of strings here - the first found column will be treated as a feature ID.
        id_column: 'id'

Deliverables

• allow id_column string param to specify the default id column if it exists. If it does not exist, log an info message. If the column exists, but it is NOT an integer, log a warning. If ID column is found, it will NOT be exported as a property, only as the ID.
• Optional: possibly allow id_column to be a list of strings, so if a table has any of the specified columns, use it.
• Optional: consider renaming id_format into source_id_format, with backwards compatibility

Discussed in #680

[Binabh]

@nyurik I am giving this a try. Can you have a quick look at draft PR and confirm if I am in right direction? Also, any hint on adding warnings if column not found/not of type integer would be helpful. I have implemented a generic info currently.

I am learning Rust and this is my first contribution using Rust. So, I would be happy to take feedback on code quality also.
Thank You!

[saneth]

Hello,

Why support only integer types as id ?
According to the feature standard they can be strings :

If a Feature has a commonly used identifier, that identifier
SHOULD be included as a member of the Feature object with the name
"id", and the value of this member is either a JSON string or
number.

[nyurik]

Could you give a link to the source? Thx

[saneth]

Thanks for answering. Sure ! I have been looking here :

https://geojson.org/
https://datatracker.ietf.org/doc/html/rfc7946#section-3.2

[nyurik]

ah, yes, geojson has a different definition than MVT specification:

A feature MAY contain an id field. If a feature has an id field, the value of the id SHOULD be unique among the features of the parent layer.

Which is defined here

        message Feature {
                optional uint64 id = 1 [ default = 0 ];  // <--- integer