Generating list of drawers
Hi, before I dig into SQL myself, does by chance somebody already made a script or SQL to generate a list showing: "who has drawn how much length of the surveys". Something along: Person A: 250m Person B: 199m Person C: 80m ... Thank you :)
Hi Benedikt, I wrote up this for you as an example. SELECT DISTINCT p.NAME, p.SURNAME, SUM(c.LENGTH) OVER(PARTITION BY p.NAME) as sum_len FROM PERSON p JOIN TOPO t ON p.ID = t.PERSON_ID JOIN CENTRELINE c ON c.ID = t.CENTRELINE_ID; I hope this get’s you started. Eoghan
On 20 Feb 2025, at 13:54, Benedikt Hallinger via Therion <therion@speleo.sk> wrote:
Hi, before I dig into SQL myself, does by chance somebody already made a script or SQL to generate a list showing: "who has drawn how much length of the surveys".
Something along: Person A: 250m Person B: 199m Person C: 80m ...
Thank you :) _______________________________________________ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
Well, that gives me "survey length per person" (isn't it?) But I need "drawed length", to see who contributet how much to the maps drawings. Am 2025-03-06 17:49, schrieb Eoghan:
Hi Benedikt,
I wrote up this for you as an example.
SELECT DISTINCT p.NAME, p.SURNAME, SUM(c.LENGTH) OVER(PARTITION BY p.NAME) as sum_len FROM PERSON p JOIN TOPO t ON p.ID = t.PERSON_ID JOIN CENTRELINE c ON c.ID = t.CENTRELINE_ID;
I hope this get’s you started.
Eoghan
On 20 Feb 2025, at 13:54, Benedikt Hallinger via Therion <therion@speleo.sk> wrote:
Hi, before I dig into SQL myself, does by chance somebody already made a script or SQL to generate a list showing: "who has drawn how much length of the surveys".
Something along: Person A: 250m Person B: 199m Person C: 80m ...
Thank you :) _______________________________________________ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
On 06/03/2025 17:00, Benedikt Hallinger via Therion wrote:
But I need "drawed length", to see who contributet how much to the maps drawings.
Looking again at p67 of the Therion Book, all the length and people are associated with the centreline, so I don't think the information you are after is in the database! Andrew
But I need "drawed length", to see who contributet how much to the maps drawings.
Is that how Therion works it out on the PDFs? I would have assumed it uses "how many scraps" with that person as -author, since it cannot really work out the "length". I could have a scrap containing only one station, which would mean 0 length. I could have a scrap containing only stations 1 and 100 (if the passage forms a loop). I could also have 5 scraps overlaying each other, all covering the same 2 stations. I don't think Therion uses any logic to count stations-per-scrap in order to boost the relative numbers. This is why there is no option for "statistics carto-length on" Instead, it just ranks people by number of scraps. I think. Assuming you mean length of cave covered by: scrap somethingSP -projection plan -author 2025.01.01 "Foo bar" (which it sounds like you do) rather than: team "Baz Qux" notes pictures Since both of those are someone doing drawings. I think you need to do a query for scraps, and count(*) AS scrapcount GROUP BY author (Sorry, I don't play with the SQL so I don't know the column titles, hoping this is enough to make sense for you.) This does mean that if you have one scrap covering 500 metres of cave, it means the same as one scrap covering 1 metre of cave. But I think that is what Therion does.
But I need "drawed length", to see who contributet how much to the maps drawings.
My understanding (from much forum watching rather than actual knowledge) is that when Therion exports map(s) made of scraps (ie not with any maps comprised of centrelines), then the length presented in the statistics is length of 'whole centrelines' associated with those scraps. Unfortunately (perhaps) the finest resolution of the length calculation is on a 'per centreline' basis (as opposed to 'per leg'). That means that the length reported will almost always overestimate the length that is directly 'under' the 'footprint' of scraps exported. So while yes, it is easiest to count the number of scraps authored by each person, it should also be possible to use a similar logic to report the length of whole centrelines beneath scraps drawn by each person. If there are many scrap authors per centreline, the statistic would be worthless due to double reporting of authors for each centreline, but if it is reasonable to assume that mostly a scrap author will draw all the scraps for a particular days survey, the double reporting will be minimised. For surveys that contain only one centreline, the blurring of resolution probably make this a forlorn effort. May as well count scraps per person instead - this is a way of encouraging and rewarding what I consider to be the good practice of drawing short scraps! Bruce
Thank you… seems like counting -author in the scrap files is the way to go then… maybe its an idea to count objects in a scrap, to get a very raw estimate of „workload done“.
Am 06.03.2025 um 20:11 schrieb Bruce Mutton <bruce@tomo.co.nz>:
But I need "drawed length", to see who contributet how much to the maps drawings.
My understanding (from much forum watching rather than actual knowledge) is that when Therion exports map(s) made of scraps (ie not with any maps comprised of centrelines), then the length presented in the statistics is length of 'whole centrelines' associated with those scraps. Unfortunately (perhaps) the finest resolution of the length calculation is on a 'per centreline' basis (as opposed to 'per leg'). That means that the length reported will almost always overestimate the length that is directly 'under' the 'footprint' of scraps exported. So while yes, it is easiest to count the number of scraps authored by each person, it should also be possible to use a similar logic to report the length of whole centrelines beneath scraps drawn by each person. If there are many scrap authors per centreline, the statistic would be worthless due to double reporting of authors for each centreline, but if it is reasonable to assume that mostly a scrap author will draw all the scraps for a particular days survey, the double reporting will be minimised. For surveys that contain only one centreline, the blurring of resolution probably make this a forlorn effort. May as well count scraps per person instead - this is a way of encouraging and rewarding what I consider to be the good practice of drawing short scraps! Bruce
_______________________________________________ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
So I finally wrote up something: https://gist.github.com/hbeni/33b807659d8d3a8ff692193712abace0 It counts scraps per author, and individual objects of the scraps. If a scrap is linked to more than one author, it counts toward all of them. All th2 files are recursively read and processed. Call with: $ therion-statistics-scrapauthors.sh # evaluate from current directory $ therion-statistics-scrapauthors.sh ../survey # evaluate from that directory Am 2025-03-06 21:00, schrieb Benedikt Hallinger via Therion:
Thank you… seems like counting -author in the scrap files is the way to go then… maybe its an idea to count objects in a scrap, to get a very raw estimate of „workload done“.
Am 06.03.2025 um 20:11 schrieb Bruce Mutton <bruce@tomo.co.nz>:
But I need "drawed length", to see who contributet how much to the maps drawings.
My understanding (from much forum watching rather than actual knowledge) is that when Therion exports map(s) made of scraps (ie not with any maps comprised of centrelines), then the length presented in the statistics is length of 'whole centrelines' associated with those scraps. Unfortunately (perhaps) the finest resolution of the length calculation is on a 'per centreline' basis (as opposed to 'per leg'). That means that the length reported will almost always overestimate the length that is directly 'under' the 'footprint' of scraps exported. So while yes, it is easiest to count the number of scraps authored by each person, it should also be possible to use a similar logic to report the length of whole centrelines beneath scraps drawn by each person. If there are many scrap authors per centreline, the statistic would be worthless due to double reporting of authors for each centreline, but if it is reasonable to assume that mostly a scrap author will draw all the scraps for a particular days survey, the double reporting will be minimised. For surveys that contain only one centreline, the blurring of resolution probably make this a forlorn effort. May as well count scraps per person instead - this is a way of encouraging and rewarding what I consider to be the good practice of drawing short scraps! Bruce
_______________________________________________ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
There are lots of searches, unfortunately in old php, so the websites are long gone at https://therion.speleo.sk/wiki/contrib:databasequery:install It looks like Searches/Who might have the search you need. But might be useful for other things, if the php does not get in the way too much Andrew On 06/03/2025 16:49, Eoghan via Therion wrote:
Hi Benedikt,
I wrote up this for you as an example.
SELECT DISTINCT p.NAME, p.SURNAME, SUM(c.LENGTH) OVER(PARTITION BY p.NAME) as sum_len FROM PERSON p JOIN TOPO t ON p.ID = t.PERSON_ID JOIN CENTRELINE c ON c.ID = t.CENTRELINE_ID;
I hope this get’s you started.
Eoghan
On 20 Feb 2025, at 13:54, Benedikt Hallinger via Therion <therion@speleo.sk> wrote:
Hi, before I dig into SQL myself, does by chance somebody already made a script or SQL to generate a list showing: "who has drawn how much length of the surveys".
Something along: Person A: 250m Person B: 199m Person C: 80m ...
Thank you :) _______________________________________________ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
_______________________________________________ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
participants (5)
-
Andrew Atkinson -
Benedikt Hallinger -
Bruce Mutton -
Eoghan -
Tarquin Wilton-Jones