michaltorma / pladias-dwc-export

pladias-dwc-export

pladias-dwc-export's Issues

VIEW darwin_core_extended_occurrence

Okomentuji SQL

CREATE VIEW darwin_core_extended_occurrence AS
SELECT
    r.id AS occurrenceID,
-- VK: chybně t.scientific_name AS scientificName,
    t.name_lat AS scientificName,
    r.latitude AS decimalLatitude,
    r.longitude AS decimalLongitude,
    r.datum AS eventDate,
    r.locality,
    r.altitude_min AS minimumElevationInMeters,
    r.altitude_max AS maximumElevationInMeters,
    r.comment AS occurrenceRemarks,
-- VK: Co tady potřebujete? Záznam má více autorů. Je to vazba M : N. Předpokládám, že id z databáze Pladias Vám nepomůže nebo ano?
    ra.authors_id AS recordedByID,  -- Assuming recorder's ID maps to an author in the authors table
    r.source,
    r.environment,
-- VK: Zde Vám stačí id integer z tabulky atlas.projects?
    r.project_id AS associatedSequences, -- Example of mapping project ID to associated sequences; adjust based on actual data mapping
-- VK: Co by mělo být zde? Jedná se o id z tabulky atlas.record_validation_status
    r.validation_status AS identificationVerificationStatus,
    r.original_name AS verbatimScientificName,
    r.gps_coords_precision AS coordinatePrecision,
-- VK: možné hodnoty Y, M nebo D, které určují, jestli je z pole datum platný pouze rok nebo i měsíc nebo celé datum.
    r.datum_precision AS verbatimEventDate,
-- VK: duplicitní s r.locality
    r.nearest_town_text AS locality,
-- VK: jedná se o integer id z tabulky fytochorionů (geodata.phytochorions)
    r.phytochorion_id AS locationID -- Example of using phytochorion_id as a location ID
FROM
    atlas.records AS r
LEFT JOIN public.taxons AS t ON r.taxon_id = t.id
LEFT JOIN atlas.records_authors AS ra ON r.id = ra.records_id
WHERE
-- VK: U tohoto příznaku je to komplikovanější, protože v určitých případech chtěli v českém Pladiasu, aby bylo možné vložit do mapy i odmítnuté záznamy. Možná by bylo vhodnější vybírat podle validation status. 
    r.include_in_map = TRUE;

Podle https://dwc.tdwg.org/terms/#dwc:recordedByID by to mohlo být možná takto:

SELECT
    r.id AS occurrenceID,
    string_agg(cast(ra.authors_id as varchar), '|') AS recordedByID
FROM
    atlas.records AS r
        LEFT JOIN atlas.records_authors AS ra ON r.id = ra.records_id
WHERE
    r.include_in_map = TRUE
GROUP BY r.id

Ochrana

Zde je návrh, jak zjistit, jestli je taxon chráněný nebo ne. Aktuálně to vrátí is_protected = TRUE pro všech pět kategorií:

• taxón národného významu
• taxón národného významu, na ktorého ochranu sa vyhlasujú chránené územia
• taxón európskeho významu
• taxón európskeho významu, na ktorého ochranu sa vyhlasujú chránené územia
• prioritný taxón európskeho významu, na ktorého ochranu sa vyhlasujú chránené územia

WITH temp_taxons AS (
    SELECT taxons.id, name_lat,
           CASE WHEN value in (200001, 200002, 200003, 200004, 200005) THEN TRUE
                ELSE FALSE END AS is_protected
    FROM taxons
        LEFT JOIN measurements.data_enum AS de ON taxons.id = de.taxon_id
    WHERE de.trait_id = 200001 AND entry_type = 1 AND is_enabled
)
SELECT
    CONCAT('BU-SAV:PLADIAS:', r.id) AS occurrenceID,
    t.name_lat AS scientificName,
    r.latitude AS decimalLatitude,
    r.longitude AS decimalLongitude,
    CASE
        WHEN r.datum_precision = 'Y' THEN TO_CHAR(r.datum, 'YYYY')
        WHEN r.datum_precision = 'M' THEN TO_CHAR(r.datum, 'YYYY-MM')
        WHEN r.datum_precision = 'D' THEN TO_CHAR(r.datum, 'YYYY-MM-DD')
        ELSE NULL
        END AS eventDate,
    r.locality,
    r.altitude_min AS minimumElevationInMeters,
    r.altitude_max AS maximumElevationInMeters,
    r.comment AS occurrenceRemarks,
    (
        SELECT string_agg(CASE WHEN a.name = '' THEN a.surname ELSE CONCAT(a.name, ' ', a.surname) END, '|')
        FROM atlas.records_authors
                 INNER JOIN atlas.authors AS a ON records_authors.authors_id = a.id
        WHERE records_id = r.id) AS recordedBy,
    r.source,
    r.environment,
    CASE
        WHEN r.validation_status IN (0, 1) THEN 'verification required'
        WHEN r.validation_status = 3 THEN 'verified'
        ELSE NULL
        END AS identificationVerificationStatus,
    r.original_name AS verbatimScientificName,
    r.gps_coords_precision AS coordinateUncertaintyInMeters,
    is_protected
FROM
    atlas.records AS r
        LEFT JOIN temp_taxons AS t ON r.taxon_id = t.id
        LEFT JOIN atlas.projects AS p ON r.project_id = p.id
WHERE
    r.validation_status != 2