2

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

Vince
20.5k16 gold badges49 silver badges65 bronze badges
asked May 14, 2023 at 17:05

1 Answer 1

3

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.

answered May 14, 2023 at 17:52

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.