SELECT source.shortname AS datasource, sourcelist.name AS "institutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, location.locationnarrative AS locality, CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg ELSE county_centroids.latitude END AS "decimalLatitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg ELSE county_centroids.longitude END AS "decimalLongitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m ELSE _km_to_m(county_centroids.error_km) END AS "coordinateUncertaintyInMeters", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource WHEN (county_centroids.row_num IS NOT NULL) THEN 'georeferencing'::coordinatesource ELSE NULL::coordinatesource END AS "coordinateSource_bien", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text WHEN (county_centroids.row_num IS NOT NULL) THEN 'county centroid'::text ELSE NULL::text END AS "georeferenceProtocol_bien", (canon_place.geovalid)::integer AS geovalid_bien, ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien", COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID", COALESCE(location.sourceaccessioncode, concat_delim('; '::text, VARIADIC ARRAY[COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode])) AS "locationID", COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName", CASE WHEN (parent_location.location_id IS NOT NULL) THEN location.authorlocationcode ELSE NULL::text END AS subplot, plantobservation.authorplantcode AS "individualCode", COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters", _m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha", method.name AS "samplingProtocol", collector.fullname AS "recordedBy", plantobservation.authorplantcode AS "recordNumber", COALESCE(locationevent.obsstartdate, parent_event.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", datasource_taxonverbatim.family AS family_verbatim, datasource_taxonlabel.taxonomicname AS "scientificName_verbatim", datasource_taxonverbatim.author AS "scientificNameAuthorship_verbatim", identifiedby.fullname AS "identifiedBy", datasource_taxondetermination.determinationdate AS "dateIdentified", datasource_taxondetermination.notes AS "identificationRemarks", matched_taxonverbatim.family AS family_matched, matched_taxonverbatim.taxonname AS "taxonName_matched", matched_taxonverbatim.author AS "scientificNameAuthorship_matched", family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", canon_taxonverbatim.family, canon_taxonverbatim.genus, COALESCE(concat_delim(' '::text, VARIADIC ARRAY[COALESCE(canon_taxonverbatim.genus, canon_taxonverbatim.family), canon_taxonverbatim.specific_epithet, canon_taxonverbatim.morphospecies]), canon_taxonverbatim.taxonname, canon_taxonverbatim.taxonomicname) AS "speciesBinomialWithMorphospecies", canon_taxonverbatim.taxonname AS "taxonName", canon_taxonverbatim.author AS "scientificNameAuthorship", ((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, taxonoccurrence.growthform AS "growthForm", plantobservation.reproductivecondition AS "reproductiveCondition", (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)))::integer AS cultivated_bien, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis_bien", _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", taxonoccurrence.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", plantobservation.sourceaccessioncode AS "individualObservationID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationevent USING (location_id)) LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.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 "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."isoCode" = iso_code_gadm."2-digit iso code"))) LEFT JOIN geoscrub.county_centroids ON ((((canon_place.country = 'United States'::text) AND (county_centroids.state = canon_place.stateprovince)) AND (county_centroids.county = canon_place.county)))) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id)))) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.institution_id))) LEFT JOIN taxondetermination datasource_taxondetermination ON (((datasource_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND datasource_taxondetermination.is_datasource_current))) LEFT JOIN party identifiedby ON ((identifiedby.party_id = datasource_taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxondetermination matched_taxondetermination ON (((matched_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND (matched_taxondetermination.determinationtype = 'matched'::text)))) LEFT JOIN taxonverbatim matched_taxonverbatim ON ((matched_taxonverbatim.taxonverbatim_id = matched_taxondetermination.taxonverbatim_id))) LEFT JOIN taxondetermination canon_taxondetermination ON (((canon_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND canon_taxondetermination.iscurrent))) LEFT JOIN taxonverbatim canon_taxonverbatim ON ((canon_taxonverbatim.taxonverbatim_id = canon_taxondetermination.taxonverbatim_id))) LEFT JOIN taxonlabel canon_taxonlabel ON ((canon_taxonlabel.taxonlabel_id = canon_taxonverbatim.taxonlabel_id))) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = canon_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = canon_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = canon_taxonlabel.taxonlabel_id)));