Bounding box of set of MULTILINESTRINGs in PostGIS?

Question

I have a PostGIS table with a geometry column (geom) and a type column. The geom column contains only MULTILINESTRINGs and the type column contains tags that tell me what each MULTILINESTRING represents. The tags are just letters. I’m trying to get the bounding box of all the MULTILINESTRINGs in the table that have type='a'. I tried the following but it does not work:

SELECT *
FROM line_table
WHERE line_table.geom && SELECT ST_Envelope(line_table.geom) FROM line_table WHERE type='a';

I’m new to PostGIS and SQL in general so I’m not sure if I’m even close to the answer. Right now I think the problem is that I'm getting one independent envelope for each MULTILINESTRING where type='a' instead of the bounding box of all the MULTILINESTRINGs together. Perhaps there is a way of merging all the MULTILINESTRINGs of type='a' into one geometry but so far I have not been able to figure out how.

Answer 1

The ST_Envelope() function operates on single geometries, therefore you are right, you need aggregate your MULTILINESTRINGs before you pass them by St_Envelope(). St_Collect() is a good aggregation function for that job.

SELECT ST_Envelope(st_Collects(foo.geom))
FROM line_table as foo
WHERE foo.type='a';

If you want to retrieve the bounding box for each different value of the "type" column in one single statement, then you would need to use the GROUP BY. Something like this:

SELECT foo.type, ST_Envelope(st_Collects(foo.geom))
FROM line_table as foo
GROUP BY foo.type;

Answer 2

Just to expand on Alexandre's answer, as of at least version 2.3.2, PostGIS now does allow you to get a bounding box from multi geometries.

SELECT st_astext(
         st_envelope(
           'MULTILINESTRING((0 0, 1 1), (2 2, 3 3))'::geometry))

There is also the Box2D function, which serves the same purpose, but also works with other types of geometries such as curves, surfaces and TIN. They also perform the same, even for large datasets.