การสืบค้น (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
สร้างฐานข้อมูล spatialite ใหม่
  • จากนั้นใช้เมนู Files > Advanced > Load Shapefile
Import Shapefile
  • จากข้อมูลที่ดาวน์โหลดมา ผมเลือก subdist50 ก่อน อย่าลืมตั้งระบบพิกัด (SRID) เป็น 32647 (UTM47N/WGS84)
เลือก shapefile ตั้ง SRID เลือกโค๊ดภาษาไทยเป็น CP874
  • จากนั้นนำเข้า shapefile ชื่อ landmark_n แล้วตั้งค่า SRID เป็น 32647 และเลือกโค๊ดภาษาไทยเป็น CP874

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

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

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

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

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

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

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

เริ่มต้น 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 โรง
ผลลัพธ์แสดงผลการ Query

กลไกการทำงานของ 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 ตรวจสอบก่อนจะทุ่นระยะเวลาได้มาก
MBR ของ polyline (รูปซ้าย MBR ไม่ทับกัน, รูปกลาง MBR ทับกันแต่เส้นไม่ตัด และรูปขวา MBR ทับกันและเส้น polyline ตัดกันด้วย)

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

  • มีหลากหลายรูปแบบมากลองดูรูปภาพประกอบ
ลักษณะการ operation ของรูปทรงเรขาคณิต
  • ก็ติดตามกันตอนต่อไปนะครับ

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

  1. สวัสดีครับ
    รบกวนหน่อยนะครับ
    มีวิธี connect sqlite กับ php ไหมครับ
    ขอบคุณมากครับ

    1. สวัสดีครับ ลองเข้าไปดูที่ลิงค์นี้ น่าจะใช้ได้เผอิญผมไม่ได้เขียน php ต้องขอโทษด้วยครับ

Leave a Reply

Your email address will not be published.