Support foreign table relationships in PostgreSQL provider

[webb-ben]

Is your feature request related to a problem? Please describe.
Currently, pygeoapi's PostgreSQL provider does not support querying across multiple tables using a configurable ForeignKey relationship. This limits users who need to join tables dynamically based on external configurations.

I propose adding support for external table relationships. At a minimum I would want this to support 1:1 and N:1 relationships. Use cases are an observations table with external relationships to a site table with geometry, a table providing additional parameter metadata, or context inclusion from a table of encompassed geometry.

Describe the solution you'd like
A user may want to join counties with states using counties.statefp = states.geoid. From some tinkering I am thinking the relationship could be expressed in the config.yml as follows:

    providers:
     - type: feature
        name: PostgreSQL
        data: ...
        id_field: geoid
        table: counties
        external_tables:
          states:
            foreign: statefp
            remote: geoid
            properties: [geoid, name, uri]

and provide a response like:

{
  "type": "Feature",
  "properties": {
    "countyfp": "001",
    "statefp": "01",
    "name": "Autauga",
    "census_profile": "https://data.census.gov/cedsci/profile?g=0500000US01001",
    "uri": "https://geoconnex.us/ref/counties/01001",
    "states": {
      "uri": "https://geoconnex.us/ref/states/01",
      "name": "Alabama",
      "geoid": "01"
    }
  },
  "id": "01001",
  "geometry": {},
  "prev": "01001",
  "next": "01003",
  "links": []
}

There are obviously a myriad of ways to format the configuration and the response but I would support something simple.

Describe alternatives you've considered
I am working with a Postgres database that will not support materialized views.

I understand that automap might be able to include these types of relationships for a postgres database with the table relation defined as a foreign key (https://www.postgresql.org/docs/current/tutorial-fk.html), but this type of relationship does not work well for N:1.

Additional context
Add any other context or screenshots about the feature request here.

[Youssef-Harby]

This will be super helpful.. thanks @webb-ben

[dghouse]

We have a highly normalized Oracle database that contains a hierarchy of metadata. Only the leaves of the tree have SDO geometry, the rest of the tree is metadata. We would love to use Pygeoapi for all of our I/O, but I think it has to be able to handle database table relationships like.

I imagine that setting editable=true would cause some headaches. Maybe editable=[true|false|parent] where the third choice allows you to only edit the root table in the collection.

This feature would be a huge step forward for us.