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
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.