Creating polygon from points with other attributes in pgRouting

Question

I have table, which looks like this:

This is the result of pgr_drivingdistance. I would like to create a polygons for each mz.

I tried to use group by.

drop table if exists service_area_polygon;
create table service_area_polygon as (
SELECT ST_SetSRID(pgr_pointsAsPolygon('SELECT id_node::integer as id, mz
, ST_X(the_geom)::float AS x, ST_Y(the_geom)::float AS y 
FROM service_area_points group by mz, id_node, the_geom'),2180));

But this query creates one polygon with a extent of all points. Something like this:

I also tried to use partition, but I'm new to these issues.

The new table with polygons should have the mz attribute.

I hope, that my problem is clear.

Answer 1

Grouping is the right idea.

I think a lateral join might work here:

create table service_area_polygon as
SELECT distinct mz, ij.geom from
service_area_points s1
left join lateral
(select
ST_SetSRID(pgr_pointsAsPolygon('SELECT id_node::integer as id,
ST_X(the_geom)::float AS x, ST_Y(the_geom)::float AS y 
FROM service_area_points s2 where s2.mz = ' || s1.mz ||';'),2180) as geom
) ij on true
;

You might need to do some casting on the s1.mz, but worth trying first.