[postgis-users] ST_AsRaster and gdal_translate

[Guillaume Drolet DRF]

Hi,

I have been struggling with this since yesterday and now I think it is 
time for me to request some help from the community :

I am trying to convert a geometry table (polygons) to a raster that will 
have 1000 m-pixels, and then export that raster to a TIF file using 
gdal_translate.
I've tried loads of different queries but nothing seems to work... Here 
are examples of some the queries I tried:

CREATE TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m AS
     SELECT ST_AsRaster(hyd.geom, 1000.0, 1000.0) AS rast
     FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset AS hyd;

ALTER TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m ADD COLUMN 
rid serial PRIMARY KEY;

CREATE INDEX
     ON rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m
     USING gist(st_convexhull(rast));

SELECT AddRasterConstraints('rasters'::name,
         'bdtq_20k_hydro_so_gfsm_aea_subset_1000m'::name,
         'rast'::name);

Then at the command line:

 >gdal_translate "PG:host=localhost port=5432 dbname=testspdb 
user=postgres password=*** schema=rasters 
table=bdtq_20k_hydro_so_gfsm_aea_subset_1000m mode=2" E:\temp\hydro1000m.tif

I get this error:

ERROR 1: Error, the table 
rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m contains tiles with 
different size, and irregular blocking is not supported yet
GDALOpen failed - 1
Error, the table rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m 
contains tiles
with different size, and irregular blocking is not supported yet

My problem seems to be related to the irregular blocking of my raster. I 
though I could create a raster with regular block and pixel size but I 
didn't figure how to do this yet. I tried to achieve this by creating an 
empty raster:

CREATE TABLE rasters.test_rasterize (
     rid serial PRIMARY KEY,
     rast raster
);

INSERT INTO rasters.test_rasterize VALUES (
     1,
     ST_MakeEmptyRaster(45, 62, 1828552.00, 1422569.00, 1000.0, 1000.0, 
0.0, 0.0, 3175)
);
-- 45 and 62 are the approximate numbers of 1000 m pixels in x and y 
that fit inside the extent of my geometry layer
UPDATE rasters.test_rasterize
     SET rast = ST_AsRaster(geom, 1000.0, 1000.0)
         FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset;


When I call gdal_translate with rasters.test_rasterize, I get this:

Input file size is 2, 4
0...10...20...30...40...50...60...70...80...90...100 - done.

I'm getting desperate and will greatly appreciate any hints!

If that can help, I'm using PostGIS 2.0.0 r9605 and GDAL 1.9.0

Many thanks,

Guillaume