Implementing the ST_PointN GIS Function in OceanBase ActionDB

1 ST_PointN Function Introduction

ST_PointN extracts the N‑th point from a given geometry object, which is commonly used in geometric analysis and GIS applications. The function accepts a geometry (such as a line or polygon) and an index N, and returns the N‑th point, enabling users to isolate specific points for further geographic processing.

Common application scenarios

Road and route analysis : Extract particular points on a traffic route for detailed analysis or optimization.

Environmental monitoring : Pull monitoring points from polygons or lines to achieve more precise environmental data analysis.

Urban planning : Retrieve specific points from complex polygons to assist planners in decision making.

Navigation systems : Extract key points along a path to improve navigation guidance and route planning.

2 Function Implementation

How to add registration information?

Adding the ST_PointN function requires appending registration data to the existing GIS metadata in OceanBase.

Define function name : Add the function name to the header file ob_name_def.h so it can be referenced elsewhere.

Define function ID : Assign a unique ID to the function; each GIS system function uses a distinct T_FUN_SYS_ST_xx identifier.

Add serialization information : Register the function in ObExpr::EvalFunc g_expr_eval_functions for serialization of function metadata.

Register the function implementation class : Register the implementation class in the factory method register_expr_operators so it can be recognized during SQL execution.

Add mapping between function and GIS algorithm : Use ob_geo_func_register to maintain the correspondence between the function and the underlying GIS algorithm.

How to implement ST_PointN?

The algorithm implementation consists of two main parts:

ObExprSTPointN – the expression entry point that validates GIS parameters and prepares the GIS context.

ObExprSTPointN algorithm – the concrete algorithm that performs the point extraction.

During SQL execution, the expression handling resides under src/sql/engine/expr . After parameter validation, ObExprSTPointN calls ObGeoFunc::geo_func::eval , which dispatches to the template implementation ObGeoFuncPointNImpl where the actual eval logic resides.

1. Add the function implementation class

Key interface methods:

calc_result_type1 : Checks that the number and types of parameters are correct. The family of calc_result_type functions distinguishes parameter counts via suffixes (e.g., calc_result_type1 for a single‑parameter function).

eval_st_PointN : Validates the generated GIS object, checks GIS metadata and context, and sets up the required GIS objects ( gis_ctx ) for execution.

After validation, the call chain reaches ObGeoFunc<ObGeoFuncType::PointN>::geo_func::eval(gis_context, result) , which finally invokes ObGeoFuncPointNImpl::eval .

2. Add the algorithm implementation

The core logic registers supported GIS sub‑types (e.g., linestring and multi_point ) and defines the eval function family.

Since Boost.Geometry does not provide a direct pointN function, the implementation accesses the internal structure of geometry types (such as linestring and multi_point ) to emulate the functionality.

3 Advantages of the Solution

Tailored customization : Adding a custom GIS function like ST_PointN allows the database to meet specific business needs, improving flexibility and efficiency.

Performance improvement : Optimized algorithms reduce computation time for complex GIS operations, enhancing query performance.

Enhanced extensibility : The new function expands OceanBase’s capabilities, supporting a broader range of data manipulation scenarios.

Better user experience : Users can perform GIS tasks directly within the database without external tools, simplifying development.

Data security and consistency : Implementing the function inside the database minimizes data transfer risks and ensures consistent processing.

Rapid adaptation to changing requirements : Custom functions enable quick responses to evolving business demands without waiting for upstream updates.

4 Summary

By following the steps above, the ST_PointN function was added to OceanBase, including defining its name and ID, registering the function, performing parameter checks, setting up execution context, dispatching through template functions, and implementing the concrete algorithm.

The custom pointN implementation extends ActionDB’s GIS capabilities, enhancing its value in geographic information processing and competitive edge.

5 What is ActionDB?

ActionDB is an enterprise‑grade distributed database built on the open‑source OceanBase kernel, enriched with ActionTech’s expertise, offering MySQL 8.0 compatibility, secure features, and user‑friendly operations tools.

For more information, visit the official ActionDB page: ActionDB – Commercial Release Based on OceanBase .