BinBase - generate histograms

sql statements to generate histograms, to learn more about your data

similarity


select count("range"),"range" from
(
select
case
when match > 500 and match <= 550 then '501 - 550' when match > 550 and match <= 600 then '550 - 600' when match > 600 and match <= 650 then '600 - 650' when match > 650 and match <= 700 then '650 - 700' when match > 700 and match <= 750 then '700 - 750' when match > 750 and match <= 800 then '750 - 800' when match > 800 and match <= 850 then '800 - 850' when match > 850 and match <= 900 then '850 - 900' when match > 900 and match <= 950 then '900 - 950' when match > 950 and match <= 1000 then '950 - 1000' end AS "range" from spectra a, bin b where a.bin_id = b.bin_id and b.export = 'FALSE' ) a group by "range"

purity


select count("range"),"range" from
(
select
case
when a.purity > 0 and a.purity <= 0.1 then '0 - 0.1' when a.purity > 0.1 and a.purity <= 0.5 then '0.1 - 0.5' when a.purity > 0.5 and a.purity <= 0.9 then '0.5 - 0.9' when a.purity > 0.9 and a.purity <= 1.3 then '0.9 - 1.3' when a.purity > 1.3 and a.purity <= 1.7 then '1.3 - 1.7' when a.purity > 1.7 and a.purity <= 2.1 then '1.7 - 2.1' when a.purity > 2.1 and a.purity <= 2.5 then '2.1 - 2.5' when a.purity > 2.5 then '> 2.5'
end
AS "range" from spectra a, bin b where a.bin_id = b.bin_id and b.export = 'TRUE'
)
a group by "range"


signal noise




BinBase - reset database

simple sql script to reset the binbase database


delete from bin where bin_id not in (select bin_id from standard)
GO
delete from spectra where bin_id not in (select bin_id from standard)
GO
delete from correction_data
GO
delete from samples where sample_id not in (select sample_id from bin)
GO
delete from rawdata
GO
delete from result_link
GO
delete from result
GO
delete from runtime
GO
delete from quantification
GO


BinBase - simulate the sifter algorithm directly on the database

currently I'm generating statistics for the BinBase system and so needed to simulate the annotation algorithm in sql.

It was rather simple....



-- 0.05 settings
select
count(*) as "count",
lower('0 < 0.05') as purity, lower('> 500') as signalNoise,
lower('> 800') as similarity

from spectra where purity > 0 and purity < 0.05 and signal_noise > 500 and match > 800

UNION

select
count(*) as "count",
lower('0 < 0.05') as purity, lower('> 50 && < 500') as signalNoise, lower('> 700') as similarity

from spectra where purity > 0 and purity < 0.05 and signal_noise > 50 and signal_noise <= 500 and match > 700


UNION

select
count(*) as "count",
lower('0 < 0.05') as purity, lower('> 5 && < 50') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 0.05 and signal_noise > 5 and signal_noise <= 50 and match > 600

-- 0.3 settings

UNION

select
count(*) as "count",
lower('0 < 0.3') as purity, lower('> 500') as signalNoise,
lower('> 700') as similarity

from spectra where purity > 0 and purity < 0.3 and signal_noise > 500 and match > 700

UNION

select
count(*) as "count",
lower('0 < 0.3') as purity, lower('> 50 && < 500') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 0.3 and signal_noise > 50 and signal_noise <= 500 and match > 600

UNION

select
count(*) as "count",
lower('0 < 0.3') as purity, lower('> 5 && < 50') as signalNoise, lower('> 500') as similarity

from spectra where purity > 0 and purity < 0.03 and signal_noise > 5 and signal_noise <= 50 and match > 500

-- all ither settings

UNION

select
count(*) as "count",
lower('> 0.3') as purity,
lower('> 500') as signalNoise,
lower('> 600') as similarity

from spectra where purity > 0.3 and signal_noise > 500 and match > 600

UNION

select
count(*) as "count",
lower('> 0.3') as purity,
lower('> 50 && < 500') as signalNoise, lower('> 500') as similarity

from spectra where purity > 0.3 and signal_noise > 50 and signal_noise <= 500 and match > 500

UNION

select
count(*) as "count",
lower('> 0.3') as purity,
lower('> 5 && < 50') as signalNoise, lower('> 450') as similarity

from spectra where purity > 0.03 and signal_noise > 5 and signal_noise <= 50 and match > 450

-- total --

union

select
count(*) as "count",
lower('no filter') as purity,
lower('no filter') as signalNoise,
lower('no filter') as similarity

from spectra



leco 2.22 settings


-- 0.1 settings
select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 250') as signalNoise,
lower('> 800') as similarity

from spectra where purity > 0 and purity < 0.1 and signal_noise > 250 and match > 800

UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 25 && < 250') as signalNoise, lower('> 700') as similarity

from spectra where purity > 0 and purity < 0.1 and signal_noise > 25 and signal_noise <= 250 and match > 700


UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('>35 && < 25') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 0.1 and signal_noise > 3 and signal_noise <= 25 and match > 600

-- 1.5 settings

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 250') as signalNoise,
lower('> 700') as similarity

from spectra where purity > 0 and purity < 1.5 and signal_noise > 250 and match > 700

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 25 && < 250') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 1.5 and signal_noise > 25 and signal_noise <= 250 and match > 600

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 3 && < 25') as signalNoise, lower('> 500') as similarity

from spectra where purity > 0 and purity < 1.5 and signal_noise > 3 and signal_noise <= 25 and match > 500

-- all other settings

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 250') as signalNoise,
lower('> 600') as similarity

from spectra where purity > 1.5 and signal_noise > 250 and match > 600

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 3 && < 250') as signalNoise, lower('> 500') as similarity

from spectra where purity > 1.5 and signal_noise > 3 and signal_noise <= 250 and match > 500

UNION

-- total --

select
count(*) as "count",
lower('no filter') as purity,
lower('no filter') as signalNoise,
lower('no filter') as similarity

from spectra


and as query to filter polysiloxanes out of the result for leco 2.22




-- 0.1 settings
select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 250') as signalNoise,
lower('> 800') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 0.1 and a.signal_noise > 250 and match > 800 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 25 && < 250') as signalNoise, lower('> 700') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 0.1 and a.signal_noise > 25 and a.signal_noise <= 250 and match > 700 and a.bin_id = b.bin_id and b.export = 'TRUE'


UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('>35 && < 25') as signalNoise, lower('> 600') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 0.1 and a.signal_noise > 3 and a.signal_noise <= 25 and match > 600 and a.bin_id = b.bin_id and b.export = 'TRUE'

-- 1.5 settings

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 250') as signalNoise,
lower('> 700') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 1.5 and a.signal_noise > 250 and match > 700 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 25 && < 250') as signalNoise, lower('> 600') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 1.5 and a.signal_noise > 25 and a.signal_noise <= 250 and match > 600 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 3 && < 25') as signalNoise, lower('> 500') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 1.5 and a.signal_noise > 3 and a.signal_noise <= 25 and match > 500 and a.bin_id = b.bin_id and b.export = 'TRUE'

-- all other settings

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 250') as signalNoise,
lower('> 600') as similarity

from spectra a, bin b where a.purity > 1.5 and a.signal_noise > 250 and match > 600 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 3 && < 250') as signalNoise, lower('> 500') as similarity

from spectra a, bin b where a.purity > 1.5 and a.signal_noise > 3 and a.signal_noise <= 250 and match > 500 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

-- total --

select
count(*) as "count",
lower('no filter') as purity,
lower('no filter') as signalNoise,
lower('no filter') as similarity

from spectra a, bin b where a.bin_id = b.bin_id and b.export = 'TRUE'


BinBase queries

short overview of binbase queries to generate statistics



count of spectra

select count(*) from spectra a, samples b where a.sample_id = b.sample_id and visible = 'TRUE'

count of spectra with no annotations

select count(*) from spectra a, samples b where a.sample_id = b.sample_id and visible = 'TRUE' and bin_id is null

count of samples

select count(*) from samples where visibile = 'TRUE'

known species

select distinct value from sample_info a, meta_key b where a.key_id = b.key_id and b.key = 'Species'

count of species

select count(distinct value) from sample_info a, meta_key b where a.key_id = b.key_id and b.key = 'Species'

sample count for species

select count(a.sample_id),a.value from sample_info a, meta_key b,samples c where a.key_id = b.key_id and b.key = 'Species' and a.sample_id = c.sample_id and c.visible = 'TRUE' group by a.value

bin generation by species

select a.value,count(d.spectra_id) from sample_info a, meta_key b,samples c,bin d where a.key_id = b.key_id and b.key = 'Species' and a.sample_id = c.sample_id and d.sample_id = c.sample_id group by a.value

ri correction successful

select count(*) from samples where visible = 'TRUE' and correction_failed = 'FALSE'

ri correction successful

select count(*) from samples where visible = 'TRUE' and correction_failed = 'FALSE'

named bins

select count(bin_id) from bin where name = TO_CHAR(bin_id,'FM9999999999')

distribution of similarities


select count("range"),"range" from (
select
case
when match >= 0 and match <= 100 then '0 - 100' when match > 100 and match <= 200 then '101 - 200' when match > 200 and match <= 300 then '201 - 300' when match > 300 and match <= 400 then '301 - 400' when match > 400 and match <= 500 then '401 - 500' when match > 500 and match <= 600 then '501 - 600' when match > 600 and match <= 700 then '601 - 700' when match > 700 and match <= 800 then '701 - 800' when match > 800 and match <= 900 then '801 - 900' when match > 900 and match <= 1000 then '901 - 1000' end AS "range" from spectra ) a group by "range"

grails - provide a dynamically generated file as download

today I had the rather simple request

generate a file on the fly and provide it as download.

Well the easiest solution was to just create a closure in a controller and set the content type in there.



def download = {

File file = new File(params.file)
response.setHeader "Content-disposition", "attachment; filename=${file.name}.txt"
response.contentType = 'text-plain'
response.outputStream << file.text response.outputStream.flush() }