SELECT source.shortname AS datasource, COALESCE(geoscrub_output."acceptedCountry", place.country) AS country, COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince) AS "stateProvince", COALESCE(geoscrub_output."acceptedCounty", place.county) AS 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", (geoscrub_output.geovalid)::integer AS geovalid_bien, ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien", COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID", locationevent.locationevent_id AS locationevent__pkey, COALESCE(location.sourceaccessioncode, NULLIF(pg_catalog.concat_ws('; '::text, COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode), ''::text)) 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, 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", COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature_C", COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS precipitation_m, stratum.stratumname AS stratum__name, COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS communities, COALESCE(locationevent__contributors__array(locationevent.locationevent_id), locationevent__contributors__array(parent_event.locationevent_id)) AS plot__collectors 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 USING (place_id)) LEFT JOIN coordinates USING (coordinates_id)) LEFT JOIN geoscrub.geoscrub_output ON ((((((ARRAY[geoscrub_output."decimalLatitude"] = ARRAY[coordinates.latitude_deg]) AND (ARRAY[geoscrub_output."decimalLongitude"] = ARRAY[coordinates.longitude_deg])) AND (ARRAY[geoscrub_output.country] = ARRAY[place.country])) AND (ARRAY[geoscrub_output."stateProvince"] = ARRAY[place.stateprovince])) AND (ARRAY[geoscrub_output.county] = ARRAY[place.county])))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."*GADM country" = COALESCE(geoscrub_output."acceptedCountry", place.country)))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."*isoCode" = iso_code_gadm."*2-digit iso code"))) LEFT JOIN geoscrub.county_centroids ON ((((place.country = 'United States'::text) AND (county_centroids.state = COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince))) AND (county_centroids.county = COALESCE(geoscrub_output."acceptedCounty", place.county))))) 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 stratum ON ((stratum.stratum_id = COALESCE(locationevent.stratum_id, parent_event.stratum_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id))));