1

Context

I have a table filled with ASTER GDEM data for the state of Colorado tiled at 100x100 pixels. I want to query the table and return a single raster that covers all of the supplied bounding box (it surrounds Fort Collins, in this case).

Then, I export that raster in the selected GDAL format using a custom function written in Python/PL called write_raster.

I am attempting to do so using this query:

SELECT write_raster(ST_AsGDALRaster(ST_Union(rast), 'Gtiff'),  -- Raster bytea to write
  '/home/nronnei/gis/data/postgis-raster/colorado_elevation.tif'::text  -- Path to write to
)
FROM public.co_dem_tiled
WHERE ST_Intersects(
  ST_MakeEnvelope(-105.4330444336, 40.3789822705,
    -104.7024536133, 40.7170785158, 4326),
  ST_Envelope(co_dem_tiled.rast)
);

I'm using PostGIS v2.1, and the 'same_alignment' constraint is not set on the table. When I try to add the constraint it fails because the data aren't aligned.

The Problem

When I run this query, I get the following output:

ERROR:  rt_raster_from_two_rasters: The two rasters provided do not have the same alignment

Everything runs just fine if I remove ST_Union from the query above. What really makes this weird is that I'm only pulling raster from a single, constrained table.

Naturally, I tried using ST_NotSameAlignmentReason like so:

WITH base AS (
  SELECT rast
  FROM public.co_dem_tiled
  WHERE ST_Intersects(
    ST_MakeEnvelope(-105.4330444336, 40.3789822705,
      -104.7024536133, 40.7170785158, 4326),
    ST_Envelope(co_dem_tiled.rast)
  )
),
top AS (
  SELECT rast
  FROM public.co_dem_tiled
  WHERE ST_Intersects(
    ST_MakeEnvelope(-105.4330444336, 40.3789822705,
      -104.7024536133, 40.7170785158, 4326),
    ST_Envelope(co_dem_tiled.rast)
  )
)
SELECT ST_NotSameAlignmentReason(base.rast, top.rast)
FROM base, top;

However, this doesn't give me anything useful. It simply reports that all rasters have the same alignment, as expected.

What's going on here?!

  • What is your postgis_full_version() return? Does your table co_dem_tiled have a constraint called enforce_same_alignment_rast? – LR1234567 Nov 26 '16 at 21:15
  • @LR1234567 Updated the question to answer yours. – nronnei Nov 28 '16 at 14:36
1

I'm puzzled ST_NotSameAlignmentReason is returning Same Alignment for all, is it possible you missed one. I think there was a bug a while back with this in 2.1 but can't remember the version. The checks use ST_SameAlignment.

What does this return:

    WITH base AS (
SELECT rid, rast
 FROM public.co_dem_tiled
WHERE ST_Intersects(
 ST_MakeEnvelope(-105.4330444336, 40.3789822705,
  -104.7024536133, 40.7170785158, 4326),
 ST_Envelope(co_dem_tiled.rast) )
)
SELECT base.rid, ST_SameAlignment(base.rast, top.rast) AS same,   
ST_NotSameAlignment(base.rast, top.rast) AS reason
FROM base, (SELECT rast FROM base LIMIT 1) As top
WHERE NOT ST_SameAlignment(base.rast, top.rast) ;

If you truly have all aligned in this set, it should return no records.

To answer your question, it's best to avoid different alignments in same table if you plan to union them since ST_Union requires all tiles in operation to be aligned.

  • Great catch, just didn't realize it because there were so many tiles. This query reveals I have 4 rasters with different scales on the X axis. Thanks for your help! – nronnei Nov 29 '16 at 11:03

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.