PolarDB：GanosBase H3地理網格能力解析與最佳實務

背景

地理網格是一種用於再現地球表面的多邊形網格資料格集合，能夠有效表示地物在地理空間中的位置資訊，並融合其他各類時空資料。地理格線運算通常採用由粗到細的逐級分割方式，對地球表面進行處理。通過將地球的曲面用一定大小的多邊形網格進行近似類比，實現地理空間定位與地理特徵描述的有機結合，同時將誤差範圍控制在網格單元的可接受範圍內。每個網格單元都會進行編碼，網格與編碼是一一對應的。三維地理網格不只考慮經緯度，還把高度維納入剖分和編碼範圍。

GanosBase地理網格引擎目前涵蓋GeoSOT和H3兩種地理網格。

• GeoSOT是中國提出的一套地球空間剖分理論，並在此基礎上發展出的一種離散化、多尺度地區位置標識體系。關於GeoSOT網格的最佳實務可參見基於GeoSOT地理網格模型：無人機路徑規劃能力實踐。

• H3是Uber研發的一種覆蓋全球表面的二維地理網格，採用的基本網格是正六邊形。

H3地理網格設計獨特之處在於其採用的六邊形結構。相較於傳統的四邊形或三角形網格，六邊形網格具有分布更均勻、鄰居關係固定且無方向性等優點，這使得在進行空間資料分析、路徑規劃、地理編碼以及地理柵欄等領域時，能夠更加精確和高效地組織和查詢地理空間資料。利用GanosBase地理網格的相關函數，可以將不同的空間範圍轉換為網格編碼，並能夠確定網格編碼所對應的空間範圍、層級及其父子網格關係。GanosBase支援退化格線運算（如下圖），充分利用網格的層級關係，通過更精簡的網格組合對空間範圍進行有效表達。此外，GanosBase自研的地理網格索引，可用於高效查詢網格編碼並加速彙總計算。

應用情境

H3地理網格技術在諸多業務情境中得到廣泛應用，主要包括：

• 物流與出行服務：基於地理網格開展路線規劃、地區覆蓋分析、配送範圍界定及作用區發現等功能的建設。

• 資料分析：基於地理網格進行人口密度分析、移動使用者行為分析以及地理市場細分等巨量資料分析領域的研究。

• 物聯網（IoT）：面向智能城市、環境監測和資產追蹤等需要即時監控的資料，基於地理網格進行監測資料空間分布分析的情境。

• 社交網路：基於地理網格構建的面向位置服務（LBS）、好友位置共用、事件通知等社交情境的應用。

• 應急響應與公用服務：基於地理網格開展災害分布分析、災害預警熱力圖、應急資源分布以及緊急救援地區的劃分等工作。

綜上，H3地理網格技術為企業和開發人員提供了一種強大工具，能夠更有效地管理和利用地理空間資料，從而提升與位置相關的決策效率和準確性。

能力解析

GanosBase H3地理網格具備多種功能，包括網格輸入/輸出、網格父子關係判斷、網格路徑分析和網格查詢等。此外，還支援轉換為GanosBase Geometry類型，以便與其他向量資料進行空間分析。值得強調的是，GanosBase H3地理網格同樣支援退化功能，通過更精簡的網格組合對空間範圍進行表達，從而降低使用者因資料編碼而帶來的資料庫儲存成本。關於GanosBase H3地理網格詳細功能，請參見GeomGrid SQL參考。

技術優勢

相比其他H3開源產品，GanosBase H3具有如下技術優勢：

• 支援更為多樣化的打碼方式，例如可以將GanosBase的點、線、面類型直接轉換為H3編碼。

• GanosBase H3在打碼效率與格網查詢效率方面進行了大量的效能最佳化。

• GanosBase H3支援與其他GanosBase模型進行聯集查詢分析，能夠將幾何類型直接轉換為H3編碼，或利用H3與柵格模型進行基于格網的像素統計等。

• GanosBase H3基於PolarDB底層的多態階層式存放區技術，能夠實現基於OSS的大規模資料點的打碼與儲存，從而顯著降低儲存成本。

最佳實務

本案例採用真實情境資料，為您介紹如何使用GanosBase H3地理網格實現空間點資料的入庫、打碼、查詢及最終顯示等功能。本案例使用Uber發布的2023年紐約出租車位置資料集FOIL進行測試。

資料匯入

1. 使用GanosBase H3地理網格能力前，需要安裝ganos_geomgrid外掛程式。

   CREATE EXTENSION ganos_geomgrid CASCADE;

2. 建立帶有h3grid類型的資料表FOIL2013，用於儲存FOIL點資料。地理空間模型提供了h3grid欄位類型，用於表示H3編碼。以下SQL語句中的h3_lev13代表使用的是第13層級的H3編碼。H3不同層級網格具有不同解析度，您可以根據具體業務需求靈活定義。H3各個層級對應的空間解析度請參見Github文檔。

   -- 建立表，用於儲存foil點資料，h3_lev13代表13級編碼
   CREATE TABLE FOIL2013 (
     id text, 
     lon float, 
     lat float, 
     h3_lev13 h3grid);

3. 資料入庫。FOIL檔案以CSV格式儲存，您可以通過編程方式從CSV中提取相關資訊通過SQL入庫，也可以通過FDW方式入庫。此處使用GanosBase FDW模組，使用FDW方式實現資料快速入庫。

   1. 將測試資料檔案上傳至OSS指定路徑。詳細操作請參考上傳檔案到OSS。

   2. 在測試資料庫中安裝ganos_fdw外掛程式。

      CREATE EXTENSION ganos_fdw CASCADE;

   3. 建立SERVER，負責管理CSV檔案。以下SQL語句中的format為'CSV'，代表管理的資料格式為CSV。datasource參數資訊請參見OSS檔案路徑。

      CREATE SERVER csvserver
        FOREIGN DATA WRAPPER ganos_fdw
        OPTIONS (
          datasource 'OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file.csv',
          format 'CSV' );
      
      CREATE USER MAPPING FOR CURRENT_USER SERVER csvserver OPTIONS (user '<access_id>', password '<secrect_key>');

   4. 通過外部表格形式，將OSS上的CSV檔案對應到資料庫中，以便作為普通表進行查詢。詳細的SQL語句如下。

      此處選擇medallion、pickup_longitude和pickup_latitude三列資料，其映射的外部表格名稱為trip_data_1：

      CREATE FOREIGN TABLE trip_data_1 (
        medallion varchar,
        pickup_longitude varchar,
        pickup_latitude varchar)
        SERVER csvserver
        OPTIONS ( layer 'trip_data_1' );

      查詢外部表格：

      SELECT * FROM trip_data_1;

   5. 將外部表格資料匯入到FOIL2013中。

      INSERT INTO FOIL2013
      SELECT medallion as id ,cast (pickup_longitude as double precision) as lon, cast(pickup_latitude as double precision) as lat FROM trip_data_1;

      查詢FOIL2013表確認CSV資料已經成功匯入：

      SELECT * FROM FOIL2013;

對象打碼

資料入庫後，可以對點資料進行編碼。Ganos H3提供了多種編碼方式，例如通過指定經緯度、標準H3字串、Integer類型H3編碼、二進位類型H3編碼，以及直接將Point類型轉換為H3等方式。詳細內容請參見：

• ST_H3FromText

• ST_H3FromInt

• ST_H3FromLatLng

• ST_AsH3Grid

本案例採用ST_H3FromLatLng函數，通過指定經緯度及目標層級，可以直接獲得對應的H3編碼。以下SQL語句利用FOIL表中的lat和lon欄位，產生第13層級的H3編碼，並將其儲存於h3_lev13列中。隨後，通過ST_AsText函數進行具體H3編碼的查詢。

-- Level 13
UPDATE FOIL2013 SET h3_lev13 = ST_H3FromLatLng(lat,lon,13);

-- 查詢
SELECT id,lon,lat,ST_AsText(h3_lev13) AS h3 FROM FOIL2013 LIMIT 100;

網格彙總

地理網格引擎的一個典型應用情境是對空間資料進行基于格網編碼的空間彙總統計分析，以擷取熱力圖等專題地圖資訊。以下SQL語句依據h3_lev13列中的H3編碼，從FOIL23表中統計每個網格內的點數量（count(*)）：

--按照h3_lev13進行統計
CREATE TABLE h3_count_lev13 AS
SELECT ST_AsText(h3_lev13) AS h3code,count(*) FROM FOIL2013 GROUP BY h3_lev13;

-- 查詢統計結果
SELECT ST_AsText(h3_lev13), ST_AsText(geometry),count FROM h3_count_lev13 ORDER BY count DESC;

網格查詢

GanosBase H3地理網格模型提供了多種基於H3編碼的操作。以下SQL語句通過ST_GridDistance函數擷取FOIL23中所有與空間位置(40.71481749,-73.99100368)對應的格網距離小於10的格網點：

SELECT * FROM foil2013
WHERE
ST_GridDistance(ST_H3FromLatLng(40.71481749,-73.99100368,13),h3_lev13)<10;

網格可視化

GanosBase支援像幾何資料一樣可視化H3網格，即可以把H3網格轉化為向量瓦片，再由前端渲染查看。GanosBase提供原生H3網格MVT函數及相應索引，能夠方便地查詢和可視化H3網格及其所包含的統計資訊。值得一提的是，GanosBase支援可視化動態產生的H3網格。例如，當希望可視化層級為10的H3網格時，如果表中並未儲存層級為10的H3網格，可以利用ST_AsMVT和ST_AsMVTGeom(ST_H3FromLatLng(lat, lon, 10), ...)命令動態產生層級為10的H3網格的可視化結果。然而，使用此方法在效率上不及預先儲存的H3網格。因此，以下內容將主要介紹如何可視化儲存在表中的H3網格。

1. 建立H3網格索引（可選，建立索引對可有效提升可視化效率）。

   CREATE INDEX ON h3_count_lev13 USING GIST(h3_lev13);

2. 根據H3網格擷取編號為(14, 4826, 6157)的向量瓦片。

   SELECT ST_AsMVT(tile) FROM 
     (SELECT ST_AsMVTGeom(h3_lev13, ST_Transform(ST_TileEnvelope(14, 4826, 6157), 4326)) AS grid, count 
     FROM h3_count_lev13 
     WHERE h3_lev13 && ST_Transform(ST_TileEnvelope(14, 4826, 6157), 4326)) AS tile;

3. 視覺效果展示。前端即時渲染，以下展示在資料庫端動態查詢H3網格的向量瓦片的結果。在此過程中，網格的顏色是根據其對應的統計值動態確定的。

   說明

   前端部分僅需一個Python指令碼和一個HTML檔案。啟動時，只需運行該Python指令碼，並在瀏覽器中輸入localhost:5100即可查看結果。Python指令碼將根據使用者在地圖上的滑鼠位置和縮放層級自動產生相應的SQL查詢，並將其發送至資料庫，隨後將資料庫返回的查詢結果展示在網頁上。具體代碼請參見附錄。

   

總結

地理網格是移動對象相關應用情境的重要支撐。在與軌跡、向量、柵格等資料類型的融合過程中，它能夠帶來極大的業務價值和廣闊的想象空間。本文利用GanosBase H3地理網格的相關功能，進行向量資料的彙總、空間關係的判斷與可視化。Ganos作為全球首個支援移動對象（MOD）的資料庫，相關能力已經在交通、物流、出行、汽車等多個客戶側得到有效驗證。相較於傳統中介軟體或業務代碼實現電子圍欄的方式，GanosBase從資料庫系統的底層為大規模移動對象提供了時空處理架構，顯著提高了計算效率，並在綜合成本方面實現了大幅改善。未來，GanosBase將繼續提供更高效的面向移動對象情境的庫內原生分析能力，推動相關領域的空間資訊應用全面邁向“線上化”。

附錄

• 可視化前端Python指令碼：

  from quart import Quart, send_file, render_template
  import asyncpg
  import io
  import re
  
  ## 資料庫連接參數
  CONNECTION = {"host": "YOUR-HOST-NAME-OR-IP", "port": PORT_NO, "database": "DATABASE_NAME",
                "user": "USER_NAME", "password": "PASSWORD"}
  
  ## 目標表名/欄位/ID
  TABLE = "h3_count_lev13"
  H3_COL = "h3_lev13"
  H3_GEOM_COL = "geometry"
  AGG_VAL_COL = "count"
  COL_SRID = 4326
  
  app = Quart(__name__, template_folder='./')
  
  
  @app.before_serving
  async def create_db_pool():
      app.db_pool = await asyncpg.create_pool(**CONNECTION)
  
  
  @app.after_serving
  async def close_db_pool():
      await app.db_pool.close()
  
  
  @app.route("/")
  async def home():
      sql = f'''
      SELECT ST_Extent(ST_Transform(ST_Envelope({H3_GEOM_COL}), 4326))
      FROM {TABLE};
      '''
      async with app.db_pool.acquire() as connection:
          box = await connection.fetchval(sql)
          box = re.findall('BOX\((.*?) (.*?),(.*?) (.*?)\)', box)[0]
          min_x, min_y, max_x, max_y = list(map(float, box))
          bounds = [[min_x, min_y], [max_x, max_y]]
          center = [(min_x + max_x) / 2, (min_y + max_y) / 2]
          return await render_template('./index.html', center=str(center), bounds=str(bounds))
  
  
  @app.route("/h3_mvt/<int:z>/<int:x>/<int:y>")
  async def h3_mvt(z, x, y):
      sql = f'''
      SELECT ST_AsMVT(tile.*)
      FROM
        (SELECT ST_AsMVTGeom({H3_COL}, ST_Transform(ST_TileEnvelope($1,$2,$3),{COL_SRID}), 4096, 512, true) geometry,
         {AGG_VAL_COL} count
        FROM {TABLE}
        WHERE ({H3_COL} && ST_Transform(ST_TileEnvelope($1,$2,$3),{COL_SRID}))) tile'''
      async with app.db_pool.acquire() as connection:
          tile = await connection.fetchval(sql, z, x, y)
          return await send_file(io.BytesIO(tile), mimetype='application/vnd.mapbox-vector-tile')
  
  if __name__ == "__main__":
      app.run(port=5100)

• 前端頁面index.html檔案：

  <!DOCTYPE html>
  <html>
  
  <head>
    <meta charset="utf-8">
    <title>map viewer</title>
    <meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no">
    <link href="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.css" rel="stylesheet">
    <script src="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/chroma-js/2.4.2/chroma.min.js"></script>
  </head>
  
  <body>
    <div id="map" style="position: absolute;left:0; top: 0; bottom: 0; width: 100%;cursor:pointer;"></div>
    <div class="counter"
      style="position: absolute;left:2%;font-size: 20px;padding: .1em .1em;text-shadow: 3px 3px 3px black;">
      <span>當前網格計數:</span>
      <span id="count">0</span>
    </div>
    <script>
      let YOUR_TOKEN = "pk.eyJ1Ijoia3pmaWxlIiwiYSI6ImNqbHZueXdlZjB2cG4zdnFucGl1OHJsMjkifQ.kW_Utrh8ETQltRk6fnpa_A"
  
      mapboxgl.accessToken = YOUR_TOKEN;
      const map = new mapboxgl.Map({
        container: "map",
        style: "mapbox://styles/mapbox/navigation-night-v1",
        center: {{ center }},
        zoom: 1
      })
  
      map.on("load", () => {
        map.fitBounds({{ bounds }})
  
        map.on('mousemove', 'h3', (e) => {
          map.getCanvas().style.cursor = "default";
          if (e.features.length > 0)
            document.getElementById('count').innerText = e.features[0].properties.count
        })
        map.on('mouseleave', 'h3', () => {
          map.getCanvas().style.cursor = "grab";
          document.getElementById('count').innerText = 0
        })
        map.addSource("h3_source", {
          type: "vector",
          tiles: [`${window.location.href}h3_mvt/{z}/{x}/{y}`],
          tileSize: 512
        });
  
        // make color map
        const MIN = 1
        const MAX = 600
        const STEP = 10
        color_map = chroma.scale(["#536edb", "#5d96a5", "#68be70", "#91d54d", "#cddf37", "#fede28", "#fda938", "#fb7447", "#f75a40", "#f24734", "#e9352a", "#da2723", "#cb181d"])
          .domain([MIN, MAX]);
        let colors = []
        for (let i = MIN; i < MAX; i += STEP)
          colors.push(color_map(i).hex(), i)
        colors.push(color_map(MAX).hex())
  
        map.addLayer({
          id: "h3",
          type: "fill",
          source: "h3_source",
          "source-layer": "default",
          paint: {
            "fill-color": [
              "step", ["get", "count"],
              ...colors
            ],
            "fill-opacity": 0.8
          }
        });
  
      });
    </script>
  </body>
  
  </html>