LAMPIRAN
24
Lampiran 1 SQL pembangunan Indexing Spasial ######### insert ROOT (level 4) ########## INSERT INTO polygons_rtree (son_id, son_mbr) SELECT nama, mbr FROM polygons_mbr WHERE keterangan = 'indonesia' UPDATE polygons_rtree SET page_id = 'ROOT' , page_lev = '4' WHERE page_id is NULL; ########## insert INDONESIA (level 3) ########### INSERT INTO polygons_rtree (page_id, son_id, son_mbr) SELECT parent,son,mbr FROM (SELECT parent.nama AS parent , son.nama AS son, ST_Within(son.mbr,parent.mbr) AS result, son.mbr FROM (SELECT nama, mbr FROM polygons_mbr WHERE keterangan = 'pulau') AS son , (SELECT nama,mbr FROM polygons_mbr WHERE keterangan = 'indonesia') AS parent) AS compare WHERE result is true ORDER BY parent; UPDATE polygons_rtree SET page_lev = '3' WHERE page_lev is NULL; ########## insert PULAU (level 2) ############ INSERT INTO polygons_rtree (page_id, son_id, son_mbr) SELECT parent,son,mbr FROM (SELECT parent.nama AS parent , son.nama AS son, ST_Within(son.mbr,parent.mbr) AS result, son.mbr FROM (SELECT nama, mbr FROM polygons_mbr WHERE keterangan = 'provinsi') AS son , (SELECT nama,mbr FROM polygons_mbr WHERE keterangan = 'pulau') AS parent) AS compare WHERE result is true ORDER BY parent; UPDATE polygons_rtree SET page_lev = '2' WHERE page_lev is NULL; ########## insert PROVINSI (level 1) ############ INSERT INTO polygons_rtree (page_id, son_id, son_mbr) SELECT parent,son,mbr FROM (SELECT parent.nama AS parent , son.nama AS son, ST_Within(son.mbr,parent.mbr) AS result, son.mbr FROM (SELECT nama, mbr FROM polygons_mbr WHERE keterangan = 'kabupaten') AS son , (SELECT nama,mbr FROM polygons_mbr WHERE keterangan = 'provinsi') AS parent) AS compare WHERE result is true ORDER BY parent; UPDATE polygons_rtree SET page_lev = '1' WHERE page_lev is NULL; Lampiran 2 SQL pembangunan Indexing Temporal ###### insert ROOT (level 3) ############ INSERT INTO temp_tree(parent, son) SELECT parent.time, child.time FROM (SELECT * FROM temp_list WHERE ket = 'ROOT') AS parent , (SELECT * FROM temp_list WHERE ket = 'tahun') AS child WHERE child.time <= parent.time ORDER BY parent.time UPDATE temp_tree SET lev = '3' WHERE lev is NULL; ###### insert Tahun (level 2) ############ INSERT INTO temp_tree(parent, son) SELECT parent.time, child.time FROM (SELECT time, EXTRACT(year FROM time) AS year FROM temp_list WHERE ket = 'tahun') AS parent , (SELECT time,EXTRACT(year FROM time) AS year FROM temp_list WHERE ket = 'bulan') AS child WHERE child.year = parent.year ORDER BY parent.time, child.time UPDATE temp_tree SET lev = '2' WHERE lev is NULL; ###### insert Bulan (level 1) ############ INSERT INTO temp_tree(parent, son) SELECT parent.time, child.time FROM (SELECT time, EXTRACT(year FROM time) AS year , EXTRACT(month FROM time) AS month FROM temp_list WHERE ket = 'bulan') AS parent , (SELECT time,EXTRACT(year FROM time) AS year , EXTRACT(month FROM time) AS month FROM temp_list WHERE ket = 'hari') AS child 25
WHERE child.year = parent.year and child.month = parent.month ORDER BY parent.time, child.time UPDATE temp_tree SET lev = '1' WHERE lev is NULL; Lampiran 3 Hasil perbandingan waktu pencarian dengan kueri spasial Grafik perbandingan waktu pencarian dengan kueri spasial pada level provinsi 1200 1000 800 600 400 200
INDEX NON-INDEX
0 (ms)
Grafik perbandingan waktu pencarian dengan kueri spasial pada level kabupaten 150 145 140 135 130 125
INDEX
120
NON-INDEX
115 110 (ms)
26
Lampiran 4 Kueri Temporal ######### LEVEL TAHUN ############# # kueri temporal dengan indexing SELECT * FROM hotspot WHERE vs in ( SELECT son FROM temp_tree WHERE parent in (SELECT son FROM temp_tree WHERE parent = '2004-1231')) # kueri temporal tanpa indexing SELECT * FROM hotspot WHERE vs between '01-01-2004' and '31-12-2004'
######## LEVEL BULAN ############# # kueri temporal dengan indexing SELECT * FROM hotspot WHERE vs in (SELECT son FROM temp_tree WHERE parent = '2004-08-01') # kueri temporal tanpa indexing SELECT * FROM hotspot WHERE vs between '01-08-2004' and '31-08-2004'
######## LEVEL HARI ############## # kueri temporal dengan indexing SELECT * FROM hotspot WHERE vs in (SELECT son FROM temp_tree WHERE son = '2004-08-01') # kueri temporal tanpa indexing SELECT * FROM hotspot WHERE vs = '01-08-2004'
Lampiran 5 Perbandingan waktu pencarian pada kueri spatiotemporal Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tahun tertentu di provinsi tertentu 3000
1200
2500
1000
2000
800
1500
600
1000
400
500
200
0
0
(ms)
(ms)
INDEX
INDEX
NON-INDEX
NON-INDEX
(a) Menggunakan indexing spatial dan temporal
(b) Menggunakan indexing spatial saja
27
1800 1600 1400 1200 1000 800 600 400 200 0 (ms)
INDEX NONINDEX (c) Menggunakan indexing temporal saja Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tahun tertentu di kabupaten tertentu (ms)
INDEX
NON-INDEX
(ms)
300
INDEX
NON-INDEX
180 160 140 120 100 80 60 40 20 0
250 200 150 100 50 0
(a) Menggunakan indexing spatial dan temporal
(b) Menggunakan indexing spatial saja
250 200 150 100 50 0
INDEX NON-INDEX
(ms)
(c) Menggunakan indexing temporal saja 28
Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tahun dan bulan tertentu di pulau tertentu 1200 1000 800 600 400 200 0
800 700 600 500 400 300 200 100 0
(ms)
(ms)
INDEX
NON-INDEX
INDEX
(a) Menggunakan indexing spatial dan temporal
NON-INDEX
(b) Menggunakan indexing spatial saja
4000 3500 3000 2500 2000 1500 1000 500 0 (ms)
INDEX
NON-INDEX
(c) Menggunakan indexing temporal saja Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tahun dan bulan tertentu di provinsi tertentu 3000 2500 2000 1500 1000 500 0
700 600 500 400 300 200 100 0
(ms)
(ms)
INDEX
INDEX NON-INDEX
(a) Menggunakan indexing spatial dan temporal
NON-INDEX
(b) Menggunakan indexing spatial saja 29
1400 1200 1000 800 600 400 200 0 (ms)
INDEX NON-INDEX
(c) Menggunakan indexing temporal saja Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tahun dan bulan tertentu di kabupaten tertentu 200 180 160 140 120 100 80 60 40 20 0
160 155 150 145 140 135 130 125 120 115
(ms)
(ms)
INDEX
NON-INDEX
INDEX
(a) Menggunakan indexing spatial dan temporal
NON-INDEX
(b) Menggunakan indexing spatial saja
250 200 150 100 50 0 (ms)
INDEX
NON-INDEX
(c) Menggunakan indexing temporal saja
30
Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tanggal tertentu di pulau tertentu 145 140 135 130 125 120 115 110 105
145 140 135 130 125 120 115 110 105
(ms)
(ms)
INDEX
NON-INDEX
INDEX
NON-INDEX
(a) Menggunakan indexing spatial dan temporal
(b) Menggunakan indexing spatial saja
135 130 125 120 115 110 105 (ms)
INDEX
NON-INDEX
(c) Menggunakan indexing temporal saja Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tanggal tertentu di provinsi tertentu 140 135 130 125 120 115 110 105
140 135 130 125 120 115 110 105 (ms)
(ms)
INDEX
NON-INDEX
INDEX
NON-INDEX
(a) Menggunakan indexing spatial dan temporal
(b) Menggunakan indexing spatial saja
31
126 124 122 120 118 116 114 112 (ms)
INDEX
NON-INDEX
(c) Menggunakan indexing temporal saja Grafik perbandingan waktu pencarian dengan kueri mencari hotspot pada tanggal tertentu di kabupaten tertentu 130 128 126 124 122 120 118 116 114
130
(ms)
(ms)
125 120 115 110 105
INDEX
NON-INDEX
(a) Menggunakan indexing spatial dan temporal
INDEX
NON-INDEX
(b) Menggunakan indexing spatial saja
135 130 125 120 115 110 105 (ms)
INDEX
NON-INDEX
(c) Menggunakan indexing temporal saja
32
