การใช้ SQLite สำหรับเก็บและจัดการข้อมูล GeoJSON (Part 2)

กรณี 1 เก็บข้อมูลแบบปกติทั่วไป (text, number, ...) แล้วใช้คำสั่งเรียกมาประกอบกันเป็น GeoJSON เมื่อต้องการ

เราจะใช้ sqlite3 ทำงานแบบโต้ตอบเพื่อสาธิตการทำงาน
หมายเหตุ
 jsDb    เป็น database file 
 ~#      คือ system prompt
 sqlite> คือ sqlite3 prompt

ใช้คำสั่ง sqlite3 jsDb เริ่มต้นใช้งาน sqlite3 กับ database file ชื่อ jsDb
(กรณีนี้เพิ่งเริ่มงาน ยังไม่มี jsDb อยู่ก่อน จึงถูกสร้างขึ้นใหม่)

~# sqlite3 jsDb
SQLite version 3.51.2 2026-01-09 17:27:48
Enter ".help" for usage hints.

ใช้คำสั่ง CREATE TABLE สร้างตาราง (table) ชื่อ jpoint สำหรับข้อมูลจำเป็นของ geoJSON ประเภท point
sqlite> CREATE TABLE jpoint (feat_ID INTEGER UNIQUE, title TEXT, value REAL, lon REAL, lat REAL);

ใช้คำสั่ง .schema ขอดูโครงสร้างข้อมูลของตารางที่สร้างไว้
sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE jpoint (feat_ID INTEGER UNIQUE, title TEXT, value REAL, lon REAL, lat REAL);

ใช้คำสั่ง INSERT INTO ป้อนข้อมูล
sqlite> INSERT INTO jpoint values(1001, "apple", 1.2, 100.71 , 13.82);
sqlite> INSERT INTO jpoint values(1002, "banana", 2.3, 100.72 , 13.83);
sqlite> INSERT INTO jpoint values(1003, "carrot", 3.4, 100.73 , 13.84);

ใช้คำสั่ง SELECT สืบค้นข้อมูลทั้งหมด แสดงผลแบบ list
sqlite> SELECT * from jpoint;
1001|apple|1.2|100.71|13.82
1002|banana|2.3|100.72|13.83
1003|carrot|3.4|100.73|13.84

เปลี่ยนรูปแบบการแสดงผลเป็น "table" (ปกติเป็น list)
sqlite> .mode table
sqlite> select * from jpoint;
+---------+--------+-------+--------+-------+
| feat_ID | title  | value |  lon   |  lat  |
+---------+--------+-------+--------+-------+
| 1001    | apple  | 1.2   | 100.71 | 13.82 |
| 1002    | banana | 2.3   | 100.72 | 13.83 |
| 1003    | carrot | 3.4   | 100.73 | 13.84 |
+---------+--------+-------+--------+-------+

สั่งสืบค้นข้อมูลตามเงื่อนไข WHERE แบบพื้นฐาน เลือกระเบียนข้อมูลด้วยเงื่อนไข title='banana'

sqlite> .mode list
sqlite> select * from jpoint where title = 'banana';

ผลที่ได้
1002|banana|2.3|100.72|13.83

สั่งสืบค้นข้อมูลตามเงื่อนไข WHERE โดยให้ผลออกมาเป็น geoJSON ชนิด point
- มีการใช้ฟังก์ชัน json_object() และ json_array() ช่วย
- ผลที่ได้จะเป็น text string ที่สามารถนำไป parse เป็น JSON object ได้

sqlite> SELECT json_object('type','Feature','properties',json_object('id',feat_ID,'title',title,'value',value),
'geometry',json_object('type','Point','coordinates',json_array(lon,lat))) FROM jpoint WHERE title = 'banana';

ผลที่ได้
{"type":"Feature","properties":{"id":1002,"title":"banana","value":2.3},"geometry":{"type":"Point","coordinates":[100.72,13.83]}}

ข้อสังเกต
- มีการใช้ฟังก์ชัน json เฉพาะตอนเรียกค้นข้อมูลเมื่อต้องการผลลัพธ์รูปแบบ geoJSON เท่านั้น: json_object(), json_array()
- ตัวอย่างที่แสดงเหมาะกับ point feature เพราะส่วนของ geometry เป็นข้อมูลพื้นฐาน (lon, lat)
- สำหรับ linestring หรือ polygon เป็นกรณีท้าทาย เพราะข้อมูลพิกัดตำแหน่งเป็นแบบเชิงซ้อน และอาจมีขนาดใหญ่

การใช้ฟังก์ชัน json_group_array() เพื่อเก็บ point feature ทั้งหมดมารวมกันเป็น FeatureCollection สามารถทำได้โดยสั่งประมวลผลแบบ batch processing
คำสั่งที่ใช้ค่อนข้างยาว ควรรวมกันเป็นบรรทัดเดียวก่อนใช้งาน  ถ้าใช้บ่อยให้เก็บไว้ในไฟล์ เช่น "featCollOutput.sql" และสั่งใช้งานดังนี้: .read featCollOutput.sql 
หรือ sqlite3 jsDb ".read featCollOutput.sql"

~# sqlite3 jsDb "SELECT json_object('type', 'FeatureCollection','features', json_group_array(json_object('type', 
 'Feature','properties', json_object('id', feat_ID, 'title', title, 'value', value),'geometry', json_object('type', 'Point',
 'coordinates', json_array(lon, lat))))) FROM jpoint;"
 
ผลลัพธ์
{"type":"FeatureCollection","features":[
  {"type":"Feature","properties":{"id":1001,"title":"apple","value":1.2},"geometry":{"type":"Point","coordinates":[100.71,13.82]}},
  {"type":"Feature","properties":{"id":1002,"title":"banana","value":2.3},"geometry":{"type":"Point","coordinates":[100.72,13.83]}},
  {"type":"Feature","properties":{"id":1003,"title":"carrot","value":3.4},"geometry":{"type":"Point","coordinates":[100.73,13.84]}}]}

ถ้าต้องการเก็บผลลัพธ์เป็นแฟ้มข้อมูล สามารถเปลี่ยนการแสดงผลให้ส่งออกไปยังแฟ้มข้อมูลที่ต้องการ
ทำได้โดยเพิ่มรหัสข้างล่างที่ท้ายคำสั่งเดิม
  > path_to_file.json
  
ถ้าเป็นการ append ข้อมูลเข้าไปในแฟ้มข้อมูลเดิม ให้ใช้ >> แทน >