Feature ID on Materialized View

[Jnb2387]

I am able to get a feature ID for geoJSON from a table that has a primary key, but is there a way to get feature id from a materialized view? I tried creating unique index but does not work. I think its possible to just make a function to return the materialized view data and set a column as ID but I would like to see if it is possible with just using the collections instead of function.

[dr-jts]

The problem is that there is no concept of Primary Key on materialized views, and hence no metadata for it. As you say, it is possible to define unique key indexes on materialized views. We might be able to add some logic to detect the first column with a unique index on it and use that as the primary key. But it's going to be a pretty hairy query against the metadata tables.

[reyemtm]

If this is regarding tile id generation for use in something like Mapbox GL JS, you can use generateId to generate a tile id from a any property that can be cast to a string for a geojson source.

[PanierAvide]

Hello,

I'm facing the exact same issue, could it be a possible solution for user to set while querying which column should be used as a primary key ? This avoid the hassle of complex reading of metadata table, plus is a good compromise for users.

Best regards.

[alex-tate]

While I've been aware of this issue for some time, it hasn't been a priority for us until now. I am exclusively using views and materialized views and accepted that the resulting GeoJSON would have no featureID information as there could be no primary key on the source data. Our end users are visualising the data using a variety of methods but mostly using GIS software. Pending confirmation from ESRI, it seems that the latest version of ArcGIS Pro (v3.3.0) is a bit more particular than previous versions and is now not returning any data without a featureID. I like the simplicity of our existing solution and it would be great if pg_featureserv could pick up a non-indexed column and use that to populate the FeatureID. Possibly a configurable pg_featureserv setting where we could provide a column name?

[dr-jts]

I like the simplicity of our existing solution and it would be great if pg_featureserv could pick up a non-indexed column and use that to populate the FeatureID. Possibly a configurable pg_featureserv setting where we could provide a column name?

Configuring a column name to use as the featureId does seem like the simplest approach to getting ids emitted when no primary key is present.

• Should this take predence over the primary key field, if present?
• Is it ok that this will apply to every table? Or should there be an additional inclusion/exclusion list capability?

[alex-tate]

Should this take predence over the primary key field, if present?

I think the configuration option should take precedence. I can think of plenty of examples where a primary key is meaningless to end users and I would prefer to expose a more human-meaningful column as the featureID.

Is it ok that this will apply to every table? Or should there be an additional inclusion/exclusion list capability?

A trickier one. All our present use cases (all views) could be satisfied with a single configurable column name but I can imagine future scenarios where I couldn't, or wouldn't want to, change the table structure and would want more fine-grained control for configuring the featureID column name.

[dr-jts]

I can imagine future scenarios where I couldn't, or wouldn't want to, change the table structure and would want more fine-grained control for configuring the featureID column name.

We've been trying to avoid getting so fine-grained in configuration that we need some mechanism for per-table config (such as a config table). (Although that would provide some interesting capabilities.). So for now I don't a way just in the config file to specify more than one featureID column name (at least not without some complex syntax.)

[alex-tate]

@dr-jts Completely understand about the wish to stay away from per-table configuration options. Thinking about it, I'd probably continue to only expose views (in a specific whitelisted schema) and use column aliases to match to the configured featureID column name. Many thanks for considering this enhancement request.

[aj-he]

@dr-jts @alex-tate We are using standard views to join a geometry table and attribute table. As such, we cannot use a primary key. The geometry table has a PK column, which we are using and the view column is called "gid". The issue is that this seems to error when consuming in ESRI software (Desktop and Portal).

It looks like this is working for you by giving the column name "featureID", which I am assuming is defined in the OGC standard? If this is the case, can you confirm the casing so I can implement, and could this information please be put in the docs for clarity?

[alex-tate]

@aj-he The functionality that you are after doesn't currently exist but is described in the discussion above as an enhancement request.

[aj-he]

@alex-tate thanks for clarifying. Definitely a +1 for having a way to define the ID column, or having a reserved name like fid or featureid