Coordinate Implied/False Precision Issue

[grantfitzsimmons]

Original Asana task

Raw data like 37°11’ will usually have a measurement precision of ± 1’ . This level of precision (± 1’ or ± 1.85km) requires only 2 decimal places to sufficiently store the value as decimalised degrees in the database eg 37.18° . However, due to the fact that say 6 decimal places are used to store the data eg 37.183333°, there is an implication that the original precision was at   ± 0.004” or ± 0.11m, which also implies that the original measurement was 37°11’00.000”.   This introduces false precision due to the storage of numbers in the database.

Quoting Chapman AD & Wieczorek JR (2020) Georeferencing Best Practices:

false precision An artefact of recording data with a greater number of decimal places than implied by the original data. This often occurs following transformations from one unit or coordinate system to another, for example from feet to meters, or from degrees, minutes, and seconds to decimal degrees. In general, precision cannot be conserved across metric transformations; however, in practice it is often recorded as such. For example, a record of 10°20’ stored in a database in decimal degrees is ~10.3°. When exported from some databases, it will result in a value of 10.3333333333 with a precision of 10 decimal places in degrees rather than the original precision of 1-minute. Misinterpreting the precision of the coordinate representation as a precision in distance on the ground, 10-10 degrees corresponds to about 0.002 mm at the equator, while the precision of 1- minute corresponds to about 2.6 km. This is not a true precision as it relates to the original data, but a false precision as reported from a combination of the coordinate conversion and the representation of resulting fraction in the export from a database.

Specify should be able to use a coordinate precision value (entered by the user) which can be used to determine the correct level of detail with which to report/display coordinate values.

Requested By: Mark Wallace at CSIRO

[benanhalt]

It's a shame that the SQL standard does not provide a SCIENTIFIC or MEASUREMENT type to represent such data. In principle we could represent the values using multiple columns (or a composite type if we ever switch to postgres) comprising the mantissa decimal(n+1, n), exponent integer, precision non negative integer, and possible estimated_final non negative integer values and add a database function or generated field to cast the value to native types as needed for ordering or computations.

See: https://en.wikipedia.org/wiki/Scientific_notation and in particular: https://en.wikipedia.org/wiki/Scientific_notation#Significant_figures