michaltorma / pladias-dwc-export Goto Github PK
View Code? Open in Web Editor NEWLicense: MIT License
License: MIT License
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í:
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
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.