Floating- point fields for MinElevation, MaxElevation, StartDepth, EndDepth

When updating Specify to version 6.8.02 the very concerning message ‘Converting float fields to decimal’ came floating by. Surely enough, in the release notes:

The Specify 2.10 schema update changes all data fields previously declared as a floating point values (Float) to the fixed-point Decimal data type. The Decimal data type accurately represents any number within the precision of the format, whereas Floats round and change decimal values. This is important when exact decimal values for fields like measurements need to be protected without changes due to rounding.

I can see the rationale to use the decimal data type for some fields where the precision is important – and in fact the decimal data type was already used in Specify – but to do this across the board is an extremely bad move.

Fields like MinElevation, MaxElevation, StartDepth and EndDepth absolutely need to be floating point numbers. The difference might seem insignificant, but it is not, as a value for altitude (or depth) of say 1200 meters becomes 1200.0000000000. Nobody wants all these extra zeros. I now have to change all our labels, our data delivery to ALA and GBIF and everything else I have developed that uses fields that once were floating point numbers (and have now been turned into strings really).

I would very much appreciate if this incredibly poorly conceived change could be rolled back.

This is indeed a very annoying problem for us and we too ask you to roll back this change.
(If there is no possibility to reduce the decimals via the views.xml)

In the same context: if you include Lat/Long in the aggregated view of Locality, the 12 decimals are also very awkward. This is an old problem of Specify and it has never been resolved.

Hi @NielsKlazenga and @Schuchert,

We have identified that this is a problem and we are actively seeking solutions. We appreciate your feedback and we are considering ways to engineer a solution to this issue!

Are your main concerns regarding data exporting or the display on the forms?

In Specify 7, this issue with the spurious trailing zeroes is resolved, but with data exports the same behavior will occur. If you have any other input we would appreciate to hear it!

Thank you again for sharing your concerns and we will be addressing this soon!

Thanks @Grant,

Problems with decimals are:

  • they misleadingly suggest a much bigger precision than is really in the data
  • display in forms (as @Schuchert 's screen shots show)
  • more importantly, display on labels: the values have become three or four times as long, so they will not fit anymore
  • data delivery to GBIF, ALA etc.

On the other hand, there seems to be no benefit in having the values changed to decimals at all, as Specify removing “spurious zeros” already indicates that we are not working at the kind of precisions where the additional precision that decimals provide makes a difference (I do not know when that kicks in, but I expect you’ll need more than ten decimal places for that).

The bigger problem though is that a very significant change was made without consultation with users and ostensibly without thinking. You might be able to engineer solutions for the Specify clients, but some of us have Specify integrated with other systems and I think that is something the SCC would want to encourage rather than derail. We make our own labels and reports and use the IPT for delivery of data, so we do not use the Specify clients for that. Yes, I can fix everything that this change will break, but I should not have to. And what if a collection hired a contractor to develop some tool around Specify some years ago and are not able to fix things?

Also, rather than engineer patches for things that have broken, why not tackle the problem at the source and just roll back the change?

We modified the underlying database schema, hopefully that doesn’t present problems moving forward. Fixed it for us; we are happy to have two decimals of precision. This truncated some values that had more than two decimal places populated, but those records were found to be in error.

ALTER TABLE locality MODIFY MinElevation DECIMAL(9, 2)
ALTER TABLE locality MODIFY MaxElevation DECIMAL(9, 2)
ALTER TABLE locality MODIFY ElevationAccuracy DECIMAL(9, 0)
ALTER TABLE locality MODIFY LatLongAccuracy DECIMAL(9, 0)

Hi @foozleface,

Modifying the underlying database schema is at your own risk and could result in issues when upgrading in the future.

We strongly recommend that you do not make any changes to the database schema as it could cause unexpected issues when attempting to upgrade Specify in the future.

If you do make any changes, it is essential that you revert them prior to upgrading Specify, otherwise you are likely to encounter issues.

Hi @NielsKlazenga,

We are working on a response internally now. We have a potential solution that we will be sharing with the community very soon so we can collect feedback before making any additional changes.

Once again, thank you for your feedback!

another unwanted effect of the format change: at least in SPecify 6 the value 0 is not accepted any more (e. g for altitude 0m at sea level, or depth 0m) !