| 1 |
-- |
| 2 |
-- functions operating on featureloc ranges |
| 3 |
-- |
| 4 |
|
| 5 |
-- create a point |
| 6 |
CREATE OR REPLACE FUNCTION create_point (bigint, bigint) RETURNS point AS |
| 7 |
'SELECT point ($1, $2)' |
| 8 |
LANGUAGE 'sql'; |
| 9 |
|
| 10 |
-- create a range box |
| 11 |
-- (make this immutable so we can index it) |
| 12 |
CREATE OR REPLACE FUNCTION boxrange (bigint, bigint) RETURNS box AS |
| 13 |
'SELECT box (create_point(0, $1), create_point($2,500000000))' |
| 14 |
LANGUAGE 'sql' IMMUTABLE; |
| 15 |
|
| 16 |
-- create a query box |
| 17 |
CREATE OR REPLACE FUNCTION boxquery (bigint, bigint) RETURNS box AS |
| 18 |
'SELECT box (create_point($1, $2), create_point($1, $2))' |
| 19 |
LANGUAGE 'sql' IMMUTABLE; |
| 20 |
|
| 21 |
--functional index that depends on the above functions |
| 22 |
CREATE INDEX binloc_boxrange ON featureloc USING GIST (boxrange(fmin, fmax)); |
| 23 |
|
| 24 |
|
| 25 |
CREATE OR REPLACE FUNCTION featureloc_slice(bigint, bigint) RETURNS setof featureloc AS |
| 26 |
'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)' |
| 27 |
LANGUAGE 'sql'; |
| 28 |
|
| 29 |
CREATE OR REPLACE FUNCTION featureloc_slice(varchar, bigint, bigint) |
| 30 |
RETURNS setof featureloc AS |
| 31 |
'SELECT featureloc.* |
| 32 |
FROM featureloc |
| 33 |
INNER JOIN feature AS srcf ON (srcf.feature_id = featureloc.srcfeature_id) |
| 34 |
WHERE boxquery($2, $3) @ boxrange(fmin,fmax) |
| 35 |
AND srcf.name = $1 ' |
| 36 |
LANGUAGE 'sql'; |
| 37 |
|
| 38 |
CREATE OR REPLACE FUNCTION featureloc_slice(bigint, bigint, bigint) |
| 39 |
RETURNS setof featureloc AS |
| 40 |
'SELECT * |
| 41 |
FROM featureloc |
| 42 |
WHERE boxquery($2, $3) @ boxrange(fmin,fmax) |
| 43 |
AND srcfeature_id = $1 ' |
| 44 |
LANGUAGE 'sql'; |
| 45 |
|
| 46 |
|
| 47 |
-- can we not just do these as views? |
| 48 |
CREATE OR REPLACE FUNCTION feature_overlaps(bigint) |
| 49 |
RETURNS setof feature AS |
| 50 |
'SELECT feature.* |
| 51 |
FROM feature |
| 52 |
INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) |
| 53 |
INNER JOIN featureloc AS y ON (y.feature_id = $1) |
| 54 |
WHERE |
| 55 |
x.srcfeature_id = y.srcfeature_id AND |
| 56 |
( x.fmax >= y.fmin AND x.fmin <= y.fmax ) ' |
| 57 |
LANGUAGE 'sql'; |
| 58 |
|
| 59 |
CREATE OR REPLACE FUNCTION feature_disjoint_from(bigint) |
| 60 |
RETURNS setof feature AS |
| 61 |
'SELECT feature.* |
| 62 |
FROM feature |
| 63 |
INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) |
| 64 |
INNER JOIN featureloc AS y ON (y.feature_id = $1) |
| 65 |
WHERE |
| 66 |
x.srcfeature_id = y.srcfeature_id AND |
| 67 |
( x.fmax < y.fmin OR x.fmin > y.fmax ) ' |
| 68 |
LANGUAGE 'sql'; |
| 69 |
|
| 70 |
|
| 71 |
|
| 72 |
--Evolution of the methods found in range.plpgsql (C. Pommier) |
| 73 |
--Goal : increase performances of segment fetching |
| 74 |
-- Implies to optimise featureloc_slice |
| 75 |
|
| 76 |
--Background : The existing featureloc_slice uses uses a spatial rtree index. The spatial objects used are a boxrange ((0,fmin), (fmax,500000000)) and a boxquery ((fmin,fmax),(fmin,fmax)) . The boxranges are indexed. |
| 77 |
-- To speed up things (for gbrowse) featureloc_slice has been overiden to filter simultaneously on the boxrange and the srcfeature_id. This gives good results. |
| 78 |
-- The goal here is to push this logic further and to include the srcfeature_id filter directly into the boxrange object. We propose to consider the following boxs : |
| 79 |
-- boxrange : ((srcfeature_id,fmin),(srcfeature_id,fmax)) |
| 80 |
-- boxquery : ((srcfeature_id,fmin),(srcfeature_id,fmax)) |
| 81 |
|
| 82 |
|
| 83 |
|
| 84 |
CREATE OR REPLACE FUNCTION boxrange (bigint, bigint, bigint) RETURNS box AS |
| 85 |
'SELECT box (create_point($1, $2), create_point($1,$3))' |
| 86 |
LANGUAGE 'sql' IMMUTABLE; |
| 87 |
|
| 88 |
-- create a query box |
| 89 |
CREATE OR REPLACE FUNCTION boxquery (bigint, bigint, bigint) RETURNS box AS |
| 90 |
'SELECT box (create_point($1, $2), create_point($1, $3))' |
| 91 |
LANGUAGE 'sql' IMMUTABLE; |
| 92 |
|
| 93 |
CREATE INDEX binloc_boxrange_src ON featureloc USING GIST (boxrange(srcfeature_id,fmin, fmax)); |
| 94 |
|
| 95 |
CREATE OR REPLACE FUNCTION featureloc_slice(bigint, bigint, bigint) |
| 96 |
RETURNS setof featureloc AS |
| 97 |
'SELECT * |
| 98 |
FROM featureloc |
| 99 |
WHERE boxquery($1, $2, $3) && boxrange(srcfeature_id,fmin,fmax)' |
| 100 |
LANGUAGE 'sql'; |