PostgreSQL空間查詢與JSON應用

PostgreSQL 提供強大的空間查詢功能，允許開發者有效地分析地理資料。透過 ST_Area() 函式，可以計算指定區域的幾何面積，並配合 geography 型別精確計算地理面積。ST_Within() 函式則能根據經緯度座標判斷點是否位於特定多邊形內，實作地理定位功能。此外，ST_DWithin() 函式可以計算兩個地理物件之間的距離，方便評估特定範圍內的人口或其他屬性總和。空間查詢搭配座標轉換函式 ST_Transform()，能確保不同來源的空間資料正確疊加與分析，例如將美國普查資料轉換至 WGS 84 座標系統。除了空間資料處理，PostgreSQL 也支援 JSON 資料格式，提供 json 和 jsonb 兩種型別。jsonb 型別以二進位制格式儲存，具備更佳的查詢效能和索引功能，適合儲存非結構化資料或需要彈性 schema 的應用場景。理解 PostgreSQL 中 JSON 的特性與操作方式，能有效提升資料函式庫應用彈性。

使用空間查詢分析地理資料

本章節將探討如何利用PostgreSQL的空間查詢功能來分析地理資料，包括計算區域面積、根據經緯度查詢縣市，以及評估特定範圍內的人口數量。

計算區域面積

要計算特定區域的面積，我們可以利用PostgreSQL的geometry資料型別和相關的空間函式。首先，將geometry型別的列轉換為geography型別，以便獲得以平方米為單位的面積。

SELECT 
    name,
    round(ST_Area(geom::geography) / 2589988.110336, 2) AS square_miles
FROM 
    us_counties_2019_shp
ORDER BY 
    square_miles DESC
LIMIT 5;

內容解密：

1. ST_Area(geom::geography)：計算geom列轉換為geography型別後的面積（平方米）。
2. / 2589988.110336：將面積從平方米轉換為平方英里。
3. round(..., 2)：將結果四捨五入到小數點後兩位。
4. ORDER BY square_miles DESC：按面積從大到小排序。
5. LIMIT 5：僅顯示前五個結果。

執行此查詢後，我們可以發現美國阿拉斯加州的幾個自治市鎮面積遼闊，排名前五的區域皆位於該州。

根據經緯度查詢縣市

利用ST_Within()函式，我們可以根據給定的經緯度座標確定其所屬的縣市。

SELECT 
    sh.name,
    c.state_name
FROM 
    us_counties_2019_shp sh
JOIN 
    us_counties_pop_est_2019 c ON sh.statefp = c.state_fips AND sh.countyfp = c.county_fips
WHERE 
    ST_Within('SRID=4269;POINT(-118.3419063 34.0977076)'::geometry, geom);

內容解密：

1. ST_Within()：判斷第一個幾何物件是否包含於第二個幾何物件內。
2. 'SRID=4269;POINT(-118.3419063 34.0977076)'::geometry：定義一個具有指定SRID的點，並將其轉換為geometry型別。
3. sh.statefp = c.state_fips AND sh.countyfp = c.county_fips：根據州和縣的FIPS程式碼進行連線。

執行此查詢後，我們可以發現座標(-118.3419063, 34.0977076)位於加利福尼亞州的洛杉磯縣。

評估特定範圍內的人口數量

要評估特定地點周圍的人口數量，我們可以使用ST_DWithin()函式來找出在指定距離內的縣市，並匯總其人口數量。

SELECT 
    sum(c.pop_est_2019) AS pop_est_2019
FROM 
    us_counties_2019_shp sh
JOIN 
    us_counties_pop_est_2019 c ON sh.statefp = c.state_fips AND sh.countyfp = c.county_fips
WHERE 
    ST_DWithin(sh.geom::geography, ST_GeogFromText('SRID=4269;POINT(-96.699656 40.811567)'), 80467);

內容解密：

1. ST_DWithin()：判斷兩個地理物件之間的距離是否在指定範圍內。
2. sh.geom::geography：將geom列轉換為geography型別，以便進行距離計算。
3. ST_GeogFromText('SRID=4269;POINT(-96.699656 40.811567)')：根據指定的經緯度和SRID建立一個地理點。
4. 80467：指定的距離（米），相當於50英里。

執行此查詢後，我們可以得到內布拉斯加州林肯市周圍50英里範圍內的人口總數。

視覺化縣市邊界

為了更好地理解查詢結果，我們可以將縣市邊界視覺化。

SELECT 
    sh.name,
    c.state_name,
    c.pop_est_2019,
    ST_Transform(sh.geom, 4326) AS geom
FROM 
    us_counties_2019_shp sh
JOIN 
    us_counties_pop_est_2019 c ON sh.statefp = c.state_fips AND sh.countyfp = c.county_fips
WHERE 
    ST_DWithin(geom::geography, ST_GeogFromText('SRID=4269;POINT(-96.699656 40.811567)'), 80467);

內容解密：

1. ST_Transform(sh.geom, 4326)：將geom列轉換為指定的SRID（4326），以便在pgAdmin的幾何檢視器中正確顯示。

執行此查詢後，我們可以在pgAdmin中檢視縣市邊界的地圖表示。

空間資料分析：以美國普查資料為例

在進行空間資料分析時，正確地處理和轉換不同座標系統的資料至關重要。以美國普查資料（US Census data）為例，這些資料通常以 Shapefile 格式提供，並包含地理空間資訊，如縣界、道路和水道等。這些資料的座標系統可能與我們使用的底圖（如 OpenStreetMap）不同，因此需要進行座標轉換，以確保資料能夠正確疊加顯示。

座標轉換：ST_Transform() 函式

在 PostgreSQL 中，我們可以使用 ST_Transform() 函式將一個幾何物件從一個座標參考系統（CRS）轉換到另一個 CRS。例如，美國普查資料使用的座標系統可能是 North American Datum 83 (NAD83)，而 OpenStreetMap 使用的是 WGS 84，其對應的 SRID（Spatial Reference System Identifier）是 4326。要將普查資料轉換到 WGS 84，我們可以這樣寫：

SELECT ST_Transform(geom, 4326)
FROM census_data;

內容解密：

1. ST_Transform(geom, 4326)：此函式將 geom 列中的幾何物件轉換到 SRID 為 4326 的座標系統，即 WGS 84。
2. geom：代表輸入的幾何物件，通常是來自某個表格的幾何欄位。
3. 4326：是 WGS 84 座標系統的 SRID。

空間連線：分析道路與水道

空間連線允許我們根據空間關係（如相交、包含等）合併不同表格的資料。例如，若要找出 Santa Fe 河與哪些道路相交，我們可以對包含道路和水道資訊的表格進行空間連線。

首先，下載並匯入包含 Santa Fe County 道路和水道資訊的 Shapefile 到 PostgreSQL 資料函式庫，分別命名為 santafe_roads_2019 和 santafe_linearwater_2019。然後，使用 ST_Intersects() 函式檢查哪些道路與 Santa Fe 河相交：

SELECT water.fullname AS waterway,
       roads.rttyp,
       roads.fullname AS road,
       ST_AsText(ST_Intersection(water.geom, roads.geom)) AS intersection_point
FROM santafe_linearwater_2019 water
JOIN santafe_roads_2019 roads
ON ST_Intersects(water.geom, roads.geom)
WHERE water.fullname = 'Santa Fe Riv'
AND roads.fullname IS NOT NULL
ORDER BY roads.fullname;

內容解密：

1. ST_Intersects(water.geom, roads.geom)：檢查 water.geom 和 roads.geom 是否相交，回傳布林值。
2. ST_Intersection(water.geom, roads.geom)：計算 water.geom 和 roads.geom 的交點，並以幾何物件表示。
3. ST_AsText()：將幾何物件轉換為 WKT（Well-Known Text）格式，以便閱讀。
4. water.fullname = 'Santa Fe Riv'：過濾出名稱為 ‘Santa Fe Riv’ 的水道記錄，即 Santa Fe 河。
5. roads.fullname IS NOT NULL：排除道路名稱為 NULL 的記錄。

結果與應用

上述查詢將傳回 Santa Fe 河與各道路的交點座標，以及相關的道路和水道資訊。這些資訊對於城市規劃、緊急應變計畫等具有重要價值。例如，透過分析建築物輪廓資料與河道或洪水區的空間關係，可以識別出可能受到洪水威脅的建築物。

使用 JSON 資料格式

JavaScript 物件表示法（JSON）是一種廣泛使用的文字格式，用於以平台無關的方式儲存資料，以便在電腦系統之間共用。在本章中，您將學習 JSON 的結構以及如何在 PostgreSQL 中儲存和查詢 JSON 資料型別。在探索 PostgreSQL 的 JSON 查詢運算元之後，我們將分析一個月的地震資料。

瞭解 JSON 結構

JSON 資料主要由兩個結構組成：物件（object）和陣列（array）。物件是一種無序的名稱/值對集合，而陣列則是一種有序的值集合。如果您曾經使用過 JavaScript、Python 或 C# 等程式語言，那麼 JSON 的這些方面應該看起來很熟悉。

在物件內部，我們使用名稱/值對作為儲存和參照個別資料專案的結構。整個物件被包圍在花括號內，每個名稱（通常稱為鍵）被包圍在雙引號中，後面跟著一個冒號和其對應的值。物件可以封裝多個鍵/值對，以逗號分隔。

以下是一個使用電影資訊的例子：

{"title": "The Incredibles", "year": 2004}

鍵是 title 和 year，它們的值分別是 "The Incredibles" 和 2004。如果值是字串，則用雙引號括起來。如果是數字、布林值或 null，則省略引號。如果您熟悉 Python 語言，您會將這種結構視為字典。

陣列是一種有序的值列表，被包圍在方括號內。我們用逗號分隔陣列中的每個值。例如，我們可以列出電影型別如下：

["animation", "action"]

陣列在程式語言中很常見，我們在 SQL 查詢中已經使用過它們。在 Python 中，這種結構稱為列表。

我們可以建立許多這種結構的變體，包括在彼此內巢狀物件和陣列。例如，我們可以建立一個物件陣列，或將陣列用作鍵的值。我們可以新增或省略鍵/值對，或建立額外的物件陣列，而不違反預設的架構。這種靈活性與 SQL 表格的嚴格定義形成對比，既是使用 JSON 作為資料儲存的一部分吸引力，也是處理 JSON 資料的最大困難之一。

JSON 資料範例

清單 16-1 顯示了以 JSON 格式儲存的我最喜歡的兩部電影的資訊。最外層的結構是一個具有兩個元素的陣列，每個元素代表一部電影。我們知道最外層的結構是一個陣列，因為整個 JSON 以方括號開始和結束。

[{
    "title": "The Incredibles",
    "year": 2004,
    "rating": {
        "MPAA": "PG"
    },
    "characters": [{
        "name": "Mr. Incredible",
        "actor": "Craig T. Nelson"
    }, {
        "name": "Elastigirl",
        "actor": "Holly Hunter"
    }, {
        "name": "Frozone",
        "actor": "Samuel L. Jackson"
    }],
    "genre": ["animation", "action", "sci-fi"]
}, {
    "title": "Cinema Paradiso",
    "year": 1988,
    "characters": [{
        "name": "Salvatore",
        "actor": "Salvatore Cascio"
    }, {
        "name": "Alfredo",
        "actor": "Philippe Noiret"
    }],
    "genre": ["romance", "drama"]
}]

清單 16-1：包含兩部電影資訊的 JSON

在最外層的陣列內，每個電影物件都被花括號包圍。第一部電影 The Incredibles 的物件以 { 開始。對於兩部電影，我們都將標題和年份儲存為鍵/值對，它們分別具有字串和整數值。第三個鍵 rating 的值是一個 JSON 物件。該物件包含一個單一的鍵/值對，顯示電影的美國電影協會評級。

#### 內容解密：

此 JSON 資料展示了 JSON 的靈活性。首先，如果我們稍後想要新增另一國家的評級，我們可以輕鬆地將第二個鍵/值對新增到 rating 值物件中。這種靈活性使得 JSON 成為一種受歡迎的資料儲存格式。

PostgreSQL 中的 JSON 資料型別

PostgreSQL 支援兩種 JSON 資料型別：json 和 jsonb。json 資料型別儲存 JSON 資料的文字表示，而 jsonb 資料型別則以二進位格式儲存 JSON 資料。jsonb 資料型別支援索引，因此查詢效能更好。

在SQL中使用JSON的時機與考量

在探討何時應該在SQL中使用JSON之前，我們需要了解使用NoSQL或檔案資料函式庫（document databases）儲存資料的優缺點。這些資料函式庫以JSON或其他文字為基礎的資料格式儲存資料，與SQL使用的關聯式表格（relational tables）不同。檔案資料函式庫在資料定義上具有彈性，可以根據需要動態調整資料結構。此外，檔案資料函式庫通常能夠透過增加伺服器來擴充套件，因此適合處理高容量應用。然而，這種彈性可能意味著放棄SQL的一些優勢，例如容易新增的約束條件（constraints），這些約束條件能夠強制資料完整性（data integrity）並支援交易（transactions）。

幸運的是，SQL中對JSON的支援使得我們能夠同時享受兩者的優勢，將JSON資料新增為關聯式表格中的欄位。選擇使用SQL或NoSQL資料函式庫應該是一個多導向的決定。PostgreSQL在速度方面相對於NoSQL表現良好，但我們還需要考慮儲存的資料種類別和數量、所服務的應用程式等。

在某些情況下，使用JSON與SQL可能是合理的，例如：

使用JSON的時機

1. 使用者或應用程式需要任意建立鍵/值對：例如，在標記一系列醫學研究論文時，一個使用者可能想要新增一個鍵來追蹤化學名稱，而另一個使用者可能想要一個鍵來追蹤食物名稱。

2. 將相關資料儲存在JSON欄位中，而不是單獨的表格：員工表格（employees table）可以包含通常的姓名和聯絡資訊欄位，加上一個JSON欄位，該欄位包含一個彈性的鍵/值對集合，這些鍵/值對可能包含不適用於每位員工的額外屬性，例如公司獎項或績效指標。

3. 透過分析從其他系統取得的JSON資料而節省時間，而無需先將其解析成一系列表格。

然而，在PostgreSQL或其他SQL資料函式庫中使用JSON也可能帶來挑戰。在常規SQL表格上很容易設定的約束條件，在JSON資料上設定和執行可能會更加困難。JSON資料可能會佔用更多空間，因為鍵名稱與引號、逗號和大括號一起在文字中重複出現。最後，如果鍵意外消失或值的資料型別發生變化，JSON的彈性可能會對與之互動的程式碼（無論是SQL還是其他語言）造成問題。

PostgreSQL中的json與jsonb資料型別

PostgreSQL提供了兩種用於儲存JSON的資料型別：json和jsonb。兩者都允許插入有效的JSON，也就是符合JSON規範要求的文字，例如物件周圍的大括號、分隔物件的逗號以及鍵的正確參照。如果嘗試插入無效的JSON，資料函式庫將產生錯誤。

這兩種型別之間的主要區別在於，一種將JSON儲存為文字，另一種將其儲存為二進位制資料。二進位制實作是PostgreSQL較新的功能，通常由於其查詢速度更快且具有索引功能而受到青睞。

json與jsonb的區別

• json型別：將JSON儲存為文字，保持空白並維持鍵的順序。如果單個JSON物件多次包含特定鍵（這是有效的），json型別將保留每個重複的鍵/值對。每次資料函式庫函式處理json儲存的文字時，都必須解析該物件以解釋其結構。這使得從資料函式庫讀取的速度比jsonb型別慢。並且，不支援索引。通常，當應用程式具有重複鍵或需要保留鍵的順序時，json型別很有用。

• jsonb型別：將JSON以二進位制格式儲存，刪除空白且不維持鍵的順序。如果單個JSON物件多次包含特定鍵，jsonb型別將只保留最後一個鍵/值對。二進位制格式為寫入表格增加了一些開銷，但處理速度更快。並且，支援索引。

PostgreSQL的檔案建議，除非需要保留鍵/值對的順序，否則使用jsonb。本章的其餘部分將專門使用jsonb，因為它在速度方面更具優勢，而且許多PostgreSQL的JSON函式對json和jsonb的工作方式相同——並且有更多函式可用於jsonb。

匯入和索引JSON資料

在書籍資源的第16章資料夾中（位於https://nostarch.com/practical-sql-2nd-edition/），檔案films.json包含清單16-1中JSON的修改版本。用文字編輯器檢視該檔案，您將看到每個電影的JSON物件被放在單獨的一行上，元素之間沒有換行符。我還刪除了最外層的方括號和分隔兩個電影物件的逗號。每個物件仍然是有效的JSON物件。接下來，我們將探索如何將這些JSON資料匯入表格並建立索引。