[GRASS-user] Entering '<1' and 'BDL' in Database

[Rich Shepard]

On Mon, 19 Apr 2010, Adam Dershowitz, Ph.D., P.E. wrote:

> I would suggest that NULL doesn't mean "unknown".

Adam,

   Actually, it does.

> Instead it means, "can't be represented as a valid number".

   One can have NULL in a text column, too, when the value is not known.

> Another option is to use your own codes.  For example, if your sample data
> is all positive, then -999, might represent "less than 1". These type of
> codes are often used, but realize that they are somewhat of a kluge
> because fundamentally a column that holds numbers should hold NUMBERS, and
> putting in <1 is not really a number, but a flag that means something
> else.  You have to be careful if you do things like that because it is
> easy to do things like average a column, and accidentally include
> -999 in there.

   All database experts, including Joe Celko and Rick van der Lans, write
that such codes should never be used for exactly the reasons you present.

   However, thinking more about the situation, NULL would work. If the value
is below detection limits (which '<1' also means), then we really don't know
the value, so NULL is a reasonable entry.

Thanks to you and Dylan for suggestions,

Rich