[postgis-users] Documenting performance tip

[strk at refractions.net]

On Fri, Feb 04, 2005 at 10:57:52AM +0100, Markus Schaber wrote:
> Hi, strk,
> 
> strk at refractions.net schrieb:
> 
> > I see. Well, we can use Envelope(the_geom) then.
> > It's OGC-compliant and retains SRID.
> >
> >   SELECT addGeometryColumn('mydb','mytable','bbox','4326','GEOMETRY','2');
> >   UPDATE mytable set bbox = Envelope(geom_column);
> 
> This query is a lot more readable, its a pity that I did not have that idea.
> 
> But it seems to suffer from the box2d precision loss, I get 15 digit
> output on lwgeom, while hwgeom and my query return the original
> ordinates with 6-7 digits. The question is whether this does hurt, as
> the && index operation also uses the tweaked coordinates.

LWGEOM && always use float values (the geometry bbox as a BOX2D).
The BOX2D is guaranteed (actually promised) to be at lest as large
as the corresponding BOX3D.

> > I'm not sure about dimensions. Currently both box3d->geometry cast and
> > Envelope return a 2d geometry, but both could change in the future.
> > Would a ZMpolygon be a problem ?
> 
> I do not know if any user will actually use measured geometries in a &&
> query. Does && honour Z and M in any way? If not, it is fine to ignore
> it in the bbox column.
> 
> If yes, we should try to preserve it in our bbox column, or document
> that the sematics are changed.

No, the && query won't use Z or M, question was wheter a ZM box polygon
would be TOASTED... if yes the query should be:

set bbox = Envelope(force_2d(geom_column));

> My personal opinion: The whole thing is just only a workaround for a
> hopefully soon-to-be fixed query planner problem that bites in a rather
> strange corner case. I do not think that we should spend too much energy
> polishing it.

Don't all postgresql version *suffer* from this ?
If yes I think it's worth giving a polite tip about it.

--strk;

> 
> Markus
> 
> 
> --
> markus schaber | dipl. informatiker
> logi-track ag | rennweg 14-16 | ch 8001 zürich
> phone +41-43-888 62 52 | fax +41-43-888 62 53
> mailto:schabios at logi-track.com | www.logi-track.com



> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users