By using AWS re:Post, you agree to the Terms of Use
/PostGIS - Aurora database Raster GIST index support/

PostGIS - Aurora database Raster GIST index support

0

Hello,

I have created a PostgreSQL database, including the PostGIS extension, however, I'm unable to create GiST index on my rast column.

I'm trying to execute the following line:

CREATE INDEX IF NOT EXISTS rast_index ON public.raster_data USING gist(rast);

I get the following response:

ERROR:  data type raster 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
1 Answers
0

Hi,

I understand you are not able to create GiST index on my rast column using the below command CREATE INDEX IF NOT EXISTS rast_index ON public.raster_data USING gist(rast); Kindly note that GiST indexes enable building general B-Tree structures that you can use for operations more complex than equality and range comparisons. They are mainly used to create indexes for geometric data types and they support full-text search indexing. With that said above you can only create GIST indexes for geometric data types such as ( point , polygon , lseg , box , circle , and path) for PostgreSQL.

See the example below:

postgres=> CREATE TABLE my_points (

id serial PRIMARY KEY,

p geometry(POINT),

pz geometry(POINTZ),

pm geometry(POINTM),

pzm geometry(POINTZM),

p_srid geometry(POINT,4269) ); CREATE TABLE postgres=> CREATE INDEX IF NOT EXISTS rast_index ON public.my_points USING gist(p);

CREATE INDEX

For more information about GIST, kindly visit the below link:

[+]https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.tables.indexes.html#chap-oracle-aurora-pg.tables.indexes.pg [+]https://www.postgresql.org/docs/9.0/textsearch-indexes.html

answered 20 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions