GSAK - Basic macro help

[The-Eaves]

Hi there guys...

 

I am keen to get my head around this GSAK monstrosity - Thus far I have made some simple macros but now I want to work some numbers based on find counts and more specifically finds at certain cache ages...

 

To do this I will need to know how to pull the total find and DNF count for a particular cache. This must be possible somehow?

 

Also if I manage to take this higher grade I would like to be able to get hold of the date on which a find or DNF was made....

 

Thanks...

Edited August 29, 2011 by The-Eaves

[+Bear and Ragged]

They are very helpful on the GSAK forums: http://gsak.net/board/

(Have you checked, the macro may exist already?)

 

The only way to get all logs for a cache since the release of the Groundspeak API is by using GSAK new version 8, which is in Beta... Feel confident using a Beta release?

[+Danie Viljoen]

I think it will be easier (and faster) to do it with SQL queries. Use a program like SQLiteSpy or even the new built-in SQL manager (in GSAK 8).

[+DamhuisClan]

Yea, best place to ask about macros is on the GSAK board. If you need help with the SQL queries Danie or I can help.

[The-Eaves]

Yea, best place to ask about macros is on the GSAK board. If you need help with the SQL queries Danie or I can help.

 

Many many thanks to you both! I agree that an SQL query would be great way collect the data - The suggestion leaves me with some questions though:

 

I know that GSAK uses SQL for its own database (into which one loads GPX files generated by PQs)... But is it then possible to access GC.com data directly using SQL queries? My question is really this - where is the database I would be collecting the logs from using SQL and the application you describe?

 

(I have used some basic SQL in the past to capture info entered on a questionnaire and then recall data that meets certain criteria in one particular field... I.e. basic basic... but always excited to get a book and learn!)

[+DamhuisClan]

Well the first thing you need to do is get a list of all South African caches. If you like I can send you this list. It includes all archived caches since the beginning.

Then you will need to get all the logs for all these caches. With GSAK 7.<something> it has been very difficult. But with the beta release of GSAK 8, there is a menu which allows one to access the Groundspeak API, and one can get all the logs for all the caches.

 

This is what DRM Raider has done with the beautiful YouTube vids.

 

Once you have all the data in the SQLite database, then you can use any SQLite Query manager (there is one built into GSAK 8 as well) to access the SQLite database.

You could also write a macro to process this info.

 

I have now decided to take the plunge and start learning this macro language.

 

Basic SQL knowledge will help. All you really need to know are join statements, and if you get stuck I am more then willing to help with SQL.

[The-Eaves]

Well the first thing you need to do is get a list of all South African caches. If you like I can send you this list. It includes all archived caches since the beginning.

Then you will need to get all the logs for all these caches. With GSAK 7.<something> it has been very difficult. But with the beta release of GSAK 8, there is a menu which allows one to access the Groundspeak API, and one can get all the logs for all the caches.

 

This is what DRM Raider has done with the beautiful YouTube vids.

 

Once you have all the data in the SQLite database, then you can use any SQLite Query manager (there is one built into GSAK 8 as well) to access the SQLite database.

You could also write a macro to process this info.

 

I have now decided to take the plunge and start learning this macro language.

 

Basic SQL knowledge will help. All you really need to know are join statements, and if you get stuck I am more then willing to help with SQL.

 

Ok... Now I have GSAK beta (latest) and the whole country in my database... Downloaded SQLSpy... And now I'm lost...

 

How do I connect SQLSpy to the GSAK database? I'm hoping I can browse the GSAK databse to learn its structure before making some queries...

[+DamhuisClan]

In GSAK goto the Tools menu

Then select "Show Folders" and then "Database" then click "Show"

 

Then you must go in the directory name as per your Dabase name in GSAK.

 

In this directory you will find a "sqlite.db3" file.

 

That is the database you must use to open up in SQLSpy

 

If you need "realtime" help you can skype me on Anton.Damhuis

[The-Eaves]

In GSAK goto the Tools menu

Then select "Show Folders" and then "Database" then click "Show"

 

Then you must go in the directory name as per your Dabase name in GSAK.

 

In this directory you will find a "sqlite.db3" file.

 

That is the database you must use to open up in SQLSpy

 

If you need "realtime" help you can skype me on Anton.Damhuis

 

Beautiful! I see the database structure is quite straight forward!

 

@DamhuisClan: Using the SQLSpy it is quite easy to pull all your logs for example:

 

SELECT * from Logs WHERE lBy="DamhuisClan"

 

Now I know you mentioned in a previous post that you where going to take the plunge and learn the macro language - and given that I'm a newbie I figure it best to learn that from the outset...

 

So... To use the above query in GSAK macro I figure you would do this:

 

$SQL_Query = "SELECT * from Logs WHERE lBy="DamhuisClan"

$AllLogsByDamhuiClan = SQLlite ("sql", $SQL_Query, "Headings=Y")

 

Now this is where I'm stumped... Say I'd now like to examine each entry in $AllLogsByDamhuiClan using a loop of sorts... How would I do that?

 

Say for example I want to make a loop which goes through each database entry returned and check a field against some condition - e.g. contains the word "Zambesiboy".... (I'm sure there is an SQL function which can do this but for argument sake say I want to do this manually using a loop going through each line from top to bottom)

 

All I have figured out is outputting the collected lines on an html form using SqlToHtml....

 

Hopefully I am making some sense..

Edited September 1, 2011 by The-Eaves

[+DamhuisClan]

That is the part I still need to learn as well.

 

The best is to open up some other macros in NotePad or something and have a look how the others have done it.

Learn by example as such,.

 

The macro language is a little different then I am used to.... reminds me a little of PHP.

Currently I am looking at all the functions available.

It allows me to create a mind map of what I can and cant do.

 

The list of functions is here

 

It might be better to ask the next set of help on the GSAK forum, as I cant help much with the macro language itself at this stage.

[The-Eaves]

I have asked the question on GSAK forums... Will feedback an answer once there is one

[+iPajero]

When using the macro FindStatGen3.gsk version 4.1.15 I get an error message saying "you have an unrecognized South African State name in your database" being KwaZulu-Natal. All KZN caches are listed as the aforementioned.

 

Is there a macro I can run that will fix this or is there an alternative way to fix?

[+Jors]

When using the macro FindStatGen3.gsk version 4.1.15 I get an error message saying "you have an unrecognized South African State name in your database" being KwaZulu-Natal. All KZN caches are listed as the aforementioned.

 

Is there a macro I can run that will fix this or is there an alternative way to fix?

iPajero, I also noticed a bit of a hiccup with the stats and the KZN province.

 

Kwazulu Natal is the name as it appears on the web site, and also the name needed by the map of the provinces.

However, if the State column is blank in GSAK, the macro uses downloaded polygons to fill that column, and then you get KwaZulu-Natal in the State column, which seems to cause problems for some.

 

A simple solution would probably be to do a Global Replace, replacing KwaZulu-Natal with Kwazulu Natal in the State column of GSAK.

 

To be safe, make a backup first, then

 

Database, Global Replace...

Select State in the dropdown, and type "KwaZulu-Natal;Kwazulu Natal" (without the quotes)

In Replace options, select Swap, then click "Replace"

 

This should fix things, but if something goes wrong, you can just restore the backup.

 

If this doen't work, we should get a solution on the GSAK forums.

[+iPajero]

Thank you Jors, this has sorted out my problem. Have also done a few pocket queries and it does not revert back to the "old" Province name.

[+Jors]

Thank you Jors, this has sorted out my problem. Have also done a few pocket queries and it does not revert back to the "old" Province name.

Glad I could help.

 

It's been a while since the South African County map (Municipal Districts) was created, and there has been quite a few major changes in the Districts since then, so the map is in need of updating.

 

This problem you encountered is a direct cause of the County polygons, and will be fixed in the next round, but cannot say when.