Strange significant digits when importing

[e-zaline]

There is a strange (little) bug when importing my excel file with my SpineToolbox importer.

I get that

when what I'm importing is that (so 6.318 exactly, not 6.31800......0005)

This is in the context of FlexTool, but I thought it was maybe more related to SpineToolbox directly? @jkiviluo

[DillonJ]

I see this a lot in SpineOpt - might be able to handle it at SpineDBAPI level... it would be nice to be able to specify a system level -precision for the input data and then dbAPI rounds everything to that precision. There can also be significant advantages in terms of optimisation... there is a big difference in terms of the sparse matrix manipulations between a zero and a very small value.

[DillonJ]

This also causes problems when zeros sometimes become very small negative values causing infeasibilities

[jkiviluo]

If there's an easy solution, please fix it right away (@manuelma or @soininen are maybe most familiar with this part in the code). Otherwise, it should be done in 0.8.2. when we'll be working with parameters anyway.

[soininen]

Floating point numbers used by computers are of fixed size and therefore have limited precision inherently. Since they are also 2-based, they cannot accurately represent every 10-based number out there. Small rounding errors when converting "our" numbers to computer numbers are to be expected.

That being said, I could not reproduce the rounding error in this particular case so I think it is worth investigating further. @e-zaline Is it possible to provide the Excel file or parts of it for testing purposes?

[DillonJ]

@soininen I think to avoid this, we need to be able to specify a user, or system level precision that is less than the machine precision to avoid this. As I say above, this also has advantages for the optimisation

[soininen]

I think that Database editor should be honest about the numbers and show them in all their gruesome detail.

That being said, I understand the problems regarding zeros and close-to-zero numbers. I would have expected solvers to master that voodoo but honesty I am not familiar with them at all. Perhaps these cases could be handled in SpineInterface or by some spinedb_api filter that rounds very small numbers to zeros?

[DillonJ]

Perhaps these cases could be handled in SpineInterface or by some spinedb_api filter that rounds very small numbers to zeros?

That's what I was thinking - perhaps it should be handled at spinedb_api level. Solvers do generally have a tolerance below which they assume a number is zero - but the default is usually very very small. Small negative numbers are also another, different problem

[e-zaline]

Floating point numbers used by computers are of fixed size and therefore have limited precision inherently. Since they are also 2-based, they cannot accurately represent every 10-based number out there. Small rounding errors when converting "our" numbers to computer numbers are to be expected.

That being said, I could not reproduce the rounding error in this particular case so I think it is worth investigating further. @e-zaline Is it possible to provide the Excel file or parts of it for testing purposes?

Sure, I'll send it to you by email @soininen