GIS 3 — Truy vấn không gian & JOIN theo vị trí

Ở bài PostGIS ta đã có kiểu dữ liệu geometry/geography, hàm dựng hình, và GIST index. Bài này là phần thực chiến: dùng các công cụ đó để trả lời những câu hỏi mà một ngân hàng thật sự hỏi mỗi ngày. "Giao dịch này xảy ra ở địa bàn nào?", "Quận nào có ít ATM nhất so với dân số?", "Chi nhánh nào gần khách hàng này nhất?", "Có bao nhiêu khách trong bán kính 5km quanh chi nhánh Cầu Giấy?". Tất cả đều là truy vấn không gian — nối và lọc dữ liệu theo vị trí thay vì theo khoá.

Điểm mấu chốt cần nắm ngay: SPATIAL JOIN là nối hai bảng bằng một quan hệ hình học (điểm nằm trong vùng, hai vùng giao nhau, hai điểm cách nhau dưới X mét) chứ không phải bằng cột khoá bằng nhau. Bạn không cần một cột district_id viết sẵn trong bảng giao dịch — bạn tính nó ra từ toạ độ. Đây là sức mạnh lớn nhất và cũng là cái bẫy hiệu năng lớn nhất của GIS.

Lưu ý sandbox: PostGIS KHÔNG được cài trên sandbox Knowledge Base (chỉ có PostgreSQL read-only chuẩn). Vì vậy mọi block SQL không gian trong bài là MINH HOẠ — dùng đúng cú pháp PostGIS thật nhưng không đánh dấu -- ▶ Chạy được. Ở cuối phần đầu có một câu non-spatial gộp theo city chạy được thật, làm phép loại suy cho ý "đếm theo địa bàn".

Mô hình dữ liệu ví dụ

Xuyên suốt bài, giả định các bảng đã có cột geometry (SRID 4326 — WGS84 kinh/vĩ độ) và đã đánh GIST index như bài trước hướng dẫn:

• districts(id, name, geom geometry(MultiPolygon, 4326)) — ranh giới quận/huyện.
• branches(id, name, geom geometry(Point, 4326)) — chi nhánh/PGD.
• atms(id, code, geom geometry(Point, 4326)) — máy ATM.
• cust_geo(customer_id, geom geometry(Point, 4326)) — vị trí khách (địa chỉ đã geocode).
• txn_geo(txn_id, amount, geom geometry(Point, 4326), created_at) — vị trí giao dịch (ATM/POS/geolocation app).

Mẫu 1 — Spatial join điểm-trong-vùng (point-in-polygon)

Bài toán nền tảng nhất: gán mỗi điểm vào vùng chứa nó. Mỗi khách hàng thuộc quận nào? Mỗi giao dịch phát sinh ở địa bàn nào? Ta join cust_geo với districts bằng vị từ ST_Contains(vùng, điểm) (vùng chứa điểm) — tương đương ST_Within(điểm, vùng) đảo vế.

-- MINH HOẠ (PostGIS, không chạy trên sandbox)
SELECT c.customer_id, d.name AS district
FROM cust_geo c
JOIN districts d
  ON ST_Contains(d.geom, c.geom);

Vài lưu ý quan trọng:

• ST_Contains vs ST_Intersects. ST_Contains yêu cầu điểm nằm hẳn bên trong, không tính điểm nằm đúng trên ranh giới (boundary). Với điểm — trường hợp thường gặp nhất — điều này hiếm khi thành vấn đề. Nhưng khi join vùng với vùng (ví dụ giao dịch có toạ độ nhiễu, hoặc polygon vùng phủ sóng), dùng ST_Intersects (có giao nhau, kể cả chạm biên) an toàn hơn và cũng tận dụng GIST index.
• Điểm rơi vào 2 quận? Nếu ranh giới có chồng lấn hoặc điểm đúng biên, một điểm có thể khớp nhiều dòng. Dùng LEFT JOIN LATERAL ... LIMIT 1 để lấy đúng một quận nếu cần đảm bảo 1-1.
• GIST index bắt buộc. Vị từ không gian (ST_Contains, ST_Intersects, ST_DWithin) chỉ dùng được index nếu cả hai cột là kiểu không gian có GIST index. Đây là điều kiện sống còn về hiệu năng — nói kỹ ở phần cuối.

Đếm điểm theo vùng

Có join là có tổng hợp. "Mỗi quận có bao nhiêu ATM?" là spatial join + GROUP BY. Dùng LEFT JOIN để quận không có ATM nào vẫn hiện với số 0 (quan trọng cho phân tích khoảng trống):

-- MINH HOẠ (PostGIS)
SELECT d.name AS district, COUNT(a.id) AS atm_count
FROM districts d
LEFT JOIN atms a
  ON ST_Contains(d.geom, a.geom)
GROUP BY d.name
ORDER BY atm_count ASC;   -- quận thiếu ATM lên đầu

Loại suy "đếm theo địa bàn" — câu này chạy thật trên sandbox. Sandbox không có PostGIS, nhưng bản chất "đếm khách theo địa bàn" là một GROUP BY bình thường nếu địa bàn đã lưu dạng cột city. Đây là phiên bản non-spatial của cùng ý tưởng, dùng đúng schema sandbox (customers, accounts):

-- ▶ Chạy được
SELECT c.city,
       COUNT(DISTINCT c.id)   AS num_customers,
       COUNT(a.id)            AS num_accounts,
       SUM(a.balance)         AS total_balance
FROM customers c
LEFT JOIN accounts a ON a.customer_id = c.id
GROUP BY c.city
ORDER BY num_customers DESC;

Sự khác biệt: ở trên city là chuỗi gán sẵn nên chỉ cần =; với GIS, "địa bàn" được suy ra từ toạ độ nên phải dùng ST_Contains. Spatial join giải phóng bạn khỏi việc phải điền cột địa bàn thủ công cho từng dòng.

Mẫu 2 — Nearest neighbor (KNN): tìm cái gần nhất

Câu hỏi "chi nhánh nào gần nhất với khách hàng này?" là nearest-neighbor search. Cách ngây thơ — tính khoảng cách tới mọi chi nhánh rồi sắp xếp — chạy được với vài chục chi nhánh nhưng là O(n×m), không co giãn.

PostGIS có toán tử khoảng cách theo chỉ mục <-> (KNN operator). Điểm đặc biệt: khi <-> đứng trong ORDER BY, planner có thể dùng index-only KNN scan trên GIST — duyệt cây theo thứ tự khoảng cách tăng dần và dừng sớm ngay khi đủ LIMIT, thay vì quét toàn bảng. Đây là lý do <-> nhanh hơn nhiều ST_Distance(...) trong ORDER BY.

Tìm chi nhánh gần nhất cho một khách:

-- MINH HOẠ (PostGIS) — 1 khách, 1 chi nhánh gần nhất
SELECT b.id, b.name,
       b.geom <-> ST_SetSRID(ST_MakePoint(105.80, 21.03), 4326) AS dist
FROM branches b
ORDER BY b.geom <-> ST_SetSRID(ST_MakePoint(105.80, 21.03), 4326)
LIMIT 1;

Để làm cho mọi khách cùng lúc — "mỗi khách, chi nhánh gần nhất" — dùng LATERAL join. LEFT JOIN LATERAL cho phép truy vấn con tham chiếu cột của hàng bên ngoài (c.geom), và với mỗi khách nó chạy lại KNN scan LIMIT 1:

-- MINH HOẠ (PostGIS) — KNN theo từng khách bằng LATERAL
SELECT c.customer_id, nn.name AS nearest_branch, nn.dist
FROM cust_geo c
LEFT JOIN LATERAL (
  SELECT b.name, c.geom <-> b.geom AS dist
  FROM branches b
  ORDER BY c.geom <-> b.geom
  LIMIT 1
) nn ON TRUE;

Cảnh báo đơn vị. Toán tử <-> trên geometry SRID 4326 trả về khoảng cách theo độ (degree) — không phải mét. Để xếp hạng cái gần nhất thì độ vẫn đúng thứ tự (đủ dùng cho LIMIT). Nhưng nếu cần con số mét thật, hãy tính lại khoảng cách bằng geography sau khi đã chọn ra ứng viên, ví dụ ST_Distance(c.geom::geography, nn.geom::geography). Sẽ nói kỹ ở phần cạm bẫy.

Mẫu 3 — Truy vấn bán kính (radius / within-distance)

"Tất cả khách hàng trong bán kính 5km quanh chi nhánh Cầu Giấy" — đây là within-distance. Hàm chuẩn là ST_DWithin(a, b, d): trả về TRUE nếu a và b cách nhau không quá d. Điều làm nó vượt trội: ST_DWithin dùng được GIST index (nó nở một hộp bao quanh rồi lọc trước bằng index), trong khi ST_Distance(a,b) < d thì không — buộc quét toàn bảng và tính khoảng cách từng cặp.

Mấu chốt là đơn vị của d. Với geography, d tính bằng mét — đúng thứ ta muốn. Với geometry SRID 4326, d tính bằng độ, gần như luôn sai ý định. Hai cách ra mét đúng:

Cách A — ép sang geography (khuyến nghị cho bán kính nhỏ, dữ liệu kinh/vĩ độ):

-- MINH HOẠ (PostGIS) — mọi khách trong 5km quanh 1 chi nhánh
SELECT c.customer_id
FROM cust_geo c, branches b
WHERE b.name = 'CN Cầu Giấy'
  AND ST_DWithin(c.geom::geography, b.geom::geography, 5000);  -- 5000 m

Cách B — ST_Transform sang hệ toạ độ mét (ví dụ VN-2000 hoặc UTM zone 48N, EPSG 32648) rồi đo bằng mét:

-- MINH HOẠ (PostGIS) — chiếu sang UTM 48N rồi đo 5000 m
SELECT c.customer_id
FROM cust_geo c, branches b
WHERE b.name = 'CN Cầu Giấy'
  AND ST_DWithin(
        ST_Transform(c.geom, 32648),
        ST_Transform(b.geom, 32648),
        5000);

Cách A gọn hơn và không cần biết SRID mét địa phương; cách B chính xác hơn cho vùng rộng và cho phép đo diện tích/độ dài về sau. Cạm bẫy chí mạng của cách B: đừng gọi ST_Transform trên cột của bảng lớn trong WHERE như trên — nó biến đổi từng hàng, index vô dụng. Với bảng lớn, hãy tạo cột hình học đã chiếu sẵn (materialized) và index nó, hoặc dùng cách A với geography.

Mẫu 4 — Tổng hợp không gian (spatial aggregation)

Khi đã gán điểm vào vùng, ta gom nhóm và tổng hợp cả hình học:

• ST_Union(geom) — hàm gộp (aggregate) nhiều hình thành một; ví dụ gộp tất cả quận nội thành thành một polygon "vùng lõi".
• ST_Centroid(geom) — tâm hình học của một vùng; hữu ích để đặt nhãn bản đồ hoặc lấy "điểm đại diện" của quận cho phân tích khoảng cách thô.
• ST_Collect(geom) — gom các hình rời thành một collection (nhanh hơn ST_Union khi không cần hoà tan biên).

-- MINH HOẠ (PostGIS) — mỗi quận: số giao dịch, tổng tiền, tâm quận
SELECT d.name,
       COUNT(t.txn_id)        AS num_txn,
       SUM(t.amount)          AS total_amount,
       ST_Centroid(d.geom)    AS district_center
FROM districts d
LEFT JOIN txn_geo t
  ON ST_Intersects(d.geom, t.geom)
GROUP BY d.name, d.geom;

Gom cụm (clustering) nâng cao có ST_ClusterDBSCAN(geom, eps, minpoints) — hàm cửa sổ gán nhãn cụm cho các điểm dày đặc, dùng để phát hiện "điểm nóng" giao dịch mà không cần lưới quận định trước.

Mẫu 5 — Phủ & khoảng trống (coverage & gap analysis)

Câu hỏi mở rộng mạng lưới: "Vùng nào chưa có ATM/chi nhánh?" Đây là spatial join phủ định — tìm quận không chứa điểm nào. Dùng LEFT JOIN ... WHERE ... IS NULL (anti-join) hoặc NOT EXISTS:

-- MINH HOẠ (PostGIS) — quận KHÔNG có ATM nào
SELECT d.name
FROM districts d
WHERE NOT EXISTS (
  SELECT 1 FROM atms a
  WHERE ST_Contains(d.geom, a.geom)
);

Phiên bản tinh vi hơn: quận có dân/giao dịch cao nhưng thưa ATM — kết hợp đếm giao dịch (Mẫu 4) với đếm ATM (Mẫu 1) rồi lọc num_txn cao AND atm_count thấp. Đây chính là đầu vào cho quyết định đặt máy mới. Ta cũng có thể tính vùng chưa phủ: ST_Difference(quận, ST_Union(buffer bán kính phục vụ của các ATM)) cho ra đúng polygon "lỗ hổng dịch vụ".

Hiệu năng: quy tắc bất di bất dịch

Truy vấn không gian dễ nhanh, cũng dễ chậm gấp nghìn lần nếu sai. Các nguyên tắc:

Khi đọc EXPLAIN ANALYZE cho truy vấn không gian, điều bạn muốn thấy là dòng Index Scan using idx_..._geom hoặc Index Cond: (geom && ...) — dấu && là toán tử "bounding-box giao nhau" mà GIST dùng để lọc thô trước, rồi vị từ chính xác (ST_Contains...) chỉ chạy trên số ít ứng viên còn lại. Nếu thấy Seq Scan kèm Filter: st_contains(...) trên bảng triệu dòng, gần như chắc chắn thiếu index hoặc bạn đã vô tình bọc cột trong một hàm (như ST_Transform) làm index vô hiệu.

Cạm bẫy thường gặp

• Nhầm độ vs mét. Trên SRID 4326, khoảng cách/<->/ST_DWithin tính bằng độ. ST_DWithin(g, g2, 5) nghĩa là 5 độ (~550km ở xích đạo!), không phải 5 mét. Muốn mét: ép ::geography hoặc ST_Transform sang hệ mét.
• SRID lệch. Join hai cột khác SRID (ví dụ 4326 với 3857) sẽ báo lỗi Operation on mixed SRID geometries hoặc — tệ hơn — cho kết quả vô nghĩa. Luôn kiểm ST_SRID(geom) và thống nhất một SRID khi nạp dữ liệu.
• geometry vs geography. geometry = hình học phẳng, nhanh, nhưng khoảng cách/diện tích sai trên phạm vi rộng vì Trái Đất cong. geography = tính trên mặt cầu, ra mét chính xác, nhưng chậm hơn và ít hàm hơn. Quy tắc thực dụng: lưu geometry 4326 để dùng chung, ép ::geography cục bộ khi cần đo lường chính xác.
• Toạ độ đảo (lat/lng vs lng/lat). PostGIS quy ước ST_MakePoint(x, y) = (kinh độ, vĩ độ). Nhập nhầm thứ tự đưa điểm Hà Nội ra giữa đại dương. Luôn nhớ: X = longitude trước, Y = latitude sau.
• ST_Contains bỏ sót điểm trên biên. Nếu cần tính cả điểm nằm đúng ranh giới, dùng ST_Intersects hoặc ST_Covers.

Use case thực tế

Bối cảnh NCB — gán giao dịch vào địa bàn để phân tích rủi ro & mở rộng mạng lưới.

Đội Data nhận yêu cầu: (1) phân bổ ~2,4 triệu giao dịch tháng vào quận/huyện để dựng bản đồ nhiệt rủi ro và doanh thu theo địa bàn; (2) đề xuất 5 vị trí đặt ATM mới; (3) gắn cờ giao dịch "địa lý bất thường".

Các bước:

1. Geocode & nạp một lần. Chuyển địa chỉ khách và toạ độ giao dịch (từ log ATM/POS/app) thành Point SRID 4326, đánh GIST index. Việc nạp chỉ chạy một lần — mọi truy vấn sau tái dùng.
2. Spatial join gán địa bàn (Mẫu 1). ST_Contains(district.geom, txn.geom) gán 2,4 triệu giao dịch vào 30 quận. Với GIST index, batch này chạy vài chục giây thay vì hàng giờ. Kết quả materialize thành cột district_id để BI đọc lại tức thì.
3. Bản đồ nhiệt & khoảng trống (Mẫu 4 + 5). Đếm giao dịch/khách/số dư theo quận, chồng với số ATM hiện có. Phát hiện 4 quận có mật độ giao dịch cao nhưng < 2 ATM → shortlist mở rộng. ST_Difference chỉ ra polygon vùng chưa phủ trong bán kính phục vụ 3km.
4. KNN cho trải nghiệm khách (Mẫu 2). Cung cấp truy vấn "chi nhánh gần nhất" cho app mobile: mỗi lần khách bấm "tìm chi nhánh", một KNN <-> LIMIT 5 trả về ngay dưới 10ms nhờ GIST.
5. Cờ địa lý bất thường (Mẫu 3). Với mỗi giao dịch, nếu vị trí cách quá xa các điểm giao dịch lịch sử của khách trong 24h (ví dụ hai giao dịch cách 500km trong 30 phút — bất khả thi vật lý), gắn cờ nghi vấn. Đây là một tín hiệu khoảng cách bổ trợ cho hệ thống giám sát; nó feed vào luồng giám sát giao dịch AML như một feature rủi ro, không thay thế các luật ngưỡng tiền.

Kết quả: quyết định đặt ATM dựa trên dữ liệu thay vì cảm tính, dashboard rủi ro theo địa bàn cập nhật hằng đêm, và một tín hiệu địa lý mới cho đội chống gian lận — tất cả từ một cột toạ độ và các mẫu truy vấn ở trên. Việc lập lịch chạy các batch gán địa bàn hằng đêm có thể tham khảo thêm ở tối ưu mạng lưới & lập lịch.

Ghi nhớ

• Spatial join nối bảng theo quan hệ hình học (ST_Contains, ST_Intersects, ST_DWithin), không theo khoá bằng nhau — bạn tính địa bàn từ toạ độ.
• Point-in-polygon (ST_Contains) gán điểm vào vùng; thêm GROUP BY để đếm điểm theo vùng. Dùng LEFT JOIN để vùng rỗng vẫn hiện số 0.
• KNN dùng toán tử <-> trong ORDER BY ... LIMIT; kết hợp LATERAL để tìm "cái gần nhất theo từng hàng". <-> tận dụng KNN index scan trên GIST, nhanh hơn hẳn ST_Distance trong ORDER BY.
• Bán kính dùng ST_DWithin (dùng được index), không ST_Distance < x (không dùng index). Ra mét thì ép ::geography hoặc ST_Transform sang hệ toạ độ mét.
• Hiệu năng: GIST index trên mọi cột geom; đừng bọc cột trong ST_Transform giữa WHERE/JOIN; đọc EXPLAIN tìm Index Scan + toán tử &&, thấy Seq Scan là báo động.
• Cạm bẫy: nhầm độ vs mét, SRID lệch giữa hai cột, geometry vs geography, và đảo thứ tự ST_MakePoint(lng, lat).
• Trên sandbox không có PostGIS: SQL không gian là minh hoạ; chỉ câu gộp theo city trên customers/accounts là chạy thật — phiên bản non-spatial của "đếm theo địa bàn".