Main»Bioseg Usage

Bioseg Usage

Query Example

For a 'feature' table with fmin and fmax columns, find all rows overlapping the base range 2000..3000:
  SELECT * FROM feature
           WHERE '2000..3000'::bioseg && bioseg_create(fmin, fmax);
or:
  SELECT * FROM feature
           WHERE bioseg_create(2000, 3000) && 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);
  CREATE INDEX test_bioseg_index ON test_bioseg USING gist (seg);
and populated like this:
  INSERT INTO test_bioseg VALUES (1, 'name1', '501..2500'::bioseg);
or by using a function to create the values:
  INSERT INTO test_bioseg VALUES (1, 'name2', bioseg_create(2001, 3000));

Query example 1 - overlaps:

Find rows where the 'seg' bioseg overlaps the range 501..2500:
  SELECT * FROM test_bioseg WHERE seg && '501..2500'::bioseg;
Giving these results:
   id | name  |    seg     
  ----+-------+------------
    1 | name1 | 501..2500
    1 | name2 | 2001..3000
  (2 rows)

Example 2 - overlaps and ordering:

The start, end or length can be extracted if needed:
  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);
Giving these results:
   id | name  | bioseg_lower | bioseg_upper | bioseg_size 
  ----+-------+--------------+--------------+-------------
    1 | name2 |         2001 |         3000 |        1000
    1 | name1 |          501 |         2500 |        2000
  (2 rows)

There are more example queries in sql/bioseg.sql.