[postgis-users] tweaking a query to use less memory

[David Epstein]

Hello,

I am running postGIS on a Ubuntu linux laptop with 4Gb of memory. I need
to calculate a value for each of 320,000 polygons based on neighboring
polygons (within 1/10 of a mile). I stopped the query below after 2.5
hours because it used 3.8Gb of memory and the swap was at 1Gb and
rising. Can anyone recommend changes in the query that would allow it to
run in RAM? Do I need a more powerful machine (or more patience) for
tasks like this?

thank you,
-david


****


CREATE TABLE bad_housing_neighbors AS

SELECT p1.ogc_fid, 

avg(case when 
p2.vod='1' or 
p2.fire='1' or
p2.condition='3' or
p2.condition='4'
then 1 else 0 
end)

--528 feet is 1/10 of a mile
FROM parcels09_d3_v2 as p1, parcels09_d3_v2 as p2
WHERE st_intersects(buffer(p1.wkb_geometry,528), p2.wkb_geometry)
GROUP BY p1.ogc_fid
ORDER BY p1.ogc_fid