I have a PostGIS query that will return several million rows:
SELECT t1.id AS id1, t2.id AS id2, ABS(t1.mean_h - t2.mean_h) AS h_diff, ST_Distance(t1.the_geom, t2.the_geom) AS dist FROM tas_ponds as t1, tas_ponds as t2 WHERE (t1.gid > t2.gid) AND ST_DWithin(t1.the_geom, t2.the_geom, 17000)
When run in
psql, I get an
out of memory for query result error.
Googling suggests that this an error within psql rather than postgres/PostGIS. Would amending the query into the form
SELECT ... INTO x FROM ... fix the problem? Are there any other recommended approaches for dealing with very large datasets?