Has anyone used the data in Sailwave to produce turnout statistics or demographic analysis?
If so how have you done it, please?
I have started by importing the JSON file, created by Sailwave, into a database (PostgreSQL) column. I am now struggling to get the data out, which I think should be possible from what I have read. I have been able to get some data out but not the data I need for turnout / demographic analysis.
I want to use JSON because it will allow the import of data from Sailwave files that will have different set-ups but still allow extraction of information for aggregation analysis.
I am using PostgreSQL v17 because it seems to better support including JSON data in a column. I have not looked at Microsoft SQL because I am trying this on my Linux system.
Think I could write a plugin which saved the data to a SQLite database fairly quickly. Certainly the competitor data would be easy. What other info do you need?
I too get asked at the end of each season to produce a list of the club’s most committed members (i.e. those who sailed the most days, not races). So I wondered if there had been any more development of this topic?
I have given up on the route I was trying to follow using PostgreSQL and importing JSON.
However, I am following a different route to get some Sailwave data into a database. I have been provided two different ways of getting competitor data into a database and I am trying to extend to include race data and results information.
A proof on concept from Jon Eskdale in Visual Studio directly accessing a Sailwave file and creating an SQLlite database
A proof of concept by Andy Barrow using Python to import a JSON file created by Sailwave into a MySQL or MariaDB database
As I understand the Python solution better than the Visual Studio I am progressing with the Python route. I can see with this route a way to import into a database in addition to the competitor data, the race & results data that would be required to produce the information you are suggesting you want. It would require race dates to be entered in the race information.
I do it by merging every race to create a huge series. For us thats about 96 races over the year. I then export it from Sailwave to mess about with it in excel. Its also how I calculate new personal handicaps for the following year by exporting all the BCR’s. Its very quick and easy to do.
Much the same as my artistic skills - faced with a blank NotePad++ page I’m lost, but given a working solution I can often adapt the supplied code to my requirements, as least with VBA / PowerShell. That said I have no Python knowledge. I also don’t have access to VS.
Ah, I already have a whole season series for our handicapping using Huw’s EHS template. What do you then do in Excel to see who turned out the most often, and do you use races or days sailed to find that competitor?
You have to decide if you want the number of days a competitor raced or the number of races they competed or both!
If the data is in a database it would, with appropriate SQL queries, be possible to produce all sorts of statistics! A proof of concept written by Jon just exported competitor data from Sailwave into an SQLite data base, which can be opened using applications like HeidiSQL and SQLite Expert Personal for creating SQL queries to produce tables of appropriate information.
SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.
I have imported competitor data from Sailwave via spreadsheet into a database (MariaDB) which allowed an SQL query to generate the following:
The export to clip board from Sailwave for pasting into a spreadsheet does not included any race data apart from the race name. If the race name includes the date that is good, however if the race date has been entered into the date box of race information then it is not copied to the clip board. NOTE - The race date needs to be in the date field of race information if data is being uploaded to RYA PY Online.
If Jon would consider enhancing his proof of concept, which currently only outputs competitor data, to include race information and race results then you could produce all sorts of turnout information with appropriate SQL queries.
A challenge would be deciding what competitor data is written out from a Sailwave file to an SQLite database as there are over 200 fields available in Sailwave, the list can be seen at Field names | Sailwave. It is not appropriate have so many columns in a database table. The race information data and race results information are much more limited and appropriate for being in a race information table and a race results table.
Think HUW pretty much covered it. I do number of races. Then you can do other stuff like performance to handicap, percentages raced etc. Whatever you want really. Its the main way I calculate personal handicaps becuase I knock back any BCR greater than 125% to stop catastrophe handing an advantage. Could do median instead, I prefer to average.
