Coder Social home page Coder Social logo

pladias-dwc-export's Introduction

pladias-dwc-export

pladias-dwc-export's People

Contributors

michaltorma avatar jules- avatar

Watchers

 avatar  avatar

pladias-dwc-export's Issues

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

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

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.