WITH on Postgresql

_____คงจะไม่มีอะไรที่จะมีความสุขเท่าการได้นอนพักผ่อนในวันหยุดยาวๆแบบนี้ จากที่ทั้งต้องทำงานหนักมาทั้งปี การได้พักผ่อนและหยุดให้เวลาบนเข็มนาฬิกาเร่งรีบเร่งรัดการดำเนินชีวิตเป็นอะไรที่ดีอย่างบอกไม่ถูก ผมใช้เวลาตอนเช้าในการพักผ่อนตื่นไปทำบุญที่วัดและทำกิจกรรมกับครอบครัว รู้สึกอิจฉาคนที่ใช้ชีวิตอยู่ต่างจังหวัดจริงๆ ไม่ต้องเจอกับควันผิดและรถติด ถึงจะไม่โก้หรูแต่ก็มีความสุขสบายบนความเรียบง่าย ตอนค่ำๆก็มานั้งอ่านเมล์และตอบกระทู้สนทนาตามเว็บบอร์ดที่เป็นสมาชิก ผมไปเจอเรื่องเกี่ยวกับ WITH ที่น่าสนใจบน Postgresql บน Blog ของ Paul Ramsey ที่เขียนไว้อย่างน่าสนใจมาก เลยอยากนำมาฝากท่านที่ใช้งาน Spatial Database หรือเป็น DBA

______ผมเองใช้งาน WITH อยู่บน Postgresql 8.4 มาตั้งแต่แรกที่ Upgrade โปรแกรมจาก 8.3 เป็น 8.4 แล้วเพราะด้วยความสะดวกในการสร้าง Subquery ขนาดใหญ่หรือแบบที่ซับซ้อมมันค่อนข้างสะดวกและมีประสิทธิภาพแบบเดิม หลายท่านที่ยังไม่เคยใช้งานผมมีตัวอย่างง่ายๆมาแนะนำเช่น

WITH c_pro AS (SELECT *,st_area(the_geom)/1000000 AS area_ FROM province WHERE st_area(the_geom) > 5000000000),
a_pro as (SELECT SUM(tot_all) AS pop,province_t FROM amphoe GROUP BY province_t)
SELECT c.province_t,c.area_,a.pop FROM a_pro a,c_pro c WHERE c.province_t=a.province_t  AND a.pop/c.area_ > 100
_____ตัวอย่างการใช้ WITH เพื่อสร้าง Sub-query ในการคำนวณหาความหนาแน่นประชากรต่อพื้นที่ โดยใช้ Spatial Function คำนวณหาพื้นที่จริงจาก ข้อมูล Geometry ของตาราง Province ข้อดีคือเราสามารถสร้าง Sub-query รอไว้คล้ายๆ View เพื่อรองรับการประมวลผลการสืบค้นกับ SQL หลายๆแบบได้ ลดการต้องสร้าง SQL สำหรับ Sub-query ซ้ำและเหมาะกับข้อมูลขนาดใหญ่ นอกจาก WITH และยังมี WITH RECURSIVE อีกหนึ่งที่เพิ่มเข้ามา เป็นการใช้ฟังก์ชั่น Recursive บน SQL ที่ลดการเขียน code ที่ซับซ้อนถ้าเป็นก่อนหน้าการสร้าง Recursive Function ต้องเขียน PL/SQL เพื่อประมวลผลคำสั่งสืบค้น แต่ใน 8.4 เราสามารถใช้ SQL WITH RECURSIVE ได้เลยดังตัวอย่างนี้

WITH RECURSIVE t(m,n) AS (
VALUES (0,0)
UNION ALL
SELECT m+1 ,n-1  FROM t WHERE m < 100
)
SELECT sum(m),sum(n) FROM t;
______ตัวอย่างด้านบนเป็นการหาค่าผลรวมเพิ่มทีละ 1 จาก 0 ถึง 100และผลรวมของการลดลงทีละ 1 จาก 0 – 100 การใช้ฟังก์ชั่น Recursive ประยุกต์ใช้ได้ในหลายแบบทั้งแบบการคำนวณหาความต่อเนื่องของข้อมูล เรื่องของโครงสร้างแบบกราฟหรือแบบต้นไม้ ในงาน GIS ที่ใช้มากคือเรื่องของ Network โดยเฉพาะการหา Connectivity ของข้อมูล ตัวอย่างเป็นการประยุกต์ใช้ฟังก์ชั่น ST_Dwithin กับการเปรียบเทียม StartPoint และ EndPoint ของ แต่ละ line Segment ที่อยู่ใกล้กันจบครบ โดยในตัวอย่าง SQL เริ่มจาก node ที่ 13 ไปจบที่ 1

WITH RECURSIVE walk_network(id, segment) AS (
SELECT id, segment FROM network WHERE id = 13
UNION ALL
SELECT n.id, n.segment
FROM network n, walk_network w
WHERE ST_DWithin(ST_EndPoint(w.segment),ST_StartPoint(n.segment),0.001)
)

SELECT * FROM walk_network

_____ผลลัพธ์ที่ได้คือ line ที่ {13,9,6,3,1} ซึ่งมี connected point ร่วมกันและมองได้ว่าเป็นเส้นทางจาก จุดเริ่มต้นที่ 13 ไปจบที่จุดสิ้นสุดของ 1 ดังภาพด้านล่างครับ
______จะเห็นได้ว่าการใช้ WITH Recursive มีประโยชน์และสามารถใช้ในงาน QC ข้อมูลเช่นข้อมูล network การตรวจงานที่จ้างเด็กเพื่อ digitize ข้อมูลถนน,ทางน้ำ,โครงข่ายท่อ การตรวจสอบการ snap และการปิด node เพื่อเตรียมข้อมูลสำหรับการวิเคราะห์โครงข่าย ที่จำเป็นจะต้องใช้คุณสมบัติการเชื่อมต่อของข้อมูล และอีกประเด็นที่อยากนำเสนอคือ Spatial Database มันมีความสามารถที่สูงขึ้นไม่ได้เป็นแค่ storage อีกต่อไป เพราะฟังก์ชั่นการวิเคราะห์และประมวลผลข้อมูลเชิงตำแหน่งขั้นสูงก็ได้ถูกรวมและเตรียมให้ DBA ได้ใช้งาน แต่แน่นอนว่าอาจจะไม่ง่ายในการกดปุ่มเหมือนโปรแกรม GIS Desktop แต่ถ้าท่านต้องตรวจสอบข้อมูลขนาดใหญ่เป็นแสนเป็นล้านบรรทัด อย่างไรก็จำเป็นที่ต้องใช้งานระบบฐานข้อมูลที่มีความสามารถในการประมวลผลคำสั่งการสืบค้น และแน่นอนว่าบุคคลที่ทำการประมวลผลต้องเข้าใจทั้งเรื่องของ Spatial Relation และเข้าใจเรื่องของ SQL Programming ด้วยครับ
อ้างอิงจาก

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

สร้างเว็บไซต์หรือบล็อกฟรีที่ WordPress.com.

Up ↑

%d bloggers like this: