GanosBase H3 geospatial grid capabilities and best practices

The geospatial grid model provided by GanosBase can efficiently process geospatial data based on a hexagonal structure. It is widely used in various scenarios such as logistics, social networks, data analysis, and emergency response. This model leverages a unique hexagonal grid system to achieve uniform distribution of data and fixed neighbor relationships, optimizing features such as spatial data analysis and path planning. The geospatial grid model provided by GanosBase supports GeoSOT and H3 grid types, providing various encoding methods, high-performance query and aggregation analysis capabilities. It can effectively integrate with geometric and raster data to significantly improve data processing efficiency and storage cost-effectiveness.

Background

The geographical grid is a collection of polygon grid units that represent the Earth's surface and can be used to represent the location information of features in geographical space, integrating with other types of spatiotemporal data. Geographical grid computing approximates the Earth's surface by subdividing it into polygon grids of a certain size from coarse-grained to fine-grained. The goal is to integrate the positioning and description of geographical features within the grid units' error range. Each grid unit is encoded, with a one-to-one correspondence between grids and codes. Three-dimensional geographical grids consider not only latitude and longitude but also include height in the subdivision and encoding range.

GanosBase's geographical grid engine currently covers two types of geographical grids: GeoSOT and H3.

GeoSOT is a set of geospatial subdivision theories proposed by China, developed into a discrete, multi-scale regional location identification system.
H3 is a two-dimensional geographical grid covering the Earth's surface developed by Uber, using a global, unified, multi-layered hexagonal grid system to represent the Earth's surface.

H3's unique design lies in its hexagonal structure, which, compared to traditional square or triangular grids, offers more uniform distribution, fixed neighbor relationships, and directionlessness. This makes it more precise and efficient in organizing and querying geospatial data in fields such as spatial data analysis, path planning, geocoding, and geofencing. Using GanosBase's geographical grid functions, different spatial ranges can be converted into grid codes, and the spatial range, hierarchy, and parent-child grids of grid codes can be determined. GanosBase supports degenerated grid computing (as shown in the figure below), fully utilizing the hierarchical relationship of grids to express the spatial range with a more streamlined grid combination. Additionally, GanosBase's self-developed geographical grid index can be used for efficient grid code querying and accelerating aggregate calculations.

Scenarios

H3 geographical grid technology is widely used in many business scenarios, including:

Logistics and travel services: developing features such as route planning, area coverage analysis, delivery range delineation, and hotspot area discovery based on the geographical grid.
Data analysis: donducting population density analysis, mobile user behavior analysis, geographical market segmentation, and other big data analysis fields based on the geographical grid.
Internet of Things (IoT): analyzing the spatial distribution of monitoring data based on geographical grids in scenarios requiring real-time monitoring such as smart cities, environmental monitoring, and asset tracking.
Social networks: building applications for social networking scenarios including location-based services (LBS), friend location sharing, and event notifications based on geographical grids.
Emergency response and public services: conducting disaster distribution analysis, disaster warning heatmaps, emergency resource distribution, and emergency rescue area delineation based on geographical grids.

In summary, H3 grid technology provides enterprises and developers with a powerful tool to better manage and utilize geospatial data, improving the efficiency and accuracy of location-related decision-making.

Capabilities

GanosBase H3 geographical grid includes various capabilities such as grid input/output, parent-child grid relationship judgment, grid path analysis, and grid querying. The geographical grid also supports conversion to the GanosBase Geometry type for spatial analysis with other vector data. Notably, GanosBase H3 geographical grid also supports degradation, using a more streamlined grid combination to express spatial ranges, thereby reducing the database storage costs incurred by encoding. For more information about GanosBase H3 geographical grid, see GeomGrid SQL Reference.

Technical advantages

Compared with other open-source H3 products, GanosBase H3 provides the following technical advantages:

Supports richer encoding methods, such as allowing you to directly convert GanosBase point, line, and surface types to H3 encoding.
Provides extensive performance optimization in both encoding efficiency and grid query efficiency.
Supports joint query analysis with other GanosBase models, allowing you to directly convert geometry types to H3 encoding or perform pixel statistics based on grids with H3 and raster models.
Leverages the underlying polymorphic tiered storage provided by PolarDB to encode and store large-scale data points in OSS, which significantly reduces storage costs.

Best practices

This case uses real-world scenario data to introduce how to use GanosBase H3 to perform functions such as spatial point data storage, encoding, querying, and final display. For testing, Uber's 2023 New York Taxi Pickup Dataset FOIL is used.

Data import

Before using GanosBase H3, you need to create the GeomGrid extension by executing the following statement.
```
CREATE EXTENSION ganos_geomgrid CASCADE;
```
GeomGrid provides the h3grid field type to represent H3 codes. The following SQL statement creates a data table FOIL2013 with the h3grid type, where the field h3_lev13 represents the 13th level H3 code. Different levels of H3 grids have different resolutions. You can define the resolutions based on your business needs. For the spatial resolution corresponding to each level of H3, see community documentation.
```
-- Create a table to store foil point data, where h3_lev13 represents the 13th level code.
CREATE TABLE FOIL2013 (
  id text, 
  lon float, 
  lat float, 
  h3_lev13 h3grid);
```
Import data into the database. The FOIL file is stored in CSV format. You can programmatically extract data from the CSV file and store it in the database by executing SQL statements or use the FDW method for import. In this example, the GanosBase FDW module is used to achieve fast data import.
1. Upload the test data file to an OSS directory. For more information, see Upload objects.
2. Create the GanosBase FDW extension in the test database.
```
CREATE EXTENSION ganos_fdw CASCADE;
```
3. Create a server to manage CSV files. In the following SQL statement, the value of format is 'CSV', indicating that the managed data format is CSV. For more information about the datasource parameter, see File path in 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. Map the CSV file on OSS to the database as a foreign table. The data in the foreign table can be queried in the same way as the data in a regular table. See the following SQL statement.
  The medallion, pickup_longitude, and pickup_latitude columns are selected, and the name of the mapped foreign table is trip_data_1:
```
CREATE FOREIGN TABLE trip_data_1 (
  medallion varchar,
  pickup_longitude varchar,
  pickup_latitude varchar)
  SERVER csvserver
  OPTIONS ( layer 'trip_data_1' );
```
  Query the foreign table:
```
SELECT * FROM trip_data_1;
```
5. Import the foreign table data into the FOIL2013 table.
```
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;
```
  Query the FOIL2013 table to confirm that the data in the CSV file is successfully imported:
```
SELECT * FROM FOIL2013;
```

Object encoding

After importing data, we can encode point data. GanosBase H3 provides multiple encoding methods, such as specifying latitude and longitude, standard H3 strings, integer-type H3 encoding, binary-type H3 encoding, and converting directly from Point type to H3. For more information, see the following topics:

This example uses the ST_H3FromLatLng function, where you can directly obtain H3 encoding by specifying latitude, longitude, and target level. The following SQL statement generates the 13th level H3 encoding from the lat and lon fields in the FOIL table and stores it in the h3_lev13 column, then queries the specific H3 encoding by using the ST_AsText function..

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

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

Grid aggregation

A typical use case for grids is spatial aggregation and statistical analysis of spatial data based on grid codes to obtain thematic maps such as heatmaps. For example, the following SQL statement counts the number of points (count(*)) within each grid based on the h3_lev13 column's H3 code in the FOIL23 table:

-- Aggregate by h3_lev13
CREATE TABLE h3_count_lev13 AS
SELECT ST_AsText(h3_lev13) AS h3code,count(*) FROM FOIL2013 GROUP BY h3_lev13;

-- Query the aggregation results
SELECT ST_AsText(h3_lev13), ST_AsText(geometry),count FROM h3_count_lev13 ORDER BY count DESC;

Grid query

GanosBase H3 provides various operations based on H3 encoding. For example, the following query uses the ST_GridDistance function to obtain all grid points in FOIL23 where the distance between the spatial location (40.71481749,-73.99100368) and the corresponding grid is less than 10:

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

Grid visualization

GanosBase supports visualizing H3 grids just like geometric data, meaning H3 grids can be converted into vector tiles, which are then rendered on the front end for viewing. GanosBase provides native H3 grid MVT functions and indexing, allowing users to easily query and visualize H3 grids and their associated statistics. Notably, GanosBase supports visualizing dynamically generated H3 grids. For instance, if you want to visualize level 10 H3 grids but the table does not store level 10 H3 grids, you can use ST_AsMVT and ST_AsMVTGeom(ST_H3FromLatLng(lat, lon, 10), ...) to dynamically generate visualization results for level 10 H3 grids. However, this is less efficient than pre-saving H3 grids. The following section mainly introduces how to visualize pre-saved H3 grids in a table.

Create an H3 grid index. Creating an index is not mandatory for visualization but can significantly improve visualization efficiency.
```
CREATE INDEX ON h3_count_lev13 USING GIST(h3_lev13);
```

Retrieve vector tiles for the grid identified as (14, 4826, 6157) based on H3 grids.

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;

Visualize the results. The animation below shows the real-time rendering of vector tiles dynamically queried from H3 grids in the database on the front end. The grid color is dynamically determined based on the statistical value corresponding to the grid.
Note
The front-end part requires only a Python script and an HTML file. To start, simply run the Python script and open the browser and enter localhost:5100 to see the results. The Python script automatically generates the corresponding SQL query based on the user's mouse position and zoom level on the map, then displays the database's query results on the web page. For information, see Appendix.

Conclusion

The geographical grid is an essential support for mobile object-related application scenarios, and it can bring enormous business value and imaginative possibilities when integrated with data types such as trajectories, vectors, and rasters. This article focuses on the related capabilities of GanosBase H3 geographical grid, introducing how to use the H3 geographical grid for vector data aggregation, spatial relationship judgment, and visualization based on a best practice. As the world's first database to support mobile objects (MOD), GanosBase's related capabilities have been effectively validated on the customer side in industries such as transportation, logistics, travel, and automotive. Compared with traditional middleware or business code implementations, GanosBase provides a spatiotemporal processing framework for large-scale mobile objects from the database system's lowest level, greatly improving computing efficiency and overall costs. In the future, GanosBase will offer more efficient native in-database analysis capabilities for mobile object-oriented scenarios, promoting the comprehensive "onlineization" of spatial information applications in related fields.

Appendix

The Python script for the front-end visualization:

from quart import Quart, send_file, render_template
import asyncpg
import io
import re

## Database connection parameters
CONNECTION = {"host": "YOUR-HOST-NAME-OR-IP", "port": PORT_NO, "database": "DATABASE_NAME",
              "user": "USER_NAME", "password": "PASSWORD"}

## Target table name/field/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)

The content of the index.html file:

<!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>Current grid count:</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>