update_collection_extent setting results in wrong JSON structure

[jonas-eberle]

I have tried to set the option update_collection_extent to true, but this results in wrong JSON when the partition stats are updated.

The following function creates the wrong JSON: https://github.com/stac-utils/pgstac/blob/0c8874113475a7bfc32a3196bc3d1e6101372e05/src/pgstac/sql/003b_partitions.sql#L168-L180

This results in the following code with an additional extent key:

  "extent": {
    "extent": {
      "spatial": {
        "bbox": [
          [
            -180,
            -90,
            180,
            90
          ]
        ]
      },
      "temporal": {
        "interval": [
          [
            "2000-02-01T00:00:00+00:00",
            "2024-03-31T23:59:59+00:00"
          ]
        ]
      }
    }
  },

[olivegak971]

I've noticed the same behaviour on my side Curiously, we also have a function called "update_collections_extents", which doesn't seem to be used.

CREATE OR REPLACE FUNCTION update_collection_extents() RETURNS VOID AS $$ UPDATE collections SET content = content || jsonb_build_object( 'extent', jsonb_build_object( 'spatial', jsonb_build_object( 'bbox', collection_bbox(collections.id) ), 'temporal', jsonb_build_object( 'interval', collection_temporal_extent(collections.id) ) ) ) ;

And the code jonas-eberle mentions belongs to fucntion "update_partition_stats"

[olivegak971]

I've found a solution. You need to create the following SQL script to replace the function collection_extent :

CREATE OR REPLACE FUNCTION collection_extent(_collection text, runupdate boolean default false) RETURNS jsonb AS $$
DECLARE
    geom_extent geometry;
    mind timestamptz;
    maxd timestamptz;
    extent jsonb;
BEGIN
    IF runupdate THEN
        PERFORM update_partition_stats_q(partition)
        FROM partitions_view WHERE collection=_collection;
    END IF;
    SELECT
        min(lower(dtrange)),
        max(upper(edtrange)),
        st_extent(spatial)
    INTO
        mind,
        maxd,
        geom_extent
    FROM partitions_view
    WHERE collection=_collection;

    IF geom_extent IS NOT NULL AND mind IS NOT NULL AND maxd IS NOT NULL THEN
        extent := jsonb_build_object(
                'spatial', jsonb_build_object(
                    'bbox', to_jsonb(array[array[st_xmin(geom_extent), st_ymin(geom_extent), st_xmax(geom_extent), st_ymax(geom_extent)]])
                ),
                'temporal', jsonb_build_object(
                    'interval', to_jsonb(array[array[mind, maxd]])
                )
        );
        RETURN extent;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;