[postgis-users] Problem with EXTENT function

[Mark Cave-Ayland]

On Tue, 2007-11-20 at 18:40 +0100, Christoph Stasch wrote:
> Hi,
> 
> I am using PostgreSQL 8.2 together with PostGIS 1.2.1. and I have a 
> problem with the EXTENT function of PostGIS:
> 
>  "POINT(-86.644724 34.725302)" and "POINT(-86.644724 34.725183)" are 
> contained in the "geom" column of "myTable".
> 
> If I use "SELECT EXTENT(geom) FROM myTable;", I get the following result:
> 
> "BOX(-86.6447296142578 34.7251815795898,-86.6447219848633 
> 34.7253036499023)".
> 
> Maybe this is a bug?
> 
> Best regards,
> 
> Christoph


Hi Christoph,

*blinks* that's definitely a bug, and I'm surprised no-one has noticed
it before now :(  

The culprit is the ST_Extent() aggregate in that it uses the box2d base
type instead of box3d. BOX2D geometries should only ever be used for
queries against the GiST indices, and never for anything else because
they 'inflate' the extent as part of the conversion from double
precision to single precision float.

The ultimate fix would be to change the definition of the ST_Extent()
aggregate function in lwpostgis.sql to use the box3d base type like
this:


CREATE AGGREGATE ST_Extent(
	sfunc = ST_combine_bbox,
	basetype = geometry,
	stype = box3d
	);


The bad news is that this change would alter the text representation of
the bounding box from this:


bug=# select st_extent(geom) from foo;
                                 st_extent
----------------------------------------------------------------------------
 BOX(-86.6447296142578 34.7251815795898,-86.6447219848633
34.7253036499023)
(1 row)


to this:


bug=# select st_extent(geom) from foo;
                     st_extent
------------------------------------------------------
 BOX3D(-86.644724 34.725183 0,-86.644724 34.725302 0)
(1 row)


So unfortunately I don't think I could make the fix in a minor release
without hearing the cries of application developers everywhere as their
client-side BOX() parsing code breaks :(

The best solution at the moment would be to use ST_Extent3d() instead of
ST_Extent() which will give you the correct extents, e.g.


bug=# select st_extent3d(geom) from foo;
                     st_extent3d
------------------------------------------------------
 BOX3D(-86.644724 34.725183 0,-86.644724 34.725302 0)
(1 row)


ATB,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk