bioseg : Bioseg Usage

August 18, 2007, at 08:34 PM by 82.69.238.134 -

Changed lines 3-4 from:

For a 'feature' table with fmin and fmax columns, find all rows overlapping
the base range 2000..3000:

to:

For a 'feature' table with fmin and fmax columns, find all rows overlapping the base range 2000..3000:

August 18, 2007, at 10:36 AM by 131.111.60.110 -

Changed lines 16-18 from:

[[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2FREADME.bioseg
| README]] for a full list of operators.

to:

[[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2FREADME.bioseg | README]] for a full list of operators.

Changed lines 73-74 from:

[[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2Fsql%2Fbioseg.sql
| sql/bioseg.sql]].

to:

[[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2Fsql%2Fbioseg.sql | sql/bioseg.sql]].

August 18, 2007, at 10:35 AM by 131.111.60.110 -

Changed lines 3-4 from:

For a 'feature' table with fmin and fmax columns, find all rows overlapping the base range 2000..3000:

to:

For a 'feature' table with fmin and fmax columns, find all rows overlapping
the base range 2000..3000:

Changed lines 6-7 from:

select * from feature
where '2000..3000'::bioseg && bioseg_create(fmin, fmax);

to:

SELECT * FROM feature
WHERE '2000..3000'::bioseg && bioseg_create(fmin, fmax);

Changed lines 11-12 from:

select * from feature
where bioseg_create(2000, 3000) && bioseg_create(fmin, fmax);

to:

SELECT * FROM feature
WHERE bioseg_create(2000, 3000) && bioseg_create(fmin, fmax);

Changed lines 15-16 from:

The "&&" is the overlaps operator, the [[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2FREADME.bioseg | README]] for a full list of operators.

to:

The "&&" is the overlaps operator, the
[[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2FREADME.bioseg
| README]] for a full list of operators.

Changed line 23 from:

create index test on feature using gist (bioseg_create(fmin, fmax));

to:

CREATE INDEX test ON feature USING gist (bioseg_create(fmin, fmax));

Changed line 31 from:

CREATE INDEX test_bioseg_index on test_bioseg using gist (seg);

to:

CREATE INDEX test_bioseg_index ON test_bioseg USING gist (seg);

Changed line 35 from:

insert into test_bioseg values (1, 'name1', '501..2500'::bioseg);

to:

INSERT INTO test_bioseg VALUES (1, 'name1', '501..2500'::bioseg);

Changed line 39 from:

insert into test_bioseg values (1, 'name2', bioseg_create(2001, 3000));

to:

INSERT INTO test_bioseg VALUES (1, 'name2', bioseg_create(2001, 3000));

Changed line 45 from:

select * from test_bioseg where seg && '501..2500'::bioseg;

to:

SELECT * FROM test_bioseg WHERE seg && '501..2500'::bioseg;

Changed lines 59-62 from:

select id, name, bioseg_lower(seg), bioseg_upper(seg), bioseg_size(seg)
from test_bioseg
where seg && '501..2500'::bioseg
order by bioseg_size(seg);

to:

SELECT id, name, bioseg_lower(seg), bioseg_upper(seg), bioseg_size(seg)
FROM test_bioseg
WHERE seg && '501..2500'::bioseg
ORDER BY bioseg_size(seg);

Changed lines 73-75 from:

There are more example queries in [[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2Fsql%2Fbioseg.sql | sql/bioseg.sql]].

to:

There are more example queries in
[[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2Fsql%2Fbioseg.sql
| sql/bioseg.sql]].

August 18, 2007, at 10:34 AM by 131.111.60.110 -

Added line 28:

CREATE INDEX test_bioseg_index on test_bioseg using gist (seg);

August 18, 2007, at 10:31 AM by 131.111.60.110 -

Changed line 6 from:

where bioseg_create(2000, 3000) && bioseg_create(fmin, fmax);

to:

where '2000..3000'::bioseg && bioseg_create(fmin, fmax);

Changed line 11 from:

where '2000..3000'::bioseg && bioseg_create(fmin, fmax);

to:

where bioseg_create(2000, 3000) && bioseg_create(fmin, fmax);

August 18, 2007, at 10:21 AM by 131.111.60.110 -

Changed line 38 from:

!!!! Query example 1 - overlaps

to:

!!!! Query example 1 - overlaps:

August 18, 2007, at 10:20 AM by 131.111.60.110 -

Changed line 3 from:

Find all features overlapping the base range 2000..3000:

to:

For a 'feature' table with fmin and fmax columns, find all rows overlapping the base range 2000..3000:

August 18, 2007, at 10:19 AM by 131.111.60.110 -

Changed line 39 from:

The table can be queried like this:

to:

Find rows where the 'seg' bioseg overlaps the range 501..2500:

Changed line 53 from:

Or if the start, end or length is needed directly in the results:

to:

The start, end or length can be extracted if needed:

August 18, 2007, at 10:14 AM by 131.111.60.110 -

Changed line 31 from:

insert into test_bioseg values (1, 'name1', '1501..2000'::bioseg);

to:

insert into test_bioseg values (1, 'name1', '501..2500'::bioseg);

Changed line 47 from:

1 | name1 | 1501..2000

to:

1 | name1 | 501..2500

Deleted line 48:

Added line 52:

!!!! Example 2 - overlaps and ordering:

Changed line 55 from:

select id, bioseg_lower(seg), bioseg_upper(seg), bioseg_size(seg)

to:

select id, name, bioseg_lower(seg), bioseg_upper(seg), bioseg_size(seg)

Changed lines 57-58 from:

where seg && '501..2500'::bioseg;

to:

where seg && '501..2500'::bioseg
order by bioseg_size(seg);

Changed lines 62-65 from:

id | bioseg_lower | bioseg_upper | bioseg_size
----+--------------+--------------+-------------
1 | 1501 | 2000 | 500
1 | 2001 | 3000 | 1000

to:

id | name | bioseg_lower | bioseg_upper | bioseg_size
----+-------+--------------+--------------+-------------
1 | name2 | 2001 | 3000 | 1000
1 | name1 | 501 | 2500 | 2000

August 18, 2007, at 10:11 AM by 131.111.60.110 -

Added line 38:

!!!! Query example 1 - overlaps

August 18, 2007, at 10:10 AM by 131.111.60.110 -

Changed line 31 from:

insert into test_bioseg values (1, 'name1', '1001..2000'::bioseg);

to:

insert into test_bioseg values (1, 'name1', '1501..2000'::bioseg);

Changed line 35 from:

insert into test_bioseg values (1, 'name2', bioseg_create(2501, 3000));

to:

insert into test_bioseg values (1, 'name2', bioseg_create(2001, 3000));

Changed lines 46-47 from:

1 | name1 | 1001..2000
1 | name2 | 2501..3500

to:

1 | name1 | 1501..2000
1 | name2 | 2001..3000

Changed lines 59-66 from:

to:

[@
id | bioseg_lower | bioseg_upper | bioseg_size
----+--------------+--------------+-------------
1 | 1501 | 2000 | 500
1 | 2001 | 3000 | 1000
(2 rows)
@]

August 18, 2007, at 10:07 AM by 131.111.60.110 -

Changed line 31 from:

insert into test_bioseg values (1, 'name1', '1000..2000'::bioseg);

to:

insert into test_bioseg values (1, 'name1', '1001..2000'::bioseg);

Changed line 35 from:

insert into test_bioseg values (1, 'name2', bioseg_create(2500, 4000));

to:

insert into test_bioseg values (1, 'name2', bioseg_create(2501, 3000));

Changed line 40 from:

select * from test_bioseg where seg && '200..3000'::bioseg;

to:

select * from test_bioseg where seg && '501..2500'::bioseg;

Changed lines 43-44 from:

to:

[@
id | name | seg
----+-------+------------
1 | name1 | 1001..2000
1 | name2 | 2501..3500
(2 rows)
@]

Or if the start, end or length is needed directly in the results:
[@
select id, bioseg_lower(seg), bioseg_upper(seg), bioseg_size(seg)
from test_bioseg
where seg && '501..2500'::bioseg;
@]
Giving these results:

August 18, 2007, at 10:00 AM by 131.111.60.110 -

Changed line 31 from:

insert into test_bioseg values (1, 'some_name', '1000..2000'::bioseg);

to:

insert into test_bioseg values (1, 'name1', '1000..2000'::bioseg);

Changed line 33 from:

then queried:

to:

or by using a function to create the values:

Changed line 35 from:

select * from test_bioseg where seg && '200..3000'::bioseg;

to:

insert into test_bioseg values (1, 'name2', bioseg_create(2500, 4000));

Added lines 38-44:

The table can be queried like this:
[@
select * from test_bioseg where seg && '200..3000'::bioseg;
@]
Giving these results:

August 17, 2007, at 12:40 PM by 131.111.60.110 -

Added line 13:

August 17, 2007, at 12:05 PM by 131.111.60.110 -

Added lines 1-37:

!!!Query Example

Find all features overlapping the base range 2000..3000:
[@
select * from feature
where bioseg_create(2000, 3000) && bioseg_create(fmin, fmax);
@]
or:
[@
select * from feature
where '2000..3000'::bioseg && bioseg_create(fmin, fmax);
@]
The "&&" is the overlaps operator, the [[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2FREADME.bioseg | README]] for a full list of operators.

!!!Index Creation

The speed of the query above will be improved by creating a function index:
[@
create index test on feature using gist (bioseg_create(fmin, fmax));
@]

!!!Using the bioseg type directly

The bioseg type can be used as a column type. eg.
[@
CREATE TABLE test_bioseg (id integer, name text, seg bioseg);
@]
and populated like this:
[@
insert into test_bioseg values (1, 'some_name', '1000..2000'::bioseg);
@]
then queried:
[@
select * from test_bioseg where seg && '200..3000'::bioseg;
@]

There are more example queries in [[http://bioinformatics.org/websvn/filedetails.php?repname=bioseg&path=%2Ftrunk%2Fsql%2Fbioseg.sql | sql/bioseg.sql]].

Edit SideBar \| Edit Page \| Page History	Page last modified on August 18, 2007, at 08:34 PM	Recent Changes (All) \| Admin

Main.BiosegUsage History