Project

General

Profile

« Previous | Next » 

Revision 377

Added by Aaron Marcuse-Kubitza about 13 years ago

SALVIAS verification: Added plot methods

View differences:

inputs/SALVIAS/verify.0.plots.sql
17 17 

  		




  18
  18
  
    
SELECT '# plot observations' AS ___;


  		




  19
  19
  
    
SELECT count(*) AS count FROM plotevent;


  		




  
  20
  
    

  		




  
  21
  
    
SELECT '1st methods' AS ___;


  		




  
  22
  
    
SELECT DISTINCT


  		




  
  23
  
    
    projectname AS project


  		




  
  24
  
    
    , stemSampleMethod AS method -- placeholder until we have a method field


  		




  
  25
  
    
FROM project


  		




  
  26
  
    
JOIN plotevent USING (project_id)


  		




  
  27
  
    
ORDER BY project, method


  		




  
  28
  
    
LIMIT 10;


  		




  
  29
  
    

  		




  
  30
  
    
SELECT '1st method narratives' AS ___;


  		




  
  31
  
    
SELECT DISTINCT


  		




  
  32
  
    
    projectname AS project


  		




  
  33
  
    
    , methodNarrative AS method_narrative


  		




  
  34
  
    
FROM project


  		




  
  35
  
    
JOIN plotevent USING (project_id)


  		




  
  36
  
    
ORDER BY project, method_narrative


  		




  
  37
  
    
LIMIT 10;


  		












  

    
      inputs/SALVIAS/verify.0.plots.ref.sql
    
  





  5
  5
  
    
SELECT CAST(project_id AS char) AS project FROM projects ORDER BY project;


  		




  6
  6
  
    

  		




  7
  7
  
    
SELECT 'each project\'s # plots' AS ___;


  		




  8
  
  
    
SELECT CAST(plotMetadata.project_id AS char) AS project, count(PlotID) AS plots_count


  		




  
  8
  
    
SELECT


  		




  
  9
  
    
    CAST(projects.project_id AS char) AS project


  		




  
  10
  
    
    , count(PlotID) AS plots_count


  		




  9
  11
  
    
FROM projects


  		




  10
  12
  
    
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id


  		




  11
  13
  
    
GROUP BY project


  		




  ......




  18
  20
  
    
SELECT '# plot observations' AS ___;


  		




  19
  21
  
    
SELECT count(*) AS count FROM plotMetadata;


  		




  20
  22
  
    

  		




  21
  
  
    
/*


  		




  22
  
  
    
SELECT DISTINCT pr.project_id, project_name, PlotMethod, m.MethodCode, m.Description


  		




  23
  
  
    
FROM projects pr JOIN plotMetadata p JOIN lookup_MethodCode m


  		




  24
  
  
    
ON pr.project_id=p.project_id AND p.MethodCode=m.MethodCode


  		




  
  23
  
    
SELECT '1st methods' AS ___;


  		




  
  24
  
    
SELECT DISTINCT


  		




  
  25
  
    
    CAST(projects.project_id AS char) AS project


  		




  
  26
  
    
    , lookup_MethodCode.Description AS method


  		




  
  27
  
    
FROM projects


  		




  
  28
  
    
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id


  		




  
  29
  
    
JOIN lookup_MethodCode ON lookup_MethodCode.MethodCode = plotMetadata.MethodCode


  		




  
  30
  
    
ORDER BY project, method


  		




  25
  31
  
    
LIMIT 10;


  		




  26
  32
  
    

  		




  
  33
  
    
SELECT '1st method narratives' AS ___;


  		




  
  34
  
    
SELECT DISTINCT


  		




  
  35
  
    
    CAST(projects.project_id AS char) AS project


  		




  
  36
  
    
    , PlotMethod AS method_narrative


  		




  
  37
  
    
FROM projects


  		




  
  38
  
    
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id


  		




  
  39
  
    
ORDER BY project, method_narrative


  		




  
  40
  
    
LIMIT 10;


  		




  
  41
  
    

  		




  
  42
  
    
/*


  		




  27
  43
  
    
SELECT p.PlotID, p.SiteCode, COUNT(*)


  		




  28
  44
  
    
FROM projects pr JOIN plotMetadata p JOIN plotObservations o


  		




  29
  45
  
    
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID


  		












  

    
      inputs/SALVIAS/verify.0.plots.ref
    
  





  61
  61
  
    
# plot observations


  		




  62
  62
  
    
count


  		




  63
  63
  
    
13661


  		




  
  64
  
    
___


  		




  
  65
  
    
1st methods


  		




  
  66
  
    
project	method


  		




  
  67
  
    
1	species (stems)


  		




  
  68
  
    
10	individuals


  		




  
  69
  
    
11	species (percent cover)


  		




  
  70
  
    
12	individuals


  		




  
  71
  
    
13	individuals


  		




  
  72
  
    
14	individuals


  		




  
  73
  
    
17	individuals


  		




  
  74
  
    
18	individuals


  		




  
  75
  
    
19	individuals


  		




  
  76
  
    
2	individuals


  		




  
  77
  
    
___


  		




  
  78
  
    
1st method narratives


  		




  
  79
  
    
project	method_narrative


  		




  
  80
  
    
1	0.1 ha  transect, stems >= 2.5 cm dbh


  		




  
  81
  
    
10	NULL


  		




  
  82
  
    
10	"Pseudotransect": collection of leaf samples from species recorded in previous 0.1 ha transect. Species sampled  from same location as previous transect "c2750-1" (project "Boyle Transects"); do not necessarily correspond to same individuals.


  		




  
  83
  
    
10	"Pseudotransect": collection of leaf samples from species recorded in previous 0.1 ha transect. Species sampled  from same location as previous transect "Savegre1" (project "OTS Transects"); do not necessarily correspond to same individuals.


  		




  
  84
  
    
10	0.1 ha  transect, stems >= 2.5 cm dbh


  		




  
  85
  
    
11	20 m x 20 cover plots, all vascular plants


  		




  
  86
  
    
12	0.01 ha, stems >= 10 cm dbh; 16 subplots of 10 x 10 m per plot, 4 rows (UU, U, S, SS), 4 columns (BB, B, T, TT)


  		




  
  87
  
    
13	1 ha, stems >= 10 cm dbh


  		




  
  88
  
    
14	0.1 ha  transect, stems >= 2.5 cm dbh


  		




  
  89
  
    
16	NULL


  		












Also available in:  Unified diff