Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers 4

up vote 7 down vote accepted

Some poking around does confirm this is a Postgres client problem, independent of spatial or server considerations: the client has a limited amount of memory to buffer the results before displaying them on the screen, which you're exceeding.

The recommended approach to handle this is to use a DECLARE / FETCH approach to access the data in smaller blocks than the total result set. You could also create a view with components of the query (e.g. distance) to cut down on the memory needed for the query operation itself.

share|improve this answer
    
+1 This is an excellent solution. –  Adam Matan Aug 12 '10 at 5:46
add comment

For the record, in my case storing the returned dataset in another table using the SELECT ... INTO ... syntax worked.

It not only solved the out-of-memory issue, but was also substantially faster than original query.

share|improve this answer
add comment

scw got me by two minutes, so I won't repeat his answer. Here are some other possible solutions:

  • Edit the Memory section of postgresql.conf. Try to see if you have extremely low memory settings that might prevent the query from running.

  • Try to write the query into a file and run from the command line, using:

    psql -f filename db_name > output_file

  • If you intend to use the results in an external application, try to Run the query using a cursor from outside psql. For example, a Python script that would run your query:

script:

try:
    conn = psycopg2.connect("dbname='' user='' host='' password=''") # Fill in
    cur = conn.cursor()
except:
        print 'Unable to connect to the database'
else:
        print 'Connected to database.'

query="""YOUR
         QUERY
         HERE"""
cur.execute(query)

A cursor is iterable, so you can either:

for result in cur:
    print result

Or get them all:

 results=cur.fetchall()
share|improve this answer
2  
I could be wrong, but I don't think the first (and perhaps the second) solutions will work. The first one modifies the server memory, and this is a client issue, not related to the available memory within the DMBS, and the second would likely suffer the same issue unless just buffering it to the screen is the issue. Your third solution is better than mine IMO, since it gives fine-grained control in a flexible environment with the cursor, which is a bit tricker in raw SQL. +1 –  scw Aug 12 '10 at 7:36
    
@scw you're probably right about the first (and perhaps second) solution. Problem is, sometimes these specific tricks seem to work with PostgreSQL, they might improve general performance, and they take little time and effort to try. Learned a lot from your answer. –  Adam Matan Aug 12 '10 at 8:37
add comment

Indexing is very important in Postgres and PostGIS

http://postgis.refractions.net/docs/ch04.html#id2794434

Recommend GiST (Generalized Search Trees) indexes for very large datasets.... (is also "null safe") example CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );

After building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans

VACUUM ANALYZE [table_name] [column_name]; SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);

also a good reference is: Taking Advantage of Indexes http://postgis.refractions.net/docs/ch04.html#id2794685

share|improve this answer
    
Yep, I indexed and vacuumed the tables, and confirmed my indexes are being used via EXPLAIN, etc. However, my issue isn't with speed (at this point), its with an out of memory error. –  fmark Aug 12 '10 at 2:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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