Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
Perhaps you could help us out by providing some more details: How many records are in my_table? How big is the data in the raster column? How many distinct dates do you have in date_of_data? –  dwurf Dec 11 '12 at 11:43
    
Add to this: what is the SRID of the rast column? –  dwurf Dec 11 '12 at 11:45
add comment

3 Answers 3

up vote 8 down vote accepted
+50

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.

share|improve this answer
    
The tiling thing seems to be the way to go - see this link. You'll also need to add an index like this: CREATE INDEX srtm_tiled_rast_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast)); (source) –  dwurf Dec 11 '12 at 11:55
add comment

Depending on the distribution of your data, you might get some very good speedups just by indexing the date_of_data column.

You can use the EXPLAIN ANALYZE syntax to figure out if your indexes are being used or not.

share|improve this answer
    
what kind of index? could you be more specific? –  f.ashouri Dec 10 '12 at 22:10
    
Just a standard btree index: create index tbl_name_date_idx on tbl_name (date_of_data). If you have many distinct dates this will drastically cut down the amount of data PostGIS has to process. –  dwurf Dec 10 '12 at 23:12
    
Thank you, but it didn't work for my query. –  f.ashouri Dec 10 '12 at 23:26
    
How did it not work? No noticeable performance gain, or other problems? If you have a table column which regularly appears in a WHERE clause, you should always consider indexing it. It will not only help in this case if you have many distinct dates (i.e. a large value domain) but also if you have a large number of records in the table. –  bhell Dec 11 '12 at 7:57
    
Is the query using the index? Can you pastebin the output of explain analyze SELECT st_value(rast,(st_GeomFromText('POINT(30.424 -1.66)', 4326))) as val from my_table where date_of_data='2002-01-09'? –  dwurf Dec 11 '12 at 11:29
add comment

Two aspects that I found sped up my PostGIS raster calculations, were using integer values in the raster, and using multi-band rasters where possible. In this case, can the DN value be stored as integers, if this is not already being done?

The other thought (and I'm not certain it is relevant here) is to use multi-band rasters. For example, if you are looking at monthly slices of data, each month could be a raster layer. Then you can retrieve multiple values of a point at different time slices by querying the layered raster. I found this approach to be much quicker than querying separate rasters.

Finally, when you load your data there is the -t flag for TILE_SIZE. You could explore if the tile size that you are using works well for your query.

share|improve this answer
    
Multiband rasters will likely help if you need to query the same pixel's value for several months at the same time (to stick with your example), e.g. to analyse time series. The query in the question only retrieves one specific date. If the date was contained in one band, the DBMS would need to read all the other bands as well, even though they are of no interest for answering the query. This would probably deteriorate performance. –  bhell Dec 11 '12 at 8:04
    
I agree - perhaps I did not emphasize that it is only useful if several values are needed at the same time; I'll clarify this. –  djq Dec 11 '12 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.