Map Materialized View untuk Postgis

Materialized view adalah tabel yang berisi baris dan kolom, TETAPI memiliki sifat seperti table view. Artinya setiap perubahan dari table view akan dialami juga oleh table materialized view.

Enaknya apa sih?
Pernah tidak Anda membuat 1 peta view administrasi dengan 30 kolom hasil query rumit crosstab, hasil join table dari 8 table view dengan 1000 sub-administratif, ternyata untuk menampilkannya membutuhkan waktu 5 menit?
User Anda pasti akan mencak2 kayak cicak gara-gara wasting time menunggu minimal 5 menit untuk melihat peta yang Anda buat. Belum lagi ditambah keterbatasan bandwidth jaringan misalnya.

Anda sebagai database engineer sangat kelelahan atas upaya Anda mempercepat query peta view tersebut, dan akhirnya Anda menyadari bahwa untuk mempercepat query adalah memindahkan peta view tersebut keformat peta permanen (table view to table).
So trik yang dilakukan adalah menayangkan peta view dari basisdata tadi, kemudian mengeksportnya ke basisdata peta permanen melalui software GIS, Cadcorp misalnya.

OK, sekarang query sudah cepat, dengan peta permanen user bisa tersenyum melihat peta Anda tampil dalam <5 detik, 1/60 kali lebih cepat dari metode sebelumnya.

Masalah timbul lagi ketika ternyata, peta view yang diubah ke peta permanen secara reguler perlu diupdate karena data yang ditampilkan adalah data peta harian misalnya. Mungkin tidak masalah bila peta tersebut hanya < 10 layer. Manual juga gak papa yang sehingga ada tugas rutin harian ala PNS...:D
Kalo peta view yang diubah ke peta permanen puluhan layer gimana coba? Berapa banyak orang yg perlu Anda libatkan untuk mengubah peta tersebut satu per satu :D

Inilah pentingnya materialized view!
So, materialized view peta merupakan cara untuk menjadikan peta permanen memiliki sifat seperti peta view. Apapun perubahan yang ada pada peta view akan diikuti oleh peta permanennya. Anda tidak perlu lagi mengubahnya satu per satu, cukup memanggil query satu baris untuk menjalankan proses tersebut secara otomatis.

Dari grafik di atas, materialized view akan bersifat sama dengan view table. Saat ini magic spell-nya perlu dibuat secara manual di postgres 8.*. Tentunya kedepan postgres akan menambahkan fitur ini secara built-in mengingat pentingnya fasilitas ini.

OK, langsung hajar saja yah:

Bahan ajar:
1. PostgreSQL 8.* atau lebih tinggi
2. PostGIS yang sesuai dengan seri PostgreSQL 8.* atau lebih tinggi.
3. Contreng PLPGSQL pada saat Anda install PostgreSQL.
4. Peta basisdata hasil export dari shp2pgsql.exe atau menggunakan software GIS.
5. Peta view hasil query beberapa table.

Berikut adalah tabel yang digunakan untuk menampung informasi materialized view peta:

Tujuan:
Mencatat judul-judul peta sekaligus mencatat data timestamp terhadap perubahan dari table materialized view peta.

Kolom:
mv_name untuk judul peta permanen
v_name untuk judul peta view
last_refresh untuk Timestamp update terakhir terhadap peta permanen.

CREATE TABLE matviews (
mv_name NAME NOT NULL PRIMARY KEY
, v_name NAME NOT NULL
, last_refresh TIMESTAMP WITH TIME ZONE);

Berikutnya adalah fungsi untuk membuat materialized view peta

Tujuan:
- untuk mengubah peta view menjadi peta permanen dalam satuan koordinat yang sesuai.
- Menyamakan struktur kolom tabel peta view terhadap peta permanen, karena materialized view tidak dapat berjalan bilamana struktur kolom tidak sama antara peta permanen dengan peta view.

Input:
- $1 untuk judul peta permanen yang akan dibuat
- $2 untuk judul peta view yang menjadi dasar peta permanen
- $3 untuk kode SRID dari peta tersebut, misal 4326 untuk Derajat Desimal atau 32749 untuk UTM.

-- Function: create_matview_map(name, name, integer)

-- DROP FUNCTION create_matview_map(name, name, integer);

CREATE OR REPLACE FUNCTION create_matview_map(name, name, integer)
RETURNS void AS
$BODY$
DECLARE
matview ALIAS FOR $1;
view_name ALIAS FOR $2;
srid_code ALIAS FOR $3;
entry matviews%ROWTYPE;
BEGIN
SELECT * INTO entry FROM matviews WHERE mv_name = matview;

IF FOUND THEN
--RAISE EXCEPTION 'Materialized view ''%'' already exists.',
DELETE FROM matviews WHERE mv_name = matview;
END IF;

EXECUTE 'REVOKE ALL ON ' || view_name || ' FROM PUBLIC';

EXECUTE 'GRANT SELECT ON ' || view_name || ' TO PUBLIC';

EXECUTE 'CREATE TABLE ' || matview || ' AS SELECT * FROM ' || view_name;
EXECUTE 'CREATE INDEX ' || matview || '_geometry_sidx ON ' || matview || ' USING gist (geometry)';
EXECUTE 'ALTER TABLE ' || matview || ' ADD CONSTRAINT ' || matview || '_pkey PRIMARY KEY (gid)';
EXECUTE 'ALTER TABLE ' || matview || ' ADD CONSTRAINT enforce_dims_geometry CHECK (ndims(geometry) = 2)';
EXECUTE 'ALTER TABLE ' || matview || ' ADD CONSTRAINT enforce_srid_geometry CHECK (srid(geometry) = ' || srid_code || ')';

EXECUTE 'REVOKE ALL ON ' || matview || ' FROM PUBLIC';

EXECUTE 'GRANT SELECT ON ' || matview || ' TO PUBLIC';

INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, type) VALUES ('', 'public', matview, 'geometry', 2, srid_code, 'GEOMETRY');

INSERT INTO matviews (mv_name, v_name, last_refresh)
VALUES (matview, view_name, CURRENT_TIMESTAMP);

RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION create_matview_map(name, name, integer) OWNER TO postgres;

Selanjutnya adalah fungsi untuk melakukan drop off terhadap peta permanen.

Tujuan:
Menghapus peta permanen yang sudah dibuat sebelumnya, dapat diikuti dengan membuat materialized view peta baru sesuai struktur kolom peta view terbaru, atau sekedar membuang materialized view peta.

Input:
$1 untuk judul peta permanen yang akan dihapus

-- Function: drop_matview_map(name)

-- DROP FUNCTION drop_matview_map(name);

CREATE OR REPLACE FUNCTION drop_matview_map(name)
RETURNS void AS
$BODY$
DECLARE
matview ALIAS FOR $1;
entry matviews%ROWTYPE;
BEGIN

SELECT * INTO entry FROM matviews WHERE mv_name = matview;

IF NOT FOUND THEN
RAISE EXCEPTION 'Materialized view % does not exist.', matview;
END IF;

EXECUTE 'DROP TABLE ' || matview;
DELETE FROM geometry_columns WHERE f_table_name=matview;
DELETE FROM matviews WHERE mv_name=matview;

RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION drop_matview_map(name) OWNER TO postgres;

Selanjutnya adalah fungsi untuk melakukan refresh terhadap peta permanen:

Tujuan:
- melakukan refresh terhadap peta permanen yang memiliki struktur tabel sama dengan peta view.

Input:
- $1 untuk judul peta permanen yang akan direfresh

-- Function: refresh_matview(name)

-- DROP FUNCTION refresh_matview(name);

CREATE OR REPLACE FUNCTION refresh_matview(name)
RETURNS void AS
$BODY$
DECLARE
matview ALIAS FOR $1;
entry matviews%ROWTYPE;
BEGIN

SELECT * INTO entry FROM matviews WHERE mv_name = matview;

IF NOT FOUND THEN
RAISE EXCEPTION 'Materialized view % does not exist.', matview;
END IF;

EXECUTE 'DELETE FROM ' || matview;
EXECUTE 'INSERT INTO ' || matview
|| ' SELECT * FROM ' || entry.v_name;

UPDATE matviews
SET last_refresh=CURRENT_TIMESTAMP
WHERE mv_name=matview;

RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION refresh_matview(name) OWNER TO postgres;

Udah lengkap nih fungsi-fungsi materialized view peta untuk Postgisnya.

So sekarang implementasinya:
Saya gunakan:
viewmap1, untuk judul peta view dan
map1, untuk judul peta permanen.
4326, sebagai kode SRID untuk peta basisdata yang memiliki proyeksi Derajat Desimal.

Bila materialized view peta baru pertama kali dibuat, maka peta membutuhkan struktur tabel yang sama dengan peta view, maka gunakan perintah:

SELECT create_matview_map('map1','viewmap1',4326);

Bilamana hanya ingin merefresh peta terhadap perubahan nilai data pada peta view, dapat digunakan perintah:

SELECT refresh_matview('map1');

Bilamana terjadi perubahan struktur peta view, misalnya ada perubahan syntax query dari peta view, dapat digunakan perintah:
-- hapus peta permanen

SELECT drop_matview_map('map1');

-- buat peta permanen dengan struktur peta view terbaru

SELECT create_matview_map('map1','viewmap1',4326);

Untuk menghapus materialized view peta secara permanen dapat digunakan perintah:

SELECT drop_matview_map('map1');

That's all folks. Silahkan modifikasi sesukanya, so far ini yang Saya gunakan untuk menangani 30-an layer peta. 15 diantaranya adalah peta dinamis yang perlu diupdate secara harian/bulanan/tahunan.

Untuk Materialized View yang khusus format table alphanumerik (bukan peta) dapat menuju artikel ttg materialized view or snapshot.
Dimana artikel tersebut menjadi dasar tulisan map materialized view ini.

Sebagai komparasi dengan oracle dapat dibaca lewat artikel materialized view pada Oracle, yang ternyata fungsi tersebut built-in.

Best regards
[irmant]