[postgis-devel] Proposal for sensible number output semantics

[Martin Davis]

Paul & I experimented a bit with numeric output, to see what the effect of
the precision parameter really is currently.  It definitely seems a bit...
odd.

Consider this:

select i, ST_AsText('POINT(12345678.123456789 1)'::geometry, i )

from generate_series(0,20) as t(i);

 i  |           st_astext
----+--------------------------------
  0 | POINT(12345678 1)
  1 | POINT(12345678.1 1)
  2 | POINT(12345678.12 1)
  3 | POINT(12345678.123 1)
  4 | POINT(12345678.1235 <123456781235> 1)
  5 | POINT(12345678.12346 1)
  6 | POINT(12345678.123457 1)
  7 | POINT(12345678.1234568 1)
  8 | POINT(12345678 1)
  9 | POINT(12345678.1 1)
 10 | POINT(12345678.12 1)
 11 | POINT(12345678.123 1)
 12 | POINT(12345678.1235 <123456781235> 1)
 13 | POINT(12345678.12346 1)
 14 | POINT(12345678.123457 1)
 15 | POINT(12345678.1234568 1)
 16 | POINT(12345678.12345679 1)
 17 | POINT(12345678.123456789 1)
 18 | POINT(12345678.1234567892 1)
 19 | POINT(12345678.12345678918 1)
 20 | POINT(12345678.123456789181 1)

But then this:

select i, ST_AsText('POINT(123.123456789 1)'::geometry, i )
                                        from generate_series(0,20) as
t(i);
 i  |           st_astext
----+--------------------------------
  0 | POINT(123 1)
  1 | POINT(123.1 1)
  2 | POINT(123.12 1)
  3 | POINT(123.123 1)
  4 | POINT(123.1235 1)
  5 | POINT(123.12346 1)
  6 | POINT(123.123457 1)
  7 | POINT(123.1234568 1)
  8 | POINT(123.12345679 1)
  9 | POINT(123.123456789 1)
 10 | POINT(123.123456789 1)
 11 | POINT(123.123456789 1)
 12 | POINT(123.123456789 1)
 13 | POINT(123.123456789 1)
 14 | POINT(123.123456789 1)
 15 | POINT(123.123456789 1)
 16 | POINT(123.123456789 1)
 17 | POINT(123.123456789 1)
 18 | POINT(123.123456789000002 1)
 19 | POINT(123.1234567890000022 1)
 20 | POINT(123.12345678900000223 1)

The second test is working as expected.  The first one, not so much.  It
looks like at 15 TOTAL digits, a switch happens to change to limiting the
TOTAL digits, rather than the decimal places.  Not sure why that doesn't
happen in the second case.

So this might explain why specifying a large precision doesn't actually
provide that level of precision (at least for larger-magnitude numbers).

It would be nice to fix this.

A proposal that fixes this problem, has relatively simple semantics, and
doesn't require changing the precision default value is:

- for precision < 15, decimal places are limited to that precision
- for precision >= 15, full precision is output (supporting round-tripping)

Notes:
- for very large magnitude numbers PostGIS switches to scientific notation
for output.  This may not be ideal, but it does mean that the size of
output text is bounded (to around 30 chars max).
- for any precision, trailing zeros are dropped.  So a number could still
output with only a few chars, if it doesn't have many sig digits (e.g. 1.5)

Questions:
- does this solve the problem of round-tripping?
- How much impact does it have on unit tests?
- How can this be implemented? (hopefully there are some numeric output
routines in place which can provide this)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20200417/8f580f49/attachment.html>