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:

    ST_AsText(geom) AS coord
            (ST_PixelAsCentroids(rast, 1)).*
    ) 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:


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.

  • Please edit the question to report the size of the rasters. I expect the extra time is spent doing the things you didn't (or wouldn't if you weren't constrained to SQL) – Vince Oct 13 '16 at 20:41
  • Edit made as requested. I'm not sure I'm following the second half of your statement, but if your implication is that the extra time is spent calculating the lat/longs of each pixel, I'm quite confident that I can write a Python script to do that against an output raster PNG image of this size in a matter of seconds, if not milliseconds. – John Chrysostom Oct 14 '16 at 11:50
  • 1
    Raster exists as a data format because it is orders of magnitude more efficient than constucting regular {X,Y,value} pairs. 10 million points with 20-30 bytes per point isn't going to write to disk in milliseconds. – Vince Oct 14 '16 at 12:41
  • Thanks for the tips. I ultimately ended up writing the Python script, though. Takes <1 sec per raster to query them as PNG files and write them to disk. Another ~4 seconds to open the PNG, process the pixels, and write the results to CSV. Not ideal, but fast enough for my needs. – John Chrysostom Oct 14 '16 at 19:29

Vince is right, rasters are not supposed to be in a database as individual cell values. It's a waste of resources.

There are however valid reasons to store pixel values as separate entities. In science, Hierarchical data formats are doing a bit the same thing, where every cell has it's own XY value but also references to arrays of other data (like multiple bands in one raster). This goes in the direction of column storage and you could mimic that by at least storing all your raster values in one row with the help of arrays. So you instead of {X,Y,value} you do: {X:[values],Y:[values],value1:[values]} in one row. There are other disadvantages to it, but at least it may be quicker and still compatible with non-gis databases.

(I understand this is answer is missing a lot of detail but it is more like giving you a direction to look for)

  • Thanks for the suggestions. I'm totally fine with broad suggestions as opposed to specific code. I can build what I need, as long as I know what tools are best. – John Chrysostom Oct 14 '16 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.