Project

General

Profile

« Previous | Next » 

Revision 12609

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: use psql var :datasource instead of current_schema() so that the queries are runnable without special configuration of the search_path

View differences:

trunk/validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
17 17 
-- Set variables specific to this source
18 18 
-- Currently configured for SALVIAS
19 19 
-- ------------------
20 
\set datasource 'SALVIAS'
20 21 

  		




  21
  22
  
    
SET search_path TO public_validations, public;


  		




  22
  23
  
    

  		




  ......




  24
  25
  
    
 SELECT count(*) AS projects


  		




  25
  26
  
    
   FROM (project p


  		




  26
  27
  
    
   JOIN source s ON ((p.source_id = s.source_id)))


  		




  27
  
  
    
  WHERE (s.shortname = ("current_schema"())::text);


  		




  
  28
  
    
  WHERE (s.shortname = :datasource::text);


  		




  28
  29
  
    

  		




  29
  30
  
    
-- _plots_02_list_of_project_names


  		




  30
  31
  
    
 SELECT p.projectname AS project_name


  		




  31
  32
  
    
   FROM (project p


  		




  32
  33
  
    
   JOIN source s ON ((p.source_id = s.source_id)))


  		




  33
  
  
    
  WHERE (s.shortname = ("current_schema"())::text);


  		




  
  34
  
    
  WHERE (s.shortname = :datasource::text);


  		




  34
  35
  
    

  		




  35
  36
  
    
-- _plots_03_count_of_all_plots_in_this_source


  		




  36
  37
  
    
 SELECT count(*) AS plots


  		




  ......




  38
  39
  
    
   JOIN locationevent le ON ((l.location_id = le.location_id)))


  		




  39
  40
  
    
   JOIN project p ON ((p.project_id = le.project_id)))


  		




  40
  41
  
    
   JOIN source s ON ((p.source_id = s.source_id)))


  		




  41
  
  
    
  WHERE (s.shortname = ("current_schema"())::text);


  		




  
  42
  
    
  WHERE (s.shortname = :datasource::text);


  		




  42
  43
  
    

  		




  43
  44
  
    
-- _plots_04_count_of_plots_in_each_project_in_this_source


  		




  44
  45
  
    
 SELECT p.projectname AS project_name, 


  		




  ......




  47
  48
  
    
   JOIN locationevent le ON ((l.location_id = le.location_id)))


  		




  48
  49
  
    
   JOIN project p ON ((p.project_id = le.project_id)))


  		




  49
  50
  
    
   JOIN source s ON ((p.source_id = s.source_id)))


  		




  50
  
  
    
  WHERE (s.shortname = ("current_schema"())::text)


  		




  
  51
  
    
  WHERE (s.shortname = :datasource::text)


  		




  51
  52
  
    
  GROUP BY p.projectname;


  		




  52
  53
  
    

  		




  53
  54
  
    
-- _plots_05_list_of_plot_codes_by_project


  		




  ......




  57
  58
  
    
   JOIN locationevent le ON ((l.location_id = le.location_id)))


  		




  58
  59
  
    
   JOIN project p ON ((p.project_id = le.project_id)))


  		




  59
  60
  
    
   JOIN source s ON ((p.source_id = s.source_id)))


  		




  60
  
  
    
  WHERE (s.shortname = ("current_schema"())::text);


  		




  
  61
  
    
  WHERE (s.shortname = :datasource::text);


  		




  61
  62
  
    

  		




  62
  63
  
    
-- _plots_06_list_of_plots_with_stem_measurements


  		




  63
  64
  
    
 SELECT project.projectname AS project_name, 


  		




  ......




  65
  66
  
    
   FROM ((plot


  		




  66
  67
  
    
   JOIN locationevent USING (location_id))


  		




  67
  68
  
    
   JOIN project USING (project_id))


  		




  68
  
  
    
  WHERE ((plot.source_id = ( SELECT source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown


  		




  
  69
  
    
  WHERE ((plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown


  		




  69
  70
  
    
   FROM (((((location


  		




  70
  71
  
    
   JOIN locationevent locationevent_1 USING (location_id))


  		




  71
  72
  
    
   JOIN taxonoccurrence USING (locationevent_id))


  		




  ......




  107
  108
  
    
   JOIN plot l ON ((place_visit.location_id = l.location_id)))


  		




  108
  109
  
    
   JOIN taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id)))


  		




  109
  110
  
    
   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))


  		




  110
  
  
    
  WHERE (s.shortname = ("current_schema"())::text)


  		




  
  111
  
    
  WHERE (s.shortname = :datasource::text)


  		




  111
  112
  
    
  GROUP BY p.projectname, l.authorlocationcode


  		




  112
  113
  
    
  ORDER BY p.projectname, l.authorlocationcode;


  		




  113
  114
  
    
/*


  		




  ......




  128
  129
  
    
   JOIN plot l ON ((place_visit.location_id = l.location_id)))


  		




  129
  130
  
    
   JOIN taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id)))


  		




  130
  131
  
    
   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))


  		




  131
  
  
    
  WHERE (s.shortname = ("current_schema"())::text)


  		




  
  132
  
    
  WHERE (s.shortname = :datasource::text)


  		




  132
  133
  
    
  ORDER BY p.projectname, l.authorlocationcode, ao.sourceaccessioncode;


  		




  133
  134
  
    
/*


  		




  134
  135
  
    
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.


  		




  ......




  147
  148
  
    
   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))


  		




  148
  149
  
    
   JOIN plantobservation po ON ((po.aggregateoccurrence_id = ao.aggregateoccurrence_id)))


  		




  149
  150
  
    
   JOIN stemobservation so ON ((so.plantobservation_id = po.plantobservation_id)))


  		




  150
  
  
    
  WHERE (s.shortname = ("current_schema"())::text)


  		




  
  151
  
    
  WHERE (s.shortname = :datasource::text)


  		




  151
  152
  
    
  GROUP BY p.projectname, l.authorlocationcode


  		




  152
  153
  
    
  ORDER BY p.projectname, l.authorlocationcode;


  		




  153
  154
  
    
/*


  		




  ......




  166
  167
  
    
   JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))


  		




  167
  168
  
    
   JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))


  		




  168
  169
  
    
   JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))


  		




  169
  
  
    
  WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))


  		




  
  170
  
    
  WHERE ((s.shortname = :datasource::text) AND (td.isoriginal = true))


  		




  170
  171
  
    
  GROUP BY p.projectname, l.authorlocationcode


  		




  171
  172
  
    
  ORDER BY p.projectname, l.authorlocationcode;


  		




  172
  173
  
    
/*


  		




  ......




  187
  188
  
    
   JOIN taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))


  		




  188
  189
  
    
   JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))


  		




  189
  190
  
    
   JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))


  		




  190
  
  
    
  WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))


  		




  
  191
  
    
  WHERE ((s.shortname = :datasource::text) AND (td.isoriginal = true))


  		




  191
  192
  
    
  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));


  		




  192
  193
  
    
/*


  		




  193
  194
  
    
Note: Must perform equivalent concatenation of taxonomic field in source db.


  		




  ......




  207
  208
  
    
   JOIN taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))


  		




  208
  209
  
    
   JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))


  		




  209
  210
  
    
   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))


  		




  210
  
  
    
  WHERE ((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true))


  		




  
  211
  
    
  WHERE ((s.shortname = :datasource::text) AND (td.isoriginal = true))


  		




  211
  212
  
    
  GROUP BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))


  		




  212
  213
  
    
  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));


  		




  213
  214
  
    
/*


  		




  ......




  229
  230
  
    
   JOIN taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))


  		




  230
  231
  
    
   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))


  		




  231
  232
  
    
   JOIN coverindex ci ON ((ao.coverindex_id = ci.coverindex_id)))


  		




  232
  
  
    
  WHERE (((s.shortname = ("current_schema"())::text) AND (td.isoriginal = true)) AND (ci.coverpercent IS NOT NULL))


  		




  
  233
  
    
  WHERE (((s.shortname = :datasource::text) AND (td.isoriginal = true)) AND (ci.coverpercent IS NOT NULL))


  		




  233
  234
  
    
  GROUP BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))


  		




  234
  235
  
    
  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));


  		




  235
  236
  
    
/*


  		




  ......




  262
  263
  
    
   JOIN locationevent le ON ((p.project_id = le.project_id)))


  		




  263
  264
  
    
   JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))


  		




  264
  265
  
    
   JOIN plot l ON ((le.location_id = l.location_id)))


  		




  265
  
  
    
  WHERE (s.shortname = ("current_schema"())::text)


  		




  
  266
  
    
  WHERE (s.shortname = :datasource::text)


  		




  266
  267
  
    
  GROUP BY p.projectname, l.authorlocationcode


  		




  267
  268
  
    
  ORDER BY p.projectname, l.authorlocationcode;


  		




  268
  269
  
    

  		




  ......




  275
  276
  
    
   JOIN locationevent le ON ((p.project_id = le.project_id)))


  		




  276
  277
  
    
   JOIN locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))


  		




  277
  278
  
    
   JOIN plot l ON ((sub_locationevent.location_id = l.location_id)))


  		




  278
  
  
    
  WHERE (s.shortname = ("current_schema"())::text)


  		




  
  279
  
    
  WHERE (s.shortname = :datasource::text)


  		




  279
  280
  
    
  ORDER BY p.projectname, l.authorlocationcode;


  		




  280
  281
  
    
/*


  		




  281
  282
  
    
DOESN''T YET WORK PROPERLY


  		












Also available in:  Unified diff