[postgis-users] Postgis Raster Performance

[Pierre Racine]

> Regarding the query: I've processed one linestring (only 1) with an 70mb DTM. It
> took probably a little more than a minute. But what I'd expect from this query is
> that it returned a linestring just like mine, but with the intersected data. So It
> could be a linestring with many points representing the pixel values. But instead,
> I got a list containing pieces of my line. That's why I'm asking. I think there
> should be another way to preserve my data.

The result is correct. ST_Intersection() vectorize intersecting tiles of the raster and returns the intersection of those geometries with your line. 

"what I'd expect from this query is that it returned a linestring just like mine, but with the intersected data"

What should this look like? What should be "the intersected data"?

ST_Intersection(rast, geometry) perform intersection at the pixel level, not the raster level. If you want info about which tile intersect with which line then the query is different:

SELECT r.rid, l.lineid 
FROM rasttable r, linetable l
WHERE ST_Intersects(r.rast::geometry, l.geom)

> I tested both single blocked (in a row) and 128x128 tiled raster. Tiled with gist
> got a better performance and their result was the same.

If what you (still) want is an intersection at the pixel level then you should tile your raster 10x10 and you will get very good performance.

If you prefer intersecting at the raster level then it should be very fast and the tiling size is not a major factor.

Pierre