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

กรณี 2 เก็บข้อมูลเป็นรูปแบบที่คล้าย GeoJSON มากที่สุด แล้วใช้ฟังก์ชันพิเศษช่วยแปลงเป็น GeoJSON

ขอตั้งเป้าหมายว่าต้องการเก็บข้อมูลของ point feature ตามนี้
- ตัวอย่าง geojson: {"type":"Feature","properties":{"id":1001,"title":"apple","value":1.2},
  "geometry":{"type":"Point","coordinates":[100.71,13.82]}}
- ไม่เก็บข้อมูลซ้ำซ้อน เช่น id, title, value มีอยู่ภายใน geojson ของpoint feature แล้ว ต้องไม่มีที่อื่นอีก

สร้าง table ชื่อ jsonpoint ดังนี้
- มี 2 column
  - feat_ID เหมือนแบบแรก (INTEGER UNIQUE)
  - geojson เป็นชนิด json (เก็บจริงเป็น text string)

sqlite> CREATE TABLE jsonpoint (feat_ID INTEGER UNIQUE, geojson json);

ตรวจดูผลลัพธ์ (มองหา jsonpoint พบว่าอยู่ที่บรรทัด 3)
sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE jpoint (feat_ID INTEGER UNIQUE, title TEXT, value REAL, lon REAL, lat REAL);
CREATE TABLE jsonpoint (feat_ID INTEGER UNIQUE, geojson json);

ป้อนข้อมูล
sqlite> insert into jsonpoint values(1001,json('{"type":"Feature","properties":{"id":1001,"title":"apple","value":1.2},"geometry":{"type":"Point","coordinates":[100.71,13.82]}}'));
sqlite> insert into jsonpoint values(1002,json('{"type":"Feature","properties":{"id":1002,"title":"banana","value":2.3},"geometry":{"type":"Point","coordinates":[100.72,13.83]}}'));
sqlite> insert into jsonpoint values(1003,json('{"type":"Feature","properties":{"id":1003,"title":"carrot","value":3.4},"geometry":{"type":"Point","coordinates":[100.73,13.84]}}'));

เรียกค้นดูข้อมูลที่ป้อน
sqlite> select * from jsonpoint;
1001|{"type":"Feature","properties":{"id":1001,"title":"apple","value":1.2},"geometry":{"type":"Point","coordinates":[100.71,13.82]}}
1002|{"type":"Feature","properties":{"id":1002,"title":"banana","value":2.3},"geometry":{"type":"Point","coordinates":[100.72,13.83]}}
1003|{"type":"Feature","properties":{"id":1003,"title":"carrot","value":3.4},"geometry":{"type":"Point","coordinates":[100.73,13.84]}}

สถานการณ์
- โครงสร้างข้อมูลง่าย มีเพียง 2 คอลัมน์
- ค่า attributes ซ่อนอยู่ในคอลัมน์ 2 ใช้สืบค้นแบบปกติไม่ได้
  ใช้แบบนี้ไม่ได้: select * from jsonpoint where title='apple';
- คอลัมน์ 2 มีข้อมูลที่เป็น geojson แบบสมบูรณ์ที่ต้องการ

ล้วงลึกข้อมูลในคอลัมน์ geojson โดยใช้ตัวช่วย (ฟังก์ชันจัดการ JSON)
- เรียกดูเฉพาะ title (เส้นทางเข้าถึง: properties.title)

sqlite> SELECT json_extract(geojson, '$.properties.title') from jsonpoint;
apple
banana
carrot

- เรียกดูเฉพาะ value (เส้นทางเข้าถึง: properties.value)
sqlite> SELECT json_extract(geojson, '$.properties.value') from jsonpoint;
1.2
2.3
3.4

- เรียกดูเฉพาะ geojson (เส้นทางเข้าถึง: properties.title='banana')
sqlite> SELECT geojson from jsonpoint where json_extract(geojson, '$.properties.title') = 'banana';
{"type":"Feature","properties":{"id":1002,"title":"banana","value":2.3},"geometry":{"type":"Point","coordinates":[100.72,13.83]}}

สรุป
- ฟังก์ชันจัดการ JSON ที่ใช้
  - json() ใช้กลั่นกรองตอนป้อนข้อมูลไม่ให้ผิดพลาด
  - json_extract() ใช้ล้วงลึกข้อมูลภายใน JSON ออกมาใช้งาน
- ฟังก์ชันจัดการ JSON ทำให้เราสามารถใช้ sqlite จัดการกับข้อมูล geojson ได้ไม่ยากนัก