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 |
|