5

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?

8
  • Using PostGIS 3 with PostgreSQL 12 on Ubuntu 20.04 your first query works. I got a Polygon layer in the same EPSG as the source table. – Zoltan May 3 at 5:51
  • But are you able to open the table in QGIS @Zoltan? – Scorpioooooon21 May 3 at 6:01
  • 1
    There is only ST_Point in the mypoints table @BERA – Scorpioooooon21 May 3 at 6:56
  • @Scorpioooooon21, yes I can open it from QGIS. Of course I use one of my point tables to generate concave-hull. – Zoltan May 3 at 8:31
  • 1
    Title talks about convex hull but the SQL is using concave hull. Please edit one or another. – user30184 May 3 at 14:15
8

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>'
;
0
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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