2

Context

I have a PostGIS database with ~ 300'000 records having many attributes (fields) in a main table:

the main table of the database

In this table, I have many attributes, amongst which are:

  • the column holding the primary key (depicted in green),
  • the "master" geometry (points) column (depicted in red),
  • other geometry features (multi-or-single{points|lines|polygons} but no mix within each column) (the 4 next blue columns),
  • several independent JSON encoded fields (depicted in yellow),
  • two "groups" of features, depicting different aspects of a common knowledge, for example "stuff" and "animals" (one depicted in purple and the other in cyan),
  • other "single" fields holding some metadata (white)

I almost succeeded, using QGIS server, to feed these data through an "OGC API - Features" (aka WFS3) at http://my.remote.server:8080/wfs3/ but what I observed while browsing the unique collection corresponding to the PostGIS table, is that any of the record, for example the first one available at /wfs3/collections/mycollection/items/1.json is a "flat" JSON object. What I mean by "flat" is that each and every single attribute of the table is represented in the unique first level in the properties of the (Geo)JSON object:

JSON master structure

I didn't expand the properties, but each entry there actually corresponds to one column of the PostGIS table. And that's great.

What I want to achieve

But I also want to be able to give these properties a more nested structure, in order to keep grouped all fields sharing a common sense, knowledge or semantic.
For example, by grouping all the secondary geometries or the two "groups" of features (purple and cyan) in their own second JSON level.

For example from this current state:

(...)
  "properties": {
    "attribute_1": 200,
    "attribute_2": 318.5,
    "caption": "a string",
    "cyan_attribute_1": "Rex the dog",
    "cyan_attribute_2": "other dogs friends",
    "cyan_attribute_3": "Fluffy the cat",
    "group_id": 2,
    "has_house": false,
    "purple_attribute_1": "a thing",
    "purple_attribute_2": "other things",
    "purple_attribute_3": "a commodity",
    "purple_attribute_4": "other commodities",
    "secondary_geometry_1": (WTK geom),
    "secondary_geometry_2": (WTK geom),
    "secondary_geometry_3": (WTK geom),
    "secondary_geometry_4": (WTK geom),
    (...)
  }

I wish I could end up with something as follow:

(...)
  "properties": {
    "attribute_1": 200,
    "attribute_2": 318.5,
    "caption": "a string",
    "cyan_animals": {
        "cyan_attribute_1": "Rex the dog",
        "cyan_attribute_2": "other dogs friends",
        "cyan_attribute_3": "Fluffy the cat"
    },
    "group_id": 2,
    "has_house": false,
    "purple_stuff": {
        "purple_attribute_1": "things",
        "purple_attribute_2": "other things",
        "purple_attribute_3": "commodity",
        "purple_attribute_4": "other commodities"
    },
    "secondary_geometries": {
        "secondary_geometry_1": (WTK geom),
        "secondary_geometry_2": (WTK geom),
        "secondary_geometry_3": (WTK geom),
        "secondary_geometry_4": (WTK geom)
    }
    (...)
  }

By the way, if I can also control the order of appearance of the fields in the JSON element it would be great because for the moment they come out in alphabetical order.

For the moment I'm playing with the camptocamp/qgis-server docker image (simply because it's the only one I could get to work) but as the "OGC API - Features" specification is quite "new", I may give other tools (if other tools exist) a try.

Information

Server: Headless Ubuntu 18.04 or 20.04.
Database: dockerized postgis

2 Answers 2

2

OGC API Features (core) only supports simple features, it states:

Additional capabilities such as support for transactions, complex data structures, rich queries, other coordinate reference systems, subscription/notification, returning aggregated results, etc., may be specified in future parts of the OGC API Features series or as vendor-specific extensions.

Source: https://docs.opengeospatial.org/is/17-069r3/17-069r3.html

So I think you won't be able to do what you want currently.

1

Well, even so nmtoken quoted something which is crystal clear from the OGC specification itself, I was able to succeed in my task by updating the SQL layer as follow in QGIS:

SELECT
  id,
  attribute_1,
  attribute_2,
  caption,
  group_id,
  has_house,
  (...), -- < each individual property has to be listed, well, individually...
  jsonb_build_object(
    'cyan_attribute_1', "cyan_attribute_1_column",
    'cyan_attribute_2', "cyan_attribute_2_column",
    'cyan_attribute_3', "cyan_attribute_3_column"
  ) AS cyan_animals,
  jsonb_build_object(
    'purple_attribute_1', "purple_attribute_1_column",
    'purple_attribute_2', "purple_attribute_2_column",
    'purple_attribute_3', "purple_attribute_3_column",
    'purple_attribute_4', "purple_attribute_4_column"
  ) AS purple_stuff,
  jsonb_build_object(
    'secondary_geometry_1', ST_AsTet(secondary_geometry_1_column),
    'secondary_geometry_2', ST_AsTet(secondary_geometry_2_column),
    'secondary_geometry_3', ST_AsTet(secondary_geometry_3_column),
    'secondary_geometry_4', ST_AsTet(secondary_geometry_4_column)
  ) AS secondary_geometries
FROM "public"."mytable"

Notice: for clarification, I purposefully named the database field by appending '_column' inside each of the most nested jsonb_build_object() otherwise one could confuse these values with the name of their corresponding keys, which is a pure textual information, hence the simple quotes.

It's also possible to actually nest objects, e.g.:

SELECT
  id,
  attribute_1,
  attribute_2,
  caption,
  group_id,
  has_house,
  (...), -- < each individual property has to be listed, well, individually...
  jsonb_build_object(
    'cyan_animals', jsonb_build_object(
      'cyan_attribute_1', "cyan_attribute_1_column",
      'cyan_attribute_2', "cyan_attribute_2_column",
      'cyan_attribute_3', "cyan_attribute_3_column"
    ),
    'purple_stuff', jsonb_build_object(
      'purple_attribute_1', "purple_attribute_1_column",
      'purple_attribute_2', "purple_attribute_2_column",
      'purple_attribute_3', "purple_attribute_3_column",
      'purple_attribute_4', "purple_attribute_4_column"
    )
  ) AS nested_object,
  jsonb_build_object(
    'secondary_geometry_1', ST_AsTet(secondary_geometry_1_column),
    'secondary_geometry_2', ST_AsTet(secondary_geometry_2_column),
    'secondary_geometry_3', ST_AsTet(secondary_geometry_3_column),
    'secondary_geometry_4', ST_AsTet(secondary_geometry_4_column)
  ) AS secondary_geometries
FROM "public"."mytable"

which will render as follow in your browser:

(...)
  "properties": {
    "attribute_1": 200,
    "attribute_2": 318.5,
    "caption": "a string",
    "group_id": 2,
    "has_house": false,
    "nested_object": {
        "cyan_animals": {
            "cyan_attribute_1": "Rex the dog",
            "cyan_attribute_2": "other dogs friends",
            "cyan_attribute_3": "Fluffy the cat"
        },
        "purple_stuff": {
            "purple_attribute_1": "things",
            "purple_attribute_2": "other things",
            "purple_attribute_3": "commodity",
            "purple_attribute_4": "other commodities"
        }
    },
    "secondary_geometries": {
        "secondary_geometry_1": (WTK geom),
        "secondary_geometry_2": (WTK geom),
        "secondary_geometry_3": (WTK geom),
        "secondary_geometry_4": (WTK geom)
    }
    (...)
  }

Please, also note that as

A (JSON) object is an unordered set of name/value pairs.

Source: https://www.json.org/json-en.html

the result in the browser MAY NOT have the same ordering than the one you decided when writing your SQL query! And you cannot do anything for that, unless you tinker your query, see [1] and [2].

Also beware that these (sub-)nested parts are probably no more compliant with the OGC API - Features specification. So you probably won't be able to access them anymore, either using OGC API - Features - Part 3: Filtering and the Common Query Language (CQL) or any other way for officially querying WFS3 features, at least for these parts. But still, this is a valid JSON objects...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.