How to round Double's to exact bit-representation?

Question

I'm doing geography calculations, and ultimately end up with a latitude and longitude to store in a Geography::Point object. Both latitude and longitude can have 7 digits at most (which also gives precision up to 11 mm, which is plenty).

The problem is: if the value of a field cannot be stored correctly in a Double, MS SQL rounds towards the nearest number that can, but does so by adding a bunch of digits.
=> e.g. 5.9395772 is stored as 5.9395771999999996

The problem this creates, is that [Position].ToString() then exceeds the maximum amount of characters is allowed for that column (and no, I can't increase that limit).
Since we're dealing with Latitude, Longitude, Altitude and Accuracy, there's space for exactly 11 characters for Latitude and Longitude each:
String.Format(CultureInfo.InvariantCulture, "{0:##0.0######}", num)

I've tried simply Math.Round()ing to 6 digits, but then other numbers (e.g. 6.098163 to 6.0981629999999996) get the same problem.

How do I Math.Round towards the nearest 7-digit valid bit representation?

EDIT/ADD

    Public Function ToString_LatLon(ByVal num As Double) As String
        num = Math.Round(num, 7, MidpointRounding.AwayFromZero)
        Return String.Format(CultureInfo.InvariantCulture, "{0:##0.0######}", num) 
    End Function 'IN = 5.9395772, OUT = 5.9395772

• The above code receives a Double and correctly returns the String representation. I've checked it, this is correct also for troubling numbers.

• It's stored in SQL Server through the framework we use. I think the problem occurs when storing the value

• When I retrieve the value, I get an error in VB, saying the value is wider than the framework allows (max of 50 characters).

• If I run a query in SSMS, I find e.g. POINT (X.0981629999999996 XX.664725 NULL 15602.707) (51 characters, anonimized).

EDIT 2

I've done some more research and some calculations. It seems that the stored value 5.9395772 is converted to binary and returned as 5.9395771999999996, which is stored as a double inside the database (in a binary Geography::Point object, not to worry.) Convert the binary 0 10000000001 0111110000100010000010000110100010000100010011011101 back to decimal, and you get 5.93957719999999955717839839053340256214141845703125, but abbreviated at 16 decimals - whereas I would like it abbreviated at 7 decimals.

Solutions:

1. Round the value down/up to the nearest value where everything from the 8th decimal onward is 0 (or enough zeroes before another nonzero digit is found)
2. Query for only so many decimals.
3. Query the actual (hexadecimal) value, and convert that (instead of the string representation)
4. Keep the string representation, but round the values before storing and after retrieving to the required amount of decimals.

Discussions:

1. Both in office and here (at @RobertBaron's answer): this is quite tricky, might have a huge decrease in precision, and is basically a lot of work.
2. Perhaps this is possible, I don't know.
3. This would be the cleanest solution, as my colleagues and I agree, however this is a lot of work in developing and testing.
4. Instead of caring about the value in memory to be equal to the value in the database, we don't care about the value in the database (too much).

In the end, after quite some whiteboard bit-calculations and a lengthy discussion, we've gone with option 4. After we retrieve the [Position].ToString() (for which we've increased the string limit) from the database, we convert that as we're already doing, and as additional step before using it anywhere we round the value to the required amount of decimals. When returning the value to the database, we once again round the value to the amount of decimals, and don't care what the database really does with it.
Essentially, this is option 2, but then on the program-side instead of database-side.

Answer 1

This is only a partial answer.

If by valid bit representation you mean exact bit representation, then this is possible. The decimal numbers that have exact bit representation are 1/2, 1/4, 3/4, 1/8, 3/8, 5/8, 7/8, 1/16, 3/16, ...

The challenge is to characterize among these powers of two, those whose base 10 representation has 7 digits or less, and then to round any base 10 number to the closest of these numbers.

I am posting this in the hope that it may get you one step further toward a solution.

Answer 2

If you cannot change the data type into a DECIMAL for whatever reasons, you have to cast it into a DECIMAL every time you need the value. It's that simple. And you can either do it on the SQL Server side or in VB.NET, but you need a DECIMAL. DOUBLEs are imprecise.

By the way, it is not the SQL Server that rounds towards the nearest number it recognizes by adding a bunch of digits - it's the processor that does it. That's also why you may get slightly different DOUBLE values after restoring your database on another server.

And never ever even think of using them as an ID: I know an application that uses FLOAT values containing the timestamp (<creation day since whatever>.<time as fractals of the day>) as part of the primary key (of nearly every table!). Every 10000th record or so cannot be addressed directly by its ID because the value differs somewhat on the client that sends the query and the server by some nanoseconds although the number looks exactly the same in SSMS on the client and the server.