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: latitude, longitude, 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.)

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: latitude, longitude, 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.