PostGIS - Aurora database Raster GIST index support

Question

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
```

Answer

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

PostGIS - Aurora database Raster GIST index support

相關內容