How can ST_VoronoiPolygons be used instead of the broken built-in algorithm in QGIS?

Question

I tried to use QGIS to create voronoi polygons from a set of points with an attribute (group) and then merge the polygons with the same value for that attribute. (This last step is what QGIS calls Dissolve.) It worked in principle, but since the built-in voronoi algorithm produces erroneous output, I am trying alternatives, especially ST_VoronoiPolygons.

I managed to load a table of points (my_points) into PostgreSQL. It has a geom column (automatically created for PostGIS) and a group column.

I read that I should use the following commands:

drop table if exists voronoi_output;
create table voronoi_output as
    select ST_Dump(ST_VoronoiPolygons(ST_Collect(geom))) from my_points;

But the newly created table voronoi_output is missing the group column, which is needed for the next step (dissolve). How can this be fixed?

(If ST_VoronoiPolygons would accept polylines as input, I could just feed it a bunch of polylines instead of "groups" of points. Then the "dissolve" step would not be needed), but I do not expect that to work.)

Answer 1

You need to reassign the group ids to the polygons. You could

   CREATE TABLE voronoi_output AS
     SELECT points.group,
            polys.geom
     FROM (
       SELECT (ST_Dump(ST_VoronoiPolygons(ST_Collect(geom))).geom AS geom
       FROM my_points
     ) AS polys
     JOIN my_points AS points
       ON ST_Intersects(points.geom, polys.geom);

to simply do that, or

    CREATE TABLE voronoi_output AS
      SELECT points.group,
             ST_Collect(polys.geom) AS geom  -- or ST_Union(...) here; returns merged
      FROM (                                 -- POLYGONS where possible, but is slower
        SELECT (ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom AS geom
        FROM my_points
      ) AS polys
      JOIN my_points AS points
        ON ST_Intersects(points.geom, polys.geom)
      GROUP BY points.group;

to actually get the 'dissolved' multipolygons grouped by the group id.

Answer 2

You would need to spatially associate the points to the polygons.

create table voronoi_output as
SELECT geom, my_points.group 
FROM (
     SELECT(ST_Dump(ST_VoronoiPolygons(ST_Collect(a.geom)))).geom as geom 
        FROM my_points a) as voronoi,
      my_points b
WHERE st_within(b.geom, voronoi.geom);

Answer 3

Combining the answers from ThingumaBob and JGH gives

create table voronoi_output as
select ST_Union(voronoi.geom), my_points.group from
    (select (ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom as geom 
     from my_points) as voronoi,
    my_points
where ST_Within(my_points.geom, voronoi.geom)
group by my_points.group;