How to retrieve the corners of the bounding box from ST_Extent?

Question

Is there any way to fetch the lat/long of the corners of the bounding box we got from ST_Extent?

select ST_Extent(geom) from tableName;

Answer 1

ST_Extent returns a box composed of xmin, ymin, xmax, ymax (bottom left, upper right coordinates):

osm=# select st_extent(way) from planet_osm_point;
                            st_extent                                
-------------------------------------------------------------------------
 BOX(2259828.73261444 5412478.55751597,3301031.7673783 6147766.43413396)

To retrieve the point corresponding to bottom left, you can build a point composed of the bottom left X coordinate (st_xmin) and bottom left Y coordinate (st_ymin), e.g.:

osm=# select st_astext(st_makepoint(st_xmin(st_extent(way)), st_ymin(st_extent(way)))) 
      from planet_osm_point;
            st_astext                 
------------------------------------------
 POINT(2259828.73261444 5412478.55751597)

Note that since st_extent returns a bounding box, it strips of the SRID off of your geometry so you may want to add it to your newly created point (i'm using 900913 to match my OSM data's SRID):

osm=# select st_asewkt(st_setsrid(st_makepoint(st_xmin(st_extent(way)), st_ymin(st_extent(way))), 900913)) 
      from planet_osm_point;
            st_astext                 
------------------------------------------
 SRID=900913;POINT(2259828.73261444 5412478.55751597)