Create index on PostGIS geometry type

Question

I need to query geometries in PostGIS by "type". For example, I need to get all of the "POINT" data, or all of the "POLYGON" data, or all of the "MULTIPOLYGON" data, etc etc.

Is it possible to create an index on the "type", or do I need to store this information in a separate column in order to index it?

Answer 1

You can create a functional index. Make sure to use the exact same expression in your queries. You can either get the exact type (ST_Polygon, ST_MultiPolygon etc) or just the dimension (0=point, 1=line, 2=polygon)

CREATE INDEX geo_type ON my_table(st_geometryType(geom));
CREATE INDEX geo_dim ON my_table(st_Dimension(geom));