17 May 2008

Implementasi Kode Wilayah

Pada Tutorial SQL terdahulu telah sedikit disinggung mengenai kode wilayah. Kode tersebut sebenarnya adalah standar kodifikasi wilayah yang resmi dikeluarkan oleh Pemerintah Indonesia melalui Biro Pusat Statistik, yang fungsinya antara lain sebagai dasar pelaporan berbagai indikator berbasis wilayah. Kodifikasi tersebut cukup lengkap, yaitu meliputi pembagian wilayah administratif mulai dari propinsi, kabupaten/kota, kecamatan hingga desa/kelurahan.

Pembahasan kali ini akan difokuskan kepada bagaimana implementasi kode wilayah tersebut kedalam database dan model query yang bisa digunakan. Model implementasi ini penting sebagai landasan awal desain database untuk berbagai keperluan/kepentingan, terutama yang satu standar dengan bentuk pelaporan pada instansi-instansi pemerintah, supaya nantinya kita dapat memetik manfaat lebih dalam menggali informasi.

Untuk itu, terlebih dahulu kita perlu melihat kodenya, kita ambil sedikit contoh:

+-------------+-----------------+
| kode | nama_wilayah |
+-------------+-----------------+
| 3400000000 | D I YOGYAKARTA | << propinsi
| 3401000000 | KULON PROGO | << kabupaten
| 3401010000 | TEMON | << kecamatan
| 3401010001 | JANGKARAN | << kelurahan
| 3401010002 | SINDUTAN | << kelurahan
| 3401010003 | PALIHAN | << kelurahan
| 3401010004 | GLAGAH | << kelurahan
| 3403000000 | GUNUNG KIDUL | << kabupaten
| 3403011000 | PURWOSARI | << kecamatan
| 3403011001 | GIRIJATI | << kelurahan
| 3403011002 | GIRIASIH | << kelurahan
| 3403011003 | GIRICAHYO | << kelurahan
| 3403011004 | GIRIPURWO | << kelurahan
| 3403011005 | GIRITIRTO | << kelurahan
| 3403020000 | PALIYAN | << kecamatan
| 3100000000 | DKI JAKARTA | << propinsi
| 3171000000 | JAKARTA SELATAN | << kota
| 3171010000 | JAGAKARSA | << kecamatan
| 3171010001 | CIPEDAK | << kelurahan
| 3171010002 | SRENGSENG SAWAH | << kelurahan
| 3171010003 | CIGANJUR | << kelurahan
+-------------+-----------------+
Jika kita perhatikan, kode diatas merangkum struktur hirarki wilayah administratif dalam satu atribut numerik (angka) yang terdiri dari 10 digit dengan susunan sebagai berikut (mulai dari kiri ke kanan):
  1. Diawali 2 digit kode propinsi.
  2. Diikuti 2 digit kode kabupaten.
  3. Diikuti 3 digit kode kecamatan.
  4. Diikuti 3 digit kode desa.
Contoh 1 : 3171010003 melambangkan:
   31 << Propinsi DKI JAKARTA
71 << Kota JAKARTA SELATAN
010 << Kecamatan JAGAKARSA
003 << Kelurahan CIGANJUR
Contoh 2 : 3400000000
   34 << Propinsi D I YOGYAKARTA
00 << kota, kosong
000 << kecamatan, kosong
000 << kelurahan, kosong
Contoh 3 : 3171000000
   31 << Propinsi DKI JAKARTA
71 << kota JAKARTA SELATAN
000 << kecamatan, kosong
000 << kelurahan, kosong
Dari ketiga contoh diatas, dapat kita lihat bagaimana struktur hirarki diperoleh/disusun dalam kodifikasi tersebut. Selanjutnya struktur tersebut akan kita manfaatkan dalam query-query.


IMPLEMENTASI

Implementasi akan kita mulai dengan membuat sebuah tabel yang sederhana terlebih dahulu, yaitu tabel dengan 2 atribut, yaitu kode dan nama_wilayah:
CREATE TABLE wilayah (
kode char(10) NOT NULL default '',
nama_wilayah char(100) NOT NULL default '',
PRIMARY KEY (kode)
);
Kemudian kita isi dengan data contoh:
INSERT INTO wilayah VALUES ('3400000000','D I YOGYAKARTA');
INSERT INTO wilayah VALUES ('3401000000','KULON PROGO');
INSERT INTO wilayah VALUES ('3401010000','TEMON');
INSERT INTO wilayah VALUES ('3401010001','JANGKARAN');
INSERT INTO wilayah VALUES ('3401010002','SINDUTAN');
INSERT INTO wilayah VALUES ('3401010003','PALIHAN');
INSERT INTO wilayah VALUES ('3401010004','GLAGAH');
INSERT INTO wilayah VALUES ('3403000000','GUNUNG KIDUL');
INSERT INTO wilayah VALUES ('3403011000','PURWOSARI');
INSERT INTO wilayah VALUES ('3403011001','GIRIJATI');
INSERT INTO wilayah VALUES ('3403011002','GIRIASIH');
INSERT INTO wilayah VALUES ('3403011003','GIRICAHYO');
INSERT INTO wilayah VALUES ('3403011004','GIRIPURWO');
INSERT INTO wilayah VALUES ('3403011005','GIRITIRTO');
INSERT INTO wilayah VALUES ('3403020000','PALIYAN');
INSERT INTO wilayah VALUES ('3100000000','DKI JAKARTA');
INSERT INTO wilayah VALUES ('3171000000','JAKARTA SELATAN');
INSERT INTO wilayah VALUES ('3171010000','JAGAKARSA');
INSERT INTO wilayah VALUES ('3171010001','CIPEDAK');
INSERT INTO wilayah VALUES ('3171010002','SRENGSENG SAWAH');
INSERT INTO wilayah VALUES ('3171010003','CIGANJUR');

QUERY

Selanjutnya akan kita lakukan test untuk mengetahui apakah desain tabel yang sederhana tersebut sudah cukup mampu mengakomodasi kebutuhan informasi kita.

1. Tampilkan propinsi:
SELECT * FROM wilayah
WHERE kode LIKE '__00000000';
hasilnya:
+------------+----------------+
| kode | nama_wilayah |
+------------+----------------+
| 3400000000 | D I YOGYAKARTA |
| 3100000000 | DKI JAKARTA |
+------------+----------------+
2. Tampilkan kota/kabupaten saja:
SELECT * FROM wilayah
WHERE kode LIKE '____000000'
AND kode NOT LIKE '__00000000';
hasilnya:
+------------+-----------------+
| kode | nama_wilayah |
+------------+-----------------+
| 3401000000 | KULON PROGO |
| 3403000000 | GUNUNG KIDUL |
| 3171000000 | JAKARTA SELATAN |
+------------+-----------------+
3. Tampilkan kecamatan saja:
SELECT * FROM wilayah
WHERE kode LIKE '_______000'
AND kode NOT LIKE '____000000';
hasilnya:
+------------+--------------+
| kode | nama_wilayah |
+------------+--------------+
| 3401010000 | TEMON |
| 3403011000 | PURWOSARI |
| 3403020000 | PALIYAN |
| 3171010000 | JAGAKARSA |
+------------+--------------+
4. Tampilkan desa/kelurahan saja:
SELECT * FROM wilayah
WHERE kode NOT LIKE '0';
hasilnya:
+------------+-----------------+
| kode | nama_wilayah |
+------------+-----------------+
| 3401010001 | JANGKARAN |
| 3401010002 | SINDUTAN |
| 3401010003 | PALIHAN |
| 3401010004 | GLAGAH |
| 3403011001 | GIRIJATI |
| 3403011002 | GIRIASIH |
| 3403011003 | GIRICAHYO |
| 3403011004 | GIRIPURWO |
| 3403011005 | GIRITIRTO |
| 3171010001 | CIPEDAK |
| 3171010002 | SRENGSENG SAWAH |
| 3171010003 | CIGANJUR |
+------------+-----------------+
5. Tampilkan desa/kelurahan yang berada di kecamatan JAGAKARSA saja:
SELECT * FROM wilayah
WHERE kode LIKE '3171010%'
AND kode NOT LIKE '0';
hasilnya:
+------------+-----------------+
| kode | nama_wilayah |
+------------+-----------------+
| 3171010001 | CIPEDAK |
| 3171010002 | SRENGSENG SAWAH |
| 3171010003 | CIGANJUR |
+------------+-----------------+
6. Berapa jumlah kelurahan di propinsi D I YOGYAKARTA:
SELECT COUNT(*) FROM wilayah
WHERE kode LIKE '34%'
AND kode NOT LIKE '0';
hasilnya:
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

APLIKASI

Sudah tergambar idenya? Kami kira tidak terlalu sulit bagaimana aplikasi praktisnya. Kita ambil contoh sebuah metode sederhana survei kasus-kasus penyakit x, dengan cukup menggunakan satu tabel untuk data kasus dengan struktur sebagai berikut:
+--------------+----------+
| Field | Type |
+--------------+----------+
| kasus_no | int(10) | << nomor kasus
| kode | char(10) | << kode wilayah
+--------------+----------+
Berikut ini perintah untuk membuat tabel dan sekaligus mengisinya dengan contoh datanya:
CREATE TABLE kasus (
kasus_no int(10) unsigned NOT NULL default '0',
kode char(10) NOT NULL default '',
PRIMARY KEY (kasus_no)
);

INSERT INTO kasus VALUES(1,'3171010003');
INSERT INTO kasus VALUES(2,'3171010002');
INSERT INTO kasus VALUES(3,'3171010001');
INSERT INTO kasus VALUES(4,'3403011005');
INSERT INTO kasus VALUES(5,'3403011004');
INSERT INTO kasus VALUES(6,'3403011005');
INSERT INTO kasus VALUES(7,'3403011003');
INSERT INTO kasus VALUES(8,'3401010004');
INSERT INTO kasus VALUES(9,'3171010003');
INSERT INTO kasus VALUES(10,'3403011003');
INSERT INTO kasus VALUES(11,'3403011005');
INSERT INTO kasus VALUES(12,'3171010003');
INSERT INTO kasus VALUES(13,'3401010004');
INSERT INTO kasus VALUES(14,'3401010003');
INSERT INTO kasus VALUES(15,'3401010001');
INSERT INTO kasus VALUES(16,'3401010002');
INSERT INTO kasus VALUES(17,'3401010003');
INSERT INTO kasus VALUES(18,'3171010001');
INSERT INTO kasus VALUES(19,'3401010003');
INSERT INTO kasus VALUES(20,'3171010003');
Lalu segera kita coba hasilnya dengan query berikut:
SELECT b.nama_wilayah as 'Wilayah',
COUNT(a.kasus_no) as 'Jumlah Kasus'
FROM kasus a
LEFT JOIN wilayah b USING(kode)
GROUP BY a.kode ORDER BY a.kode;
hasilnya:
+-----------------+--------------+
| Wilayah | Jumlah Kasus |
+-----------------+--------------+
| CIPEDAK | 2 |
| SRENGSENG SAWAH | 1 |
| CIGANJUR | 4 |
| JANGKARAN | 1 |
| SINDUTAN | 1 |
| PALIHAN | 3 |
| GLAGAH | 2 |
| GIRICAHYO | 2 |
| GIRIPURWO | 1 |
| GIRITIRTO | 3 |
+-----------------+--------------+
Kita coba lagi dengan query yang lain:
SELECT b.nama_wilayah as Wilayah,
COUNT(a.kasus_no) as 'Jumlah Kasus'
FROM kasus a
LEFT JOIN wilayah b
ON b.kode = CONCAT(SUBSTRING(a.kode,1,2),'00000000')
GROUP BY b.kode
ORDER BY b.kode;
hasilnya:
+----------------+--------------+
| Wilayah | Jumlah Kasus |
+----------------+--------------+
| DKI JAKARTA | 7 |
| D I YOGYAKARTA | 13 |
+----------------+--------------+
Selanjutnya terserah Anda. Tentunya akan lebih menarik lagi jika struktur tabel kasus tersebut dilengkapi dengan atribut lain misalnya: waktu, jenis kelamin, usia, dll. Okay, sekian dan enjoy ...