9/6/2023 0 Comments Sqlite regex case insensitive![]() ![]() Select count(*) from logs where lisowner and g_foundlog(ltype) Select count(*) from logs where lisowner and (ltype = 'Found it' or ltype='Attended' or ltype='Webcam Photo Taken') 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: G_FoundLog(sLogType) : Boolean - Use only with the "logs" table. G_Equal(sData1,sData2) : Boolean - Test two strings for equality, ignoring case. IRound - Number of digits to round (optional, defaults to 1) 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. The elevation data stored in the database is *always* in meters. G_Elevation(rEle,) : Real - This functions have been added to simplify usage of elevation. This will enable Sqlite queries to display date fields formatted to the User's locale settings. G_DateFormat(sDate) : string This function is virtually the same as the macro function DateFormat() but for use in Sql. This will open your browser with a report similar to: $data = sqltohtml($data,"Log dates different to Groundspeak","Y" $data = sqlite("sql","select lparent,ltype ,lby ,ldate,ltime,g_gcdate(ldate,ltime) as GcDate from logs where ldate gcdate","Headings=Y") 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.Īn example of macro code to list all dates that differ: However, after a fair bit analysing different logs in different years I have included a "kludge" in the algorithm that appears to work for all dates. 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. This function will interrogate the given log date and time and then calculate the corresponding Groundspeak Date. However, the Date that is provided in the GPX file is GMT. The date that the Groundspeak site shows for logs is based on PST (Pacific Standard Time). ![]() G_GcDate(sLogDate,sLogTime) : string (YYYY-MM-DD) $TotalDistance = SqLite("sql","SELECT sum(g_Distance(t1.latitude,t1.longitude,t2.latitude,t2.longitude,'M')) FROM caches AS t1, caches AS t2 WHERE t1.rowid = t2.rowid - 1") 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: G_Distance(sLat1,sLon1,sLat2,sLon2,sUnits) : Real - calculate the distance between two points. For more information see SQLite Collation G_Contains(sFind,sData) : Boolean - MS Ansi Case insensitive search. However, you can use this function to simulate the same sorting sequence for the waypoint code if required. G_CodeSort(sData) : String - Used internally by GSAK to sort the code colum when the " special sort " option is selected. G_Chr(nChr) : String - Same as the macro Chr() function This function would normally only be used on the "CacheType" field in the "Caches/CachesAll" table. The return value is the expanded cache type description. G_CacheType(sType) : String - Where sType1 is the 1 character cachetype abbreviation (which is how GSAK stores the cache type in the database). G_Bearing2(sLat1,sLon1,sLat2,sLon2) : Number - Calculates the bearing in degrees between two points G_Bearing1(sLat1,sLon1,sLat2,sLon2) : String - Calculates the cardinal bearing (N, NE, E, SE, etc) between two points G_AttributeName(nId) : String - Returns the full attribute name of the corresponding attribute ID ![]() To prevent confusion with native/shipped SQLite functions, all GSAK functions begin with "g_" GSAK has harnessed this power to add functions that are specific to GSAK. SQLite also provides the infrastructure to build custom functions that can be used in native SQL queries. 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() Core functions, Aggregate functions, and DateTime functions. SQLite ships with its own inbuilt functions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |