2

I have a PostGIS DB containing a set of daily raster data from 1981 up to 2021. The inserted raster tile size is 83x90.

Problem: I wanna query the data through ST_Value for a specified Lat Lon, however, I notice it's so slow according to the coordinate I use. The first query execution takes so long (around 1.5 minutes) to be executed but if I query again passing a Lat Lon nearby to the latest query, it is faster (around 10 sec).

Basically, this is the way I'm querying.

SELECT
    date,
    ST_Value(rast, 1, (ST_Transform(ST_SetSRID(ST_MakePoint(-119.167504,44.962215 ), 4326),4326)))
FROM
    myraster 
WHERE
    AND date >= '19810101' AND date <= '20210101'
    AND ST_Intersects(rast, ST_Transform(ST_SetSRID(ST_MakePoint(-119.167504,44.962215), 4326),4326))

Table:

column type Nullable
id bigserial not null
variable character varying(10) not null
date date not null
rast raster not null
filename text not null

Explain analyze result:

1. Index Scan using myraster_rast_gist on myraster  (cost=0.41..7.18 rows=1 width=12) (actual time=4.881..60347.936 rows=14618 loops=1)'
2. Index Cond: ((rast)::geometry && '0101000020E61000007D04FEF0F39354C0A25D85949FA03D40'::geometry)'
3. Filter: _st_intersects('0101000020E61000007D04FEF0F39354C0A25D85949FA03D40'::geometry, rast, NULL::integer)
4. Planning time: 0.272 ms
5. Execution time: 60353.772 ms

Looks like the raster structure maintains a kind of cache for each query, where, as farther away is the coordinate from the latest fetched point, the query takes so long to be executed. I'm trying to figure out a way to reduce the 1.5 minutes.

13
  • Can you show us \d myraster ? – Timothy Dalton Jan 9 at 11:10
  • Have you indexed your raster table? If not try the following: CREATE INDEX myraster_convexhull_idx ON myraster USING GIST(ST_ConvexHull(rast)); – Trashmonk Jan 9 at 12:22
  • @Trashmonk Yes. I'm using GIST exactly as you described – mkdev Jan 9 at 16:22
  • @TimothyDalton this is the table scheme. id:bigserial not null | variable:character varying(10) | date:date | rast:raster | filename:text | – mkdev Jan 9 at 16:36
  • If you remove AND date >= '19810101' AND date <= '20210101' does it speed up the query? Can you provide what EXPLAIN ANALYZE ... yields? How many records are in this table and what kind of machine are you using? – Timothy Dalton Jan 9 at 19:22
0

My guess is that querying a single raster is probably very fast, but you are querying 40 years of daily rasters = ~14600 rasters. You can verify this by querying a single date.

What type of data do you need to extract? Here are some ideas:

If you are frequently querying the same time range and need to extract (say) the sum or mean for a particular point, you could consider pre-aggregating your daily data to a single raster using ST_MapAlgebra()

You could also experiment with a smaller tile size.

If your tiles are perfectly aligned with a common id, you could do a single intersect with your point to obtain the tile id and pixel x and y, and then use those to query your rasters.

3
  • Unfortunately, the application requires daily values, so for this case aggregation cant' be used. I think querying through the pixel x and y would help, but I don't have a unique id for each tile. I'm working to try it and I'll try to decrease the tile size as well. Thanks – mkdev Jan 11 at 17:32
  • Thanks @amball I've performed tests using a tile size of 10x10. I've inserted the same amount of data and compared the results with my original tile size. At this analysis, with 10x10 tile it took around 1/3 time of the original one (83x90) using the same query. Apparently, it looks like a nice result. However, I notice a difference of 90 GB in the new table with the 10x10 tile. Should the smaller tile increase the table size? In the same way, is there a better way to determine the optimal tile size? – mkdev Jan 27 at 4:31
  • @mkdev I don't know if there is a single optimal tile size, although see the suggestions here: stackoverflow.com/questions/24083732/… In general, think of this as a two stage process: (i) identify the tiles that intersect with your geometry (that should be fast if you have a spatial index), and (ii) for those tiles, identify the pixels of interest. Smaller tiles will make (ii) faster on a per-tile basis, but may return more tiles. – amball Jan 27 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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