0Pricing
SQL Academy · บทเรียน

การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม

จุดใดอยู่ในพื้นที่ใดบ้าง

การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

การเชื่อมตารางเชิงพื้นที่คืออะไร

การเชื่อมตารางเชิงพื้นที่ คือตัวเชื่อมตารางสองตารางโดยอาศัยความสัมพันธ์ทางภูมิศาสตร์แทนคีย์ที่ตรงกัน แทนที่จะถามว่า "ID นี้เท่ากับ ID นั้นหรือไม่" คุณจะถามว่า "จุดนี้อยู่ภายในรูปหลายเหลี่ยมนั้นหรือไม่" หรือ "รูปร่างสองรูปนี้ซ้อนทับกันหรือไม่"

PostGIS ขยาย PostgreSQL ด้วยชนิดข้อมูลเรขาคณิตและฟังก์ชันเชิงพื้นที่ที่ทำให้การเชื่อมตารางลักษณะนี้เป็นไปได้ ผลลัพธ์เหมือนกับการเชื่อมตาราง JOIN ทั่วไปในเอสคิวแอล คือรวมแถวจากทั้งสองตารางเข้าด้วยกัน แต่เงื่อนไขจะพิจารณาจากรูปทรง

การตั้งค่าตารางตัวอย่าง

เรามาสร้างตารางสองตารางเพื่อใช้งาน ได้แก่ cities สำหรับเก็บตำแหน่งจุด และ countries สำหรับเก็บขอบเขตโพลิกอน ทั้งสองตารางใช้ชนิด GEOMETRY จาก PostGIS โดยมี SRID 4326 (ละติจูด/ลองจิจูดตามมาตรฐาน WGS84)

SRID (รหัสอ้างอิงเชิงพื้นที่) ระบุให้ PostGIS ทราบว่าควรใช้ระบบพิกัดใด โดย SRID 4326 เป็นระบบที่ใช้กันมากที่สุดสำหรับข้อมูล GPS

CREATE TABLE countries (
  id     SERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  border GEOMETRY(POLYGON, 4326)
);

CREATE TABLE cities (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  location GEOMETRY(POINT, 4326)
);

การแทรกข้อมูลตัวอย่าง

เราจะใช้ ST_GeomFromText เพื่อแทรกค่ารูปร่างเรขาคณิตจากรูปแบบ Well-Known Text (WKT) โดย WKT เป็นรูปแบบข้อความมาตรฐานสำหรับแทนเรขาคณิต ได้แก่ POINT(lon lat) สำหรับจุด และ POLYGON((...)) สำหรับโพลิกอน

โปรดสังเกตว่าค่าลองจิจูดจะอยู่ก่อนค่าละติจูดใน WKT ซึ่งสอดคล้องกับแบบแผน X, Y ทางคณิตศาสตร์

INSERT INTO countries (name, border) VALUES
  ('France', ST_GeomFromText('POLYGON((-5 42, 8 42, 8 51, -5 51, -5 42))', 4326)),
  ('Spain',  ST_GeomFromText('POLYGON((-9 36, 3 36, 3 44, -9 44, -9 36))', 4326));

INSERT INTO cities (name, location) VALUES
  ('Paris',    ST_GeomFromText('POINT(2.35 48.85)',   4326)),
  ('Madrid',   ST_GeomFromText('POINT(-3.70 40.42)',  4326)),
  ('Bordeaux', ST_GeomFromText('POINT(-0.58 44.84)',  4326)),
  ('Lisbon',   ST_GeomFromText('POINT(-9.14 38.72)',  4326));

ST_Contains: ฟังก์ชันหลัก

ST_Contains(geometry A, geometry B) จะคืนค่า TRUE เมื่อเรขาคณิต A ครอบคลุมเรขาคณิต B ทั้งหมด สำหรับกรณีของเรา ST_Contains(country.border, city.location) จะคืนค่าเป็นจริงเมื่อจุดเมืองอยู่ภายในโพลิกอนประเทศ

นี่คือหัวใจสำคัญของการเชื่อมตารางเพื่อตรวจสอบการครอบคลุมใน PostGIS ฟังก์ชันนี้เป็นส่วนหนึ่งของมาตรฐาน OGC และทำงานได้กับเรขาคณิตทุกประเภทที่นำมาจับคู่กัน

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Within: มุมมองย้อนกลับ

ST_Within(geometry A, geometry B) มีความหมายตรงข้ามกับ ST_Contains ทุกประการ โดยจะคืนค่า TRUE เมื่อเรขาคณิต A อยู่ภายในเรขาคณิต B ทั้งหมด ST_Within(city, country) จึงมีความหมายทางตรรกะเหมือนกับ ST_Contains(country, city)

ฟังก์ชันทั้งสองให้ผลลัพธ์เหมือนกันในกรณีนี้ การเลือกใช้จึงขึ้นอยู่กับความอ่านง่าย โปรดเลือกฟังก์ชันที่ทำให้คำสืบค้นของคุณอ่านได้เป็นธรรมชาติมากกว่า

-- These two queries return identical results:

-- Using ST_Contains (country contains city)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Contains(co.border, ci.location);

-- Using ST_Within (city is within country)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Within(ci.location, co.border);

ใช้ LEFT JOIN เพื่อค้นหาจุดที่ไม่ตรงกัน

JOIN แบบปกติจะตัดเมืองที่ไม่อยู่ภายในโพลิกอนประเทศใด ๆ ออก โปรดใช้ LEFT JOIN ร่วมกับการตรวจสอบ WHERE ... IS NULL เพื่อค้นหาจุดที่ไม่มีโพลิกอนครอบคลุม ซึ่งมีประโยชน์สำหรับตรวจหาปัญหาคุณภาพข้อมูลหรือจุดที่อยู่นอกพื้นที่ครอบคลุมของคุณ

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
LEFT JOIN countries co
  ON   ST_Contains(co.border, ci.location)
ORDER BY co.name NULLS LAST;

การนับจุดในแต่ละโพลิกอน

การเชื่อมตารางเชิงพื้นที่จะแสดงประสิทธิภาพได้ดีเมื่อใช้ร่วมกับการรวมผล โดยเชื่อมเมืองกับประเทศแล้วจัดกลุ่มตามประเทศ คุณจะสามารถนับได้ว่ามีกี่จุดอยู่ภายในโพลิกอนแต่ละแห่ง รูปแบบนี้พบได้บ่อยมากในการวิเคราะห์ภูมิศาสตร์ เช่น การนับร้านค้าในแต่ละภูมิภาค เหตุการณ์ในแต่ละเขต และเซนเซอร์ในแต่ละโซน เป็นต้น

SELECT
  co.name          AS country,
  COUNT(ci.id)     AS city_count
FROM   countries co
LEFT JOIN cities  ci
  ON   ST_Contains(co.border, ci.location)
GROUP BY co.name
ORDER BY city_count DESC;

การใช้ดัชนีเชิงพื้นที่เพื่อเพิ่มประสิทธิภาพ

การเชื่อมตารางเชิงพื้นที่อาจทำงานช้ากับชุดข้อมูลขนาดใหญ่ เพราะต้องทดสอบทุกจุดกับทุกโพลิกอน ดัชนี GIST (ต้นไม้ค้นหาแบบทั่วไป) ช่วยให้ PostGIS ใช้ตัวกรองล่วงหน้าด้วยกรอบขอบเขต เพื่อข้ามการเปรียบเทียบส่วนใหญ่ก่อนเรียกใช้การทดสอบ ST_Contains ที่แม่นยำ

ควรสร้างดัชนี GIST บนคอลัมน์เรขาคณิตที่ใช้เชื่อมตารางหรือกรองข้อมูลเสมอ ตัววางแผนการสืบค้นจะเลือกใช้ดัชนีนี้โดยอัตโนมัติ

-- Create GIST indexes on both geometry columns
CREATE INDEX idx_countries_border
  ON countries USING GIST (border);

CREATE INDEX idx_cities_location
  ON cities USING GIST (location);

-- The same join now benefits from index acceleration
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Intersects: เรขาคณิตที่ทับซ้อนกัน

ST_Intersects(A, B) จะคืนค่า TRUE หากเรขาคณิตสองชุดมีจุดใด ๆ ร่วมกัน รวมถึงกรณีที่สัมผัสกันเพียงตรงขอบเขต ฟังก์ชันนี้ยืดหยุ่นกว่า ST_Contains เพราะโพลิกอนสองชุดที่ทับซ้อนกันจะตัดกัน แม้ไม่มีชุดใดครอบคลุมอีกชุดหนึ่งทั้งหมด

สำหรับการทดสอบจุดในโพลิกอน ST_Intersects และ ST_Contains ให้ผลเทียบเท่ากัน แต่ ST_Intersects ใช้ดัชนี GIST ได้มีประสิทธิภาพกว่า และมักเป็นตัวเลือกที่นิยมใช้ในทางปฏิบัติ

-- ST_Intersects is index-friendly and equivalent
-- to ST_Contains for point-in-polygon tests
SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Intersects(co.border, ci.location);

การเชื่อมจุดกับโพลิกอนที่ใกล้ที่สุด

เมื่อจุดอยู่ตรงขอบเขตพอดีหรืออยู่ใกล้โพลิกอนหลายชุดมาก คุณอาจต้องการโพลิกอนที่ ใกล้ที่สุด แทนที่จะรับผลลัพธ์ทุกชุดที่ตรงกัน ST_Distance ใช้วัดระยะห่างระหว่างเรขาคณิตสองชุด จึงรองรับรูปแบบ ORDER BY + LIMIT หรือการเชื่อมตารางแบบเฉพาะทางด้วย LATERAL ร่วมกับ ORDER BY ... LIMIT 1

รูปแบบนี้เรียกว่าการเชื่อมตารางกับเพื่อนบ้านที่ใกล้ที่สุด และพบได้บ่อยเมื่อจับร่องรอย GPS เข้ากับช่วงถนน หรือกำหนดจุดให้กับภูมิภาคที่ใกล้ที่สุด

-- For each city, find the single closest country centroid
SELECT DISTINCT ON (ci.name)
  ci.name                              AS city,
  co.name                              AS nearest_country,
  ST_Distance(ci.location, ST_Centroid(co.border)) AS dist
FROM   cities   ci
CROSS JOIN countries co
ORDER BY ci.name, dist;

ตัวอย่างการใช้งานจริง: ร้านอาหารในเขตต่าง ๆ

นี่คือตัวอย่างตั้งแต่ต้นจนจบที่สมจริง นั่นคือการค้นหาว่าร้านอาหารแต่ละร้านอยู่ในเขตเมืองใด จากนั้นนับจำนวนร้านอาหารในแต่ละเขต รูปแบบนี้ใช้ได้กับทุกกรณีที่เป็นจุดในโพลิกอน เช่น ตู้ ATM ในย่านต่าง ๆ อุบัติเหตุในเขตการปกครอง และคำสั่งซื้อในโซนจัดส่ง

คำสืบค้นนี้ใช้ ST_Within ภายในการเชื่อมตารางเชิงพื้นที่ และจัดกลุ่มผลลัพธ์เพื่อสร้างรายงานสรุป

-- Assume tables: districts(id, name, boundary GEOMETRY)
--                restaurants(id, name, location GEOMETRY)

SELECT
  d.name                 AS district,
  COUNT(r.id)            AS restaurant_count,
  STRING_AGG(r.name, ', ' ORDER BY r.name) AS names
FROM   districts    d
LEFT JOIN restaurants r
  ON   ST_Within(r.location, d.boundary)
GROUP BY d.name
ORDER BY restaurant_count DESC;

ตรวจสอบความเข้าใจอย่างรวดเร็ว

ทดสอบความเข้าใจเกี่ยวกับการเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุมใน PostGIS

ทบทวน: การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม

ในบทเรียนนี้ คุณได้เรียนรู้วิธีตอบคำถาม "จุดใดอยู่ในพื้นที่ใดบ้าง" โดยใช้การเชื่อมตารางเชิงพื้นที่ของ PostGIS

ประเด็นสำคัญ:

  • ST_Contains(โพลิกอน, จุด) — คืนค่าเป็นจริงเมื่อโพลิกอนครอบคลุมจุดทั้งหมด
  • ST_Within(จุด, โพลิกอน) — เป็นมุมมองย้อนกลับ และมีความหมายทางตรรกะเทียบเท่ากับ ST_Contains
  • ST_Intersects — การทดสอบการทับซ้อนแบบทั่วไปกว่า และเหมาะกับการใช้ดัชนีสำหรับจุดในโพลิกอน
  • ดัชนี GIST บนคอลัมน์เรขาคณิตมีความสำคัญอย่างยิ่งต่อประสิทธิภาพเมื่อข้อมูลมีขนาดใหญ่
  • ใช้การเชื่อมตารางเชิงพื้นที่ร่วมกับ GROUP BY เพื่อนับหรือรวมจุดในแต่ละภูมิภาค
  • ใช้ LEFT JOIN เพื่อตรวจหาจุดที่อยู่นอกโพลิกอนทั้งหมด

การเชื่อมตารางเชิงพื้นที่ช่วยให้วิเคราะห์ภูมิศาสตร์ได้โดยตรงด้วยภาษาสืบค้นฐานข้อมูล โดยไม่จำเป็นต้องใช้เครื่องมือ GIS ภายนอก

คำถามที่พบบ่อย

บทเรียน “การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม”

จุดใดอยู่ในพื้นที่ใดบ้าง คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน

บทเรียน “การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. ชนิดข้อมูลเชิงพื้นที่
  2. ระยะทางและเพื่อนบ้านที่ใกล้ที่สุด
  3. การเชื่อมตารางเชิงพื้นที่และการตรวจสอบการครอบคลุม
  4. ดัชนีเชิงพื้นที่ (GiST)
← กลับไปที่ SQL Academy