19

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

26

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;
13

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 ;
  • 1
    If you need a bounding box for each row with no aggregation, just use ST_Envelope! That's not what was asked for though. – bugmenot123 Jul 31 at 11:37
  • 1
    You are right about st_envelope i update the answer. About the "not asked for", sometimes a try to give related answers because for people that not speak English well (like me) is difficult to choose the correct search text to find an answer. Probably i fell in this question searching an answer for the second topic. – Francisco Puga Aug 1 at 7:28
4

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.

  • 1
    The ST_Extent approach is magnitudes faster as it can operate purely on numbers and does not have to do any complex geometric calculations. Avoid ST_Union whenever you can. – bugmenot123 Jul 31 at 11:38
2

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
  • 1
    ST_Extent also does not have to do anything spatial. It is 2-3 times faster than your approach for my data (~400k polygons). Probably because it can do it all in one go, while your approach has to look at several aspects of each geom and then aggregate in the end. – bugmenot123 Jul 31 at 11:41

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.