BAB 7: Bertanya ke Basis Data: SQL Dasar

#Studi kasus: "Per petak, berapa yang sehat?"

Kepala Seksi mengirim pertanyaan singkat. "Untuk tiap petak, berapa pohon yang sehat, dan berapa rata-rata tingginya?" Dengan seleksi manual, Anda harus mengklik berkali-kali dan mencatat di kertas. Dengan satu kalimat SQL, jawabannya keluar sekaligus.

Bab ini memperkenalkan SQL sebagai bahasa bertanya. Anda tidak perlu menjadi ahli basis data. Lima kalimat dasar sudah cukup untuk sebagian besar pekerjaan survei.

#Konsep: SQL dalam tiga kalimat

SQL adalah bahasa untuk bertanya kepada tabel dan mengubah isinya, seperti memberi pesan tertulis kepada pustakawan: "ambilkan kolom ini, dari tabel itu, hanya yang begini, urutkan begitu". Setiap kalimat tanya tersusun dari bagian yang selalu berurutan, yaitu SELECT, FROM, WHERE, GROUP BY, dan ORDER BY. GeoPackage memakai SQLite, jadi dialek SQL-nya adalah SQLite.

Istilah baru bagian ini:

• SQL: bahasa untuk bertanya dan mengubah tabel.
• SELECT ... FROM ... WHERE: pilih kolom, dari tabel mana, baris yang mana.
• GROUP BY: kelompokkan baris lalu hitung per kelompok.
• JOIN: gabungkan dua tabel lewat kolom yang cocok.
• UPDATE: ubah isi baris yang terpilih.

#Sepuluh pertanyaan pada data survei

Semua kalimat berikut dijalankan pada Survei_Lapangan.gpkg (61 baris) lewat skrip I3.7. Hasilnya saya salin dari keluaran skrip. [terbukti: QGIS 4.0.2, GDAL 3.12, SQLite 3.53]

1. Berapa baris?

SELECT COUNT(*) AS jumlah FROM Titik_Survei;

Hasil: 61.

2. Lihat tiga baris pertama, hanya beberapa kolom.

SELECT ID_Survei, Kondisi, Tinggi_Phn FROM Titik_Survei LIMIT 3;

Hasil: SV-001 | Terserang Hama | 9.8, SV-002 | Terserang Hama | 16.7, SV-003 | Sehat | 24.5.

3. Titik tebangan liar, urut menurut ID.

SELECT ID_Survei, KPH, Tinggi_Phn FROM Titik_Survei
WHERE Kondisi = 'Tebangan Liar' ORDER BY ID_Survei;

Hasil: lima baris, yaitu SV-011, SV-014, SV-038, SV-044 (semuanya KPH Beta), dan SV-054 (KPH Gamma).

4. Hitung per kondisi.

SELECT Kondisi, COUNT(*) AS jumlah FROM Titik_Survei
GROUP BY Kondisi ORDER BY jumlah DESC;

Inilah tujuh baris yang membuat Kepala Seksi bingung di Bab 3. SQL membedakan huruf besar dan kecil serta spasi pada perbandingan =.

5. Rata-rata tinggi per petak, hanya nilai wajar.

SELECT KPH, COUNT(*) AS n, ROUND(AVG(Tinggi_Phn), 1) AS rata_tinggi
FROM Titik_Survei WHERE Tinggi_Phn BETWEEN 0 AND 60
GROUP BY KPH ORDER BY KPH;

Jumlah barisnya 59, bukan 61, karena dua baris dengan tinggi tidak wajar (-3 m dan 250 m) tersaring oleh WHERE. Tanpa penyaringan itu, rata-ratanya menyesatkan.

6. Cari ID ganda.

SELECT ID_Survei, COUNT(*) AS n FROM Titik_Survei
GROUP BY ID_Survei HAVING COUNT(*) > 1;

Hasil: SV-049 | 2. HAVING menyaring hasil kelompok, sedangkan WHERE menyaring baris sebelum dikelompokkan.

7. Nilai kondisi yang tidak ada di tabel acuan.

SELECT t.ID_Survei, t.Kondisi
FROM Titik_Survei t LEFT JOIN Ref_Kondisi r ON t.Kondisi = r.Kondisi
WHERE r.Kondisi IS NULL ORDER BY t.ID_Survei;

Hasil: SV-005 (sehat), SV-012 (Sehat ), SV-020 (Terserang hama), serta SV-031 dan SV-040 yang kosong. Lima baris, sama dengan temuan Bab 6, dan kali ini baris kosong ikut terjaring. Inilah gunanya tabel acuan dari Bab 2.

8. Gabungkan dengan tabel batas lewat nama petak.

SELECT b.NAMA_KPH, COUNT(*) AS jumlah_titik
FROM Titik_Survei t JOIN Batas_KPH b ON t.KPH = b.NAMA_KPH
GROUP BY b.NAMA_KPH ORDER BY b.NAMA_KPH;

Hasil: KPH Alpha 19, KPH Beta 21, KPH Gamma 21. Ini menghitung berdasarkan label KPH, bukan letak titik. Dua titik yang labelnya salah (Bab 6) ikut terhitung di petak yang salah.

9. Perbaiki ejaan (ubah isi, kerjakan pada salinan).

UPDATE Titik_Survei SET Kondisi = 'Sehat'
WHERE LOWER(TRIM(Kondisi)) = 'sehat';

10. Periksa ulang dengan kalimat nomor 7. Hasil: tersisa tiga baris, yaitu SV-020 (Terserang hama) serta SV-031 dan SV-040 yang kosong. Dua baris sehat sudah beres. Terserang hama perlu kalimat UPDATE serupa. Baris kosong tidak boleh ditebak.

#SQL atau ekspresi QGIS?

Keduanya mirip, tetapi cocok untuk kebutuhan berbeda.

Pada Bab 6, aturan "label petak cocok dengan letak" memakai fungsi spasial overlay_within. SQL spasial lebih lanjut dibahas di seri M2 (PostGIS).

SELECT Kondisi, COUNT(*) AS jumlah FROM input1 GROUP BY Kondisi ORDER BY jumlah DESC

# Inti skrip I3.7 (berkas lengkap: skrip/i3_07_sql_dasar.py)
ds = ogr.Open(SALINAN, 1)                      # 1 = boleh mengubah; pakai salinan, jangan berkas asli
hasil = ds.ExecuteSQL("SELECT Kondisi, COUNT(*) AS jumlah FROM Titik_Survei GROUP BY Kondisi")
for baris in hasil:
    print(baris.GetField(0), baris.GetField(1))
ds.ReleaseResultSet(hasil)

#Cek paham

1. Apa beda WHERE dan HAVING?
2. Mengapa hasil GROUP BY Kondisi pada data mentah menghasilkan tujuh baris?
3. Apa kebiasaan aman sebelum menjalankan UPDATE?

Jawaban:

1. WHERE menyaring baris sebelum dikelompokkan. HAVING menyaring kelompok sesudah dihitung, misalnya hanya yang jumlahnya lebih dari satu.
2. Karena SQL membedakan huruf besar dan kecil serta spasi, dan baris kosong membentuk kelompoknya sendiri. Ejaan yang berbeda dianggap nilai berbeda.
3. Kerjakan pada salinan, jalankan dulu SELECT dengan WHERE yang sama, periksa barisnya, baru ubah menjadi UPDATE.

#Kesalahan umum

• Lupa `WHERE` pada `UPDATE`. Seluruh baris berubah. Selalu uji dengan SELECT dulu.
• Mencampur kutip. Teks di SQL memakai kutip tunggal, misalnya 'Sehat'. Kutip ganda dipakai untuk nama kolom di ekspresi QGIS.
• Membandingkan dengan NULL memakai `=`. Pakai IS NULL dan IS NOT NULL.
• Rata-rata dari data yang belum diperiksa. Contoh nomor 5 menunjukkan bahwa nilai yang tidak wajar harus disaring lebih dulu.
• Menimpa berkas asli. Pakai salinan selama belajar.

#Ringkasan dan latihan

Ringkasan: SQL bertanya ke tabel dengan urutan SELECT, FROM, WHERE, GROUP BY, ORDER BY. JOIN menggabungkan tabel, UPDATE mengubah isi. Pakai salinan, uji dengan SELECT dulu, dan waspadai NULL serta huruf besar-kecil.

Latihan: tulis kalimat SQL yang menjawab pertanyaan Kepala Seksi di awal bab: untuk tiap petak, berapa pohon sehat dan berapa rata-rata tingginya. Syarat: hanya kondisi Sehat yang baku, dan tinggi yang wajar.

Salah satu jawaban yang mungkin:

SELECT KPH, COUNT(*) AS jumlah_sehat, ROUND(AVG(Tinggi_Phn), 1) AS rata_tinggi
FROM Titik_Survei
WHERE Kondisi = 'Sehat' AND Tinggi_Phn BETWEEN 0 AND 60
GROUP BY KPH ORDER BY KPH;

Bandingkan hasilnya sebelum dan sesudah memperbaiki ejaan di nomor 9. Berapa baris tambahan yang kini terhitung?

Jawaban yang diharapkan (hasil uji): sebelum perbaikan, jumlah sehat per petak 11, 9, dan 11 (Alpha, Beta, Gamma; total 31). Sesudahnya 11, 10, dan 12 (total 33). Ada dua baris tambahan, satu di KPH Beta dan satu di KPH Gamma. [terbukti]

#Tabel perbandingan: bertanya ke tabel

#Ceklis kesiapan menuju seri berikutnya

Centang sebelum lanjut ke I4 atau membuka Bab 2 M1:

• Saya bisa menjelaskan akurasi, presisi, dan R95, dan tahu cara mengukur galat alat saya.
• Saya bisa membuat GeoPackage berisi layer survei dan tabel acuan.
• Saya bisa memasang domain, aturan isian, dan nilai bawaan pada kolom.
• Saya bisa menyusun formulir berkelompok dengan alias, pilihan baku, dan foto.
• Saya tahu apa yang perlu disiapkan sebelum data dibawa ke aplikasi lapangan, dan bagaimana menghindari ID ganda.
• Saya bisa menulis aturan pemeriksaan data dan membaca hasilnya.
• Saya bisa menulis kalimat SQL dasar, dan tahu bahaya UPDATE tanpa WHERE.

#Penutup dan jalan ke seri berikutnya

Anda kini punya alur utuh: titik yang diukur dengan sadar akan galatnya, wadah yang rapi, pilihan yang dijaga, formulir yang ramah, aplikasi yang tersambung, pemeriksaan yang bisa diulang, dan SQL untuk bertanya. Di I4: Otomasi Dasar dengan Python, skrip pemeriksaan dan pertanyaan SQL di bab ini menjadi bahan latihan. Di Masterclass Seri 1 (M1), Bab 2 tentang sinkronisasi lapangan akan terasa seperti pengulangan yang menyenangkan, karena GeoPackage, domain, dan formulirnya sudah Anda kuasai.