Bounding box for PostGIS table

Question

Is there an easy way to get the bounding box for an entire table in PostGIS?

Answer 1

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;

Answer 2

As @underdark answered, ST_Extent will do the job, but keep in mind that it does not return a geometry but a box2d. If you need a geometry type you should use something like

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 ;

Answer 3

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

or

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.

see Source.

Answer 4

Not enforcing any spatial aggregation should be way quicker:

select
  min(ST_XMin(geom)) as _left,
  min(ST_YMin(geom)) as _bottom,
  max(ST_XMax(geom)) as _right,
  max(ST_YMax(geom)) as _top
from x