Project

General

Profile

task #6537

Import Edaphobase descriptive data

Added by Andreas Müller over 2 years ago. Updated 6 months ago.

Status:
New
Priority:
Highest
Category:
cdmadapter
Start date:
03/23/2017
Due date:
% Done:

0%

Severity:
critical
Tags:

Description

data is available in observation.observation

SELECT 
  obs.*, descr.*
FROM 
  observation.observation obs INNER JOIN public.tax_taxon t ON obs.taxon_fk = t.taxon_id
  LEFT OUTER JOIN observation.observation2description MN ON MN.observation_fk = obs.observation_id
  LEFT OUTER JOIN observation.description descr ON MN.description_fk = descr.description_id
WHERE  
   (1=1) AND obs.observation_type_fk IN (11486, 11485)
ORDER BY observation_id
-- LIMIT 1000
  • obs.observation_type_fk is 11486 or 11485

Open issues:

  • Should obs.deleted = true also be imported (486)
  • what is obs.first_description = true (75) => defines if it is a taxon description or an occurrence record
  • is version field relevant somehow? obs.versionfield <>1 (26578)

Maybe also interesting

  • selective_list.description_type (11463 = Taxon description)
SELECT *
FROM selective_list.element l
WHERE l.element_id IN (11486, 11485)
SELECT description_type_fk, descType.value_summary, observation_type_fk, obsType.value_summary, count(*) as n

FROM 
  observation.observation obs INNER JOIN public.tax_taxon t ON obs.taxon_fk = t.taxon_id
  LEFT OUTER JOIN observation.observation2description MN ON MN.observation_fk = obs.observation_id
  LEFT OUTER JOIN observation.description descr ON MN.description_fk = descr.description_id
  LEFT OUTER JOIN selective_list.element descType ON description_type_fk = descType.element_id
  LEFT OUTER JOIN selective_list.element obsType ON observation_type_fk = obsType.element_id
WHERE  
   (1=1) AND obs.observation_type_fk <> 4411486 
GROUP BY description_type_fk, descType.value_summary, observation_type_fk, obsType.value_summary
ORDER BY observation_type_fk, description_type_fk

Associated revisions

Revision 133c63f0 (diff)
Added by Andreas Müller almost 2 years ago

ref #6537 first Edaphobase descriptive data import (not yet ready)

History

#1 Updated by Andreas Müller over 2 years ago

  • Description updated (diff)

#2 Updated by Andreas Müller over 2 years ago

  • Description updated (diff)

#3 Updated by Andreas Müller over 2 years ago

  • Description updated (diff)

#4 Updated by Andreas Müller over 2 years ago

  • Description updated (diff)

SL:

So findest du alle Beobachtungen, die es zum Taxon gibt. Du suchst aber nur die, die das Taxon beschreiben und nicht alle Funde. Ich habe mal ein kleines Query für die Art Cernosvitoviella tatrensis (Kowalewski, 1916) geschrieben. Ich hoffe, dass dir das hilft, ansonsten schreb einfach nochmal.

SELECT c.name_de, dd.min_value, dd.max_value, dd.value, u.name, dd.text, dd.list_element_fk
FROM observation.observation o
JOIN observation.observation2description o2d ON o.observation_id = o2d.observation_fk
JOIN observation.description_detail dd ON o2d.description_fk = dd.description_fk
JOIN observation.column c ON dd.column_fk = c.column_id
LEFT JOIN observation.unit u ON dd.unit_fk = u.unit_id
WHERE o.first_description = true and o.taxon_fk is not null
AND o.taxon_fk = 97999

AM: Verstehe ich das richtig, dass die Bedingung o.first_description = true defininiert, ob es sich um eine Taxonbeschreibung (im Gegensatz zu einer Einzelbeobachtung) handelt?

SL: ja, genau.

#5 Updated by Andreas Müller over 2 years ago

  • Target version changed from Release 4.7 to Release 4.8

#6 Updated by Andreas Müller about 2 years ago

  • Target version changed from Release 4.8 to Release 4.9

#7 Updated by Andreas Müller about 2 years ago

  • Target version changed from Release 4.9 to Release 4.10

#8 Updated by Andreas Müller about 2 years ago

Column groups:

11463;;18;"Taxonbeschreibung, Taxon description";";11463;"
11661;11463;19;"Quantität, Quantity";";11463;11661;"
11663;11463;19;"Biologie, Biology";";11463;11663;"
11664;11463;19;"Morphologie, Morphology";";11463;11664;"

#9 Updated by Andreas Müller almost 2 years ago

  • Tags set to edapho
  • Target version changed from Release 4.10 to Release 4.11

#10 Updated by Andreas Müller almost 2 years ago

  • Target version changed from Release 4.11 to Release 4.12

#11 Updated by Andreas Müller over 1 year ago

  • Target version changed from Release 4.12 to Release 4.13

#12 Updated by Andreas Müller over 1 year ago

  • Description updated (diff)

#13 Updated by Andreas Müller over 1 year ago

  • Target version changed from Release 4.13 to Release 4.14

#14 Updated by Andreas Müller over 1 year ago

  • Target version changed from Release 4.14 to Release 5.0

#15 Updated by Andreas Müller over 1 year ago

  • Target version changed from Release 5.0 to Release 5.1

#16 Updated by Andreas Müller about 1 year ago

  • Target version changed from Release 5.1 to Release 5.2

#17 Updated by Andreas Müller about 1 year ago

  • Target version changed from Release 5.2 to Release 5.3

#18 Updated by Andreas Müller 12 months ago

  • Target version changed from Release 5.3 to Release 5.4

#19 Updated by Andreas Müller 10 months ago

  • Target version changed from Release 5.4 to Release 5.5

#20 Updated by Andreas Müller 6 months ago

  • Target version changed from Release 5.5 to Release 5.6

#21 Updated by Andreas Müller 6 months ago

  • Target version changed from Release 5.6 to Reviewed Next Major Release

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)