PostGIS – How to Speed Up Queries for Raster Databases

performancepostgisqueryrasterspatial-index

I have a raster database in postgresql/postgis with these columns:

(ID, rast, data_of_data).

'rast' is the column that has raster files in WKT format. An example query to find the DN value of a point in WGS84 system (30.424, -1.66) and for 2002-01-09 is the following:

SELECT 
     st_value(rast,(st_GeomFromText('POINT(30.424 -1.66)', 4326))) as val
FROM 
     my_table
WHERE
     date_of_data='2002-01-09'

Is there a method (eg. spatial index) to speed up those kind of queries?

Best Answer

This is an exciting question! How big is the raster you want to query? WKTRaster is stored in the database as a BLOB. In order to find the value at a specific point, from a known (x_0, y_0) corner coordinate row/column indices (i, j) are computed using (dx, dy) steps and rotation. With (i, j) known, the ST_Value() function can access the actual data at the correct byte offset.

This means that the DB has to read on average at least half of the data blob when answering a query for a point (depending on the implementation it may actually read all of the data at all times). I would therefore guess that WKTRaster performance suffers when the data BLOBs get too large. Tiling the dataset should speed up queries. Have a look at how SRTM data (coming in 6000x6000 pixel chunks) is handled in this tutorial. They actually tile the data into really small 50x50 pixels, which is a clear hint that my guessing may be not too far from the truth.

Spatially indexing raster data will probably just index the bounding box, which is no real help for your problem.

Related Solutions

[GIS] Accessing PostGIS raster data using Query Layer in ArcGIS Desktop

To do this, you'll want to create a separate table containing the vectorized raster or a view. If your raster table is big (lots of rows), you're better using a separate table instead of a view do to the computation occurring every time the view is accessed. The SQL for creating the view could be...

CREATE VIEW myview AS
SELECT
    rid,
    (ST_DumpAsPolygons(rast)).*
FROM ras_test1

You should be able to view that in ArcGIS. It might complain about lacking an primary key, which you could always add if needed.

[GIS] PostgreSQL/PostGIS spatial index – no speed up

The most efficient index for the query expressed in your question is the one on gid as it is the only column that appears in a where expression:

 CREATE INDEX table_gid ON table (gid);

You can safely drop the gist index as it will only consume space and slow inserts/updates/deletes down.

Long explanation

As I said the most effective index in your case is the one on gid as it will allow the db engine to retrieve rows faster (with retrieval usually being the slowest part of the process). After that it will probably better compute the result of the

  ST_Contains(a.way, b.way)

espression without looking at the index. The reason is that the query planner will likely estimate that the extra cost of looking up the gist index on both columns versus looking up the a.way and b.way values directly is not worth the effort as the total number of rows to look up is probably very small especially if the index is unique.

As a rule of thumb remember that the planner will probably favor a table scan over an index scan for small datasets (dataset sizes are estimated by looking at the table statistics).

Best Answer

Related Solutions

[GIS] Accessing PostGIS raster data using Query Layer in ArcGIS Desktop

[GIS] PostgreSQL/PostGIS spatial index – no speed up

Long explanation

Related Question