[postgis-users] ST_MakeBox2D losing precision ?

[Mark Cave-Ayland]

On Fri, 2007-10-12 at 12:56 +0200,
postgis.domain.thewild at spamgourmet.com wrote:
> Hi list !
> 
> First of all, the versions :
> PostgreSQL : "PostgreSQL 8.2.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
> PostGIS : "POSTGIS="1.3.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS"
> 
> I am trying to modify geometries stored in a table.
> They all should be squares with 158.75 meters sides, but some are bigger (vertically). I wrote a query to update them, but I realized that after the update, some geometries where 159 meters high.
> 
> Here is the test that shows this behaviour :
> 
> SELECT 
> 	ymax(makebox2d(st_makepoint(xmin(bbox),ymax(bbox)-158.75), st_makepoint(xmax(bbox),ymax(bbox)))) - 
> 	ymin(makebox2d(st_makepoint(xmin(bbox),ymax(bbox)-158.75), st_makepoint(xmax(bbox),ymax(bbox))))
> FROM 
> 	my_table
> WHERE 
> 	(st_ymax(bbox)-st_ymin(bbox)) > 158.75;
> 
> Results are 158.5 or 159, so it appears that ST_MakeBox2D is losing part of the precision of my points.
> I checked the points coordinates returned by ST_MakePoint, and those are OK !
> 
> Have I overlooked something ?
> I will rewrite this to create a polygon WKT, but I wanted to point this out.
> 
> 
> Regards
> --
> Arnaud


Hi Arnaud,

The BOX2D datatype is "lossy" since it is intended only to be used
within the GiST indices where doubles are "rounded up" to the nearest
float. Probably the easiest thing to try is working with native
geometries rather than the BOXXD types - you may find that
ST_Translate() helps for what you are trying to do.


HTH,

Mark.

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