sebms_species_per_sitetype_plot() wrong averages plus SQL call aborted

larspett commented 8 months ago

This code generates P average (2022) =10.9 and T = 14.5

but the function gives this (including a number of attemted postgres calls)

ScientiaFelis commented 8 months ago

Cannot reproduce your error!?

ScientiaFelis commented 8 months ago

With this code:

 SELECT
    sit_type,
    AVG(species)
  FROM
      (SELECT
         sit_type,
          sit_uid,
          count(DISTINCT spe.spe_uid) as species
       FROM obs_observation AS obs
           INNER JOIN vis_visit AS vis ON obs.obs_vis_visitid = vis.vis_uid
           INNER JOIN spe_species AS spe ON obs.obs_spe_speciesid = spe.spe_uid
           INNER JOIN seg_segment AS seg ON obs.obs_seg_segmentid = seg.seg_uid
           INNER JOIN sit_site AS sit ON seg.seg_sit_siteid = sit.sit_uid
           INNER JOIN spv_speciesvalidation AS spv ON spe.spe_uid = spv_spe_speciesid      -- så här bör det väl vara?

       WHERE
          extract('YEAR' from vis_begintime) IN (2022)
          AND
          vis_typ_datasourceid in (54,55,56,63,64,66,67,84)
          AND spv.spv_istrim=TRUE 

      GROUP BY
          sit_type, sit_uid
      ORDER BY species DESC
    ) AS t
GROUP BY t.sit_type

I get averages 11 and 16.2

larspett commented 8 months ago

Works on osx, error on win10

From: Georg @.> Reply to: ScientiaFelis/sebmsR @.> Date: Monday, 30 October 2023 at 17:48 To: ScientiaFelis/sebmsR @.> Cc: Lars Pettersson @.>, Author @.***> Subject: Re: [ScientiaFelis/sebmsR] sebms_species_per_sitetype_plot() wrong averages plus SQL call aborted (Issue #24)

Cannot reproduce your error!?

— Reply to this email directly, view it on GitHubhttps://github.com/ScientiaFelis/sebmsR/issues/24#issuecomment-1785645440, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEVQXZFIJ2O72LRFMCKLGCTYB7K5DAVCNFSM6AAAAAA6V7BQJGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBVGY2DKNBUGA. You are receiving this because you authored the thread.Message ID: @.***>

larspett commented 8 months ago

I had a detailed look now and the correct numbers are (for 2022): transects 14,3 and point sites 10,7. (14.28378 & 10.73034) This results when including sites with zero species (meaning that the categories in the plot need updating to include the 0 category too) and avoiding counting sites with zero species records as well non-zero species records as including nollobservation as a species. I did this manually now but will ask my brother or Mathieu to construct a query that extracts this value which is a littel bit tricky and probably involves sub-queries.

larspett commented 8 months ago

jämförelse.xlsx The calculation of correct number of species per site, including zero-species sites and correcting non-zero sites that have one or more visits with zero species observed

ScientiaFelis commented 8 months ago

Ok, good.

Is this easier to do in R? Import everything and then filter sites? Basically if I understood correct, we should sum species per site and site type but, as we do, include sites with no observations also? Thereby lowering the mean per site type to 10.7 and 14.3

larspett commented 8 months ago

Yes in principle, although I want to have the query in sql too (but not necessarily in the package). We could also implement a rule that includes species aggregates when individual species haven’t been seen. E.g. Ängsvitvinge/skogsvitvinge is one species as long as either of the two hasn’t been seen. When one of the species has been seen (plus the aggregate) then it is still one species, when both species have been seen then it is two…

31 okt. 2023 kl. 08:30 skrev Georg @.***>:

Ok, good.

Is this easier to do in R? Import everything and then filter sites? Basically if I understood correct, we should sum species per site and site type but, as we do, include sites with no observations also? Thereby lowering the mean per site type to 10.7 and 14.3

— Reply to this email directly, view it on GitHubhttps://github.com/ScientiaFelis/sebmsR/issues/24#issuecomment-1786633683, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEVQXZH6WRUUDZOTHZ2MQV3YCCSINAVCNFSM6AAAAAA6V7BQJGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBWGYZTGNRYGM. You are receiving this because you authored the thread.Message ID: @.***>

larspett commented 8 months ago

It throws these error messages at the first run but not afterwards. Including a dedicated font might be good, although I cannot see why it isn't finding Arial (which is installed)

Warning messages: 1: In grid.Call(C_stringMetric, as.graphicsAnnot(x$label)) : font family not found in Windows font database 2: In grid.Call(C_stringMetric, as.graphicsAnnot(x$label)) : font family not found in Windows font database 3: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 4: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 5: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 6: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 7: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 8: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 9: In grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database 10: In grid.Call.graphics(C_text, as.graphicsAnnot(x$label), x$x, x$y, : font family not found in Windows font database

ScientiaFelis commented 8 months ago

NEED TO FIX:

[x] include sites with zero observations to get right mean number of species per site type
[x] implement a rule that includes species aggregates when individual species haven’t been seen. E.g. Ängsvitvinge/skogsvitvinge is one species as long as either of the two hasn’t been seen. When one of the species has been seen (plus the aggregate) then it is still one species, when both species have been seen then it is two

ScientiaFelis commented 8 months ago

This was very tricky but I think it works now.

@larspett for sites without observations should we:

set to category "1-5"
set a "0" category
set first category to "0-5"

larspett commented 8 months ago

We can set 0 as a category

From: Georg @.> Sent: den 1 november 2023 17:20 To: ScientiaFelis/sebmsR @.> Cc: Lars Pettersson @.>; Mention @.> Subject: Re: [ScientiaFelis/sebmsR] sebms_species_per_sitetype_plot() wrong averages plus SQL call aborted (Issue #24)

This was very tricky but I think it works now.

@larspetthttps://github.com/larspett for sites without observations should we: