[postgis-devel] How to look for spatial index for raster

[Paragon Corporation]

Mat,

> Folks,

> What is the recommended query against the PostgreSQL catalog
> to find if spatial index is available for WKT Rater table/column?

> I imagine checking for gist_geometry_ops does not work for rasters, does
it?

 WKT Raster just uses a spatial index around the ST_Envelope geometry  of
the raster.

So the below queries should tell you which raster columns have spatial
indexes and which don't.


-- find out which raster columns are missing geometry index

SELECT c.table_schema, c.table_name, c.column_name
FROM (SELECT * FROM 
	information_schema.tables WHERE table_type = 'BASE TABLE') As t
INNER JOIN
	(SELECT * FROM information_schema.columns WHERE udt_name = 'raster')
c  
		ON (t.table_name = c.table_name AND t.table_schema =
c.table_schema)
		LEFT JOIN pg_catalog.pg_indexes i ON 
			(i.tablename = c.table_name AND i.schemaname =
c.table_schema 
				AND  indexdef LIKE '%' || c.column_name ||
'%') 
WHERE i.tablename IS NULL
ORDER BY c.table_schema, c.table_name;


-- which raster columns have spatial index

SELECT c.table_schema, c.table_name, c.column_name
FROM (SELECT * FROM 
	information_schema.tables WHERE table_type = 'BASE TABLE') As t
INNER JOIN
	(SELECT * FROM information_schema.columns WHERE udt_name = 'raster')
c  
		ON (t.table_name = c.table_name AND t.table_schema =
c.table_schema)
		LEFT JOIN pg_catalog.pg_indexes i ON 
			(i.tablename = c.table_name AND i.schemaname =
c.table_schema 
				AND  indexdef LIKE '%' || c.column_name ||
'%') 
WHERE i.tablename IS NOT NULL
ORDER BY c.table_schema, c.table_name;


Hope that helps,
Regina
http://www.postgis.us