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