CREATE VIEW analytical_stem_view AS

    SELECT source.shortname AS `institutionCode`, canon_place.country, canon_place.stateprovince AS `stateProvince`, datasource_place.county, coordinates.latitude_deg AS `decimalLatitude`, coordinates.longitude_deg AS `decimalLongitude`, coordinates.coordsaccuracy_m AS `coordinateUncertaintyInMeters`, canon_place.geovalid, `newWorldCountries`.`isNewWorld`, _m_to_km(canon_place.distance_to_country_m) AS `distanceToCountry_km`, _m_to_km(canon_place.distance_to_state_m) AS `distanceToStateProvince_km`, location.sourceaccessioncode AS `plotName`, location.elevation_m AS `elevationInMeters`, _m2_to_ha(location.area_m2) AS `plotArea_ha`, method.name AS `samplingProtocol`, aggregateoccurrence.collectiondate AS `dateCollected`, family_higher_plant_group.higher_plant_group AS `higherPlantGroup`, accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || CAST(' ' AS text)) || accepted_taxonverbatim.specific_epithet) AS `speciesBinomial`, COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS `scientificName`, accepted_taxonverbatim.author AS `scientificNameAuthorship`, CASE WHEN (accepted_taxonlabel.rank = CAST('family' AS taxonrank)) THEN accepted_taxonverbatim.family ELSE NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.genus, COALESCE(parsed_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], CAST(' ' AS text)), CAST('' AS text)) END AS `scientificNameWithMorphospecies`, (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)), CAST('' AS text)) AS `identifiedBy`, taxonoccurrence.growthform AS `growthForm`, COALESCE(taxonoccurrence.iscultivated, location.iscultivated, (geoscrub_cultivated.`isCultivated`)::int(1)) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE (geoscrub_cultivated.`isCultivatedReason` || CAST('' AS text)) END AS `cultivatedBasis`, _fraction_to_percent(aggregateoccurrence.cover_fraction) AS `coverPercent`, _m_to_cm(stemobservation.diameterbreastheight_m) AS `diameterBreastHeight_cm`, stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS `organismX_m`, stemobservation.yposition_m AS `organismY_m`, collector.fullname AS `recordedBy`, NULLIF(array_to_string(ARRAY[taxonoccurrence.sourceaccessioncode, taxonoccurrence.authortaxoncode, plantobservation.sourceaccessioncode, plantobservation.collectionnumber, stemobservation.sourceaccessioncode, stemobservation.authorstemcode, stemobservation.tag], CAST('; ' AS text)), CAST('' AS text)) AS `recordNumber` FROM (((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN geoscrub.geoscrub_cultivated ON (((geoscrub_cultivated.`latitudeDecimalVerbatim` = coordinates.latitude_deg) AND (geoscrub_cultivated.`longitudeDecimalVerbatim` = coordinates.longitude_deg)))) LEFT JOIN `newWorld`.iso_code_gadm ON ((iso_code_gadm.`GADM country` = canon_place.country))) LEFT JOIN `newWorld`.`newWorldCountries` ON (((`newWorldCountries`.`isoCode`)::text = iso_code_gadm.`2-digit iso code`))) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (NOT taxondetermination.isoriginal);

    SELECT source.shortname AS "institutionCode", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", _m_to_km(canon_place.distance_to_country_m) AS "distanceToCountry_km", _m_to_km(canon_place.distance_to_state_m) AS "distanceToStateProvince_km", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", CASE WHEN (accepted_taxonlabel.rank = 'family'::taxonrank) THEN accepted_taxonverbatim.family ELSE NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.genus, COALESCE(parsed_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) END AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", COALESCE(taxonoccurrence.iscultivated, location.iscultivated, (geoscrub_cultivated."isCultivated")::boolean) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE (geoscrub_cultivated."isCultivatedReason" || ''::text) END AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", collector.fullname AS "recordedBy", NULLIF(array_to_string(ARRAY[taxonoccurrence.sourceaccessioncode, taxonoccurrence.authortaxoncode, plantobservation.sourceaccessioncode, plantobservation.collectionnumber, stemobservation.sourceaccessioncode, stemobservation.authorstemcode, stemobservation.tag], '; '::text), ''::text) AS "recordNumber" FROM (((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN geoscrub.geoscrub_cultivated ON (((geoscrub_cultivated."latitudeDecimalVerbatim" = coordinates.latitude_deg) AND (geoscrub_cultivated."longitudeDecimalVerbatim" = coordinates.longitude_deg)))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON ((("newWorldCountries"."isoCode")::text = iso_code_gadm."2-digit iso code"))) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (NOT taxondetermination.isoriginal);