laporan tugas sistem informasi geografis11

TUGAS SIG

PENGOLAHAN BASISDATA SPASIAL

Oleh

UBL_1211504327Edi Sucipto

Jurusan Ilmu Komputer Dan Elektronika

Fakultas Matematika Dan Ilmu Pengetahuan Alam

Universitas Gadjah Mada

Yogyakarta

Laporan Tugas Sistem Informasi GeografisPengolahan Basisdata Spasial dengan Postgres dan Postgis

1. Query untuk kebutuhan data kecamatan, jalan dan wisata.

CREATE TABLE Wisata(w_id varchar(5) primary key,nm_wisata varchar(30) not null,geom geometry not null

);CREATE TABLE Kecamatan(

k_id varchar(5) primary key,nm_kec varchar(30) not null,geom geometry not null

);CREATE TABLE Jalan(

jl_id varchar(5) primary key,nm_jl varchar(30) not null,geom geometry not null

);

Membuat Index

CREATE INDEX idx_geom ON Kecamatan USING GIST(geom);CREATE INDEX idx_geom3 ON Wisata USING GIST(geom);CREATE INDEX idx_geom2 ON Jalan USING GIST(geom);

Ketika dieksekusi

Memasukkan Objek Geografis ke masing - masing table

Insert Ketabel Kecamatan

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_1','Kec. Galur',

ST_GeomFromText('POLYGON((-7.980357745922779 110.20660400390625,-7.93479548982493 110.24436950683594,

-7.919153549446241 110.18394470214844,-7.959957361963709 110.16471862792969,-7.980357745922779 110.20660400390625))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_2','Kec. Lendah',

ST_GeomFromText('POLYGON((-7.93479548982493 110.24436950683594,-7.887867887739152 110.26805877685547,-7.905551379259245 110.18840789794922,-7.919153549446241 110.18394470214844,-7.93479548982493 110.24436950683594))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_3','Kec. Sentolo',ST_GeomFromText('POLYGON((-7.887867887739152 110.26805877685547,

-7.80488143281811 110.22926330566406,-7.823248713487829 110.18325805664062,-7.919153549446241 110.18394470214844,-7.887867887739152 110.26805877685547))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_4','Kec. Nanggulan',ST_GeomFromText('POLYGON((-7.80488143281811 110.22926330566406,

-7.730723853474202 110.21621704101562,-7.731404257656398 110.20248413085938,-7.791275519273148 110.17707824707031,-7.823248713487829 110.18325805664062,-7.80488143281811 110.22926330566406))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_5','Kec. Kalibawang',ST_GeomFromText('POLYGON((-7.730723853474202 110.21621704101562,

-7.706909016739941 110.2694320678711,-7.648386879223986 110.26496887207031,-7.652470080257459 110.2062068115234,-7.717455752799995 110.19493103027344,-7.731404257656398 110.20248413085938,-7.730723853474202 110.21621704101562))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_6','Kec. Samigaluh',ST_GeomFromText('POLYGON((-7.717455752799995 110.19493103027344,

-7.652470080257459 110.2062068115234,-7.6463452640521385 110.142745971167969,-7.670844000784806 110.11802673339844,-7.70861012098104 110.13450622558594,-7.717455752799995 110.19493103027344))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_7','Kec.Girimulyo',ST_GeomFromText('POLYGON((-7.791275519273148 110.17707824707031,

-7.731404257656398 110.20248413085938,-7.717455752799995 110.19493103027344,-7.70861012098104 110.13450622558594,-7.775628172195792 110.11184692382812,-7.784472396665788 110.13038635253906,-7.7535167958781095 110.14617919921875,-7.791275519273148 110.17707824707031))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_8','Kec. Kokap',ST_GeomFromText('POLYGON((-7.874264687307978 110.11184692382812,

-7.784472396665788 110.13038635253906,-7.775628172195792 110.11184692382812,-7.868823282019818 110.04386901855469,-7.874264687307978 110.11184692382812 ))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_9','Kec. Temon',ST_GeomFromText('POLYGON((-7.915072945519083 110.07820129394531,

-7.900110385584452 110.10841369628906,-7.874264687307978 110.11184692382812,-7.868823282019818 110.04386901855469,-7.887187738338113 110.00335693359375,-7.915072945519083 110.07820129394531))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_10','Kec. Wates',ST_GeomFromText('POLYGON((-7.928674801364035 110.10704040527344,

-7.876305195867617 110.17570495605469,-7.845696513319935 110.14411926269531,-7.900110385584452 110.10841369628906,-7.915072945519083 110.07820129394531,-7.928674801364035 110.10704040527344))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_11','Kec. Panjatan',ST_GeomFromText('POLYGON((-7.959957361963709 110.16471862792969,

-7.919153549446241 110.18394470214844,-7.905551379259245 110.18840789794922,-7.876305195867617 110.17570495605469,-7.928674801364035 110.10704040527344,-7.959957361963709 110.16471862792969))'));

INSERT INTO Kecamatan(k_id, nm_kec, geom)VALUES ('kec_12','Kec. pengasih',ST_GeomFromText('POLYGON((-7.894305195867617 110.18470495605469,

-7.823248713487829 110.18325805664062,-7.791275519273148 110.17707824707031,-7.7535167958781095 110.14617919921875,-7.784472396665788 110.13038635253906,-7.874264687307978 110.11184692382812,-7.900110385584452 110.10841369628906,-7.845696513319935 110.14411926269531,-7.894305195867617 110.18470495605469))'));

Ketika Query di eksekusi

Insert Ketabel Jalan

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES ('jln_1', 'Jl. Purboyo-Ngentak',ST_GeomFromText('LINESTRING(-7.642602277539302 110.25394096970558,

-7.647281005551754 110.25411263108253,-7.648642080433627 110.25625839829445,-7.6539162045823055 110.25617256760597,-7.6642090653735115 110.26295319199562,-7.676287973067632 110.26321068406105,-7.696872362643094 110.25342598557472,-7.701890719412765 110.25273934006691,-7.710226163677953 110.23205414414406,-7.716605219317325 110.2249301970005,-7.7304687016230815 110.21789208054543,-7.737612895132434 110.21368637681007,-7.753686887826874 110.21162644028664)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES ('jln_2', 'Jl. Sentolo Nanggulan',ST_GeomFromText('LINESTRING(-7.753686887826874 110.21162644028664,

-7.774267505725855 110.20965233445168,-7.793911699576379 110.22802010178566,-7.80233035773874 110.22793427109718,-7.803095681897003 110.22398605942726,-7.807347457262307 110.22690430283546,-7.826649973041193 110.2245868742466)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES ('jln_3', 'Jl. Nagung-Brosot',ST_GeomFromText('LINESTRING(-7.893564095049542 110.1417381761712,

-7.897602403418402 110.16345334035577,-7.930587526293554 110.1926357744378,-7.9302474869561275 110.2074304818234,-7.939492206329469 110.22509014466777,-7.940023505721531 110.23989593842998)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES('jln_4', 'Jl. Pahlawan',ST_GeomFromText('LINESTRING(-7.865741204368918 110.15664017060772,

-7.8698222953760295 110.15412962296978,-7.907400450621181 110.15209114411846,-7.920407476909095 110.15728390077129,-7.927080857129446 110.15618955949321,-7.937409506087188 110.15507376054302)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES('jln_5', 'Jl. Goa Kiskendo',ST_GeomFromText('LINESTRING(-7.74705325089344 110.14107370341662,

-7.7467555852476275 110.14135193545371,-7.743778917220233 110.14208149630576,-7.74428920466152 110.15002083498985,-7.74186533381159 110.15032124239951,-7.74475696760644 110.16469788533868,-7.7428008645637085 110.16448330861749,-7.748754193404859 110.17843079549493,-7.7470107272426425 110.17890286428155,-7.753984548571783 110.21151852590265)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES('jln_6', 'Jl. Srandakan',ST_GeomFromText('LINESTRING(-7.940236025285951 110.24222642183304,

-7.950776857654461 110.2181938290596,-7.953497028508545 110.19347459077835,-7.909632077433758 110.08498460054398,

-7.894499285762694 110.05494385957718,-7.886167512139392 110.04618912935257,-7.8849772450605204 110.03606110811234)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES('jln_7', 'Jl. Purworejo-Wates',ST_GeomFromText('LINESTRING(-7.875114900429392 110.04292756319046,

-7.885827436180613 110.05030900239944,-7.8926289022200224 110.12927323579788,-7.865082274450675 110.1519325375557,-7.875795069670096 110.20531922578812,-7.828010469092214 110.22231370210648,-7.823248713487829 110.23347169160843)'));

INSERT INTO Jalan(jl_id, nm_jl, geom)VALUES('jln_8', 'Jl. Banjararum',ST_GeomFromText('LINESTRING(-7.7230267047966015 110.23121662437916,

-7.716520165874956 110.22495098412037,-7.713330649438138 110.21628208458424,-7.709120450920351 110.20349331200123,-7.694193046142439 110.1948244124651,-7.690067716214494 110.18606968224049,-7.686240054637296 110.18559761345387,-7.670248562088736 110.1653415709734,-7.66344348924789 110.16476288437843,-7.660636365012073 110.1572097837925,-7.669568059701355 110.14339104294777,-7.674161429689664 110.1421894133091,-7.670120967974004 110.118263438344)'));


Insert Ketabel Wisata

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_1','Pantai Trisik',ST_GeomFromText('POINT(-7.975256933028715 110.20432586669922)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)

VALUES ('wis_2','Pantai Bugel',ST_GeomFromText('POINT(-7.9565571991522885 110.1767349243164)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_3','Pantai Glagah',ST_GeomFromText('POINT(-7.90147064072684 110.07373809814453)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_4','Pantai Congot',ST_GeomFromText('POINT(-7.890588474156216 110.04558563232422)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_5','Makam Girigindo',ST_GeomFromText('POINT(-7.867462919536139 110.10395050048828)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_6','Waduk Sermo',ST_GeomFromText('POINT(-7.813044768423498 110.11287689208984)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_7','Taman Clereng',ST_GeomFromText('POINT(-7.837533817049727 110.17261505126953)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_8','Gua Lanang Wadok',ST_GeomFromText('POINT(-7.821888201898094 110.15201568603516)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_9','Gua Kiskendo',ST_GeomFromText('POINT(-7.73956902225241 110.1430892944336)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_10','Puncak Suroloyo',ST_GeomFromText('POINT(-7.659955847259699 110.17742156982422)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_11','Gua Sriti',ST_GeomFromText('POINT(-7.693299936880302 110.1925277709961)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_12','Makam Nyi Ageng',ST_GeomFromText('POINT(-7.6844538007565655 110.22754669189453)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_13','Ancol Bligo',ST_GeomFromText('POINT(-7.703506787754694 110.25501251220703)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_14', 'Taman Satwa',ST_GeomFromText('POINT(-7.845568972422612 110.15946719795465)'));

INSERT INTO Wisata(w_id, nm_wisata, geom)VALUES ('wis_15', 'Gunung Kucir',ST_GeomFromText('POINT(-7.701805663013471 110.17279207706451)'));


2. Data dari Hasil Query untuk Kebutuhan Pengolahan Data.

Record data di dalam masing masing table

Tabel Kecamatan

Tabel Jalan

Tabel Wisata

Jika database yang telah dibuat dikoneksikan dengan QGis makadata akan terlihat seperti dibawah ini

3. Query Jawaban untuk Menjawab Pertanyaan.

a. Tampilkan panjang jalan masing-masing diurutkan dari yang paling panjang

Query

SELECT jl_id, nm_jl, (ST_Length (geom)) * 100 as Km_jl from Jalanorder by Km_jl desc;

Ketika dieksekusi

b. Tampilkan total seluruh panjang jalan di KP

Query

SELECT sum (ST_Length (geom)) *100 as Tot_Pjg_jl from Jalan;

Ketika dieksekusi

c. Hitung jarak obyek wisata Taman Satwa dari Pantai Glagah Indah

Query

SELECT ST_distance((SELECT geom FROM Wisata WHERE nm_wisata ='Taman Satwa'),(SELECT geom FROM Wisata WHERE nm_wisata ='Pantai Glagah'))*100

as jarak;

Ketika dieksekusi

d. Hitung jarak obyek wisata Pantai Glagah Indah ke Kecamatan Sentolo

Query

SELECT ST_distance((SELECT geom FROM Wisata WHERE nm_wisata ='Pantai Glagah'),(SELECT geom FROM Kecamatan WHERE nm_kec ='Kec. Sentolo'))*100

as jarak;

Ketika dieksekusi

e. Hitung luasan masing-masing kecamatan di KP

Query

SELECT k_id, nm_kec, (ST_AREA (geom)) * 10000 luas_kec fromKecamatan;

Ketika dieksekusi

f. Tampilkan nama kecamatan yang bertetangga dengan KecamatanBendungan

Saya tidak menemukan kecamatan bendungan untuk itu saya rubahmenjadi Kecamatan Galur berikut Query nya

SELECT a.nm_kec AS "Kec. Galur" FROM kecamatan a, kecamatan bWHERE ST_Touches(a.geom, b.geom)

AND b.nm_kec = 'Kec. Galur';

Ketika dieksekusi

g. Tampilkan Kecamatan yang paling luas dan paling sempit

Query untuk mencari Kecamatan Paling luas

SELECT k_id, nm_kec, (ST_AREA (geom)) * 10000 as luas_kec_Maxfrom Kecamatan order by luas_kec_Max desc limit 1;

Ketika dieksekusi

Query untuk mencari Kecamatan Paling Sempit

SELECT k_id, nm_kec, (ST_AREA (geom)) * 10000 as luas_kec_Minfrom Kecamatan order by luas_kec_Min asc limit 1;

Ketika dieksekusi

4. Referensi tugas.http://gatotkaca.kulonprogokab.go.id/gis-investasi/investasi/showhttp://postgis.net/docs/manual-1.5/ch04.html#id361112

laporan tugas sistem informasi geografis11

Documents