2

I have table, which looks like this:

enter image description here

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:

enter image description here

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.

3

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.

  • It works! I wouldn't think about join. Thanks! – Michal De Oct 30 at 12:12

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.