Static tables for Red List publish

gistfile1.txt

vacuum analyze;
drop table if exists st_countryoccurrencesubfield_countryoccurrencelookup;
create table st_countryoccurrencesubfield_countryoccurrencelookup as select * from vw_published.vw_countryoccurrencesubfield_countryoccurrencelookup ;
drop table if exists st_countryoccurrencesubfield_presence;
create table st_countryoccurrencesubfield_presence as select * from vw_published.vw_countryoccurrencesubfield_presence;
drop table if exists st_countryoccurrencesubfield_origin;
create table st_countryoccurrencesubfield_origin as select * from vw_published.vw_countryoccurrencesubfield_origin;
DROP TABLE IF EXISTS st_countryoccurrence;
CREATE TABLE st_countryoccurrence AS
  SELECT country.taxonid, country.assessmentid, country.value, country.recordid, record."CODE" as code, record."DESCRIPTION" as country, pres."LABEL" as presence, origin."LABEL" as origin

  FROM st_countryoccurrencesubfield_countryoccurrencelookup country
  JOIN lookups."COUNTRYOCCURRENCELOOKUP" record ON record."ID" = country.value
  LEFT JOIN st_countryoccurrencesubfield_presence c_pres ON country.recordid = c_pres.recordid
  LEFT JOIN lookups."COUNTRYOCCURRENCE_PRESENCELOOKUP" pres ON pres."ID" = c_pres.value
  LEFT JOIN st_countryoccurrencesubfield_origin c_origin ON country.recordid = c_origin.recordid
  LEFT JOIN lookups."COUNTRYOCCURRENCE_ORIGINLOOKUP" origin ON origin."ID" = c_origin.value;
drop table if exists st_infrarank;
create table st_infrarank as select * from vw_published.vw_infrarank;
drop table if exists st_species;
create table st_species as select * from vw_published.vw_species;
drop table if exists st_redlistcategoryandcriteria_all;
create table st_redlistcategoryandcriteria_all as select * from vw_published.vw_redlistcategoryandcriteria;
drop table if exists st_all_taxa;
create table st_all_taxa as select * from st_infrarank union select *, '', '' from st_species;
drop table if exists st_all_taxa_criteria;
create table st_all_taxa_criteria as select t.*, c.assessmentid, c.rlcategory, c.rlcriteria, c.critversion, c.ismanual from st_all_taxa t, st_redlistcategoryandcriteria_all c where t.taxonid = c.taxonid;
drop table if exists st_all_taxa_criteria_by_region;
create table st_all_taxa_criteria_by_region as select a.*, r.value region from st_all_taxa_criteria a join vw_published.vw_regioninformation_regions r on a.assessmentid = r.assessmentid;
drop table if exists st_all_taxa_criteria_global;
create table st_all_taxa_criteria_global as select * from st_all_taxa_criteria_by_region where region=1;
drop table if exists st_all_taxa_criteria_global_ws;
create table st_all_taxa_criteria_global_ws as select g.*, name ws from st_all_taxa_criteria_global g left join vw_published.vw_workingsettaxon w on g.taxonid=w.taxonid;
drop table if exists st_all_taxa_criteria_regions_ws;
create table st_all_taxa_criteria_regions_ws as select g.*, name ws from st_all_taxa_criteria_by_region g left join vw_published.vw_workingsettaxon w on g.taxonid=w.taxonid;

drop table if exists published_2012_1;
create table published_2012_1 as select ws, count(*) from st_all_taxa_criteria_global_ws where ws ilike 'PUBLISHED_2012.1%' group by ws order by ws;
drop table if exists multi_regional_2012_1;
create table multi_regional_2012_1 as select distinct taxonid, genus, species, infratype, assessmentid, region from st_all_taxa_criteria_regions where taxonid in (select taxonid from (select taxonid, genus, species, assessmentid, count(assessmentid) from st_all_taxa_criteria_ws where ws ilike 'PUBLISHED_2012.1%' group by taxonid, genus, species, assessmentid having count(assessmentid)>1 order by assessmentid) s1) and region!=1 order by taxonid, assessmentid;