Is there an easy way to get the bounding box for an entire table in PostGIS?
ST_Extent should do the trick.
ST_Extent — an aggregate function that returns the bounding box that bounds rows of geometries.
Applied like this:
SELECT ST_Extent(the_geom) as table_extent FROM your_table;
SELECT ST_SetSRID(ST_Extent(the_geom), THE_SRID) as table_extent FROM your_table;
Also, if what you need is get the bounding box of each of the rows you can also use
ST_Extent and a fake
GROUP BY like this:
SELECT ST_SetSRID(ST_Extent(the_geom),THE_SRID) as table_extent FROM your_table GROUP BY gid;
Assuming that gid is the primary key of the table
But ST_Envelope will do a better job as @bugmenot123 stated in the comments
SELECT ST_Envelope(geom) FROM your_table ;
Another possibility is to use the
ST_Envelope function, which returns a geometry with SRID,
ST_Envelope — Returns a geometry representing the bounding box of the supplied geometry
, along with the aggregate function
ST_Union to obtain the union of all geometries (or the union of their respective envelopes) as follows:
SELECT ST_Envelope(ST_Union(geom)) AS table_extent FROM your_table
SELECT ST_Envelope(ST_Union(ST_Envelope(geom))) AS table_extent FROM your_table
The second option should be faster as it simplifies the union operation by using the envelopes of individual geometries.
Not enforcing any spatial aggregation should be way quicker:
select min(ST_XMin(geom)) as l, min(ST_YMin(geom)) as b, max(ST_XMax(geom)) as r, max(ST_YMax(geom)) as t from x