[GIS] How to convert MultiLines to MultiPolygons using Postgis

bufferpostgispostgis-2.0

In postgis I'm using the following query to buffer lines to polygons:

UPDATE rivers
  SET poly = ST_Multi(ST_Buffer(lines, 0.01))
  where id = 1;

The column 'lines' is a MultiLine and the column 'poly' is a MultiPoly. In record #1 'lines' contains 92 overlapping Line types. I would expect the result of St_Buffer to be a MultiPoly, but it is just a Polygon (If I run the above query without St_Multi I get a sql error. What am I doing wrong?

Best Answer

You have to use ST_DUMP first to split the multilines into seperate lines, buffer those and then collect them into a multi again:

UPDATE rivers
  SET poly = p.npoly
  FROM  (
    SELECT ST_MULTI(ST_COLLECT(ST_BUFFER((q.dump).geom,1) ORDER BY (q.dump).path)) as npoly    
    FROM (
     SELECT ST_DUMP(lines) AS dump from rivers   where id = 1
          ) as q
          ) as p
   where id = 1
Related Question