PolarDB:Multi-bounding box spatial index

Syntax

CREATE INDEX index_name ON table_name USING GIST(geom_column_name operator_class);

Parameters

Description

• When you create an index for a LineString and MultiLineString object that has a large number of points and a large spatial range, the target object is split to build a multi-bounding box spatial index.

• The split mode is specified by the ganos.geometry.mbbox_split_mode parameter. Valid values include length, which indicates splitting by object length, and points, which indicates splitting by number of points. The default value is length. The following rules apply:

  • When the object is split by length, the length of each segment after splitting is specified by the ganos.geometry.mbbox_split_length parameter. The maximum number of segments is specified by the ganos.geometry.mbbox_split_max_segments parameter. If the number of segments after splitting based on the minimum length value of each segment exceeds the maximum number of segments defined by this parameter, the length of each segment is calculated by using the following formula: Segment length = Total length/Maximum number of segments.

  • When the object is split by the number of points, the number of points in each segment after splitting is specified by the ganos.geometry.mbbox_split_points parameter. The maximum number of segments is specified by the ganos.geometry.mbbox_split_max_segments parameter. If the number of segments after splitting based on the specified number of points in each segment exceeds the maximum number of segments defined by this parameter, the number of points in each segment is calculated by using the following formula: Number of points = Total number of points/Maximum number of segments.

• No additional modification to the SQL statement is required.

Examples

• Objects of the geometry type

  CREATE TABLE line_test(id int, geom geometry);
  INSERT INTO line_test VALUES(1, st_geomfromtext('Linestring(0 1, 0 0, 1 0, 2 1,2 2,3 3)'));
  
  -- Create a 2D spatial index.
  CREATE INDEX line_test_gist_geom_2d ON line_test USING GIST(geom);
  
  --- Create a 2D multi-bounding box spatial index.
  CREATE INDEX line_test_gist_geom_2dx ON line_test USING GIST(geom gist_geometry_ops_2dx);

• Objects of the geography type

  CREATE TABLE line_test(id int, geog geography);
  INSERT INTO line_test VALUES(1, st_geogfromtext('Linestring(0 1, 0 0, 1 0, 2 1,2 2,3 3)'));
  
  --- Create a 3D spatial index.
  CREATE INDEX line_test_gist_geog_3d ON line_test USING GIST(geog);
  
  --- Create a 2D multi-bounding box spatial index.
  CREATE INDEX line_test_gist_geog_2dx ON line_test USING GIST(geog gist_geography_ops_2dx);