Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature ID on Materialized View #86

Open
Jnb2387 opened this issue Oct 12, 2021 · 11 comments
Open

Feature ID on Materialized View #86

Jnb2387 opened this issue Oct 12, 2021 · 11 comments
Labels
question Further information is requested

Comments

@Jnb2387
Copy link

Jnb2387 commented Oct 12, 2021

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
Copy link
Collaborator

dr-jts commented Oct 12, 2021

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.

@dr-jts dr-jts added the question Further information is requested label Oct 12, 2021
@reyemtm
Copy link

reyemtm commented Oct 19, 2022

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
Copy link

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
Copy link

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
Copy link
Collaborator

dr-jts commented May 20, 2024

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
Copy link

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
Copy link
Collaborator

dr-jts commented May 22, 2024

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
Copy link

alex-tate commented May 22, 2024

@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
Copy link

aj-he commented May 29, 2024

@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
Copy link

@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
Copy link

aj-he commented May 30, 2024

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
question Further information is requested
Projects
None yet
Development

No branches or pull requests

6 participants