Efficiently convert a GeoJSON field to a single multipoint geometry in PostGIS

Question

I have such PostGIS (PG:14-3.2) query today-:

SELECT
    p.id as "plant_id",
    p."location",
    p."name" as "plant_name",
    m."attributes_json",
    -- #placeholder,
    m."attributes_status",
    m."attributes_timestamp"
FROM metadata m
LEFT JOIN plant p
ON p.id = m.fk_plant_id
ORDER BY m.fk_plant_id ASC
;

The attributes_json is a GeoJSON feature containing multiple points.

I wish I could replace the #placeholder with a postgis MultiPoint as WKB or WKT which contains all the points of the GeoJSON field.

After some research, I end up there: Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS? and when adapting the given example to my situation, I succeed retrieving a single MultiPoint WKT object, e.g.:

SELECT ST_AsText(ST_Collect(ST_GeomFromGeoJSON(feat->>'geometry')))
FROM (
  SELECT jsonb_array_elements(attribute_json::jsonb->'features') AS feat FROM metadata
) AS f;

But I'm not successful when it comes to inserting it at the place of the #placeholder in my initial query.

And if I replace the #placeholder by a one-shot statement:

    ST_Collect(ST_GeomFromGeoJSON((SELECT jsonb_array_elements(attributes_json::jsonb->'features'))->>'geometry')) as geom,

I have to add a GROUPY BY clause on almost every fields... and when it comes to adding the m.attributes_json in this clause, it says:

ERROR:  could not identify an equality operator for type json
LINE 18:  GROUP BY i.id, m.attributes_array, m.attribute_json
                                             ^

Any hints on how to achieve that in a proper and efficient way?

geozelot · Accepted Answer · 2022-02-03T12:38:49.107

Either use

a correlated sub-query:

SELECT
    p.id as "plant_id",
    p."location",
    p."name" as "plant_name",
    m."attributes_json",
    (
      SELECT
          ST_Collect(ST_GeomFromGeoJSON(f -> 'geometry'))
      FROM
          JSONB_ARRAY_ELEMENTS(m."attributes_json"::JSONB -> 'features') AS f
    ) AS geom,
    m."attributes_status",
    m."attributes_timestamp"
FROM
    metadata m
LEFT JOIN
    plant p ON p.id = m.fk_plant_id
ORDER BY
    m.fk_plant_id ASC
;

a [LEFT JOIN | CROSS JOIN] LATERAL [ON TRUE]:

SELECT
    p.id as "plant_id",
    p."location",
    p."name" as "plant_name",
    m."attributes_json",
    _m.geom,
    m."attributes_status",
    m."attributes_timestamp"
FROM
    metadata m
CROSS JOIN LATERAL (
  SELECT
      ST_Collect(ST_GeomFromGeoJSON(f -> 'geometry')) AS geom
  FROM
      JSONB_ARRAY_ELEMENTS(m."attributes_json"::JSONB -> 'features') AS f
) AS _m
LEFT JOIN
    plant p ON p.id = m.fk_plant_id
ORDER BY
    m.fk_plant_id ASC
;

Note that with the CROSS JOIN LATERAL syntax rows are omitted when the LATERAL query returns no results (much like INNER JOIN behavior). To get a LEFT JOIN behavior, use LEFT JOIN LATERAL ( ... ) AS _m ON TRUE. However, this is mainly an issue for set-returning functions.

The LATERAL approach is more versatile (can return sets of rows), more elegant in set-returning scenarios, and, usually, much faster.

Efficiently convert a GeoJSON field to a single multipoint geometry in PostGIS

1 Answers1