I would like to store bounding boxes in a column of my table. Thus I wanted to use the box2d data-type for that column. However it seems like I can't create an index on that column. Here is my simplified SQL code:
CREATE TABLE "rm"."test" (
"id" serial PRIMARY KEY NOT NULL,
"bbox" box2d
);
CREATE INDEX IF NOT EXISTS bbox_spatial_index ON rm.test USING gist (bbox);
Error message is:
ERROR: data type box2d has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type.
SQL state: 42704
Or am I wrong and I should store the bboxes in a different format in a "geometry" column? For example converting them to Polygones or Multipoints?
Versions:
PostGIS v3.3 (3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1)
PostgreSQL 14.6, compiled by Visual C++ build 1914, 64-bit
1 Answer 1
As the error message says you can't build a gist index on a box2d. You can make them into polygons and use a gist index or look at different index types but I suspect that those won't really speed things up.