GiST index-PolarDB(PolarDB)-阿里云帮助中心

GanosBase sfmesh supports native 3D spatial indexes for the sfmesh and meshgeom types using the Generalized Search Tree (GiST) index method. GiST is not a single fixed algorithm — it is an extensible indexing infrastructure that allows custom data types like sfmesh and meshgeom to plug in their own search strategies.

Create GiST indexes

sfmesh type

Create a table with an sfmesh column.

create table mesh_gist_test (
    id integer,
    the_mesh sfmesh
);

Insert data into the sfmesh column.

insert into mesh_gist_test values(1, '{"version" : 1, "root" : 0, "meshgeoms" : ["MESHGEOM(PATCH(INDEXSURFACE Z (VERTEX(307248.723802283 296449.440306073 6500.00004882812,307248.723802283 296449.440306073 100,307258.460240141 296449.440306073 6500.00004882812,307258.460240141 296449.440306073 100,307258.460240141 296474.440306263 6500.00004882812,307258.460240141 296474.440306263 100,307248.723802283 296474.440306263 6500.00004882812,307248.723802283 296474.440306263 100),INDEX((0,1,2),(3,2,1),(4,5,6),(7,6,5),(3,1,7),(7,5,3),(2,3,4),(5,4,3),(0,2,4),(4,6,0),(1,0,7),(6,7,0)))))"], "primitives" : [{"meshgeom" : 0}], "nodes" : [{"primitive" : 0}]}'::mesh);

Create a 3D spatial GiST index on the sfmesh column.

create index mesh_gist_test_idx on mesh_gist_test using gist (the_mesh);

meshgeom type

Create a table from the sfmesh source table by extracting the meshgeom column.

create table meshgeom_gist_test as select id, meshgeom(the_mesh) as the_mgeom from mesh_gist_test;

Create a 3D spatial GiST index on the meshgeom column.

CREATE INDEX meshgeom_gist_test_idx on meshgeom_gist_test using gist (the_mgeom);