-
Notifications
You must be signed in to change notification settings - Fork 0
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Ochrana #2
Comments
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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í:The text was updated successfully, but these errors were encountered: