Ochrana

[Jules-]

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

[Jules-]

Po úprave dle e-mailu by to mohlo být takto:

WITH temp_taxons AS (
    SELECT taxons.id, name_lat,
           CASE WHEN value != 200000 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,
    CASE WHEN is_protected THEN round(r.latitude * 100) / 100
        ELSE r.latitude END AS decimalLatitude,
    CASE WHEN is_protected THEN round(r.longitude * 100) / 100
         ELSE r.longitude END 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,
    CASE WHEN is_protected THEN NULL
         ELSE r.gps_coords_precision END AS coordinateUncertaintyInMeters,
    CASE WHEN is_protected THEN 0.01
         ELSE NULL END AS coordinatePrecision
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