GanosBase向量柵格資料快速入庫 - PolarDB

向量資料與柵格資料是最常見的空間資料類型，廣泛應用於多個領域的GIS相關應用。GanosBase具備全面的矢柵資料處理能力，依託PolarDB PostgreSQL版的彈性平行處理能力，其計算效率相比傳統方案提升了數倍甚至數十倍。本文介紹GanosBase提供的向量、柵格資料高效入庫方法，旨在協助您迅速理解雲原生資料庫在空間資料寫入方面的解決方案，從而更好地利用GanosBase在向量和柵格資料處理中的能力。

背景

社區版PostgreSQL的PostGIS組件提供了shp2pgsql和raster2pgsql兩個命令列工具，用於快速匯入矢柵資料。使用這些工具時，需自行指定匯入資料的SRID、檔案編碼、表名及匯入方式等相關資訊，對於柵格資料還需額外指定柵格波段、切片大小等參數。這樣的方式對於雲原生資料庫PolarDB並不適用，主要原因為：

PostGIS提供的匯入工具需要對資料庫所部署的伺服器有完全的掌控，資料需要儘可能上傳至伺服器的本地磁碟。而雲原生資料庫PolarDB為共用儲存架構，無本地磁碟的概念，同時雲原生會考慮底層伺服器資源的彈效能力，並不清楚資料庫所在宿主機的相關資訊。
PostGIS Raster與GanosBase Raster在儲存結構上存在顯著的不同，相關對比資訊請參考開展地區面雨量分析。

因此，從雲原生架構的角度出發，GanosBase依託雲原生Object Storage Service，提供更為便捷的函數級入庫工具，支援向量、柵格資料的快速寫入。

矢柵資料入庫方法

向量資料入庫

GIS案頭工具匯入

承載GanosBase的PolarDB PostgreSQL版100%相容PostgreSQL，支援使用類似QGIS這樣的GIS案頭工具進行向量資料入庫。通過建立PostgreSQL資料連線源的方式，與PolarDB資料庫建立串連，隨後採用案頭工具內建的資料匯入功能完成資料入庫。

基於FDW匯入

GanosBase推薦使用FDW的方式匯入向量資料。外部資料封裝器FDW（FOREIGN DATA WRAPPER）是PostgreSQL提供用於訪問外部資料的外掛程式，外部資料源包括叢集中其他資料庫中的資料或其他執行個體中的資料。GanosBase FDW提供對多種空間資料類型的統一訪問，可以自動將幾何空間資料類型映射為Geometry類型，從而實現與資料庫內部表的統一訪問與查詢。

例如，可以將儲存在OSS上的poly.shp檔案匯入到GanosBase作為一張帶有Geometry類型的資料表使用，具體操作步驟如下：

安裝需要的外掛程式。

CREATE EXTENSION ganos_spatialref;
CREATE EXTENSION ganos_geometry;
CREATE EXTENSION ganos_fdw;

通過ST_ForeignTables函數將空間資料檔案poly.shp註冊為外表。

SELECT ST_RegForeignTables('oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/poly.shp');

註冊外表成功後，可通過information_schema.foreign_tables視圖查詢剛註冊的FDW表（poly）。
```
SELECT foreign_table_name FROM information_schema.foreign_tables 
  ORDER BY foreign_table_name ASC;
```
說明
將外部資料註冊為外表，只是通過映射的方式查看資料，具體資料並未真正寫入資料庫，所以還需要使用如下命令建立資料庫表並插入資料。
```
CREATE TABLE poly_db AS SELECT * FROM poly;
```

柵格資料入庫

GanosBase提供ST_ImportFrom和ST_CreateRast函數，用於將外部柵格資料來源匯入資料庫。這兩個函數均可從外部柵格資料建立Raster類型對象，以儲存影像資料的元資訊。二者的主要區別在於，ST_ImportFrom函數會將資料劃分為若干規則大小（預設為256x256像素）的資料區塊（Chunk），並將其儲存在資料庫中（具體位置通過chunkTableName參數指定）。而ST_CreateRast函數則僅建立一個Raster對象，並不將具體的像素資訊匯入資料庫。

Tiff資料入庫

Tiff資料為目前最常見的柵格資料格式之一，以下通過具體樣本展示如何從OSS匯入Tiff資料到GanosBase中。

在OSS準備Tiff資料（您可自行準備此類資料）。

建立包含有Raster欄位類型的表用於儲存Tiff資料。

CREATE TABLE raster_table
(
  id integer,
  format text,
  rast raster
);

Tiff資料入庫。

使用ST_ImportFrom函數匯入。

INSERT INTO raster_table
SELECT 1, 'TIFF', ST_ImportFrom('chunk_table','oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file');

使用ST_CreateRast函數匯入。

INSERT INTO raster_table
SELECT 2, 'TIFF', ST_CreateRast('oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file');

Tiff資料匯入成功後，可以看到兩種方式都會建立一個Raster對象，且產生的Raster對象都可以使用GanosBase提供的柵格相關UDF進行操作。
```
SELECT id,ST_Georeference(rast),st_extent(rast),ST_NumBands(rast),ST_SRID(rast),st_value(rast,0,100,100),st_value(rast,1,200,200) FROM raster_table;
```
返回結果樣本如下：
但是，ST_ImportFrom函數會建立一個新的表（chunk_table）用於儲存具體像素值資訊，此時即使原始影像刪除也不會影響使用。而ST_CreateRast函數則只建立一個Raster對象，其中包含對外部影像的邏輯映射資訊，但具體資料仍然以檔案方式存在OSS上，如果原始影像刪除將無法查看。

HDF5/NetCDF資料入庫

HDF5/NetCDF是另外兩種常見的柵格資料格式，廣泛適用於地球對地觀測、科學計算等領域，尤其在氣象、海洋、地球科學相關領域。ST_ImportFrom和ST_CreateRast函數同樣支援HDF5/NetCDF類型的資料匯入，SQL文法與Tiff資料匯入類似：

使用ST_ImportFrom函數匯入。

INSERT INTO raster_table
Select 3, 'NC', ST_ImportFrom('chunk_table','oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file');

使用ST_CreateRast函數匯入。

INSERT INTO raster_table
Select 4, 'NC', ST_CreateRast('oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file');

注意，與Tiff資料匯入不同的是，對於含有subdataset的HDF5/NetCDF檔案，在匯入時候還需在檔案路徑後面註明subdataset的名稱，具體方式如下：

NetCDF檔案。

INSERT INTO raster_table
Select 5, 'NC', ST_ImportFrom('chunk_table','oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file.nc:sub_name');

HDF5檔案。

INSERT INTO raster_table
Select 5, 'HDF5', ST_ImportFrom('chunk_table','oss://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file.hdf5://path/sub_name');

對於多維度（大於3）的NC/HDF5檔案，還需要指定chunkdim參數。這是因為Raster資料在資料庫中的chunk是按照（w，h，b）尺寸進行載入和儲存，其中，w為chunk寬，h為chunk高，b為波段數，當NC檔案波段數大於3個時候，需要將除去xy座標後其他所有波段的乘積作為chunk的波段數進行儲存。例如，樣本中的這個NC檔案：

這裡的dimension除了lon和lat外，還有兩個維度time和isobaric。所以在使用ST_ImportFrom入庫時，必須指定chunkdim的中的波段數為除去lat和lon之外的其他所有波段的乘積（此處樣本為120），才可以查詢到正確結果。查詢SQL語句如下：

INSERT INTO nc_table VALUES(1, ST_ImportFrom('nc_rbt', '/Users/xiaofei/Data/raster/nc_demo.nc','{"chunkdim":"(256,256,120)"}'));

資料批量入庫

對於OSS上的批量資料，需結合程式指令碼以實現入庫。以下以Python為例，展示如何將OSS上的柵格資料大量匯入資料庫中：

說明

以下樣本僅為將OSS上的柵格資料大量匯入資料庫。在使用之前，請進行Python初始化，詳情請參考Python初始化。

##匯入所需的庫
import oss2
import psycopg2
import logging

##串連oss
auth = oss2.Auth('AK_ID', 'AK_Secret')
bucket = oss2.Bucket(auth, '***endpoint***', '***bucket_name***')

##串連資料庫，擷取遊標
con = psycopg2.connect(database="***dataq***", user="***dde***",
                       options="-c search_path=aster,public",  # 模式
                       password="******", host="*******", port="******")

cur = con.cursor()

##入庫的SQL語句
insert_sql = "INSERT into {raster_table_name}(filename, band_index, raster_obj) VALUES ..."

## 從oss中擷取所有hdf5檔案名稱，並儲存
for obj in oss2.ObjectIterator(bucket, prefix="..."):
    ff = open("upload.txt", mode='a', encoding='utf-8')
    filename = obj.key.split('/')[-1]
    if filename.find(".hdf5") == -1:
        continue
    else:
        ff.write(filename+'\n')

## 擷取檔案清單
fileList = {}
with open('upload.txt', 'r') as f:
    fileList = f.read().split('\n')

## 遍曆檔案清單，產生sql併入庫
for file in fileList:
    # 編輯insert_sql,添加參數...

    #入庫：
    try:
        cur.execute(insert_sql)
        con.commit()
        logging.info(filename+" finished")
    except (Exception, psycopg2.Error) as e:
        logging.info(filename+" error!")
        print(filename +" 上傳失敗\n")

總結

雲原生資料庫PolarDB具備獨特的資料寫入方式。GanosBase的函數級匯入工具能夠協助構建SQL層級的入庫、查詢、分析和服務一體化能力。使您無需在不同業務階段使用不同手段操作資料，從而實現標準化的工作流程，提升易用性。