Data dissemination Webportal -> public

• related: Data ingestion from dataprovider to GBOL webportal (harvest)

Application

biocase\_media

Production

fredie:/var/data/biocase/transfer

GIT

https://fredie.eu/gitlab/BioCASe/biocase_media

	SELECT s.id, s.taxon AS identification
	, CONCAT_WS(';',
		t.tax_subfamily,
		t.tax_family,
		t.tax_order,
		t.tax_class,
		t.tax_subphylum,
		t.tax_phylum,
		t.tax_kingdom) AS higher_taxa
	, gi.institute_short AS institute
	, (SELECT
		GROUP_CONCAT(COALESCE(d.term,'') ORDER BY d.field_id SEPARATOR ';')
		FROM GBOL_Data2Specimen ds
			INNER JOIN GBOL_Data d ON d.id = ds.data_id
			INNER JOIN GBOL_Data_Fields f ON (f.id = d.field_id AND f.lang='de')
		 WHERE d.field_id IN (1,8) AND ds.specimen_id = s.id
	  ) AS AccessionNo
	, (SELECT
		GROUP_CONCAT(COALESCE(d.term,'') SEPARATOR ';')
		FROM GBOL_Data2Specimen ds
			INNER JOIN GBOL_Data d ON d.id = ds.data_id
			INNER JOIN GBOL_Data_Fields f ON (f.id = d.field_id AND f.lang='de')
		 WHERE d.field_id IN (7,26,14) AND ds.specimen_id = s.id
	  ) AS Location
	, CONCAT(REPLACE(g.center_x, ',', '.'), '/', replace(g.center_y, ',', '.')) AS Coordinates
	, COALESCE(b.`sequence`,'') AS barcode_sequence
	FROM GBOL_Specimen s
	LEFT JOIN GBOL_TaxaFlat t ON t.taxon_id = s.taxon_id
	LEFT JOIN GBOL_Institutes gi ON gi.institute_id=s.institute_id
	LEFT JOIN GBOL_Geo g ON g.specimen_id = s.id
	LEFT JOIN GBOL_Barcode b on b.specimen_id = s.id
	WHERE t.tax_phylum = 'Arthropoda'
	GROUP BY s.id
	ORDER BY `taxon`;

SELECT CONCAT('>lcl | ',s.taxon,'_',d.term, '\n', b.`sequence`) AS FASTA
FROM GBOL_Specimen s
	INNER JOIN GBOL_TaxaFlat t ON t.taxon_id = s.taxon_id
	LEFT JOIN GBOL_Institutes gi ON gi.institute_id=s.institute_id
	LEFT JOIN GBOL_Barcode b on b.specimen_id = s.id
    LEFT JOIN GBOL_Data2Specimen ds ON ds.specimen_id=s.id
	LEFT JOIN GBOL_Data d ON d.id = ds.data_id
	LEFT JOIN GBOL_Data_Fields f ON (f.id = d.field_id AND f.lang='de' and d.field_id = 1)
WHERE t.tax_phylum = 'Arthropoda' AND b.sequence is not null
GROUP BY s.id
ORDER BY s.`taxon`;