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:

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:

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 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 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 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 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

Query example 1 - overlaps

Query example 1 - overlaps:

Example 2 - overlaps and ordering:

Query example 1 - overlaps

Query Example

Index Creation

Using the bioseg type directly