On Thu, Mar 19, 2009 at 11:08 AM, Wacek Kusnierczyk <
Waclaw.Marcin.Kusnierczyk@idi.ntnu.no> wrote:

> hello Jack, Sean, and others,
>
> i have an observation and a question related to GEOmetadb.  the queries
> below are made directly in sqlite, but could have easily be done within
> R, for example.
>
> the gds (data sets) table contains two fields related to organisms:
>
>    sqlite GEOmetadb.sqlite 'pragma table_info(gds)' | grep organism
>    # 7|platform_organism|TEXT|0||0
>    # 10|sample_organism|TEXT|0||0
>
> the gse (data series) table does not contain any such field:
>
>    sqlite GEOmetadb.sqlite 'pragma table_info(gse)' | grep organism
>
> the gpl (platform) table contains one such field:
>
>    sqlite GEOmetadb.sqlite 'pragma table_info(gpl)' | grep organism
>    # 8|organism|TEXT|0||0
>
> (and likewise for the gsm (sample) table).  i would like to retrieve all
> data *series* (not data sets) where gene expression in a specific
> organism was investigated.  in the vignette to the GEOmetadb
> Bioconductor package (as well as the GEOtools Matlab one), you give the
> following example:
>
>    "we would like to find all the human breast cancer-related
> Affymetrix gene expression GEO series."
>
> and then you use the gse_gpl table to retrieve the *platform* organism
> -- which doesn't seem quite right, because you can hybridize non-human
> samples to human arrays, as well as human samples to non-human arrays:
>
>    sqlite GEOmetadb.sqlite '
>        select gds, sample_organism as so, platform_organism as po
>            from gds
>            where (so like "%homo%" and po not like "%homo%")
>                or (so not like "%homo%" and po like "%homo%")'
>
> (and likewise for data series, but the query would be a little bit more
> involved.)
>
> the comment:  you may want to update the documentation, unless i
> misunderstood your intentions.
>

Good point.  Technically, you can hyb samples from one organism to a
platform from another organism.  We should add a sentence to the vignette to
that effect.  Thanks.


>
> the question:  is there no direct way to check the sample organism for
> data series entries in GEO?  it would be very useful to filter gse by
> sample organism, without having to join the table with gse_gsm and gsm.
> you already have it for gds, it would be pretty simple to add an
> analogous field to gse.  (and likewise for platform organism).  surely,
> a gse may include more than one organism in the platforms or samples,
> but that's what we have in gds already:
>
>    sqlite GEOmetadb.sqlite '
>        select platform_organism as po, sample_organism as so
>            from gds
>            where po like "%,%" or so like "%,%"'
>
> so i would like to be able to
>
>    sqlite GEOmetadb.sqlite '
>        select gds, sample_organism as so, platform_organism as po from gds
>          where so like "%homo%"
>            and po like "%homo%"'
>
> rather than
>
>    sqlite GEOmetadb.sqlite '
>        select distinct gse.gse, gsm.
>            from gse
>                join gse_gsm on gse.gse = gse_gsm.gse
>                join gsm on gsm.gsm = gse_gsm.gsm
>                join gse_gpl on gse.gse = gse_gpl.gse
>                join gpl on gpl.gpl = gse_gpl.gpl
>            where (gsm.organism_ch1 like "%homo%" and gsm.organism_ch2
> like "%homo%")
>                and gpl.organism like "%homo%"'
>
> after all, gds are backed by gse, so if gds entries can have
> sample_organism and platform_organism, why could not gse have them too?
>

A GSE can have samples from multiple platforms and can, therefore, represent
multiple organisms.  That is why doing the join to platform (if you are
willing to trust that the platform was used for the specified organism) or a
join to samples is necessary.  A GDS, on the other hand, does not have that
characteristic and represents a set of samples from a single platform.

Sean

	[[alternative HTML version deleted]]

