Debian Codegraph @

modules/sequence/functions/range.plpgsql

raw
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';