|GSAK (Geocaching Swiss Army Knife)|
SQLite ships with its own inbuilt functions. They are grouped into 3 types. Core functions, Aggregate functions, and DateTime functions.
In addition, the implementation of Sqlite that GSAK is using ships with the standard math functions of ACOS(), ASIN(), ATAN(), ATAN2(), CEIL(), CEILING(), COS(), COT(), DEGREES(), EXP(), FLOOR(), LN(), LOG(), LOG2(), LOG10(), MOD(), PI(), POW(), RADIANS(), SIGN(), SIN(), SQRT(), TAN(), TRUNCATE()
SQLite also provides the infrastructure to build custom functions that can be used in native SQL queries. GSAK has harnessed this power to add functions that are specific to GSAK. To prevent confusion with native/shipped SQLite functions, all GSAK functions begin with "g_"
Current GSAK functions include:
g_AttributeName(nId) : String - Returns the full attribute name of the corresponding attribute ID
g_Bearing1(sLat1,sLon1,sLat2,sLon2) : String - Calculates the cardinal bearing (N, NE, E, SE, etc) between two points
g_Bearing2(sLat1,sLon1,sLat2,sLon2) : Number - Calculates the bearing in degrees between two points
g_CacheType(sType) : String - Where sType1 is the 1 character cachetype abbreviation (which is how GSAK stores the cache type in the database). The return value is the expanded cache type description. This function would normally only be used on the "CacheType" field in the "Caches/CachesAll" table.
g_Chr(nChr) : String - Same as the macro Chr() function
g_CodeSort(sData) : String - Used internally by GSAK to sort the code column when the "special sort" option is selected. However, you can use this function to simulate the same sorting sequence for the waypoint code if required.
g_Contains(sFind,sData) : Boolean - MS Ansi Case insensitive search. For more information see SQLite Collation
g_Distance(sLat1,sLon1,sLat2,sLon2,sUnits) : Real - calculate the distance between two points. sUnits can be K or M (K=Kilometers, M=Miles - see also the system variable $_Units) Assuming we have created a "caches" table with latitude and longitude fields and the records are sorted in the correct order, we can calculate the total distance in miles between all these waypoints with the following code:
Note: By default the calculation uses the more common Haversine forumlae (otherwise known as "Greater circle). However, the more accurate (but time consuming) high accuracy Vincenty formula is supported by appending a "H" to the units parameter. So if we wanted to use the Vincenty formulate in th example above the code would be:
g_GcDate(sLogDate,sLogTime) : string (YYYY-MM-DD)
The date that the Groundspeak site shows for logs is based on PST (Pacific Standard Time). However, the Date that is provided in the GPX file is GMT. This function will interrogate the given log date and time and then calculate the corresponding Groundspeak Date.
Unfortunately due to the "inconsistencies" of the Groundspeak date/times provided in the GPX file it is difficult to guarantee this date will precisely match Groundspeak. However, after a fair bit analyzing different logs in different years I have included a "kludge" in the algorithm that appears to work for all dates.
I would suggest that all macro authors use this function when they need a log date that corresponds to the same date that shows on the Groundspeak site.
An example of macro code to list all dates that differ:
This will open your browser with a report similar to:
g_DateFormat(sDate) : string This function is virtually the same as the macro function DateFormat() but for use in Sql. This will enable Sqlite queries to display date fields formatted to the User's locale settings.
g_Elevation(rEle,[iRound]) : Real - This functions have been added to simplify usage of elevation. The elevation data stored in the database is *always* in meters. However, this function will take into account the user's settings and return the value as per the users miles/kms setting doing the conversion as required.This saves you having to worry about testing the $_Units system variable and doing the corresponding conversion.
rEle - Elevation from database
iRound - Number of digits to round (optional, defaults to 1)
g_Equal(sData1,sData2) : Boolean - Test two strings for equality, ignoring case.
g_FoundLog(sLogType) : Boolean - Use only with the "logs" table. The sole purpose is to return a Boolean to indicate the current log type is a "found" log or not.For example, instead of having to "hard wire" in the required log types to count all the find logs for a user as in:
We can now simply code as:
Using this GSAK function in Sqlite macro code has several advantages:
a) Avoids typos and hard wiring the required log types
b) We don't have to remember what those log types are any more
c) Future proof macros, so will automatically work if Groundspeak decided to add a new "Found log" type. This of course would required a GSAK update, but at least we won't have to go back and change all macros that use this function.
g_IsValidDate(sDate) : Boolean - Tests if sDate is a valid SQLite date in the format YYYY-MM-DD
g_Left(sData,nLength) : String - Same as the macro left() function
g_LatLonFormat(sLatLon,sId) : String - Format the latitude or longitude in Minutes or seconds format. sLatLon must be one of Latitude or Longitude in decimal degree format (which is how the GSAK database stores lat and lon). sId identifies the transformation - latm = latitude minutes, lats = latitude seconds, lonm = longitude minutes, lons = longitude seconds. for example:
g_NameSort(sData) : String - Used internally by GSAK to sort the waypoint name when the option "Special Sort" is selected. However, you can use this function to simulate the same sorting sequence for the waypoint name if required.
g_Project(sLatitude,sLongitude,nDegrees,nDistance,sUnits,sCalcType) : String - Project a waypoint
sLatitude - Latitude, must always be decimal degree format
sLongitude - Longitude, must always be decimal degree format
nDegrees - Degrees for projection
nDistance - Distance for projection (as per sUnits)
sUnits - Distance type, K = Kilometers, N = Nautical miles, M = miles, T = Meters, Y = Yards, F = Feet
sCalcType - G = Greater circle, V = Vincenty (High accuracy)
The return result is the projected coordinate, which is in decimal degree format and the latitude is separated from the longitude by a ; (semicolon).
g_Regex(sRegex,sData) : Boolean - Matching using regular expressions.Same definition and syntax as used in the native macro function RegEx()
g_RegexCount(sRegex,sData) : Integer - Count the number of times a regular expression is matched. Same definition and syntax as used in the native macro function RegExCount()
g_RegExData(sRegex, sData, nNumber) : String - Use this function to return the matched data instance of sRegex found in sData. Same definition and syntax as used in the native macro function RegExData()
g_RegexReplace(sRegex,sData,sReplaceWith,sBackRefSymbol) : String - String replace using Regex. Same definition and syntax as used in the native macro function RegExReplace()
g_Right(sData,nLength) : String - Same as the macro Right() function
g_SortBearing(sBearing) : String - Returns an "intuitive" sort sequence for Cardinal bearings (N, NE, E, SE, S, etc). That is, the bearing will be sorted in the same order as per a compass.
g_SortLatorLon(sLatLon) : String - Returns an "intuitive" sort sequence for Latitude and longitude, instead of purely numerical.
g_Upper(sData) : String - Return the MS ANSI uppercase string of sData. For more information see SQLite Collation
Copyright 2004-2015 CWE Computer Services |