[postgis-users] Does ST_AsText truncate or round

[Paul Ramsey]

On Sat, May 17, 2008 at 6:45 PM, Paragon Corporation <lr at pcorp.us> wrote:

> SELECT ST_Transform(ST_Transform(the_geom, srid2), srid1)
>
> It is off a bit from what you started out with.  It gets progressively worse
> the more transformations you pile on.  Perhaps there is no easy solution to
> it.
> A large part of it I assume is just accumulation of floating point errors.

All of it is (if you stay entirely inside the database).

If you go outside the database, as a client application developer, you
should not be transiting your data in WKT, you should be doing it in
WKB. Make use the the PostGIS ST_AsBinary() functions to get true
representations of the internal data, and the PostgreSQL encode()
function to turn that byte array into a packed text format. Then do
the reverse in your client-side software.

Precision models are a topic of some debate, whether they improve the
situation inside the database or not. They can improve apparent
consistency in some cases, and reduce it in others. A proper precision
model would probably have to be applied after every transformation, so
that fully functional tests were consistent
centroid(transform(transform(X))) == centroid(X), but that would leave
some interesting issues hanging around, like: if my precision model is
a 0.001m grid in UTM, what is it in WGS84?

Carefully application of snapping in an application can help reduce
these issues, but they still manage to creep through. Kevin probably
has some interesting examples.

Try wrapping your calls in

ST_SnapToGrid(geometry, 0, 0, 0.0001, 0.0001)

Or place that as an insert/update trigger, to force everything into an
externalized grid. Note that you'll get bad results in places where
shared edges have un-matched co-linear vertices.

P.