[postgis-users] PostGIS raster performance analysis

[Etienne Bellemare]

I processed 300M lidar points in the last few weeks, so I used that
information in order to check performance of st_value in relation to PG
Rasters, the analysis was motivated by this post
http://smathermather.wordpress.com/2010/01/01/lidar-processing-and-analysis-in-postgis-i-hope
.

The operation is simply described as check the value of a raster at the
location of each points in a table. Rasters are divided in tiles by the
gdal2wktraster.py script.

I'm just beginning my exploration, but my observations lead me to believe
that (oddly) the time required for processing is more linked to the number
of raster tiles intersecting with the points table than the number of points
itself. Another thing is that there are some problems if you intersect your
points with too much raster tiles, you need to divide the tiles in a query
(in my case to around 55, more info at
http://old.nabble.com/ST_Value-out-of-memory-tt30693679.html).

I did a simple regression analysis. Time = e^(2.9 + ln(1.1*tiles)). Number
of point to count is not statistically significant in my model (that's the
weird part, as the query is getting a value for each point).So an average
table containing 1.8M points, intersecting 300 tiles, took about 6.1 ms per
point on a not-so-fit computer [WinXP SP3, Intel Core i5 CPU 750 at 2.67GHz,
2.93Gb of RAM at 2.66GHz, Postgres is on a SATA 1.5Tb 7200rpm] with PostgreSQL
8.4.5, PostGIS 1.5.2, PGRaster 0.1.6d.

I must add that I did this analysis quickly and there's some serious
heteroscedascticity I should take care of to be make it a bombproof analysis
and probably other things to consider. If you have any comment on the
analysis, I'm open to suggestions.

If you see in those results anything that could help me improve my queries,
I'd be really happy to hear it as the end of the computation for this whole
dataset is planned in late march (I'd be happy to get it sooner)!

Etienne
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110214/2ca78353/attachment.html>