Outputing regular values to file, except when they meet certain criteria (what's going wrong?)

[Iapetus]

Background:

I need to create a number of grids of regularly-spaced geographical positions.

I've done this many times before using a couple of nested for/next loops (and a GUI to make selecting the parameters easier), but in this case there are a couple of complications.

1) I need to create a large number of grids with different bounds and resolutions (0.0005 to 0.002 degrees)

2) They are eventually going to be merged into a larger grid with a coarser (0.008 degree) resolution, and I don't want any duplicate positions.

The first issue is not much of a problem: it was relatively trivial to make some code to read the parameters from a file and pass them to the main grid-creation code.

The second issue is giving me a lot of trouble. I assumed it could be easily done by dividing a position by the resolution of the course grid, and checking if the result was an integer or not:

 If (Lat - V_Lat_Min) / V_res = Int((Lat - V_Lat_Min) / V_res) And (Lng - V_Long_Min) / V_res = Int((Lng - V_Long_Min) / V_res) Then
         ' this point coincides with the main grid, so skip it  
 Else
         Print #2, CStr(Lat) & Delimiter & CStr(Lng) & Delimiter & "0" 
 End If

where Lat and Lng are the node coordinates for the high-res grid, and V_Lat_Min, V_Long_Min and V_res are the south edge, west edge, and resolution of the low-res grid.


Everything else in my code is working fine, apart for from this bit.

If I manually plug the coincident positions into the equation, it yields TRUE, which should mean the code to write the values to the output file gets skipped, but for some reason that is not happening.

(Example: position 50.456, -2.600 coincides with the main grid, but is still being written to the output file).

Can anyone see what's going wrong?


The full code is below:

 Option Explicit
' Misc variables
Dim Header As String
' High-res grid parameters
Dim River As String
Dim nLimit As Single, sLimit As Single, eLimit As Single, wLimit As Single, Resolution As Single
Dim Lat As Single, Lng As Single
Dim Delimiter As String
 
' Low-res grid parameters
Dim V_Lat_Min As Single
Dim V_Lat_Max As Single
Dim V_Long_Min As Single
Dim V_Long_Max As Single
Dim V_res As Single
 
 
Private Sub cmdCreate_Click()
 
        ' Open file for output
        If Right$(txtPath, 1) <> "\" Then txtPath = txtPath + "\"
        Close
        Open txtPath + txtDatumList For Input As #1
 
        ' Output delimiter
        If optTab = True Then
            Delimiter = Chr(9)
        ElseIf optSpace = True Then
            Delimiter = " "
        ElseIf optComma = True Then
            Delimiter = ","
        Else
            Delimiter = txtDelimiter
        End If
 
        ' Low-res grid parameters
        V_Lat_Max = 63.904  ' North limit of main grid
        V_Lat_Min = 46.8    ' South limit of main grid
        V_Long_Min = -25    ' West limit of main grid
        V_Long_Max = 3.504  ' East limit of main grid
        V_res = 0.008       ' Main grid resolution
        ' Get past parameter file header
        Line Input #1, Header
 
       ' Work through parameter file
        Do Until EOF(1)
 
            ' Input high-res grid parameters
            Input #1, River, sLimit, nLimit, wLimit, eLimit, Resolution
            Open txtPath & River & "_" & CStr(Resolution) & ".xyz" For Output As #2
 
            ' loop to output positions
             For Lat = sLimit To nLimit Step Resolution
                For Lng = wLimit To eLimit Step Resolution
                    ' Stop floating point errors getting into the positions
                    Lat = Round(Lat, 4)
                    Lng = Round(Lng, 4)
 
                    ' Check if high-res grid node co-incides with main grid node
                    If (Lat - V_Lat_Min) / V_res = Int((Lat - V_Lat_Min) / V_res) And (Lng - V_Long_Min) / V_res = Int((Lng - V_Long_Min) / V_res) Then
                        ' this point coincides with the main grid, so skip it
                    Else
                        'Print #2, Format(Lat, "00.0000") & Delimiter & Format(Lng, "000.0000") & Delimiter & "0"
                        Print #2, CStr(Lat) & Delimiter & CStr(Lng) & Delimiter & "0"
                    End If
                Next
            Next
 
            Close #2
 
        Loop
 
        ' Final feedback
        MsgBox ("Output complete")
        Close
End Sub

 

[Iapetus]

I was just about to post some additional information to see if that would help anyone answer, but I think I've found out what the problem is:


It's a floating point error.

VBA thinks that 50.456 - 46.8 is 3.65600000000001, so when I divide by 0.008 I don't get an integer even when I should.

I guess I'll have to change the check from "is it an integer" to "is it an integer when rounded to x decimal places".


*****

Additional info for those that are interested:
I'm using Excel 2002 on Windows XP SP2.

The low-res grid starts (SW corner) at 46.8N, 25W (I'm using negative values for western hemisphere coordinates), with a node spacing of 0.008 degrees

An example high-res grid starts at 50.45N 2.6W, node spacing 0.002 degrees.

A sample of the date written to the output file is:
50.454 -2.304 0
50.454 -2.302 0
50.454 -2.3 0
50.456 -2.6 0
50.456 -2.598 0
50.456 -2.596 0
50.456 -2.594 0

The bolded position coincides exactly with the low-res grid and should not have been written to the file; all the others should have.
50.456 -2.592 0