svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / main / resources / org / gvsig / sqlite / dal / sqltemplates / rtree.sql @ 47606
History | View | Annotate | Download (4.62 KB)
1 |
|
---|---|
2 |
@begin create_rtree_table
|
3 |
@param table_name
|
4 |
@param geom_name
|
5 |
CREATE VIRTUAL TABLE "rtree_%table_name$s_%geom_name$s" USING rtree(id, minx, maxx, miny, maxy) |
6 |
@end create_rtree_table
|
7 |
|
8 |
@begin create_trigger_rtree_delete
|
9 |
@param table_name
|
10 |
@param geom_name
|
11 |
@param id_name
|
12 |
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_delete" AFTER DELETE ON "%table_name$s" |
13 |
WHEN old."%geom_name$s" NOT NULL |
14 |
BEGIN
|
15 |
DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID = OLD."%id_name$s"; |
16 |
END
|
17 |
@end create_trigger_rtree_delete
|
18 |
|
19 |
@begin create_trigger_rtree_insert
|
20 |
@param table_name
|
21 |
@param geom_name
|
22 |
@param id_name
|
23 |
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_insert" AFTER INSERT ON "%table_name$s" |
24 |
WHEN (new."%geom_name$s" NOT NULL AND NOT ST_IsEmpty(NEW."%geom_name$s")) |
25 |
BEGIN
|
26 |
INSERT OR REPLACE INTO "rtree_%table_name$s_%geom_name$s" VALUES ( |
27 |
NEW."%id_name$s",
|
28 |
ST_MinX(NEW."%geom_name$s"), ST_MaxX(NEW."%geom_name$s"), |
29 |
ST_MinY(NEW."%geom_name$s"), ST_MaxY(NEW."%geom_name$s") |
30 |
); |
31 |
END
|
32 |
@end create_trigger_rtree_insert
|
33 |
|
34 |
@begin create_trigger_rtree_update1
|
35 |
@param table_name
|
36 |
@param geom_name
|
37 |
@param id_name
|
38 |
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_update1" AFTER UPDATE OF "%geom_name$s" ON "%table_name$s" |
39 |
WHEN OLD."%id_name$s" = NEW."%id_name$s" AND |
40 |
(NEW."%geom_name$s" NOTNULL AND NOT ST_IsEmpty(NEW."%geom_name$s")) |
41 |
BEGIN
|
42 |
INSERT OR REPLACE INTO "rtree_%table_name$s_%geom_name$s" VALUES ( |
43 |
NEW."%id_name$s",
|
44 |
ST_MinX(NEW."%geom_name$s"), ST_MaxX(NEW."%geom_name$s"), |
45 |
ST_MinY(NEW."%geom_name$s"), ST_MaxY(NEW."%geom_name$s") |
46 |
); |
47 |
END
|
48 |
@end create_trigger_rtree_update1
|
49 |
|
50 |
|
51 |
@begin create_trigger_rtree_update2
|
52 |
@param table_name
|
53 |
@param geom_name
|
54 |
@param id_name
|
55 |
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_update2" AFTER UPDATE OF "%geom_name$s" ON "%table_name$s" |
56 |
WHEN OLD."%id_name$s" = NEW."%id_name$s" AND |
57 |
(NEW."%geom_name$s" ISNULL OR ST_IsEmpty(NEW."%geom_name$s")) |
58 |
BEGIN
|
59 |
DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID = OLD."%id_name$s"; |
60 |
END
|
61 |
@end create_trigger_rtree_update2
|
62 |
|
63 |
@begin create_trigger_rtree_update3
|
64 |
@param table_name
|
65 |
@param geom_name
|
66 |
@param id_name
|
67 |
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_update3" AFTER UPDATE ON "%table_name$s" |
68 |
WHEN OLD."%id_name$s" != NEW."%id_name$s" AND |
69 |
(NEW."%geom_name$s" NOTNULL AND NOT ST_IsEmpty(NEW."%geom_name$s")) |
70 |
BEGIN
|
71 |
DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID = OLD."%id_name$s"; |
72 |
INSERT OR REPLACE INTO "rtree_%table_name$s_%geom_name$s" VALUES ( |
73 |
NEW."%id_name$s",
|
74 |
ST_MinX(NEW."%geom_name$s"), ST_MaxX(NEW."%geom_name$s"), |
75 |
ST_MinY(NEW."%geom_name$s"), ST_MaxY(NEW."%geom_name$s") |
76 |
); |
77 |
END
|
78 |
@end create_trigger_rtree_update3
|
79 |
|
80 |
@begin create_trigger_rtree_update4
|
81 |
@param table_name
|
82 |
@param geom_name
|
83 |
@param id_name
|
84 |
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_update4" AFTER UPDATE ON "%table_name$s" |
85 |
WHEN OLD."%id_name$s" != NEW."%id_name$s" AND |
86 |
(NEW."%geom_name$s" ISNULL OR ST_IsEmpty(NEW."%geom_name$s")) |
87 |
BEGIN
|
88 |
DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID IN (OLD."%id_name$s", NEW."%id_name$s"); |
89 |
END
|
90 |
@end create_trigger_rtree_update4
|
91 |
|
92 |
|
93 |
@begin create_table_gpkg_extensions
|
94 |
CREATE TABLE IF NOT EXISTS gpkg_extensions ( table_name TEXT, column_name TEXT, extension_name TEXT NOT NULL, definition TEXT NOT NULL, scope TEXT NOT NULL, CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) ) |
95 |
@end create_table_gpkg_extensions
|
96 |
|
97 |
@begin insert_into_table_gpkg_extensions
|
98 |
@param table_name
|
99 |
@param geom_name
|
100 |
INSERT INTO "gpkg_extensions" ( |
101 |
"table_name",
|
102 |
"column_name",
|
103 |
"extension_name",
|
104 |
"definition",
|
105 |
"scope"
|
106 |
) VALUES (
|
107 |
'%table_name$s',
|
108 |
'%geom_name$s',
|
109 |
'gpkg_rtree_index',
|
110 |
'http://www.geopackage.org/spec/#extension_rtree',
|
111 |
'read-write'
|
112 |
) |
113 |
@end insert_into_table_gpkg_extensions
|
114 |
|
115 |
|
116 |
@begin bboxintersects
|
117 |
@param table_name
|
118 |
@param id_name
|
119 |
@param geom_name
|
120 |
@param minX
|
121 |
@param minY
|
122 |
@param maxX
|
123 |
@param maxY
|
124 |
("%id_name$s" IN (SELECT "rtree_%table_name$s_%geom_name$s"."id" FROM "rtree_%table_name$s_%geom_name$s" WHERE "rtree_%table_name$s_%geom_name$s"."minx" <= (%maxX$s) AND "rtree_%table_name$s_%geom_name$s"."miny" <= (%maxY$s) AND "rtree_%table_name$s_%geom_name$s"."maxx" >= (%minX$s) AND "rtree_%table_name$s_%geom_name$s"."maxy" >= (%minY$s))) |
125 |
@end bboxintersects
|
126 |
|
127 |
@begin bbox_of_index
|
128 |
@param table_name
|
129 |
@param geom_name
|
130 |
select min(minx) as minx, min(miny) as miny, max(maxx) as maxx, max(maxy) as maxy, count(*) as rows from "rtree_%table_name$s_%geom_name$s" |
131 |
@end bbox_of_index
|
132 |
|
133 |
@begin has_index
|
134 |
@param table_name
|
135 |
@param geom_name
|
136 |
select count(1) from gpkg_extensions where table_name = '%table_name$s' and column_name = '%geom_name$s' and extension_name = 'gpkg_rtree_index' |
137 |
@end has_index
|
138 |
|
139 |
|
140 |
|