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.