On Saturday, May 21, 2016 06:51:46 AM Alec Ten Harmsel wrote: > Joost knows far more about databases than I do, so I mostly commented on > the workflow part. > > On 2016-05-20 22:36, [email protected] wrote:
<snipped> > I have never run postgresql on gentoo (hopefully soon :D), but on > Debian-derived distros and RPM-based distros, PGDATA is always somewhere > in /var. /etc seems wrong. There are symlinks from the /var location to /etc for the configuration files. The data itself, eg. PGDATA, sits, by default, in /var/..... <snipped> > `equery use gnumeric' gives the `libgda' flag, which should pull in > database support. I've never used it, so I don't know whether or not it > works/how well it works. What is in this spreadsheet? If it is financial > stuff, you can use Gnucash, which supports using a database as a backend. Does this finally work? Last time I tried this, half the functionality didn't work at all and the other half was buggy. (This was years ago) > > My main problem is that columns of several thousand rows are functions > > > > based on other columns of several thousand rows. For the time-being, > > I've split up the spreadsheet into a few pieces, but a database is the > > best solution. If I could run the calculations in the database, and > > pull in the final results as static numbers for graphing, that would > > greatly reduce the strain on the spreadsheet. Or is it possible to > > graph directly from postgresql? > > Here are my recommendations, in order of "least code" to "most code" (I > don't think postgresql supports graphing): > > 1. Write some sql scripts that compute the data you need and output CSV, > then import to Gnumeric and do the plots. For script examples: http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file > 2. Write python script(s) that run SQL commands and plot the data with > matplotlib. > 3. Write a webapp so you don't have to run scripts by hand - the plots > are generated by opening a web page. 4. Write it all in C++ :) > Depending on how much automation you want vs. how much time you want to > spend writing/debugging code, hopefully one of those helps. I help > researchers use a HPC cluster; some are very savvy programmers, some are > not. For working on "big data" projects, some will throw raw data into a > Hadoop cluster and happily do all their work using Hadoop, while some > will put in raw data, clean it up, and then pull it out and use MATLAB, > stata, R, etc., so you just need to find the workflow that works best > for you. I personally would choose option 3, as it involves the least > amount of running scripts over and over, but to each his own. > > I have actual free time now (done with school, finally), so I might be > able to help prototype if you would like as well. Something I could use (and others): A simple PHP page which I can feed: - connection parameters to a database - select-query - which result-field to use for the horizontal axis and then plots the remaining fields for the vertical axis. I haven't checked with google yet, so if there is a decent example, I'd be interested :) -- Joost
