[postgis-devel] Inconsistencies in text output

[rmrodriguez at carto.com]

On Tue, Apr 21, 2020 at 2:14 PM Sandro Santilli <strk at kbt.io> wrote:

> It depends on the direction. I guess you're after:
>   Text -> Binary -> Text

No, in fact I'm after Binary (stored) -> Text -> Binary, although you
can continue the cycle.

> But when you go the other way:
>  Binary -> Text -> Binary
> The same is not true.
> The binary number may have been computed as 1/3.

The binary number computed as 1/3 is 0x3FD5555555555555, which is
exactly 3.33333333333333314829616256247E-1, so you never, at any
moment, had a double whose value was exactly 1 / 3. Based on that,
it's obvious that the value after binary -> text -> binary can't be
exactly 1/3, both because you didn't have it in the first place and
because it's impossible to represent it.

What I'm implementing is the following:
You have the binary 0x3FD5555555555555, use ST_AsText with enough
precision and you get `POINT(0 0.3333333333333333)` (no extra
precision will ever be given). When you import back
`0.3333333333333333` you will get exactly the same binary value
(0x3FD5555555555555). There isn't any information loss as you get the
same thing you had originally.

Note that the big difference with 3.0 is that you don't get digits
that aren't significant:
* 3.0:
```
# Select ST_AsText(ST_MakePoint(0, 1.0/3.0), 1000);
                            st_astext
-------------------------------------------------------------------
POINT(0 0.333333333333333314829616256247390992939472198486328125)
(1 row)
```

* 3.1:
```
# Select ST_AsText(ST_MakePoint(0, 1.0/3.0), 1000);
          st_astext
-----------------------------
 POINT(0 0.3333333333333333)
(1 row)
```

Note that when ingesting both strings (the one for 3.0 and the one for
3.1) you get the same double (0x3FD5555555555555) so what we are doing
is to avoid printing any unnecessary digits when you want full
precision (to be able to go back to the original value).

I want to make clear that this won't solve any imprecision introduced
due to previous operations rounding errors, you get the same value you
had but if the value was imprecise to begin with there is nothing that
any output function (WKT, WKB or whatever) can do to fix that after
the value is already calculated.


-- 
Raúl Marín Rodríguez
carto.com