### GeotagDbase.sml

### Requires version 2006:73 of the TNT products

### Randy Smith
### MicroImages, Inc.
### 19 January 2007

### Sample script to demonstrate geotagging a database table using a single GPS log file.

### Source table must contain a string field with date in form "month/day/year"
### and a string field with time in form "hour:min:sec".  User-defined functions
### getDate() and getTime() can be modified to accommodate other date or time formats.

### Coordinate fields "Latitude", "Longitude", and "Elevation" are added to a copy of 
### the input database table that is written to the same database object as the source table.


############## Global variable declarations ####################
class DATABASE dbSource;		# source database object
class DBTABLEINFO tblSource, tblDest;	# source and destination tables

class GPSDBASE gpsdbase;		# class for determining location based on Date/Time from
										# any source and one or more GPS track logs.

string srctablename$;			# name of selected source database table
string srcdatefld$, srctimefld$;		# names of selected date and time fields in the source table
string desttablename$;			# name designated for the new database table
string gpsfilename$;				# name of the selected GPS log file

class DBEDITOR dbSourceView, dbDestView;
numeric dbOpen;								# flag to indicate if source database has been opened
numeric destTblOpen, srcTblOpen;			# flags to indicate if tabular view of table has been opened

# variables related to the script dialog
string xml$;						# string containing the dialog specification in XML
class XMLDOC dlgdoc;				# class instance for the XML document specifying the dialog
class XMLNODE gpsdlgnode;		# class instance for the dialog's node in the parsed XML structure
numeric err;						# error value checked at stages of processing the dialog specification

class GUI_DLG gpsdialog;		# internal class instance for the script dialog

# handles for the various controls in the script dialog
class GUI_CTRL_LABEL tblLabel;
class GUI_CTRL_PUSHBUTTON openSrcBtn, tblBtn, dateBtn, timeBtn, logBtn, newTblBtn;
class GUI_CTRL_PUSHBUTTON runBtn, testBtn, exitBtn;
class GUI_CTRL_EDIT_STRING dbText, tblText, dateText, timeText, logText, newTblText, statusText;


################################################################## 
############### Procedures and Functions #########################
##################################################################


########################################################
# Function to get date string from a field in the source database
# and parse it to get year, month, and day. 
# Returns the Julian date (numeric value).
#
# Modify this function to fit the date format in your source table.
func getDate(numeric recnum, class DBTABLEINFO tblSource, string srcdatefld$) {
	local class DATETIME dateDT;	# structure to store and convert date/time information
	local string date$;
	local numeric year, month, day;

	# get string from designated date field in source table
	date$ = TableReadFieldStr(tblSource, srcdatefld$, recnum);

	# parse date string to get year, month, and day
	year = StrToNum( GetToken(date$, "/", 3) );
	month = StrToNum( GetToken(date$, "/", 1) );
	day = StrToNum( GetToken(date$, "/", 2) );

	# set date in DATETIME class and return value
	dateDT.SetDate(year, month, day);
	return dateDT.GetDateJulian();
	} # end of func getDate

##########################################################
# Function to get time string from a field in the source database
# and parse it to get hour, min, and sec.
# Returns the time as seconds since midnight (numeric valus).
#
# Modify this function to fit the time format in your source table.
func getTime(numeric recnum, var class DBTABLEINFO tblSource, string srctimefld$) {
	local class DATETIME timeDT;		# structure to store and convert date/time information
	local string time$;
	local numeric hour, min, tsec;

	# get string from designated time field in source table
	time$ = TableReadFieldStr(tblSource, srctimefld$, recnum);

	# parse time string to get hour, min, and seconds
	hour = StrToNum( GetToken(time$, ":", 1) );
	min = StrToNum( GetToken(time$, ":", 2) );
	tsec = StrToNum( GetToken(time$, ":", 3) );

	# set time in DATETIME class to be returned
	timeDT.SetTime(hour, min, tsec);
	return timeDT.GetTimeSecondsSinceMidnight(1);
	} # end of func getTime


##################################################################
# Procedure called when exiting.  Called by Exit button on dialog.
proc ExitScript() { 

	if (srcTblOpen) then
		DBEditorCloseTable(dbSourceView, srctablename$);

	if (destTblOpen) then
		DBEditorCloseTable(dbDestView, desttablename$);

	if (dbOpen) then
		CloseDatabase(dbSource);

	print("Closing...");
 
	gpsdialog.Close(0);
	}

#############################################################
# Procedure called when the dialog opens; sets status prompt.
proc OnDlgOpen() {
	statusText = gpsdialog.GetCtrlByID("statusText");
	statusText.SetValueStr("Select source database.");
	}

###################################################################
# Procedure to get source database object.
# Called by Source Database button on dialog
proc GetSourceDatabase () { 

	local class RVC_OBJITEM dbItem;				# RVC item for source database object
	local class FILEPATH dbFilepath;				# filepath of the source database object
	local class RVC_DESCRIPTOR dbDescript;		# RVC descriptor of source database (works with long object names)

	local string srcfilename$;				# name of the Project File containing the source database object
	local string srcobjname$;				# name of source database object
	local numeric err;

	#####################################################################
	statusText = gpsdialog.GetCtrlByID("statusText");	############### delete then when OnOpen() is fixed. 

	# open pop-up dialog to select the source database object
	err = DlgGetObject("Choose standalone database object:", "Database", dbItem, "ExistingOnly");

	if (err < 0) 	# if not database object selected
		{
		statusText.SetValueStr("No source database selected; please select one.");
		}
	else 		# database object selected
		{
		dbFilepath = dbItem.GetFilePath();			# get filepath from database's RVC_OBJITEM
		srcfilename$ = dbFilepath;

		dbDescript = dbItem.GetDescriptor();		# get RVC descriptor of the database object
		srcobjname$ = dbDescript.GetFullName();	# get name of source database object (works with long names) 

		# get handle for text control used to show database object name and write name to it 
		dbText = gpsdialog.GetCtrlByID("dbText");
		dbText.SetValueStr(sprintf("%s\%s\n", srcfilename$, srcobjname$) );

		dbSource = OpenDatabase(srcfilename$, srcobjname$);	# open the source database
		dbOpen = 1;															# set flag to indicate database has been opened

		# get handle for next pushbutton control and enable it
		tblBtn = gpsdialog.GetCtrlByID("tblBtn");
		tblBtn.SetEnabled(1);

		# update status prompt
		statusText.SetValueStr("Select source table.");
		}


	} # end proc GetSourceDatabase


##################################################
# Procedure to get source table from open database
# Called by Source Table button on dialog
proc GetSourceTable () {

	local numeric err;

	# open popup dialog to select the source table from the database
	err = PopupSelectTable(dbSource, srctablename$);

	if (err < 1)	# no table was selected
		{
		statusText.SetValueStr("No table selected; please select a source table.");
		}
	else	# table was selected
		{
		# get handle for text control used to show source table name and write name to it
		tblText = gpsdialog.GetCtrlByID("tblText");
		tblText.SetValueStr(srctablename$);

		tblSource = DatabaseGetTableInfo(dbSource, srctablename$);	# get DBTABLEINFO from source table

		# get handle for the View Table pushbutton control and enable it 
		openSrcBtn = gpsdialog.GetCtrlByID("openSrcTblView");
		openSrcBtn.SetEnabled(1);

		# get handle for next pushbutton control and enable it
		dateBtn = gpsdialog.GetCtrlByID("dateBtn");
		dateBtn.SetEnabled(1);

		# update status prompt
		statusText.SetValueStr("Select field containing date string.");
		}

	} # end func GetSourceTable

#######################################################
# Procedure to open a tabular view of the source table
# Called by Open View button on dialog
proc OpenSourceTblView () {
	dbSourceView = DBEditorCreate(dbSource);
	srcTblOpen = 1;
	DBEditorOpenTabularView(dbSourceView, srctablename$);
	}

####################################################
# Procedure to get Date field from source table
# Called by Date Field button on dialog
proc GetDateField () {
	numeric err;
	local class DBFIELDINFO dateFld;

	# open popup dialog to select field in source table
	err = PopupSelectTableField(dbSource, srctablename$, srcdatefld$);

	if (err < 1) then	# no field was selected
		statusText.SetValueStr("No date field selected.");

	else		# field was selected
		{
		dateFld = FieldGetInfoByName(tblSource, srcdatefld$); # get info for selected field to check type

		if (dateFld.Type <> "string") then		# if not a string field
			statusText.SetValueStr("Selected field must be type string.");

		else		# selected field is a string field
			{
			# get handle for text control used to show field name and write name to it
			dateText = gpsdialog.GetCtrlByID("dateText");
			dateText.SetValueStr(srcdatefld$);

			# get handle for next pushbutton control and enable it
			timeBtn = gpsdialog.GetCtrlByID("timeBtn");
			timeBtn.SetEnabled(1);

			# update status prompt
			statusText.SetValueStr("Select field containing time string.");
			}
		}
	}	# end proc GetDateField


####################################################
# Procedure to get Time field from source table
# Called by Time Field button on dialog
proc GetTimeField () {
	numeric err;
	local class DBFIELDINFO timeFld;

	# open popup dialog to select field in source table
	err = PopupSelectTableField(dbSource, srctablename$, srctimefld$);

	if (err < 1) then		# no field was selected
		statusText.SetValueStr("No time field selected.");

	else			# field was selected
		timeFld = FieldGetInfoByName(tblSource, srctimefld$);	# get info for selected field to check type

		if (timeFld.Type <> "string")		# if not a string field
			statusText.SetValueStr("Selected field must be type string.");

		else		# selected field is a string field
			{
			# get handle for text control used to show field name and write name to it
			timeText = gpsdialog.GetCtrlByID("timeText");
			timeText.SetValueStr(srctimefld$);

			# get handle for next pushbutton control and enable it
			logBtn =gpsdialog.GetCtrlByID("logBtn");
			logBtn.SetEnabled(1);

			# update status prompt
			statusText.SetValueStr("Select GPS log file.");
			}
	} # end proc GetTimeField


#######################################################
# Procedure to get GPS log
# Called by GPS Log button on dialog
proc GetGPSlog () {

	# open popup dialog to choose GPS log file
	gpsfilename$ = GetInputFileName("", "Select GPS log file in NMEA or MicroImages format:", "");

	if (gpsfilename$ == "") then		# no log file selected
		statusText.SetValueStr("No GPS log file selected.");

	else		# a log file was selected
		{
		if (gpsdbase.ReadLog(gpsfilename$) < 1) then 	# log file not read 
			statusText.SetValueStr("Log file must be text in NMEA or MicroImages format.");

		else	# log file is successfully read
			{
			# get handle for text control used to show log file name and write name to it
			logText = gpsdialog.GetCtrlByID("logText");
			logText.SetValueStr(gpsfilename$);

			# get handles for label and text control for new table name and enable them
			tblLabel = gpsdialog.GetCtrlByID("tblLabel");
			tblLabel.SetEnabled(1);

			newTblText = gpsdialog.GetCtrlByID("newTblText");
			newTblText.SetEnabled(1);

			# update status prompt
			statusText.SetValueStr("Enter name for output table and press [Tab] or [Enter].");
			}
		}
	} # end proc GetGPSlog


#######################################################
# Procedure to set name for new output table.
# Called when the New Table Name editstring control is activated by Return/Enter key.
proc SetOutputTableName () {

	# get handles for controls to be enabled if non-empty string was entered
	runBtn = gpsdialog.GetCtrlByID("runBtn");
	testBtn = gpsdialog.GetCtrlByID("testBtn");

	# get handle for the text control and get the string from it to be checked and used
	newTblText = gpsdialog.GetCtrlByID("newTblText");
	desttablename$ = newTblText.GetValueStr();

	if (desttablename$ <> "")	# table name string not empty
		{
		runBtn.SetEnabled(1);		# enable Run button
		testBtn.SetEnabled(1);		# enable Test button
		# update status prompt
		statusText.SetValueStr("Set options and press [Test] or [Run].");
		}
	else	# string is empty
		statusText.SetValueStr("Please enter name for output table.");

	} # end proc SetOutputTableName


#######################################################
# Procedure to get processing options from the dialog.
# Called by Run() and Test() procedures
proc GetOptions (var numeric gpsTimeOffset, var numeric maxTimeDiff, var string method$) {

	# get gps offset time from dialog
	local numeric adjHour = gpsdialog.GetCtrlByID("adjHour").GetValueNum();
	local numeric adjMin = gpsdialog.GetCtrlByID("adjMin").GetValueNum();
	local numeric adjSec = gpsdialog.GetCtrlByID("adjSec").GetValueNum();

	if (adjHour < 0)	# check if hour is negative value and change sign for min and sec
		{
		adjMin = adjMin * -1;
		adjSec = adjSec * -1;
		}

	# convert gps offset time in H:M:S to seconds for GPSDBASE class
	local numeric gpsTimeOffset = adjHour * 3600 + adjMin * 60 + adjSec;

	# get maximum time difference; used as parameter to GPSDBASE.Compute()
	local numeric maxTimeDiff = gpsdialog.GetCtrlByID("maxDiff").GetValueNum();

	# get method (Interpolate or Closest); used as parameter to GPSDBASE.Compute()
	local string method$ = gpsdialog.GetCtrlByID("method").GetValueStr();
	} # end proc GetOptions


########################################################
# Procedure to check how many records get geotagged with current settings.
# Called by Test button on dialog.
proc Test () {

	# get GPS log processing options from dialog using user-defined function
	local numeric gpsTimeOffset, maxTimeDiff;
	local string method$;
	GetOptions(gpsTimeOffset, maxTimeDiff, method$);
	gpsdbase.SetOffset(gpsTimeOffset);		# set GPS time offset

	### loop through records in table to compute and add coordinates
	local numeric j;					# loop counter
	local numeric numTagged = 0;	# count of number of records succesfully geotagged

	local class GPSDATA gpsdata;		# current GPS location to be passed to GBSDBASE
	local class DATETIME datetime;	# date time info to be passed to GPSDBASE
	local class POINT3D position;		# point location returned by GPSDATA

	for j = 1 to tblSource.NumRecords 
		{
		# set date and time in DATETIME class to be passed to GPSDBASE
		# by calling user-defined functions that get them from the source database
		# and return Julian date and time in seconds since midnight 
		datetime.SetDateJulian( getDate(j, tblSource, srcdatefld$) );
		datetime.SetTimeSecondsSinceMidnight( getTime(j, tblSource, srctimefld$) );

		# compute coordinates; method returns 0 if time match found and -1 if not
		if (gpsdbase.Compute(datetime, gpsdata, method$, maxTimeDiff) == 0) # time match found
			{
			position = gpsdata.position;	# get position from GPSDATA as 3D point

			++ numTagged;		# increment success counter
#			printf("latitude= %.6f, longitude = %.6f, elevation = %.2f \n", position.y, position.x, position.z);
			}
		} # end for

	# update status prompt
	statusText.SetValueStr( sprintf("Current options would geotag %d of %d records.", numTagged, tblSource.NumRecords) );

	} # end proc Test

#######################################################
# Procedure called when Run button is pressed.
# Copies source table to destination, adds position fields,
# and loops through records to compute position and write
# values to the new fields. 
proc Run () {
 
	# get GPS log processing options from dialog using user-defined function
	local numeric gpsTimeOffset, maxTimeDiff;
	local string method$;
	GetOptions(gpsTimeOffset, maxTimeDiff, method$);
	gpsdbase.SetOffset(gpsTimeOffset);		# set GPS time offset

	### copy source table and rename with name previously chosen by user
	TableCopy(dbSource, tblSource, dbSource);		# copy table; name gets incremented by adding "1"
	local string ctablename$ = srctablename$ + "1";		# make string for name of copied table 
	tblDest = DatabaseGetTableInfo(dbSource, ctablename$);	# get table info for copied table
	tblDest.Name = desttablename$;		# rename table

	### add fields for Latitude, Longitude, and Elevation to the copied table
	local class DBFIELDINFO elevInfo;
	TableAddFieldFloat(tblDest, "Latitude", 10, 6);
	TableAddFieldFloat(tblDest, "Longitude", 10, 6);
	elevInfo = TableAddFieldFloat(tblDest, "Elevation", 10, 6);
	elevInfo.UnitType = "length";
	elevInfo.Units = "meters";

	### loop through records in destination table to compute and add coordinates
	local numeric j;					# loop counter
	local numeric numTagged = 0;	# count of number of records succesfully geotagged

	local class GPSDATA gpsdata;		# current GPS location to be passed to GPSDBASE
	local class DATETIME datetime;	# date time info to be passed to GPSDBASE
	local class POINT3D position;		# point location returned by GPSDATA

	for j = 1 to tblDest.NumRecords 
		{
		# set date and time in DATETIME class to be passed to GPSDBASE
		# by calling user-defined functions that get them from the destination database
		# and return Julian date and time in seconds since midnight 
		datetime.SetDateJulian( getDate(j, tblDest, srcdatefld$) );
		datetime.SetTimeSecondsSinceMidnight( getTime(j, tblDest, srctimefld$) );

		# compute coordinates; method returns 0 if time match found and -1 if not
		if (gpsdbase.Compute(datetime, gpsdata, method$, maxTimeDiff) == 0) # if time match found
			{
			position = gpsdata.position;		# get position for record from GPSDATA as 3D point
			TableWriteField(tblDest, j, "Latitude", position.y);		# write coordinates to table
			TableWriteField(tblDest, j, "Longitude", position.x);
			TableWriteField(tblDest, j, "Elevation", position.z);
			++ numTagged;			# increment success counter

#			printf("latitude= %.6f, longitude = %.6f, elevation = %.2f \n", position.y, position.x, position.z);
			}
		} # end for

	# update status prompt
	statusText.SetValueStr( sprintf("Successfully geotagged %d of %d records.", numTagged, tblDest.NumRecords) );

	dbDestView = DBEditorCreate(dbSource);
	DBEditorOpenTabularView(dbDestView, desttablename$);	# open tabular view of result table
	destTblOpen = 1;

	} # end proc Run


################################################################
################### Main Program ###############################
################################################################
clear();

xml$='<?xml version="1.0"?>
<root>
	<dialog id="gps" Title="Geotag Database Records" VertResize="Relative" Buttons="" OnOpen="OnDlgOpen()">
		<groupbox Name=" Select Input " ExtraBorder="4" VertResize="Fixed">
			<pane Orientation="horizontal" HorizResize="Fixed">
				<pushbutton Name="Source Database..." OnPressed="GetSourceDatabase()"/>
				<edittext id="dbText" HorizResize="Fixed" Width="48" ReadOnly="true"/>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<pushbutton id="tblBtn" Name="Source Table..." Enabled="false" OnPressed="GetSourceTable()"/>
				<edittext id="tblText" HorizResize="Fixed" Width="30" ReadOnly="true"/>
				<pushbutton id="openSrcTblView" Name="Open View" Enabled="false" OnPressed="OpenSourceTblView()"/>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<pushbutton id="dateBtn" Name="Date Field..." Enabled="false" OnPressed="GetDateField()"/>
				<label>(MM/DD/YYYY)</label>
				<edittext id="dateText" HorizResize="Fixed" Width="30" ReadOnly="true"/>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<pushbutton id="timeBtn" Name="Time Field..." Enabled="false" OnPressed="GetTimeField()"/>
				<label>(Hour:Min:Sec)</label>
				<edittext id="timeText" HorizResize="Fixed" Width="30" ReadOnly="true"/>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<pushbutton id="logBtn" Name="GPS Log..." Enabled="false" OnPressed="GetGPSlog()"/>
				<edittext id="logText" HorizResize="Fixed" Width="50" ReadOnly="true"/>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<label id="tblLabel" Enabled="false">New Table Name</label>
				<edittext id="newTblText" HorizResize="Fixed" Width="30" Enabled="false" ReadOnly="false" OnChanged="SetOutputTableName()"/>
			</pane>
		</groupbox>
		<groupbox Name=" Options " ExtraBorder="4" VertResize="Fixed">
			<pane Orientation="horizontal" HorizResize="Fixed">
				<label>Adjust log time to match table time  +/-UTC  </label>
				<editnumber id="adjHour" Width="3" Default="0" Precision="0" MinVal="-24" MaxVal="24"/>
				<label>:</label>
				<editnumber id="adjMin" Width="2" Default="0" Precision="0" MinVal="0" MaxVal="59"/>
				<label>:</label>
				<editnumber id="adjSec" Width="2" Default="0" Precision="0" MinVal="0" MaxVal="59"/>
				<label>H:M:S</label>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<label>Set maximum difference in time for computing coordinates: </label>
				<editnumber id="maxDiff" Width="3" Precision="0" Default="60" MinVal="0" MaxVal="120"/>
				<label> seconds</label>
			</pane>
			<pane Orientation="horizontal" HorizResize="Fixed">
				<radiogroup id="method">
					<item Value="Interpolate" Name="Interpolate Coordinates"/>
					<item Value="Closest" Name="Use Closest Coordinates"/>
				</radiogroup>
			</pane>
		</groupbox>
		<pane Orientation="horizontal" HorizAlign="Right">
			<edittext id="statusText" ReadOnly="true" Width="45"/>
			<pushbutton id="runBtn" Enabled="false" Name=" Run " HorizResize="Fixed" OnPressed="Run()"/>
			<pushbutton id="testBtn" Enabled="false" Name= " Test " HorizResize="Fixed" OnPressed="Test()"/>
			<pushbutton id="exitBtn" Name=" Exit " HorizResize="Fixed" OnPressed="ExitScript()"/>
		</pane>
	</dialog>
</root>';

### parse XML text for the dialog into memory; 
### return an error code (number < 0 ) if there are syntax errorsi
err = dlgdoc.Parse(xml$);

if ( err < 0 ) {
	PopupError( err ); 	# Popup an error dialog. "Details" button shows syntax errors.
	Exit();
}

# get the dialog element from the parsed XML document and
# show error message if the dialog element can't be found
gpsdlgnode = dlgdoc.GetElementByID("gps");

if ( gpsdlgnode == 0 ) {
	PopupMessage("Could not find dialog node in XML document");
	Exit();
}

# Set the XML dialog element as the source for the GUI_DLG class instance
# we are using for the dialog window.
gpsdialog.SetXMLNode(gpsdlgnode);
err = gpsdialog.DoModal();

if ( err == -1 ) {	# exit script if Cancel button on dialog is pressed
	Exit();
	}