Daftar Referensi Munir, Rinaldi. (2003). Diktat Kuliah IF2153 Matematika Diskrit Edisi Ketiga. Departemen Teknik Informatika, Institut Teknologi Bandung. Fathansyah. (1999). Basis Data. Penerbit Informatika, Bandung. Prahasta, E. (2001). Sistem Informasi Geografis Konsep-konsep Dasar. Penerbit Informatika, Bandung. Suwardhi, deni at al.2005. Utilization of Graph Theory and GIS on Web-based Application for Optimal Route Determination. Spatial Information Science & Technology (SISTECH) Workshop Remote Sensing and Spatial Information Science Research Group Geodetic Department, Institute of Technology Bandung.
63
Lampiran A
DAFTAR DATA DEFINITION LANGUANGE (DDL)
64
Perintah DDL untuk pembuatan tabel node kampus CREATE TABLE "public"."node_kampus" ( "gid" INTEGER NOT NULL, "the_geom" "public"."geometry", "id_node" VARCHAR(10), CONSTRAINT "node_kampus_pkey" PRIMARY KEY("gid"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POINT'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; CREATE TABLE "public"."net_kampus" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_edge" INTEGER, "source" INTEGER, "target" INTEGER, "length" DOUBLE PRECISION, "x1" DOUBLE PRECISION, "y1" DOUBLE PRECISION, "z1" DOUBLE PRECISION, "x2" DOUBLE PRECISION, "y2" DOUBLE PRECISION, "z2" DOUBLE PRECISION, "reverse_cost" DOUBLE PRECISION, "to_cost" DOUBLE PRECISION, "rule" TEXT, "id_edge2" VARCHAR(10), "a_source" DOUBLE PRECISION, "a_target" DOUBLE PRECISION, "delta_z" DOUBLE PRECISION, CONSTRAINT "net_kampus_pkey" PRIMARY KEY("gid"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'MULTILINESTRING'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel
gedung
CREATE TABLE "public"."gedung" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_gedung" VARCHAR(10) NOT NULL, "nm_gedung" VARCHAR(20), "id_jalan" VARCHAR(7), CONSTRAINT "gedung_pkey" PRIMARY KEY("id_gedung"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS;
65
Perintah DDL untuk pembuatan tabel ruang CREATE TABLE "public"."ruang" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_ruang" INTEGER NOT NULL, "nm_ruang" VARCHAR(100), "id_lantai" VARCHAR(8), "kondisi" VARCHAR(10), "luas" DOUBLE PRECISION, "kapasitas" SMALLINT, "dinding" VARCHAR(10), "atap" VARCHAR(10), "kusen" VARCHAR(10), "lantai" VARCHAR(15), "jml_saklar" SMALLINT, "jml_steker" SMALLINT, "jml_lampu" SMALLINT, "jns_ruang" VARCHAR(25), "ac" BOOLEAN, "fax" VARCHAR(10), "id_ruang2" VARCHAR(4), CONSTRAINT "ruang_pkey" PRIMARY KEY("id_ruang"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1), CONSTRAINT "ruang_fk" FOREIGN KEY ("id_lantai") REFERENCES "public"."lantai"("id_lantai") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) WITH OIDS; Perintah DDL untuk pembuatan tabel jalan CREATE TABLE "public"."jalan" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_jalan" VARCHAR(7) NOT NULL, "keterangan" VARCHAR(25), CONSTRAINT "jalan_pkey" PRIMARY KEY("id_jalan"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel jalan setapak CREATE TABLE "public"."jalan_setapak" ( "gid" SERIAL, "the_geom" "public"."geometry",
66
"id_setapak" VARCHAR(7) NOT NULL, "keterangan" VARCHAR(25), "id_jalan" VARCHAR(7), CONSTRAINT "jalan_setapak_pkey" PRIMARY KEY("id_setapak"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel jalur lift CREATE TABLE "public"."jalur_lift" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_jalur_lift" VARCHAR(7) NOT NULL, "keterangan" VARCHAR(25), CONSTRAINT "jalur_lift_pkey" PRIMARY KEY("id_jalur_lift"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel koridor CREATE TABLE "public"."koridor" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_koridor" VARCHAR(7) NOT NULL, "keterangan" VARCHAR(25), CONSTRAINT "koridor_pkey" PRIMARY KEY("id_koridor"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel lantai CREATE TABLE "public"."lantai" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_lantai" VARCHAR(8) NOT NULL, "nm_lantai" VARCHAR(25), "id_gedung" VARCHAR(10), CONSTRAINT "lantai_pkey" PRIMARY KEY("id_lantai"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1),
67
CONSTRAINT "lantai_fk" FOREIGN KEY ("id_gedung") REFERENCES "public"."gedung"("id_gedung") ) WITH OIDS; Perintah DDL untuk pembuatan tabel lapangan CREATE TABLE "public"."lapangan" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_lapangan" VARCHAR(10) NOT NULL, "keterangan" VARCHAR(25), CONSTRAINT "lapangan_pkey" PRIMARY KEY("id_lapangan"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel lift CREATE TABLE "public"."lift" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_lift" VARCHAR(5) NOT NULL, "keterangan" VARCHAR(50), CONSTRAINT "lift_pkey" PRIMARY KEY("id_lift"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel parkiran CREATE TABLE "public"."parkiran" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_parkiran" VARCHAR(10) NOT NULL, "keterangan" VARCHAR(25), "id_jalan" VARCHAR(7), "id_koridor" VARCHAR(7), "id_setapak" VARCHAR(7), CONSTRAINT "parkiran_pkey" PRIMARY KEY("id_parkiran"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel taman CREATE TABLE "public"."taman" (
68
"gid" SERIAL, "the_geom" "public"."geometry", "id_taman" VARCHAR(10) NOT NULL, "keterangan" VARCHAR(25), "id_gedung" VARCHAR(10), "id_jalan" VARCHAR(7), CONSTRAINT "taman_pkey" PRIMARY KEY("id_taman"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Perintah DDL untuk pembuatan tabel tangga CREATE TABLE "public"."tangga" ( "gid" SERIAL, "the_geom" "public"."geometry", "id_tangga" VARCHAR(10) NOT NULL, "keterangan" VARCHAR(25), CONSTRAINT "tangga_pkey" PRIMARY KEY("id_tangga"), CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3), CONSTRAINT "enforce_geotype_the_geom" CHECK ((geometrytype(the_geom) = 'POLYGON'::text) OR (the_geom IS NULL)), CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) ) WITH OIDS; Pembuatan fungsi A* CREATE OR REPLACE FUNCTION "public"."ginziro_asp" (geom_table varchar, source integer, target integer, dir boolean, rc boolean) RETURNS SETOF "public"."geoms_ku" AS $body$ DECLARE r record; path_result_ku record; v_id integer; e_id integer; geom geoms_ku4; arah DOUBLE PRECISION; id INTEGER; belok VARCHAR(25); query text; --id integer; BEGIN id :=0; arah :=0; query := 'SELECT net.vertex_id, net.edge_id, net.cost, '||quote_ident(geom_table)||'.the_geom, '|| quote_ident(geom_table)||'.v_source, '||
69
quote_ident(geom_table)||'.id_edge, '|| quote_ident(geom_table)||'.source, '|| quote_ident(geom_table)||'.a_source, '|| quote_ident(geom_table)||'.a_target, '|| quote_ident(geom_table)||'.delta_z as z '|| 'FROM ' || 'shortest_path_astar(''SELECT gid as id, source::integer, ' || 'target::integer, length::double precision as cost, ' || 'x1::double precision, y1::double precision, ' || 'x2::double precision, y2::double precision '; IF rc THEN query := query || ' , reverse_cost '; END IF; query := query || 'FROM ' || quote_ident(geom_table) || ' '', ' || quote_literal(source) || ' , ' || quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||''') as net LEFT JOIN ' || ---quote_ident(geom_table) || ' where edge_id = gid '; quote_ident(geom_table) || ' on (net.edge_id = '||quote_ident(geom_table)||'.gid) '; FOR path_result_ku IN EXECUTE query LOOP geom.vertex_id := path_result_ku.vertex_id; geom.gid := path_result_ku.edge_id; geom.the_geom := path_result_ku.the_geom; geom.jarak := path_result_ku.cost; --geom.delta_z := path_result_ku.z; if path_result_ku.vertex_id = path_result_ku.source then geom.a_jur := path_result_ku.a_source; geom.delta_z := round(path_result_ku.z); geom.v_source := path_result_ku.v_source; else geom.a_jur := path_result_ku.a_target; geom.delta_z := -round(path_result_ku.z); geom.v_source := -path_result_ku.v_source; end if; --arah vertikal-if (geom.v_source < -30 and geom.v_source >=-60) THEN if path_result_ku.id_edge like 'ta%' THEN geom.deskripsi2:='Menuruni Tangga'; ELSE geom.deskripsi2:='Menurun'; end if; elseif (geom.v_source > -30 and geom.v_source < 30) THEN geom.deskripsi2:='Mendatar'; elseif geom.v_source is NULL THEN geom.deskripsi2:=''; elseif (geom.v_source < 60 and geom.v_source >30) THEN if path_result_ku.id_edge like 'ta%' THEN geom.deskripsi2:='Menaiki Tangga';
70
ELSE geom.deskripsi2:='Menaik'; end if; elseif (geom.v_source < 90 and geom.v_source >60) THEN if path_result_ku.id_edge like 'lf%' THEN geom.deskripsi2:='Menaiki Lift'; ELSE geom.deskripsi2:='Curam Menaik'; end if; elseif (geom.v_source < -90 and geom.v_source > -60)THEN if path_result_ku.id_edge like 'lf%' THEN geom.deskripsi2:='Menuruni Lift'; ELSE geom.deskripsi2:='Curam Menurun'; end if; end if; --end arah vertikal-if geom.a_jur IS NOT NULL THEN arah := arah - geom.a_jur; --geom.arah := arah; if (geom.a_jur= -(arah) AND id=0) then geom.deskripsi := 'awal'; ELSE if arah < 0 then arah = arah+360; end if; IF (arah >= 0 and arah<30) THEN belok := 'Lurus'; ELSEIF (arah >= 30 and arah<60) THEN belok := 'Belok ke Kiri Depan'; ELSEIF (arah >= 60 and arah<90) THEN belok := 'Belok ke Samping Kiri'; ELSEIF (arah >= 90 and arah<120) THEN belok := 'Belok ke Samping Kiri'; ELSEIF (arah >= 120 and arah<150) THEN belok := 'Belok ke Kiri Belakang'; ELSEIF (arah >= 150 and arah<180) THEN belok := 'Belok ke Belakang'; ELSEIF (arah >= 180 and arah<210) THEN belok := 'Belok ke Belakang'; ELSEIF (arah >= 210 and arah<240) THEN belok := 'Belok ke Kanan Belakang'; ELSEIF (arah >= 240 and arah<270) THEN belok := 'Belok ke Samping Kanan'; ELSEIF (arah >= 270 and arah<300) THEN belok := 'Belok ke Samping Kanan'; ELSEIF (arah >= 300 and arah<330) THEN belok := 'Belok ke Kanan Depan'; ELSEIF (arah >= 330 and arah<=360) THEN belok := 'Lurus'; ELSEIF (arah IS NULL) THEN belok := ''; ELSE
71
belok := 'Tidak terdefinisi'; end IF; geom.deskripsi := belok; end if; ELSEIF (geom.a_jur IS NULL AND geom.gid=-1) THEN geom.deskripsi := 'akhir'; ELSE geom.deskripsi := ''; end if; geom.arah := arah; arah := geom.a_jur; id:=id+1; RETURN NEXT geom; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Pembuatan fungsi Dikstra CREATE OR REPLACE FUNCTION "public"."ginziro_dsp" (geom_table varchar, source integer, target integer, dir boolean, rc boolean) RETURNS SETOF "public"."geoms_ku" AS $body$ DECLARE r record; path_result_ku record; v_id integer; e_id integer; geom geoms_ku4; arah DOUBLE PRECISION; id INTEGER; belok VARCHAR(25); query text; --id integer; BEGIN arah :=0; query := 'SELECT net.vertex_id, net.edge_id, net.cost, '||quote_ident(geom_table)||'.the_geom, '|| quote_ident(geom_table)||'.source, '|| quote_ident(geom_table)||'.a_source, '|| quote_ident(geom_table)||'.a_target, '|| quote_ident(geom_table)||'.delta_z as z '|| 'FROM ' || 'shortest_path(''SELECT gid as id, source::integer, target::integer, ' || 'length::double precision as cost '; IF rc THEN query := query || ', reverse_cost '; END IF; query := query || 'FROM ' || quote_ident(geom_table) || ' '', ' || quote_literal(source) || ' , ' ||
72
quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||''') as net LEFT JOIN ' || ---quote_ident(geom_table) || ' where edge_id = gid '; quote_ident(geom_table) || ' on (net.edge_id = '||quote_ident(geom_table)||'.gid) '; FOR path_result_ku IN EXECUTE query LOOP geom.vertex_id := path_result_ku.vertex_id; geom.gid := path_result_ku.edge_id; geom.the_geom := path_result_ku.the_geom; geom.jarak := path_result_ku.cost; --geom.delta_z := path_result_ku.z; --arah vertikal-if (geom.v_source < -30 and geom.v_source >=-60) THEN if path_result_ku.id_edge like 'ta%' THEN geom.deskripsi2:='Menuruni Tangga'; ELSE geom.deskripsi2:='Menurun'; end if; elseif (geom.v_source > -30 and geom.v_source < 30) THEN geom.deskripsi2:='Mendatar'; elseif geom.v_source is NULL THEN geom.deskripsi2:=''; elseif (geom.v_source < 60 and geom.v_source >30) THEN if path_result_ku.id_edge like 'ta%' THEN geom.deskripsi2:='Menaiki Tangga'; ELSE geom.deskripsi2:='Menaik'; end if; elseif (geom.v_source < 90 and geom.v_source >60) THEN if path_result_ku.id_edge like 'lf%' THEN geom.deskripsi2:='Menaiki Lift'; ELSE geom.deskripsi2:='Curam Menaik'; end if; elseif (geom.v_source < -90 and geom.v_source > -60)THEN if path_result_ku.id_edge like 'lf%' THEN geom.deskripsi2:='Menuruni Lift'; ELSE geom.deskripsi2:='Curam Menurun'; end if; end if; --end arah vertikal-if path_result_ku.vertex_id = path_result_ku.source then geom.a_jur := path_result_ku.a_source; geom.delta_z := -round(path_result_ku.z); else geom.a_jur := path_result_ku.a_target; geom.delta_z := round(path_result_ku.z); end if; if geom.a_jur IS NOT NULL THEN arah := arah - geom.a_jur; geom.arah := arah; if geom.a_jur= -(arah) then geom.deskripsi := 'awal';
73
ELSE if arah < 0 then arah = arah+360; end if; IF (arah >= 0 and arah<30) THEN belok := 'Lurus'; ELSEIF (arah >= 30 and arah<60) THEN belok := 'Belok ke Kiri Depan'; ELSEIF (arah >= 60 and arah<90) THEN belok := 'Belok ke Samping Kiri'; ELSEIF (arah >= 90 and arah<120) THEN belok := 'Belok ke Samping Kiri'; ELSEIF (arah >= 120 and arah<150) THEN belok := 'Belok ke Kiri Belakang'; ELSEIF (arah >= 150 and arah<180) THEN belok := 'Belok ke Belakang'; ELSEIF (arah >= 180 and arah<210) THEN belok := 'Belok ke Belakang'; ELSEIF (arah >= 210 and arah<240) THEN belok := 'Belok ke Kanan Belakang'; ELSEIF (arah >= 240 and arah<270) THEN belok := 'Belok ke Samping Kanan'; ELSEIF (arah >= 270 and arah<300) THEN belok := 'Belok ke Samping Kanan'; ELSEIF (arah >= 300 and arah<330) THEN belok := 'Belok ke Kanan Depan'; ELSEIF (arah >= 330 and arah<=360) THEN belok := 'Lurus'; ELSE belok := 'Tidak terdefinisi'; end IF; geom.deskripsi := belok; end if; ELSE geom.deskripsi := 'akhir'; end if; arah := geom.a_jur; RETURN NEXT geom; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Pembuatan fungsi Shooting* CREATE OR REPLACE FUNCTION "public"."ginziro_ssp" (geom_table varchar, source integer, target integer, dir boolean, rc boolean) RETURNS SETOF "public"."geoms_ku" AS $body$ DECLARE r record; path_result_ku record; v_id integer;
74
e_id integer; geom geoms_ku4; arah DOUBLE PRECISION; id INTEGER; belok VARCHAR(25); query text; --id integer; BEGIN arah :=0; query := 'SELECT net.vertex_id, net.edge_id, net.cost, '||quote_ident(geom_table)||'.the_geom, '|| quote_ident(geom_table)||'.source, '|| quote_ident(geom_table)||'.a_source, '|| quote_ident(geom_table)||'.a_target, '|| quote_ident(geom_table)||'.delta_z as z '|| 'FROM ' || 'shortest_path_shooting_star(''SELECT gid as id, source::integer, ' || 'target::integer, length::double precision as cost, ' || 'x1::double precision, y1::double precision, ' || 'x2::double precision, y2::double precision, rule, to_cost '; IF rc THEN query := query || ' , reverse_cost '; END IF; query := query || 'FROM ' || quote_ident(geom_table) || ' '', ' || quote_literal(source) || ' , ' || quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||''') as net LEFT JOIN ' || ---quote_ident(geom_table) || ' where edge_id = gid '; quote_ident(geom_table) || ' on (net.edge_id = '||quote_ident(geom_table)||'.gid) '; FOR path_result_ku IN EXECUTE query LOOP geom.vertex_id := path_result_ku.vertex_id; geom.gid := path_result_ku.edge_id; geom.the_geom := path_result_ku.the_geom; geom.jarak := path_result_ku.cost; --geom.delta_z := path_result_ku.z; --arah vertikal-if (geom.v_source < -30 and geom.v_source >=-60) THEN if path_result_ku.id_edge like 'ta%' THEN geom.deskripsi2:='Menuruni Tangga'; ELSE geom.deskripsi2:='Menurun'; end if; elseif (geom.v_source > -30 and geom.v_source < 30) THEN geom.deskripsi2:='Mendatar'; elseif geom.v_source is NULL THEN geom.deskripsi2:=''; elseif (geom.v_source < 60 and geom.v_source >30) THEN if path_result_ku.id_edge like 'ta%' THEN
75
geom.deskripsi2:='Menaiki Tangga'; ELSE geom.deskripsi2:='Menaik'; end if; elseif (geom.v_source < 90 and geom.v_source >60) THEN if path_result_ku.id_edge like 'lf%' THEN geom.deskripsi2:='Menaiki Lift'; ELSE geom.deskripsi2:='Curam Menaik'; end if; elseif (geom.v_source < -90 and geom.v_source > -60)THEN if path_result_ku.id_edge like 'lf%' THEN geom.deskripsi2:='Menuruni Lift'; ELSE geom.deskripsi2:='Curam Menurun'; end if; end if; --end arah vertikal-if path_result_ku.vertex_id = path_result_ku.source then geom.a_jur := path_result_ku.a_source; geom.delta_z := -round(path_result_ku.z); else geom.a_jur := path_result_ku.a_target; geom.delta_z := round(path_result_ku.z); end if; if geom.a_jur IS NOT NULL THEN arah := arah - geom.a_jur; geom.arah := arah; if geom.a_jur= -(arah) then geom.deskripsi := 'awal'; ELSE if arah < 0 then arah = arah+360; end if; IF (arah >= 0 and arah<30) THEN belok := 'Lurus'; ELSEIF (arah >= 30 and arah<60) THEN belok := 'Belok ke Kiri Depan'; ELSEIF (arah >= 60 and arah<90) THEN belok := 'Belok ke Samping Kiri'; ELSEIF (arah >= 90 and arah<120) THEN belok := 'Belok ke Samping Kiri'; ELSEIF (arah >= 120 and arah<150) THEN belok := 'Belok ke Kiri Belakang'; ELSEIF (arah >= 150 and arah<180) THEN belok := 'Belok ke Belakang'; ELSEIF (arah >= 180 and arah<210) THEN belok := 'Belok ke Belakang'; ELSEIF (arah >= 210 and arah<240) THEN belok := 'Belok ke Kanan Belakang'; ELSEIF (arah >= 240 and arah<270) THEN belok := 'Belok ke Samping Kanan'; ELSEIF (arah >= 270 and arah<300) THEN belok := 'Belok ke Samping Kanan'; ELSEIF (arah >= 300 and arah<330) THEN belok := 'Belok ke Kanan Depan';
76
ELSEIF (arah >= 330 and arah<=360) THEN belok := 'Lurus'; ELSE belok := 'Tidak terdefinisi'; end IF; geom.deskripsi := belok; end if; ELSE geom.deskripsi := 'akhir'; end if; arah := geom.a_jur; RETURN NEXT geom; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Fungsi assing_vertex_id CREATE OR REPLACE FUNCTION "public"."assign_vertex_id" (geom_table varchar, tolerance double precision, geo_cname varchar, gid_cname varchar) RETURNS varchar AS $body$ DECLARE points record; i record; source_id int; target_id int; pre varchar; post varchar; srid integer; countids integer; BEGIN BEGIN DROP TABLE vertices_tmp; EXCEPTION WHEN UNDEFINED_TABLE THEN END; EXECUTE 'CREATE TABLE vertices_tmp (id serial)'; FOR i IN EXECUTE 'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||'''' LOOP END LOOP; srid := i.srid; FOR i IN EXECUTE 'SELECT count(*) as countids FROM '|| quote_ident(geom_table) LOOP
77
END LOOP; countids := i.countids; EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 3)'; CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom); pre = ''; post = ''; FOR i in EXECUTE 'SELECT count(*) as t from ' || quote_ident(geom_table) || ' WHERE NumGeometries(' || quote_ident(geo_cname) || ') is not null' loop IF (i.t > 0) THEN pre = 'geometryN('; post = ' , 1)'; END IF; END LOOP; -FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,' -|| ' startPoint(' || pre || quote_ident(geo_cname) || post || ') AS source,' -|| ' endPoint(' || pre || quote_ident(geo_cname) || post || ') as target' -|| ' FROM ' || quote_ident(geom_table) loop -FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,' || ' PointN('|| quote_ident(geo_cname) ||', 1) AS source,' || ' PointN('|| quote_ident(geo_cname) ||', NumPoints('|| quote_ident(geo_cname) ||')) as target' || ' FROM ' || quote_ident(geom_table) || ' ORDER BY ' || quote_ident(gid_cname) loop IF points.id%10=0 THEN RAISE NOTICE '% out of % edges processed', points.id, countids; END IF; source_id := point_to_id(setsrid(points.source, srid), tolerance); target_id := point_to_id(setsrid(points.target, srid), tolerance);
EXECUTE 'update ' || quote_ident(geom_table) || ' SET source = ' || source_id ||
78
quote_ident(gid_cname) || ' = END LOOP;
', target = ' || target_id || ' WHERE ' || ' || points.id;
RETURN 'OK'; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Type geom_ku CREATE TYPE "public"."geoms_ku4" AS ( "vertex_id" INTEGER, "gid" INTEGER, "the_geom" "public"."geometry", "jarak" DOUBLE PRECISION, "a_jur" DOUBLE PRECISION, "arah" DOUBLE PRECISION, "delta_z" INTEGER, "v_source" INTEGER, "deskripsi" TEXT, "deskripsi2" TEXT );
Fungsi distance_3d CREATE OR REPLACE FUNCTION "public"."distance3d" (pnt1 "public"."geometry", pnt2 "public"."geometry") RETURNS double precision AS $body$ DECLARE x1 DOUBLE PRECISION; y1 DOUBLE PRECISION; z1 DOUBLE PRECISION; x2 DOUBLE PRECISION; y2 DOUBLE PRECISION; z2 DOUBLE PRECISION; dist3d DOUBLE PRECISION; BEGIN x1 := x(pnt1); y1 := y(pnt1); z1 := z(pnt1); x2 := x(pnt2); y2 := y(pnt2); z2 := z(pnt2); dist3d := sqrt((x2-x1)*(x2-x1) + (y2-y1)*(y2-y1) + (z2-z1)*(z2z1)); RETURN dist3d; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
79
Fungsi point_to_id CREATE OR REPLACE FUNCTION "public"."point_to_id" (point "public"."geometry", tolerance double precision) RETURNS integer AS $body$ DECLARE row record; point_id int; BEGIN LOOP -- TODO: use && and index SELECT INTO row id, the_geom FROM vertices_tmp WHERE distance3d(the_geom, point) < tolerance; point_id := row.id; IF NOT FOUND THEN INSERT INTO vertices_tmp (the_geom) VALUES (point); ELSE EXIT; END IF; END LOOP; RETURN point_id; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Type path_result CREATE TYPE "public"."path_result_ku" AS ( "vertex_id" INTEGER, "edge_id" INTEGER, "cost" DOUBLE PRECISION );
80
Lampiran B
DAFTAR STRUCTURE QUERY LANGUANGE (SQL)
81
Query dari gedung Labtek IX C ke gedung Labtek III select * from ginziro_asp ('net_kampus', --awal (select node_kampus.gid from node_kampus inner join gedung on node_kampus.id_node=gedung.id_gedung where gedung.nm_gedung='Labtek IX C'), --akhir (select node_kampus.gid from node_kampus inner join gedung on node_kampus.id_node=gedung.id_gedung where gedung.nm_gedung='Labtek III'), FALSE,FALSE) Query dari gedung Labtek III ke gedung labtek IX C select * from ginziro_asp ('net_kampus', --awal (select node_kampus.gid from node_kampus inner join gedung on node_kampus.id_node=gedung.id_gedung where gedung.nm_gedung='Labtek III'), --akhir (select node_kampus.gid from node_kampus inner join gedung on node_kampus.id_node=gedung.id_gedung where gedung.nm_gedung='Labtek IX C'), FALSE,FALSE) Query dari perpustakaan lantai 6 ke ruang kuliah 3105 lantai 1 di gedung Labtek IX C select * from ginziro_asp ('net_kampus', --awal (select node_kampus.gid from node_kampus INNER JOIN ruang on node_kampus.id_node=ruang.id_ruang INNER JOIN lantai on ruang.id_lantai=lantai.id_lantai INNER JOIN gedung on lantai.id_gedung=gedung.id_gedung where ruang.nm_ruang ='Perpustakaan' and lantai.nm_lantai='lantai 6' and gedung.nm_gedung='Labtek IX C'), --akhir (select node_kampus.gid from node_kampus INNER JOIN ruang on node_kampus.id_node=ruang.id_ruang INNER JOIN lantai on ruang.id_lantai=lantai.id_lantai INNER JOIN gedung on lantai.id_gedung=gedung.id_gedung where ruang.nm_ruang ='Ruang Kuliah 3105' and lantai.nm_lantai='lantai 1' and gedung.nm_gedung='Labtek IX C'), FALSE,FALSE)
82
Query dari ruang Anas Maruf lantai 2 gedung labtek III ke ruang komputer S2 lantai 4 gedung Labtek III select * from ginziro_asp ('net_kampus', --awal (select node_kampus.gid from node_kampus INNER JOIN ruang on node_kampus.id_node=ruang.id_ruang INNER JOIN lantai on ruang.id_lantai=lantai.id_lantai INNER JOIN gedung on lantai.id_gedung=gedung.id_gedung where ruang.nm_ruang like '%Anas Maruf%' and lantai.nm_lantai='lantai 2' and gedung.nm_gedung='Labtek III'), --akhir (select node_kampus.gid from node_kampus INNER JOIN ruang on node_kampus.id_node=ruang.id_ruang INNER JOIN lantai on ruang.id_lantai=lantai.id_lantai INNER JOIN gedung on lantai.id_gedung=gedung.id_gedung where ruang.nm_ruang like '%Komputer S2%' and lantai.nm_lantai='lantai 4' and gedung.nm_gedung='Labtek III'), FALSE,FALSE) Query dari Tugu Soekarno ke Plaza Widya select * from ginziro_asp ('net_kampus', --awal (select node_kampus.gid from node_kampus INNER JOIN taman on node_kampus.id_node=taman.id_taman where taman.keterangan='Tugu Soekarno'), --akhir (select node_kampus.gid from node_kampus INNER JOIN taman on node_kampus.id_node=taman.id_taman where taman.keterangan='Plasa Widya'), FALSE,FALSE)
83
Lampiran C
DAFTAR GRAFIS DAN DESKRIPSI
84
1. Query dari gedung Labtek IXC ke gedung Labtek III
Gambar hasil query
Daftar tabel
85
2. Query dari gedung Labtek III ke gedung Labtek IXC
Gambar hasil query
Daftar tabel
86
3. Query dari perpustakaan lantai 6 ke ruang kuliah 3105 lantai 1 di gedung Labtek IX C
Gambar hasil query
Daftar tabel
87
4. Query dari ruang Bpk Anas Maruf lantai 2 gedung labtek III ke ruang komputer S2 lantai 4 gedung Labtek III
Gambar hasil query
Daftar tabel
88
5. Query dari Tugu Soekarno ke Plasa Widya
Gambar hasil query
Daftar tabel
89
6. Query dari parkiran Aula Timur ke Tugu Soekarno
Gambar hasil query
Daftar tabel
90
7. Parkiran Aula Timur ke Gedung Labtek III
Gambar hasil query
Daftar tabel
91
8. Parkiran Aula Barat ke Gerbang Belakang
Gambar hasil query
Daftar tabel
92
9. Ruang Tamu lantai dasar Gedung Labtek III ke Ruang Seminar lantai 6 Gedung IXC
Gambar hasil query
Daftar tabel
93
10. Laboratorium lantai 4 Gedung Labtek IXC ke dapur lantai 4 Gedung Labtek III
Gambar hasil query
Daftar tabel
94
11. Gerbang Depan ke Gedung Labtek IXC
Gambar hasil query
Daftar tabel
95
