4

The Problem

I have about 256000 tiles in a table of GDEM v2 at 1 arcsecond resolution. I'm on an Ubuntu server running Postgres 9.5.5 and PostGIS 2.2. The table has a GIST index on the convex hull of each tile, and according to the docs, the primary key has a unique index created automatically so rid should be indexed as well.

I want to select a not-so-random subset of them for use in ST_Quantile.

The Query

I was successfully able to accomplish this using the following query:

SELECT (stats).*
FROM (
  SELECT ST_Quantile(
      ST_Union(rast),
      1,
      ARRAY[.1, .2, .3, .4, .5, .6, .7, .8, .9, 1]
    ) As stats
    FROM gdem_100

    -- this WHERE clause doesn't work
    -- WHERE rid BETWEEN 0 AND 500 OR
    --       rid BETWEEN 75000 AND 75500 OR
    --       rid BETWEEN 150500 AND 151000 OR
    --       rid BETWEEN 250000 AND 250500

    -- this WHERE clause works
    WHERE rid BETWEEN 0 AND 500 OR
          rid BETWEEN 1500 AND 2000 OR
          rid BETWEEN 3500 AND 4000 OR
          rid BETWEEN 4500 AND 5000 OR
          rid BETWEEN 5500 AND 6000

) As foo;

When I use the first (commented out) WHERE clause, I get a memory allocation error. The second one works fine.

One suggestion in the comments points to nodata values as a possible contributing factor ST_Union doesn't consider those by default. I will investigate this, but given the dataset, I think that quanitity of nodata would be really surprising.

Why does using the first WHERE clause cause a memory problem, when the second one, which actually selects more tiles, does not?

  • Can you first simplify you query to see the minimum at which it still faults? So try to replace all raster operators by a simple count(rid) for example. This will also give you an idea of how many tiles exactly are being processed. After you did that, can you add the output of an EXPLAIN ANALYZE from a query that does work to the post? One more thing: did you build an index for the rid? – tilt May 23 '17 at 5:12
  • By default ST_Union doesn't consider no data, so it could be even though you have more tiles in first, the percent of nodata pixels is a lot higher. – LR1234567 May 23 '17 at 6:23
  • ST_Union fails because the size of the resulting raster overflow your available memory. I guess you will have to extract all the values and compute the quantile yourself. – Pierre Racine May 23 '17 at 13:42

Your Answer

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

Browse other questions tagged or ask your own question.