[postgis-users] Storage of tile offset on a raster dataset

[Pierre Racine]

James,

> When importing a large raster dataset, it is typically tiled and the only
> reference in postgis that these tiles form a single dataset is the
> raster_columns table.

You could also load your rasters with the -f option which add a column with the filename of the original raster file. You would then have a quick way to identify tiles being part of a specific raster.

> I'd like postgis to keep track of the tile offsets (from upper left to lower
> right) as I believe this should bring about performance improvements by
> reducing geometric operations to simple scaling laws. The tile offset could
> be stored as a linear index for simplicity.
> 
> Consider trying to locate which tile in a dataset a point lies within. You
> can calculate the tile offset in x and y with the following scaling law:

Computing the relation between a simple point and a tile might be easier like this but PostGIS is conceived to solve much harder problem like: Is this line or this polygon inside the tile? Do they touch, intersects or are completely contained by the tile? For that you need more complex algorithms. In your simple case, what if the tiles are rotated?

The usual query to find whether a point is inside a tile (and the raster value matching this point) goes like this:

SELECT ST_Value(rast, ST_SetSRID(St_MakePoint(170.1420703, -43.594937), 4326))
FROM raster_table
WHERE ST_Intersects(rast, ST_SetSRID(St_MakePoint(170.1420703, -43.594937), 4326))

I'm not sure the math under ST_Intersects() and ST_Value() is much more complex than what you are proposing. And for sure ST_Intersects() is much more polyvalent. What if you have 1000000 tiles? Does your maths benefit from a spacial index? ST_Intersects() does... when there is one defined.

>    - Checking alignment of tiles. This is especially important when loading
> multiple datasets which should be aligned. Lets say I load 1 'base' dataset
> which gets tiled on the way in. Any subsequent tiles I load (e.g. to update
> a region of the dataset) need to be aligned. I can check that my input tile
> has the same corner coordinates of the tile it is replacing by calculating
> the tile index and pulling out the relevant information.

You can already compare raster alignment with ST_SameAlignment() and realign a raster with ST_Resample(), ST_Rescale(), ST_Reskew() and ST_SnapToGrid().

>     - Iterating a raster dataset. Especially important when the processing
> you are doing is order-dependent (such as sequential neighbourhood
> searches
> (for e.g. terrain processing,  or imposing boundary conditions)

To find the neighbours of a tile you can intersect them with a small buffer around the tile of interest.

To return them always in the same sequence you can SORT them BY ST_UpperleftX(), ST_UpperleftY().

> To my mind, this seems quicker than the 'normal' method of doing
> something

You will have to demonstrate this affirmation... I think a lot of people would be amazed if you get something quicker than the actual PostGIS set of tools.

> Are there any facilities in postgis to support this already? Or am I
> overlooking some concepts about postgis that mean the above is
> unnecessary
> (i.e. the use cases can be met with no performance hit)

I think you underestimate the power of the PostgreSQL GiST index...

Good luck!

Pierre