SMS.exe -k
7z.exe a RVSharp.zip values.kml placemarkBY.png placemarkY.png
del RVSharp.kmz
ren RVSharp.zip RVSharp.kmz

The first line calls my program and updates values.kml
After updating the script generates the zip file, the KMZ extension cannot be used yet because of default settings issues
The KMZ is made (after deleting the previous file) by renaming the file from *.zip to *.kmz

SMSDatabse – Original Program, reads in SMS text files, removes entries with invalid data, then stores to a database

SMStoCSV – New Program, reads files from database made by SMSDatabase, scans for valid data at 10 min. intervals, then outputs a CSV or stores in a database

SMStoKML – New Program, uses output of SMStoCSV to generate a KML ship track

Seeing each programs function broken down, it’s obvious that the 1^st and 2^nd programs are doing duplicate steps and storing unnecessary data. The better solution would be to merge the CSV function from the 2^nd program into the 3^rd as an option of either KML or CSV. Then merge what remains of the 1^st and 2^nd together, making a program that reads SMS files directly for 10 min. intervals to store in a database. Additionally, a record of which files have been scanned in needs to be kept. I plan on using a database of only file names or another unique identifier to keep track of what has been done. I will be posting updates as I work through each of these steps.

The SMSDatabase program was designed to import measurement data from the Surface Mapping System onboard a research vessel into a database. The files necessary for the program are SharpHeader.csv and SMSDatabase.sdf, both must be in the program directory. Included in SharpHeader is the column headers, explanations of columns, and data types necessary for the program. The data types are contained in the 3rd row and determine how the program scans in each field. Also included is SMSDatabase – Blank, an empty database made in the required format.

Running the program

The program is called using the following console application –

     SMSDatabase.exe “DriveLetter:\\Folder\\Folder\\File”

The doubled backslashes are required as the file path is read in as a C string; an escape character to use a backslash is necessary. When run the program will scan in the specified file, format the data into a normalized set, then write the data to the database. No output is shown while running or at completion, the program is assumed to have run successfully if no error codes are displayed.

Functions of the program

The basic structure of the program is the following:

• Import header 3rd row of header, for data types
• Split header into array to match types
• Read file in as array (each row is an element)
• Open SQL connection (using SQL Compact Edition)

For each row in the file do the following:

• Split row into array then run checkForValid (does row have valid lat/lon data)
• If row is valid then run castRowData (checks decimal elements/formats position)
• Build SQL command then write to database

checkForValid requires an array (the row array), and returns a Boolean value. The function scans the latitude and longitude and final element of the row for valid data. The data is determined invalid if it meets 1 of the following; equal to -99 (null value), format exception error (the element is not a number), index out of range at row 7, 10, or 29 (the element does not exist). The Boolean value returned if the data is valid is true, if invalid it is false.

castRowData requires 2 arrays (the row array and data type array), and returns the row array as cleaned data. A comparison test is run for each element of the data type array. The array looks for a String, Dec, LatSuffix, or LonSuffix. If the type is string then nothing is done to the array. Dec requires a data test. Each element is cast as a decimal value then saved as a string, using the value if the test succeeded or NA if it failed. LatSuffix and LonSuffix are similar operations. Both scan the value for which hemisphere to use and convert the lat/lon data to an absolute reference. The value is multiplied by -1 where applicable to correct the data.

Expanding the program

In the future, the program may need to be modified to work with a file with more columns. To update the code the following changes would need to be made:

• Modify SharpHeader.csv to show the correct format and number of columns
• Modify SMSDatabase.sdf to contain the necessary number of columns
• For loop after creating SQL command, change <= 30 to correct number
• testRead = rowArray[29] in checkForValid, change 29 to correct number

So this program concludes my internship with UD. I had some prior experience with C, but never any with C++ or C#, so I learned a lot from that. Databases and file access actually turned out to be very easy with the .NET framework. Even though I never got to use the skills I’ve learned with Google Earth and KML files, I learned a lot from the graduate students while working in the ORB lab. They have been a great crew to work with.

So far the application can validate each row of data for lat/lon and useable values. Next will be writing the database application,expanding on the previous SQL CE database. Even though it’s still copy and paste from my old code, the subroutines for writing have been difficult to visualize.