I'm hoping to export a PostGIS raster into a non-GIS enabled database. Ideally, I'd like to be able to have three columns in the output:
raster_value. Easy enough.
Ideally, I'd accomplish this something like the following:
CREATE TABLE le_output AS SELECT val, ST_AsText(geom) AS coord FROM ( SELECT (ST_PixelAsCentroids(rast, 1)).* FROM raster_table ) AS inside;
The problem is that this runs really slowly (hours). However, I can do something like this in a matter of 2 or 3 minutes:
SELECT ST_AsPNG(rast) FROM raster_table;
However, if I go that route, now I have to do some post-processing in Python or some other language to open the PNG, process all the pixels, do the math against the raster grid and scale to find the lat/longs, etc. It's a huge pain I'd like to avoid.
Anybody have suggestions for a higher performance way to accomplish this? All I want is a list of raster pixel coordinates and their values, which seems like a trivial operation. I'm not sure why it's so darn fast with
ST_AsPNG but so slow otherwise.
(For what it's worth, I've also tried joining the raster table to a list of every x/y coordinate location possible in the raster and using
ST_Value. That's slow as molasses as well.)
Edit: As requested, the rasters are about 3500 x 3500 pixels, on average.