search

fathomnet / community-feedback

1 stars 0 forks source link

Provide contribution stats to MBARI #106

Closed hohonuuli closed 9 months ago

hohonuuli commented 1 year ago

From Nancy:

Heidi has asked me for some stats for her summary. I can see how to get total # localizations + images here http://fathomnet.org:8080/swagger-ui#/, but can’t find a breakdown by institution for bounding boxes and images. Does that exist in the FN Get metrics (which one?), or can you provide those results directly?

hohonuuli commented 1 year ago

Images by contributor

SELECT
    isu.contributors_email,
    COUNT(DISTINCT i.id) as image_count
FROM
    images i LEFT JOIN
    image_uploads_join iuj on i.id = iuj.image_id LEFT JOIN
    image_set_uploads isu on iuj.imagesetupload_id = isu.id
GROUP BY
    isu.contributors_email
ORDER BY
    image_count DESC
contributors_email, image_count
brian@mbari.org, 75953
brian@deepsubmergence.com, 5632
benjamin.woodward@cvisionconsulting.com, 2866
nils.piechaud@gmail.com, 3
hohonuuli commented 1 year ago

Images by institution code

SELECT
    dc.institution_code,
    COUNT(DISTINCT i.id) as image_count
FROM
    images i RIGHT JOIN
    image_uploads_join iuj on i.id = iuj.image_id RIGHT JOIN
    image_set_uploads isu on iuj.imagesetupload_id = isu.id RIGHT JOIN
    darwin_cores dc on isu.darwincore_id = dc.id
GROUP BY
    dc.institution_code
ORDER BY
    image_count DESC
institution_code,image_count
MBARI,75953
NOAA Ocean Exploration ,5632
CVision AI,2866
,3
hohonuuli commented 1 year ago

Bounding box counts on images by institution code

SELECT
    dc.institution_code,
    COUNT(DISTINCT b.id) as bounding_box_count
FROM
    bounding_boxes b RIGHT JOIN
    images i on i.id = b.image_id RIGHT JOIN
    image_uploads_join iuj on i.id = iuj.image_id RIGHT JOIN
    image_set_uploads isu on iuj.imagesetupload_id = isu.id RIGHT JOIN
    darwin_cores dc on isu.darwincore_id = dc.id
GROUP BY
    dc.institution_code
ORDER BY
    bounding_box_count DESC
institution_code,bounding_box_count
MBARI,161738
CVision AI,8112
NOAA Ocean Exploration ,6069
,57
hohonuuli commented 1 year ago

Bounding box counts by observer

SELECT
    b.observer,
    COUNT(DISTINCT b.id) as bounding_box_count
FROM
    bounding_boxes b RIGHT JOIN
    images i on i.id = b.image_id RIGHT JOIN
    image_uploads_join iuj on i.id = iuj.image_id RIGHT JOIN
    image_set_uploads isu on iuj.imagesetupload_id = isu.id RIGHT JOIN
    darwin_cores dc on isu.darwincore_id = dc.id
GROUP BY
    b.observer
ORDER BY
    bounding_box_count DESC
observer,bounding_box_count
master@mbari.org,72374
linda,19364
,14110
expert-observer,13503
gsainz,11045
schlin,9316
svonthun,7640
lonny,7035
robs,4015
lemon,2569
lynne,1951
lclary,1060
kakani.katija@gmail.com,1005
joost,972
kakani,852
kwalz,765
kthomas,708
mage,618
ndgallo,488
sjohnson,358
Ben Burford,286
anela,272
smartini,258
sbush,257
jwinnikoff,225
josi,213
Wyatt Patry,205
alapides,200
George,194
ventana,189
stephalopod@gmail.com,177
cking,177
CatMunro,163
eburton,157
emalsbury,157
brian@deepsubmergence.com,142
kuhnz.linda@gmail.com,139
diana,134
Julia Chavarry,133
mbassett,129
Megan Olhasso,126
ssiebert,122
chloeweinstock,113
paje,112
Kat Bolstad,107
Alejandro Damian-Serrano,93
Darrin Schultz,91
mackenzie bubel,87
Bill Browne,82
matteo,79
jhavassy,75
abitondo,74
wfrancis,65
ClarieLM,54
osbornk@si.edu,53
mpowers,50
thuesen,50
ClagueGroup,50
amberR,47
MBA,47
oska,45
telissa,45
Alicia Pereyra,45
vars,41
vstenvers,41
mprickett,36
astridl,36
bmejia,35
nickleo,26
Chan Lin,25
Katie Beittenmiller,24
TommyKnowles,23
jmferob@gmail.com,21
barr,20
dfabian,17
AprilWoods,16
wtruong,16
William Browne,15
Sapphitah,15
JulieStewart,15
shchurch,14
reki,13
lonny@mbari.org,12
Ellen Umeda,12
chuffard,12
Alex Hoover,11
RRobison,11
Chad,10
docricketts,10
joseph.ryan,10
jhimes,9
Alison Sweeney,9
wrf,9
whpa,9
Veronica Franklin,8
rhelm,8
cheyennepayne,8
Bret Grasse,7
gfauville,7
Elliot Lowndes,6
kbarnard,6
rebecca,6
mdebiasse,5
tknowles,5
jvalenzuela,5
BGenco,4
Biancani,4
brian@mbari.org,4
dhugal@jamstec.go.jp,4
Dan Swezey,4
Rudy Kloser,4
Matt Wandell,4
brian,3
eorenstein,3
fgoetz,3
hruhl,3
guest,3
haddock,2
keilk,2
kakani@mbari.org,2
aray,2
Nick Beck,2
oshimak@jamstec.go.jp,2
stella,2
Taylor,1
nburnett,1
MARS,1
louz,1
Lloyd Trueblood,1
henk-jan,1
fujikura,1
eumeda,1
hohonuuli commented 1 year ago

Bounding Box Counts per owning institution

SELECT
    dc.owner_institution_code,
    COUNT(DISTINCT b.id) as bounding_box_count
FROM
    bounding_boxes b RIGHT JOIN
    images i on i.id = b.image_id RIGHT JOIN
    image_uploads_join iuj on i.id = iuj.image_id RIGHT JOIN
    image_set_uploads isu on iuj.imagesetupload_id = isu.id RIGHT JOIN
    darwin_cores dc on isu.darwincore_id = dc.id
GROUP BY
    dc.owner_institution_code
ORDER BY
    bounding_box_count DESC
owner_institution_code,bounding_box_count
MBARI,161738
NOAA OER,8112
NOAA Ocean Exploration,6069
UniOfPlym,57
UHH,0
kakanikatija commented 9 months ago

this is also similar to https://github.com/fathomnet/community-feedback/issues/69. i'm going to close this.