PostgreSQL全文檢索與PostGIS空間資料應用

PostgreSQL不僅僅是關聯式資料函式庫，其全文檢索功能和PostGIS擴充套件，更讓它成為強大的文字和空間資料分析工具。全文檢索允許開發者對大量文字資料進行高效查詢，並利用ts_rank()和ts_rank_cd()函式依據關鍵字的出現頻率和分佈進行相關性排序，提升搜尋結果的精準度。此外，文章也詳細介紹了PostGIS，這個空間資料擴充套件讓PostgreSQL具備處理地理空間資料的能力，包含點、線、多邊形等幾何圖形的表示和操作。從WKT格式的理解到SRID的運用，文章逐步引導讀者瞭解空間資料的表示方式，並提供使用PostGIS函式建立空間物件的實務操作，讓開發者能快速上手，將空間資料分析整合到既有的PostgreSQL應用中。

利用全文檢索分析美國總統演講稿

在前面的章節中，我們學習瞭如何使用 PostgreSQL 的全文檢索功能來分析和查詢文字資料。本文將繼續探討如何利用 ts_rank() 和 ts_rank_cd() 函式對查詢結果進行相關性排名。

相關性排名查詢

在進行全文檢索時，我們經常需要根據查詢詞在文字中出現的頻率或接近程度對結果進行排序。PostgreSQL 提供了兩個函式來實作這一功能：ts_rank() 和 ts_rank_cd()。

使用 ts_rank() 函式

ts_rank() 函式根據查詢詞在文字中出現的頻率計算相關性分數。下面是一個例子，展示如何使用 ts_rank() 對包含 “war”、“security”、“threat” 和 “enemy” 的演講進行排名：

SELECT president,
       speech_date,
       ts_rank(search_speech_text, to_tsquery('english', 'war & security & threat & enemy')) AS score
FROM president_speeches
WHERE search_speech_text @@ to_tsquery('english', 'war & security & threat & enemy')
ORDER BY score DESC
LIMIT 5;

內容解密：

1. ts_rank() 函式接受兩個引數：search_speech_text（待檢索的文字列）和 to_tsquery()（查詢詞）。
2. WHERE 子句篩選出包含所有查詢詞的演講。
3. 結果按照相關性分數降序排列，並限制輸出前 5 筆資料。

執行結果顯示，Bill Clinton 於 1997 年的國情諮文包含查詢詞的頻率最高，但由於演講長度的影響，該結果可能不完全準確。

使用 ts_rank() 並進行長度標準化

為了消除演講長度對相關性分數的影響，可以在 ts_rank() 函式中加入第三個引數進行標準化：

SELECT president,
       speech_date,
       ts_rank(search_speech_text, to_tsquery('english', 'war & security & threat & enemy'), 1)::numeric AS score
FROM president_speeches
WHERE search_speech_text @@ to_tsquery('english', 'war & security & threat & enemy')
ORDER BY score DESC
LIMIT 5;

內容解密：

1. 第三個引數 1 表示將分數除以檔案長度，從而實作標準化。
2. 將結果轉換為 numeric 型別以保持精確度。
3. 標準化後的結果更具可比性，能夠更準確地反映查詢詞的相關性。

執行結果顯示，George W. Bush 於 2004 年的演講在標準化後排名第一，這可能更具參考價值。

練習與挑戰

1. 移除姓名中的逗號：使用正規表示式或其他字串函式，刪除 “Alvarez, Jr.” 和 “Williams, Sr.” 等姓名中的逗號，並將字尾提取到單獨的列中。
2. 統計單詞數量：選擇某位總統的演講稿，使用正規表示式將文字拆分為單詞，並統計長度大於或等於 5 的唯一單詞數量。
3. 比較 ts_rank() 和 ts_rank_cd()：使用 ts_rank_cd() 重寫 Listing 14-25 中的查詢，並比較兩者的結果差異。

這些練習有助於加深對 PostgreSQL 全文檢索功能的理解和應用能力。

使用PostGIS分析空間資料

現在，我們將探討空間資料，即有關地理空間中物體（例如點、線或多邊形）的位置、形狀和屬性的資訊。在本章中，您將學習如何使用SQL建構和查詢空間資料，並將介紹PostGIS擴充套件，它為PostgreSQL提供了對空間資料型別和函式的支援。

空間資料的重要性

空間資料已成為全球資料生態系統中的關鍵組成部分。手機應用程式可以透過查詢空間資料函式庫來查詢附近的咖啡店，政府使用空間資料來跟蹤住宅和商業用地的足跡，流行病學家則使用它來視覺化疾病的傳播。

練習內容

在我們的練習中，我們將分析美國農貿市場的位置以及新墨西哥州聖達菲市的道路和水道。您將學習如何建構和查詢空間資料型別，並結合地圖投影和網格系統。您將獲得從空間資料中提取資訊的工具，就像您分析數字和文字一樣。

設定PostGIS

PostGIS是一個免費、開源的專案，由加拿大地理空間公司Refractions Research建立，並由國際開發人員團隊在開源地理空間基金會（OSGeo）下維護。如果您按照第1章中的步驟在Windows、macOS或Ubuntu上安裝了PostgreSQL，那麼PostGIS應該已經在您的機器上。否則，請按照https://postgis.net/install/上的安裝說明進行安裝。

要啟用PostGIS，請在pgAdmin的查詢工具中執行以下陳述式：

CREATE EXTENSION postgis;

執行後，您將看到訊息“CREATE EXTENSION”，表明您的資料函式庫已更新以包含空間資料型別和分析函式。執行SELECT postgis_full_version();以顯示PostGIS的版本號以及其已安裝元件的版本。

空間資料的基本構建塊

在學習查詢空間資料之前，讓我們先了解它是如何在GIS和相關資料格式中描述的。點是空間資料的最小構建塊。網格可以用x和y軸標記，也可以使用經度和緯度。網格可以是平坦的二維空間，也可以描述三維空間，如立方體。在某些資料格式中，例如根據JavaScript的GeoJSON，點除了位置外還可以具有屬性。

二維幾何形狀

開放地理空間聯盟（OGC）和國際標準化組織（ISO）建立了一個簡單的功能存取模型，用於描述構建和查詢二維和三維形狀（有時稱為幾何形狀）的標準。PostGIS支援該標準。常見的幾何形狀包括：

• Point：二維或三維平面中的單個位置。在地圖上，點通常是標記經度和緯度的點。
• LineString：兩個或多個點，每個點由直線連線。LineString可以表示道路、腳踏車道或溪流等特徵。
• Polygon：具有三個或更多直邊的二維形狀，每個邊由LineString構成。在地圖上，多邊形代表國家、州、建築物和水體等物體。多邊形可以具有一個或多個內部多邊形，作為較大多邊形內部的孔洞。
• MultiPoint：一組點。單個MultiPoint物件可以代表零售商的多個位置，每個商店的經度和緯度。
• MultiLineString：一組LineString。例如，一條具有多個不連續路段的道路。
• MultiPolygon：一組多邊形。例如，被道路分隔成多個部分的土地可以分組在一個MultiPolygon物件中，而不是單獨的多邊形。

熟悉的文字格式

OGC標準的WKT格式指定了幾何型別的座標及其座標，放在一對或多對括號內。座標和括號的數量根據幾何型別的不同而有所不同。下表顯示了常見幾何型別的WKT格式示例。

WKT格式範例

這些WKT格式範例展示瞭如何使用文字表示不同的幾何形狀，這些文字表示可以用於PostGIS函式中。

空間參考系統識別碼（SRID）

SRID是用於指定放置物件的網格的識別符號。它對於確保空間資料的正確解釋和操作至關重要。

內容解密：

1. CREATE TABLE my_spatial_data：建立一個名為my_spatial_data的新表，用於儲存空間資料。
2. id SERIAL PRIMARY KEY：為表新增一個自動增量的主鍵欄位id。
3. geom GEOMETRY(POLYGON, 4326)：定義一個名為geom的欄位，用於儲存多邊形型別的幾何資料，SRID為4326（WGS 84座標系）。
4. INSERT INTO my_spatial_data (geom) VALUES ...：向my_spatial_data表中插入一條新的記錄，包含一個多邊形幾何資料。
5. ST_GeomFromText：將WKT格式的字串轉換為PostGIS可以理解的幾何物件。
6. POLYGON((-74.0060 40.7127, -74.0055 40.7129, -74.0062 40.7131, -74.0060 40.7127))：定義了一個多邊形的WKT表示，具有四個頂點，形成一個封閉區域。
7. 4326：指定的SRID，表示使用WGS 84座標系，這是一個常用的地理座標系統，用於GPS和其他應用。

## 空間資料處理與PostGIS基礎

在處理地理空間資料時，瞭解座標系統的表示方式至關重要。WKT（Well-Known Text）是一種常見的表示地理空間資料的文字標記語言，用於描述幾何形狀，如點、線、多邊形等。

### WKT格式與幾何形狀

WKT支援多種幾何形狀的表示，包括：

- **點（POINT）**：用於表示一個特定的地理位置，例如 `POINT (-74.9 42.7)` 表示經度 -74.9 和緯度 42.7 的位置。
- **線串（LINESTRING）**：用於表示一系列連線的點，例如 `LINESTRING (-74.9 42.7, -75.1 42.7)` 表示連線兩個座標點的直線。
- **多邊形（POLYGON）**：用於表示封閉的區域，例如 `POLYGON ((-74.9 42.7, -75.1 42.7, -75.1 42.6, -74.9 42.7))` 表示一個三角形區域。
- **多點（MULTIPOINT）**：用於表示多個獨立的點，例如 `MULTIPOINT (-74.9 42.7, -75.1 42.7)` 表示兩個獨立的點。
- **多線串（MULTILINESTRING）**：用於表示多條獨立的線，例如 `MULTILINESTRING ((-76.27 43.1, -76.06 43.08), (-76.2 43.3, -76.2 43.4, -76.4 43.1))` 表示兩條獨立的線。
- **多多邊形（MULTIPOLYGON）**：用於表示多個獨立的多邊形區域。

這些WKT表示法在實際應用中非常有用，能夠幫助我們在資料函式庫中儲存和查詢地理空間資料。

### 投影與座標系統

地球是一個球體，將其表面投影到二維平面上總是會涉及到某種程度的扭曲。不同的地圖投影方法被開發出來以適應不同的需求，例如航海、地區性地圖繪製等。常見的投影方法包括麥卡託投影（Mercator Projection）和阿爾伯斯投影（Albers Projection）。

#### 空間參考系統識別碼（SRID）

在PostGIS中，座標系統透過SRID來指定。SRID是一個唯一的識別碼，與特定的座標系統相關聯。當啟用PostGIS擴充套件時，會建立一個名為`spatial_ref_sys`的表格，其中包含了SRID及其對應的WKT表示。

常用的SRID包括4326，它對應於WGS 84座標系統，廣泛用於GPS裝置和全球定位系統。

```sql
SELECT srtext 
FROM spatial_ref_sys 
WHERE srid = 4326;

執行上述查詢，可以獲得WGS 84座標系統的WKT表示，瞭解其定義和引數。

PostGIS資料型別

PostGIS提供了兩種主要的資料型別來處理空間資料：geography和geometry。

• geography：根據球面座標系統，考慮地球的曲率，提供更精確的距離計算，結果以米為單位。適合處理跨越大區域的資料。
• geometry：根據平面座標系統，使用歐幾裡得座標，計算結果的單位取決於所使用的座標系統。適合處理區域較小的資料，提供更多的函式和更好的效能。

選擇正確的資料型別取決於具體的應用需求和資料特性。

使用PostGIS函式建立空間物件

PostGIS提供了大量的建構函式，用於根據WKT或座標建立空間物件。這些函式使得在資料函式庫中建立、操作和分析空間資料變得更加方便。

瞭解和掌握這些基礎知識，將有助於有效地利用PostGIS進行空間資料處理和分析。

使用PostGIS建立空間資料型別

PostGIS是一種流行的開源空間資料函式庫擴充套件，提供了豐富的空間函式和運算元，可以幫助開發人員處理和分析空間資料。在本文中，我們將介紹如何使用PostGIS建立空間資料型別，包括幾何（Geometry）和地理（Geography）資料型別。

從已知文字（WKT）建立幾何資料型別

ST_GeomFromText(WKT, SRID) 函式可以從WKT字串和可選的SRID建立幾何資料型別。以下是一些簡單的SELECT陳述式，用於建立不同型別的幾何資料：

SELECT ST_GeomFromText('POINT(-74.9233606 42.699992)', 24326);
SELECT ST_GeomFromText('LINESTRING(-74.9 42.7, -75.1 42.7)', 4326);
SELECT ST_GeomFromText('POLYGON((-74.9 42.7, -75.1 42.7, -75.1 42.6, -74.9 42.7))', 4326);
SELECT ST_GeomFromText('MULTIPOINT (-74.9 42.7, -75.1 42.7)', 4326);
SELECT ST_GeomFromText('MULTILINESTRING((-76.27 43.1, -76.06 43.08), (-76.2 43.3, -76.2 43.4, -76.4 43.1))', 4326);
SELECT ST_GeomFromText('MULTIPOLYGON((( -74.92 42.7, -75.06 42.71, -75.07 42.64, -74.92 42.7 )), ( (-75.0 42.66, -75.0 42.64, -74.98 42.64, -74.98 42.66, -75.0 42.66 )))', 4326);

內容解密：

1. ST_GeomFromText函式用於從WKT字串建立幾何資料型別。
2. 第一個引數是WKT字串，描述了幾何物件的形狀和位置。
3. 第二個引數是SRID（空間參考系統識別碼），用於指定幾何物件的空間參考系統。
4. 不同型別的幾何物件（例如POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON）可以使用不同的WKT語法來表示。

從已知文字（WKT）建立地理資料型別

ST_GeogFromText(WKT)函式可以從WKT字串建立地理資料型別。以下是一些簡單的SELECT陳述式，用於建立不同型別的地理資料：

SELECT ST_GeogFromText('SRID=4326;MULTIPOINT(-74.9 42.7, -75.1 42.7, -74.924 42.6)');

內容解密：

1. ST_GeogFromText函式用於從WKT字串建立地理資料型別。
2. WKT字串中包含了SRID，用於指定地理物件的空間參考系統。
3. 地理資料型別與幾何資料型別不同，它考慮了地球的曲率和空間參考系統的細節。

使用特定函式建立點、線和多邊形

PostGIS提供了許多特定函式，用於建立不同型別的幾何和地理物件。以下是一些範例：

建立點

SELECT ST_PointFromText('POINT(-74.9233606 42.699992)', 4326);
SELECT ST_MakePoint(-74.9233606, 42.699992);
SELECT ST_SetSRID(ST_MakePoint(-74.9233606, 42.699992), 4326);

內容解密：

1. ST_PointFromText函式用於從WKT字串建立點幾何物件。
2. ST_MakePoint函式用於從座標值建立點幾何物件。
3. ST_SetSRID函式用於為幾何物件指定SRID。

建立線

SELECT ST_LineFromText('LINESTRING(-105.90 35.67, -105.91 35.67)', 4326);
SELECT ST_MakeLine(ST_MakePoint(-74.9, 42.7), ST_MakePoint(-74.1, 42.4));

內容解密：

1. ST_LineFromText函式用於從WKT字串建立線幾何物件。
2. ST_MakeLine函式用於從點幾何物件建立線幾何物件。

建立多邊形

SELECT ST_PolygonFromText('POLYGON((-74.9 42.7, -75.1 42.7, -75.1 42.6, -74.9 42.7))', 4326);
SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(-74.92 42.7, -75.06 42.71, -75.07 42.64, -74.92 42.7)', 4326));

內容解密：

1.. ST_PolygonFromText函式用於從WKT字串建立多邊形幾何物件。 2.. ST_MakePolygon函式用於從線幾何物件建立多邊形幾何物件。

這些函式可以幫助開發人員更方便地建立和管理空間資料，並進行相關的分析和運算。