การสืบค้น (Query) ข้อมูลเชิงพื้นที่ด้วย Spatialite (ตอนที่ 1)

• ตอนที่แล้วผมเสนอเรื่องการแปลงไฟล์ CSV เป็นข้อมูลเชิงพื้นที่ หรือเรียกกันว่า Spatial converter ฟีเจอร์ตัวนี้สามารถทำได้ง่ายๆในโปรแกรมด้าน GIS เช่น MapWindow (ข้อสำคัญคือในข้อมูล CSV นั้นต้องมีคอลัมน์ X, Y เก็บค่าพิกัดด้วย) แต่ใน spatialite ใช้คำสั่ง query เชิงพื้นที่สามารถทำให้ user ได้เข้าใจได้ลึกซึ้งกว่าใช้โปรแกรมสำเร็จรูป ซึ่งก็ไม่ได้ยากจนเกินไป มาดูตอนนี้ผมจะเสนอ การใช้ query ลึกเข้าไปอีกนิดเพื่อแสดงความสัมพันธ์ในเชิงพื้นที่ ระหว่างฐานข้อมูล 2 ตัว

ดาวน์โหลดฐานข้อมูลตัวอย่าง

• ฐานข้อมูลดั้งเดิมจะเป็น shape file ทั้งคู่ ตัวแรกผมเสนอไปแล้วในตอนแรกเป็นฐานข้อมูลแบบ point แสดง  landmark สถานที่ในกรุงเทพฯ ส่วนตัวที่สองจะเป็น shape แบบ polygon แสดงขอบเขตของแขวงและเขต
• สนใจข้อมูลทดสอบดาวน์โหลดได้ตามลิงค์นี้ spatialite_bangkokgis2.zip เราจะใช้โปรแกรม spatialite-gui ในการ import shape file เข้าไปในฐานข้อมูล spatialite แล้วจะลอง query กันดูความสัมพันธ์ในเชิงพื้นที่ จากนั้นลองเปิดดูด้วย spatialite-gis

Import Shape file เข้า spatialite

• เมื่อดาวน์โหลดมาแล้ว unzip ดูจะเห็นไฟล์เป็น shape file สองชุด คือไฟล์  landmark_n และ subdist50 เปิดโปรแกรม spatialite-gui สร้างฐานข้อมูลของ spatialite ใหม่ คลิกที่เมนู Files > Create a New (Empty) SQLite DB… จากนั้นเลืิอกโฟลเดอร์ที่จัดเก็บฐานข้อมูลแล้วเซฟชื่อ ผมตั้งชื่อเป็น bangkokgis2.sqlite

• จากนั้นใช้เมนู Files > Advanced > Load Shapefile

• จากข้อมูลที่ดาวน์โหลดมา ผมเลือก subdist50 ก่อน อย่าลืมตั้งระบบพิกัด (SRID) เป็น 32647 (UTM47N/WGS84)

• จากนั้นนำเข้า shapefile ชื่อ landmark_n แล้วตั้งค่า SRID เป็น 32647 และเลือกโค๊ดภาษาไทยเป็น CP874

ทดสอบดูฐานข้อมูลด้วย spatialite-gis

• ปิดโปรแกรม spatialite-gui แล้วเปิด spatialite-gis เปิดฐานข้อมูล bangkokgis2.sqlite จะเห็นผลการแสดงดังรูปด้านล่าง

ฐานข้อมูลเมื่อเปิดด้วย spatialite-gis

• • จัดการแสดงผลตรงเขตกทม.ให้ดูง่ายด้วยการตั้ง layer คลิกขวาที่ subdist50 แล้วเลือกเมนู Layer configuration > Classify ทำการแยกชั้น layer ตามชื่อเขตกทม. ดังรูปด้านล่าง

• จะเห็นการแสดงผลดังรูปด้านล่าง เสร็จแล้วก็ปิด spatialite-gis

ทำไมไม่รวม spatialite-gui และ spatialite-gis เป็นโปรแกรมเดียว

• กลับมาที่ spatialite-gui อีกครั้ง ผมสงสัยว่าทำไมต้องแยกเป็น spatialite-gis กับ spatialite-gui ผมว่าสองโปรแกรมนี้สามารถรวมกันได้เป็นโปรแกรมเดียว พอเข้า spatialite-gui ก็มองไม่เห็นกราฟฟิค พอเข้า spatialite-gis ก็ไม่สามารถ query ได้
• ทำการเปิดข้อมูล bangkokgis2.sqlite ด้วย spatialite-gui ที่พาเนลด้านซ้ายคลิกที่เลเยอร์ subdist50 เลือกเมนู Edit table rows จะเห็นตารางแขวงและเขตของกทม,

เริ่มต้น Query ข้อมูลเชิงพื้นที่

• ถ้าลองมองดูแค่ตารางข้อมูลแสดงสถานที่ที่สำคัญของกทม. (landmark_n) และตารางข้อมูลแสดงแขวงและเขตของกทม, (subdist50) จะเห็นสองตารางข้อมูลนี้ไม่มีอะไรสัมพันธ์กันเลย ถ้าใช้การ query ธรรมดาว่ามีโรงเรียนทั้งหมดในเขตราชเทวีมีกี่โรง ไม่สามารถทำได้เพราะในตาราง landmark_n ไม่มีฟิลด์ที่แสดง location ของแขวงและเขต แต่ฟิลด์ที่เก็บ geometry point ของ landmark และรูป polygon ของเขต (subdist50) สามารถเชื่อมโยงกันได้ด้วยค่าพิกัดจึงเรียกว่า Spatial reference ถ้าอ่านแล้วงงมาดูตัวอย่างกัน

SELECT pt.type,pt.name,poly.dname, poly.sname,X(pt.geometry),Y(pt.geometry)
FROM “landmark_n” as pt, “subdist50” as poly
WHERE pt.Type=4700 and poly.DNAME = “เขตราชเทวี”
and intersects(pt.geometry, poly.geometry)

• ดูตัวอย่าง query ด้านบนจะเห็น syntax ธรรมดาของ Select ….From…..Where….. ผม alias ชื่อ table ให้ง่ายคือ landmark_n เป็น pt และ subdist50 เป็น poly ให้ดูง่ายและเข้าใจง่ายด้วย
• ตรงหลัง Select จะเห็น X(pt.geometry) ให้แสดงค่าพิกัด X (Easting) เช่นเดียวกันกับ Yตรง Where ผมใส่  pt.Type  = 4700 ซึ่งเป็นโรงเรียน เมื่อจะหาโรงเรียนอยู่ในเขตราชเทวีก็ใช้คำสั่งดังนี้  poly.DNAME = “เขตราชเทวี”
• สืบเนื่องจาก syntax ก่อนหน้าคือเรากำหนดเงื่อนไขคือ WHERE pt.Type=4700 and poly.DNAME = “เขตราชเทวี” เมื่อใช้คู่กับ intersects(pt.geometry,poly.geometry) จึงเป็นการหาว่ารูปทรงเรขาคณิต(polygon) ของเขตราชเทวี เมื่อ intersects กับจุด(point) ที่เป็นโรงเรียนทั้งหมดในกทม. จะได้โรงเรียนที่อยู่ในเขตราชเทวีเท่าไหร่  จัดการคัดลอก syntax ด้านบนแล้วมา paste ลงในช่อง sql statement แล้วจัดการคลิก execute จะได้ผลลัพธ์ดังรูปด้านล่าง ซึ่งในตอนนี้ผมค้นได้ทั้งหมด 45 โรง

กลไกการทำงานของ Spatial reference

• จากตัวอย่างที่ผ่านมาจะเห็นฟังก์ชั่นของ spatial คือ intersects ซึ่งเป็นส่วนหนึ่งของฟังก์ชั่นจากไลบรารี GEOS (เรียกใช้ด้วย spatialite) ที่นำรูปทรงเรขาคณิตมา operation กัน ตัวอย่างเช่นหาจุดตัดระหว่างเส้นตรงสองเส้น หรือระหว่าง polyline ตัดกันกับ polygon ก่อนจะไปต่อลองมาทำความรู้จักกับ MBR (Mininum Bounding Rectangle)

MBR (Minimum Bounding Rectangle)

• MBR บางครั้งเรียกสั้นๆว่า Bounding Box หรือ Envelope เป็นรูปทรงสี่เหลี่ยมแสดงค่าพิกัดน้อยที่สุดและมากที่สุดแทนรูปทรงเรขาคณิตต่อไปนี้ได้แก่ จุด, เส้น, โพลีกอน เพื่อให้ง่ายและสะดวกในการใช้ฟังก์ชั่นด้านเรขาคณิต เช่นจะหาว่าเส้น polyline สองเส้นตัดกันหรือไม่ ขั้นต้นฟังก์ชั่นจะหาว่า MBR ทับกันหรือไม่ ถ้าไม่ทับกันก็หยุด ถ้าทับกันจะนำ polyline แต่ละ segment มา intersect กับ polyline อีกเส้นว่ามีจุดตัดกันหรือไม่ ซึ่งถ้าใช้ MBR ตรวจสอบก่อนจะทุ่นระยะเวลาได้มาก

ลักษณะการ operation ด้วยฟังก์ชั่นด้านเรขาคณิต

• มีหลากหลายรูปแบบมากลองดูรูปภาพประกอบ

• ก็ติดตามกันตอนต่อไปนะครับ