PostgreSQL 地震資料空間分析與 JSON 操作

– 8 分鐘閱讀

PostGIS 提供強大的地理空間功能，能有效處理地震資料的空間分析需求。文章首先示範如何從 JSON 格式的地震資料中提取經緯度資訊，並利用 ST_MakePoint 和 ST_SetSRID 等 PostGIS 函式將其轉換為地理空間資料型別，方便後續的空間查詢和分析。接著，文章詳細說明如何將轉換後的地理空間資料儲存至資料表的新欄位，並建立 GIST 索引以提升查詢效能。文章也以 Tulsa 為例，示範如何使用 ST_DWithin 函式查詢特定距離內的地震事件，展現 PostGIS 在空間分析方面的實用性。除了空間分析，文章也涵蓋了 PostgreSQL 中 JSON 資料的操作技巧，包含使用 JSON 運算子和函式進行資料的提取、修改和刪除，以及如何將 SQL 查詢結果轉換為 JSON 格式，提供更全面的 JSON 資料處理方法。

將地震JSON資料轉換為空間資料並進行分析

在處理地震資料時，我們經常需要對資料進行空間分析。藉助PostGIS的GIS技術，我們可以輕鬆實作這一點。首先，我們需要將儲存在JSON中的坐標資料轉換為PostGIS的資料型別。

提取地震的位置資料

我們的JSON資料包含每個地震的經度和緯度值，這些值位於geometry下的coordinates陣列中。以下是一個例子：

"geometry": {
    "type": "Point",
    "coordinates": [-178.581, 51.8418333333333, 22.48]
}

在這個陣列中，第一個值代表經度，第二個值代表緯度，第三個值代表深度（以公里為單位），我們暫時不使用這個值。

清單 1：提取地震的位置資料

SELECT id,
       earthquake #>> '{geometry, coordinates}' AS coordinates,
       earthquake #>> '{geometry, coordinates, 0}' AS longitude,
       earthquake #>> '{geometry, coordinates, 1}' AS latitude
FROM earthquakes
ORDER BY id
LIMIT 5;

這個查詢應該傳回五行資料，結果如下：

| id | coordinates | longitude | latitude | |

-|

|

|

| | 1 | [-122.852, 38.8228333, 2.48] | -122.852 | 38.8228333 | | 2 | [-148.3859, 64.2762, 16.2] | -148.3859 | 64.2762 | | 3 | [-152.489, 59.0143, 73] | -152.489 | 59.0143 | | 4 | [-115.82, 32.7493333, 9.85] | -115.82 | 32.7493333 | | 5 | [-115.6446667, 33.1711667, 5.89]| -115.6446667| 33.1711667 |

透過與原始JSON資料進行比較，我們可以確認經度和緯度值已經正確提取。

將JSON位置資料轉換為PostGIS地理資料型別

下一步是使用PostGIS函式將經度和緯度值轉換為geography型別的Point。

清單 2：將JSON位置資料轉換為PostGIS地理資料

SELECT ST_SetSRID(
           ST_MakePoint(
               (earthquake #>> '{geometry, coordinates, 0}')::numeric,
               (earthquake #>> '{geometry, coordinates, 1}')::numeric
           ),
           4326
       )::geography AS earthquake_point
FROM earthquakes
ORDER BY id;

在ST_MakePoint()函式內，我們提取經度和緯度值，並將它們轉換為numeric型別。然後，我們使用ST_SetSRID()函式設定空間參考系統識別符號（SRID）為4326，這對應於常用的WGS 84坐標系。最後，我們將結果轉換為geography型別。

結果如下：

| earthquake_point | |

| | 0101000020E61000004A0C022B87B65EC0A6C7009A52694340 | | 0101000020E6100000D8F0F44A598C62C0EFC9C342AD115040 | | –snip– |

雖然這些字串難以直接解讀，但我們可以使用pgAdmin的幾何檢視器在地圖上繪製這些點。

將JSON坐標永久轉換為PostGIS地理列

為了方便進行更複雜的GIS分析，我們可以將JSON坐標永久轉換為earthquakes表中的一列，資料型別為geography。

清單 3：將JSON坐標轉換為PostGIS地理列

ALTER TABLE earthquakes ADD COLUMN earthquake_point geography(POINT, 4326);
UPDATE earthquakes
SET earthquake_point = ST_SetSRID(
                           ST_MakePoint(
                               (earthquake #>> '{geometry, coordinates, 0}')::numeric,
                               (earthquake #>> '{geometry, coordinates, 1}')::numeric
                           ),
                           4326
                       )::geography;
CREATE INDEX quake_pt_idx ON earthquakes USING GIST (earthquake_point);

我們首先使用ALTER TABLE新增一個名為earthquake_point的列，資料型別為geography，並指定SRID為4326。然後，我們使用UPDATE陳述式填充這個新列，使用與清單 2相同的轉換邏輯。最後，我們在新列上建立一個使用GIST索引的空間索引。

清單內容解密：

  1. 新增列：使用ALTER TABLE陳述式新增一個新的列earthquake_point到earthquakes表中，型別為geography(POINT, 4326)，表示這是一個儲存點幾何物件的列，使用WGS 84坐標系（SRID = 4326）。
  2. 更新列資料：透過UPDATE陳述式，將JSON資料中的經緯度資訊提取出來並轉換成PostGIS的geography型別，儲存在新新增的列中。
  3. 建立索引：使用CREATE INDEX陳述式在新列上建立一個GIST索引，以提高空間查詢的效率。

發現特定距離內的地震

有了儲存在earthquake_point列中的地理資料，我們現在可以輕鬆地進行空間分析，例如查詢發生在特定城市一定距離內的地震。

藉助於PostGIS強大的空間函式，我們可以進一步探索和分析地震資料，從而獲得更有價值的洞察。

使用 PostgreSQL 處理 JSON 資料：地震資料分析與 JSON 操作

在前面的章節中，我們探討瞭如何在 PostgreSQL 中處理和分析 JSON 資料。本章節將繼續探討如何利用 PostgreSQL 的功能來分析地震資料以及生成和操作 JSON 資料。

分析地震資料

首先，我們使用 Listing 16-20 中的 SQL 查詢來找出距離 Tulsa 50 英里內的地震事件。

SELECT 
  earthquake #>> '{properties, place}' AS place,
  to_timestamp((earthquake -> 'properties' ->> 'time')::bigint / 1000) AT TIME ZONE 'UTC' AS time,
  (earthquake #>> '{properties, mag}')::numeric AS magnitude,
  earthquake_point
FROM 
  earthquakes
WHERE 
  ST_DWithin(earthquake_point, ST_GeogFromText('POINT(-95.989505 36.155007)'), 80468)
ORDER BY 
  time;

內容解密：

  1. earthquake #>> '{properties, place}'：從 JSON 資料中提取 place 屬性值。
  2. to_timestamp：將地震事件的時間戳記轉換為可讀的時間格式。
  3. ST_DWithin：檢查地震事件是否在指定距離（50 英里，約 80468 米）內。
  4. ST_GeogFromText：將 Tulsa 的經緯度座標轉換為地理空間資料型別。

查詢結果顯示了在 Tulsa 附近發生的地震事件，包括地點、時間和震級。

生成和操作 JSON 資料

PostgreSQL 提供了一系列函式來生成和操作 JSON 資料。以下是一些實用的例子。

將查詢結果轉換為 JSON

使用 to_json() 函式，可以將 SQL 查詢結果轉換為 JSON 格式。

SELECT 
  to_json(employees) AS json_rows
FROM 
  employees;

內容解密：

  1. to_json(employees)：將 employees 表中的每一行轉換為 JSON 物件。

結果如下：

{"emp_id":1,"first_name":"Julia","last_name":"Reyes","salary":115300.00,"dept_id":1}
{"emp_id":2,"first_name":"Janet","last_name":"King","salary":98000.00,"dept_id":1}

指定要轉換的欄位

使用 row() 建構函式，可以指定要轉換的欄位。

SELECT 
  to_json(row(emp_id, last_name)) AS json_rows
FROM 
  employees;

內容解密：

  1. row(emp_id, last_name)：建構一個包含 emp_id 和 last_name 的行值。
  2. to_json()：將該行值轉換為 JSON 物件。

結果如下：

{"f1":1,"f2":"Reyes"}
{"f1":2,"f2":"King"}

使用子查詢生成自定義的 JSON 鍵名

SELECT 
  to_json(employees) AS json_rows
FROM 
  (SELECT emp_id, last_name AS ln FROM employees) AS employees;

內容解密：

  1. 子查詢：選擇 emp_id 和 last_name，並將 last_name 別名為 ln。
  2. to_json()：將查詢結果轉換為 JSON 物件。

結果如下：

{"emp_id":1,"ln":"Reyes"}
{"emp_id":2,"ln":"King"}

聚合多行 JSON 資料

使用 json_agg() 函式，可以將多行 JSON 資料聚合成一個 JSON 陣列。

SELECT 
  json_agg(to_json(employees)) AS json
FROM 
  (SELECT emp_id, last_name AS ln FROM employees) AS employees;

內容解密：

  1. json_agg(to_json(employees))：將多行 JSON 物件聚合成一個 JSON 陣列。

結果如下：

[{"emp_id":1,"ln":"Reyes"}, {"emp_id":2,"ln":"King"}]

新增、更新和刪除 JSON 鍵值對

PostgreSQL 也提供了函式來新增、更新和刪除 JSON 鍵值對。

新增或更新頂層鍵值對

UPDATE films
SET film = film || '{"studio": "Pixar"}'::jsonb
WHERE film @> '{"title": "The Incredibles"}'::jsonb;

內容解密：

  1. film || '{"studio": "Pixar"}'：新增或更新 studio 鍵值對。
  2. film @> '{"title": "The Incredibles"}'：檢查 title 是否為 “The Incredibles”。

更新JSON資料

在處理JSON資料時，我們經常需要更新現有的資料。PostgreSQL提供了多種方法來更新JSON資料，包括使用UPDATE陳述式和各種JSON處理函式。

新增頂層鍵值對

我們可以使用UPDATE陳述式和連線運算子||來新增頂層鍵值對。Listing 16-25展示了兩個例子。

UPDATE films
SET film = film || '{"studio": "Pixar"}'::jsonb
WHERE film @> '{"title": "The Incredibles"}'::jsonb;

UPDATE films
SET film = film || jsonb_build_object('studio', 'Pixar')
WHERE film @> '{"title": "The Incredibles"}'::jsonb;

內容解密：

  1. UPDATE films：更新films表格。
  2. SET film = film || '{"studio": "Pixar"}'::jsonb：使用連線運算子||將現有的film JSON與新的鍵值對{"studio": "Pixar"}合併。::jsonb表示將字串轉換為jsonb型別。
  3. WHERE film @> '{"title": "The Incredibles"}'::jsonb：只更新title為"The Incredibles"的列。
  4. jsonb_build_object('studio', 'Pixar')：建立一個新的jsonb物件，包含鍵studio和值Pixar。

更新路徑上的值

我們可以使用jsonb_set()函式來更新特定路徑上的值。Listing 16-26展示了一個例子。

UPDATE films
SET film = jsonb_set(film, '{genre}', film #> '{genre}' || '["World War II"]', true)
WHERE film @> '{"title": "Cinema Paradiso"}'::jsonb;

內容解密：

  1. jsonb_set(film, '{genre}', film #> '{genre}' || '["World War II"]', true)：更新film JSON中genre鍵的值。
  2. film #> '{genre}'：提取genre鍵的值。
  3. || '["World War II"]'：將新的值"World War II"新增到現有的genre陣列中。
  4. true：表示如果路徑不存在，則建立它。

刪除值

我們可以使用-和#-運算子來刪除鍵值對或路徑上的值。Listing 16-27展示了兩個例子。

UPDATE films
SET film = film - 'studio'
WHERE film @> '{"title": "The Incredibles"}'::jsonb;

UPDATE films
SET film = film #- '{genre, 2}'
WHERE film @> '{"title": "Cinema Paradiso"}'::jsonb;

內容解密：

  1. film - 'studio'：刪除studio鍵和其值。
  2. film #- '{genre, 2}'：刪除genre陣列中索引為2的值。

使用JSON處理函式

PostgreSQL提供了多種JSON處理函式，包括計算陣列長度、將陣列元素轉換為行等。

計算陣列長度

我們可以使用jsonb_array_length()函式來計算陣列的長度。Listing 16-28展示了一個例子。

SELECT id, film ->> 'title' AS title, jsonb_array_length(film -> 'characters') AS num_characters
FROM films
ORDER BY id;

內容解密：

  1. jsonb_array_length(film -> 'characters')：計算characters陣列的長度。

將陣列元素轉換為行

我們可以使用jsonb_array_elements()和jsonb_array_elements_text()函式將陣列元素轉換為行。Listing 16-29展示了一個例子。

SELECT id, jsonb_array_elements(film -> 'genre') AS genre_jsonb, jsonb_array_elements_text(film -> 'genre') AS genre_text
FROM films
ORDER BY id;

內容解密：

  1. jsonb_array_elements(film -> 'genre')：將genre陣列元素轉換為jsonb型別的行。
  2. jsonb_array_elements_text(film -> 'genre')：將genre陣列元素轉換為文字型別的行。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。