Duncan Golicher's series of articles on PostGIS Raster

Duncan Golicher is researcher in forest ecology at the Colegio de la Frontera sur in Mexico. "The Colegio de la Frontera Sur is a scientific research center that seeks to contribute to the sustainable development of the southern border of Mexico, Central America and the Caribbean through the generation of knowledge, the formation of human resources and the linkage between social and natural sciences."

In the past few months, Duncan has been experiencing a lot with the new raster/vector analysis capacities in PostGIS 2.0 and the integration of PostGIS data in his R workflow. The nicest about it is that he has posted a quite impressive series of articles about his experience in his blog and I thought it was worth sharing them here (so I gain a bit of time to write my next long article :). Here are the posts in chronological order:

• Installing PostGIS in Ubuntu Natty

• Nearest neighbour in POSTGIS

• Connecting to a Postgresql data base from R (Ubuntu Natty)

• Edge to edge distance using PostGIS

• Installing PostGIS2.0 on Windows 7

• Using PostGIS raster to hold WorldClim data

• Working with raster data in PostGIS

• Tile size for raster vector overlays in PostGIS

• Vector raster overlays in PostGIS 2

• Speeding up buffered point on raster overlays in PostGIS

• Building utility functions in PostGIS

• Comparing WorldClim with local climate data

• Loading raster data in PostGIS and visualising the results in QGIS

• Importing Modis fire pixels into PostGIS

• Ten fold speed up using PostGIS2.1.SVN

• Loading Modis NDVI time series into PostGIS raster

• PostGIS raster statistics with PLR

Nice work Duncan!

Read and write PostGIS rasters with FME Beta now!

It's done! FME 2013 Beta reads and writes rasters from and to PostGIS! Safe Software posted a short tutorial in YouTube at the beginning of the month explaining how to write rasters into PostGIS and how to read them back using FME Workbench. There is no option to tile the raster before loading it because FME provides another tool, the "RasterTiler" transformer, to tile a raster source before feeding the PostGIS Writer.

Loading many rasters into separate tables with the PostGIS raster loader

There is no way yet, with the PostGIS raster loader, to load a series of rasters into separate tables. You can certainly load a series of rasters invoking the loader like this:

raster2pgsql -s 4236 -t 100x100 -I -C -F dem*.* public.dem | psql -d mydb

But all the rasters will be loaded into the same table, splited into 100x100 tiles. If you load ten 1000x1000 rasters, you end up with a table of 1000 rows.

FME 2013 will soon read and write rasters stored in PostGIS

Safe Software will write a reader, a writer and some others functionalities to interact with rasters stored in PostGIS! These tools should be released with FME 2013 and already available in future beta versions of FME this fall. A great news for the PostGIS community!

On the GDAL side, thanks to Jorge Arevalo, lots of improvements have been recently done on the PostGIS Raster driver. You can now read irregular arrangements of raster and translate them into any format supported by GDAL. We are still looking for sponsors to implement the writer part of the driver.

A Slow, Yet 1000x Faster, Alternative to ST_Union(raster) in PostGIS!

As a first post in this new blog I'd like to share an alternative method to merge a tiled raster coverage into a single row raster. For those who followed PostGIS Raster development, you have seen how we now use the very generic ST_Union() aggregate function to merge rasters in a very identical way you would merge geometries:

SELECT ST_Union(rast) rast
FROM tiledrastertable

ST_Union(raster) is a PL/pgSQL aggregate using ST_MapAlgebraExpr(raster, raster, expression) as a state function. It is very flexible in that you can add a parameter to specify how to aggregate the pixel values when two or more pixels overlap. You can use 'FIRST', 'LAST', 'MAX', 'MIN' 'MEAN', 'SUM'. 'LAST' is the default.