[postgis-users] PostGIS Raster efficient enough to get height given coords from a large DEM?

[Stefan Keller]

Hi Bborie

Many thanks for your response.
To summarize: My goal is to get the height (in meters) given an
arbitrary lat/lon coordinate from the latest free digital elevation
model (like mentioned here
http://slashgeo.org/2011/12/01/New-Free-Elevation-Dataset-named-Global-Multi-resolution-Terrain-Elevation-Data-2010-GMTE
).

The approach using PostGIS to access raster data is
1st. to convert an ASCII GRID text file (an ESRI format) to GeoTIFF
(not tiled nor "pyramide"-ized).
Then (2nd) to load it into PostGIS, like described here
http://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01 .

I still have some basic questions about this interesting approach:

Regarding my question of "rules of thumb" in order to set the tile
size you answered:
> Generally speaking, the smaller the tile size, the faster the operation.  In
> my testing of my rasters, 200x200 is slower than 100x100 which is slower
> than 50x50.  The smaller tile size results in more rows in the table BUT the
> performance dramatically increases.  I can't remember of the top of my head
> but the performance gain was linear or better.

Thats a good hint.
Q1: But are there no calculcation rules on how to estimate an ideal
absolute size (in pixels)?

Regarding a query given lat/lon you proposed:
> Assuming you test to see what the ideal (or "good-enough") tile size
> is for the elevation rasterset, you should see good performance using
>
> ST_Value(rast, ST_SetSRID(ST_MakePoint(long, lat), srid))
>
> You could eliminate the ST_SetSRID and ST_MakePoint by passing a
> correct EWKT instead
>
> ST_Value(rast, 'YOUREWKT'::geometry)

Q2: Given the raster has a resolution (grid) of - say at best - 30m,
does this function really interpolate, given the input is an arbitrary
coordinate (I can't find a hint to that in the docs
(http://postgis.refractions.net/documentation/manual-svn/RT_ST_Value.html))?

Q3: If yes, I assume its linear. How could I proceed to get a result
using non-linear interpolation (like Kriging)?

Yours, Stefan


2011/12/2 Bborie Park <bkpark at ucdavis.edu>:
> On 12/02/2011 11:17 AM, Stefan Keller wrote:
>>
>> Hi Bborie
>>
>> 2011/12/2 Bborie Park wrote:
>>>
>>> The question of tile size comes because the serialized raster data is
>>> stored in TOAST, which may compress the data.
>>
>>
>> Good point.
>>
>> I know that a heap table is 8kb (unless changed in postgresql.conf)
>> and I assume that one wants to avoid that tuples have to use TOAST.
>> Are there any rules of thumb on how to estimate the tile size (in bytes)?
>>
>> Yours, Stefan
>
>
> Well, it basically boils down to "the bigger the tile size, the slower the
> detoasting".  The problem with trying to keep things under 8KB is that if
> you have a multi-band raster where there are more than one pixel type,
> things get very ugly quickly.
>
> Generally speaking, the smaller the tile size, the faster the operation.  In
> my testing of my rasters, 200x200 is slower than 100x100 which is slower
> than 50x50.  The smaller tile size results in more rows in the table BUT the
> performance dramatically increases.  I can't remember of the top of my head
> but the performance gain was linear or better.
>
>
> -bborie
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkpark at ucdavis.edu
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users