Computing concave hull from points using PostGIS and display in QGIS

Question

I'd like to create a boundary of a set of point using ST_ConcaveHull function in PostGIS

My query works fine and I can see the polygon through the geometry viewer embedded in the pgadmin.

CREATE TABLE boundary AS
SELECT row_number() OVER () As id,  ST_ConcaveHull(ST_Collect(p.geom), 0.99) As geom
FROM mypoints p;

However, when I try to load the table in QGIS, it gives me an error saying the layer is invalid.

I try to explicitly define the geometry type as polygon, but this error Geometry type (GeometryCollection) does not match column type (Polygon)

CREATE TABLE boundary AS
SELECT row_number() OVER () As id,  ST_ConcaveHull(ST_Collect(p.geom), 0.99)::geometry(POLYGON,4326) As geom
FROM mypoints p;

How do I refine my query to create a table that can be recognised by QGIS?

Answer 1

The convex hull algorithm is somewhat delicate, and ST_ConvexHull makes no effort to preserve and validate topology and geometry - it is possible that parts of the constructed hull collapses into lower-dimensional geometries during the process.

You'd need to dump the GEOMETRYCOLLECTION, and filter for a specifc geometry type:

CREATE TABLE boundary AS
  SELECT ROW_NUMBER() OVER() AS id,
         ST_SetSRID(dmp.geom, 4326)::GEOMETRY(POLYGON, 4326) AS geom
  FROM   (
    SELECT ST_Collect(geom) AS geom
    FROM   mypoints
  ) AS q, LATERAL ST_Dump(ST_ConcaveHull(geom, 0.99)) AS dmp
  WHERE  GeometryType(dmp.geom) = 'POLYGON'
;

---

It may be a good idea to check on, and visualize what is happening; use ST_AsText to see what gets returned, then load each geometry type as individual layer into QGIS for inspection:

SELECT ROW_NUMBER() OVER() AS id,
       ST_AsText(dmp.geom, 4326) AS wkt_geom
FROM   (
  SELECT ST_Collect(geom) AS geom
  FROM   mypoints
) AS q, LATERAL ST_Dump(ST_ConcaveHull(geom, 0.99)) AS dmp
-- WHERE  GeometryType(dmp.geom) = '<type>'
;

Answer 2

The solution from @geozelot works perfectly.

I also manage to extract polygon by using ST_CollectionExtract. Below is my revised query and the table is now accessible from QGIS too.

CREATE TABLE boundary AS
SELECT row_number() OVER () As id, ST_CollectionExtract(ST_ConcaveHull(ST_Collect(p.geom), 0.99),3) As geom
FROM mypoints p;