[600MRG] WSPRnet database

[John Langridge]

>What is preventing this simplistic solution is of course the huge size 
of the monthly files, now at least 6 million spots each.

AND



>It would be nice to see the WSPRnet download page provide monthly files 
by band. IMHO this would be the best overall solution but would require 
somebody to influence and motivate the WSPRnet support team to implement 
this change. I don't know how to do that or even if it is possible.

I agree - too big... this would be the best way to handle it if they could be convinced.  I never asked if the files could be further filtered before publication.  Its an easy programming change in the WHERE criteria that they would be using to build the outfiles.  



>If someone else were to provide SQL access and the database, the 
database manager would still need to be programmed to produce the 
required reports which would be very individual according to interest.

yeah but thats not hard.   It just takes the time to come up with a handful of SQL statements for whatever criteria is wanted.  Porting that to the web is the hard part for me.   I've been out of software development for 10 years now so I am way way behind on what the kids are using these days.
I will shoot an email to the wsprnet guy and see what he says unless you have already done it.

73!

john





From: Larry <larry at w7iuv.com>

To: John Langridge <jlangridge at sbcglobal.net>; 600 MRG <600MRG at w7ekb.com> 
Sent: Wednesday, October 29, 2014 3:41 PM
Subject: Re: [600MRG] WSPRnet database
 

John,

Your suggestion of a SQL solution is probably the best solution but I 
have problems with it.

By virtue of my web page host, I have access to MYSQL and PHPSQL. 
Unfortunately my hosting package is minimal and I could not support 
public access.

As best I can tell, I would need to download each months file and then 
upload it to the web space database. My back country internet connection 
can't easily handle even that.

In addition, my time for database management programming ( or any 
programming/coding at all!) is long past even if I had the time and 
energy to do it.

As a matter of fact, I have enough computing horsepower at home to do 
the job, I just don't have the brain horsepower to get it done!

If someone else were to provide SQL access and the database, the 
database manager would still need to be programmed to produce the 
required reports which would be very individual according to interest.

My local OpenOffice spreadsheet can easily handle one million lines of 
data. (It seems to limits out at 1.04 mil) It only takes a minute or so 
to sort a million lines on my desktop. The spreadsheet is clumsy for 
this type of analysis but still could produce useable reports. I suspect 
there are a whole lot more individuals who can handle a spreadsheet than 
can deal with SQL.

What is preventing this simplistic solution is of course the huge size 
of the monthly files, now at least 6 million spots each.

It would be nice to see the WSPRnet download page provide monthly files 
by band. IMHO this would be the best overall solution but would require 
somebody to influence and motivate the WSPRnet support team to implement 
this change. I don't know how to do that or even if it is possible.

All of the above is why I originally suggested a script that could parse 
the data into smaller files.

Larry - W7IUV / WH2XGP



On 29-Oct-14 10:31 AM, John Langridge wrote:
> Larry,
>
> The CSV record filesize is a nightmare.  It might not be so bad if the breakdown were by band, by day, etc.  I had similar aspirations in the past but found that the processing load on the machines I have here was such that the machines would lock up when trying to convert the CSV to relational database table(s).  In short, its a tough go to use MS Access to write SQL to grab desired data and build specific tables with that many records and consumer grade PC junk.
>
>
> Someone with some horsepower and an implementation of SQL Server would be the way to go.  You could easily come up with stored procedures and the queries to get just about anything you wanted and it would run pretty quickly particularly with good indexing.   The key is getting the CSV data into a relational database to play with and both access and SQL server have facilities to accomplish that exact task.
>
> Perhaps someone here with some drive space and machine horse power could load a database with the parsed csv data on a regular basis.  There are some good web query tools out there that would allow multiple user to access the data and write queries via a web portal so that many of us can look at the data in a variety of ways.  I'm not aware of what freeware might be available.  Perhaps there is an IT manager on here with access to the hard ware and software to get this done.  It would just have to be someone's pet project to do it...
>
> 73 and maybe someone with interest in DP will jump on this as a project.  I think a lot of us would like to look at numbers outside of how they are presented on wsprnet.org.
>
>
> John XIQ
>
>
>
>
>
> From: Larry <larry at w7iuv.com>
>
> To: 600 MRG <600MRG at w7ekb.com>
> Sent: Wednesday, October 29, 2014 12:07 PM
> Subject: [600MRG] WSPRnet database
>
>
> The 630 propagation over the last couple months has interested me quite
> a bit. I decided to do some analysis using the spot database that can be
> downloaded from WSPRnet.
>
> Unfortunately the monthly CSV data files are now up to over 6 million
> spots each. None of the database tools I have available and know how to
> use will handle that size file.
>
> At one time I think I remember being smart enough to write a script that
> would parse the data and create a useable file but that was decades ago
> and the old brain just ain't what it used to be.
>
> Has anyone out there solved this problem? Any suggestions? Best solution
> would be an existing tool that would do the job.
>
> Searching the WSPRnet forums and Google has not helped.
>
> Larry - W7IUV / WH2XGP
>
> _______________________________________________
> 600MRG mailing list
> 600MRG at w7ekb.com
> http://w7ekb.com/mailman/listinfo/600mrg_w7ekb.com
> _______________________________________________
> 600MRG mailing list
> 600MRG at w7ekb.com
> http://w7ekb.com/mailman/listinfo/600mrg_w7ekb.com
>