David, Don't use ST_Intersects and buffer. That is really slow. Try replacing that with ST_DWithin. If that's still too slow you may want to consider simplifying some of your larger geometries.
ST_DWithin(p1.wkb_geometry, p2.wkb_geometry,528) http://www.postgis.org/documentation/manual-svn/ST_DWithin.html http://www.postgis.org/documentation/manual-svn/ST_SimplifyPreserveTopology. html Leo and Regina http://www.postgis.us -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of David Epstein Sent: Friday, August 20, 2010 6:43 PM To: [email protected] Subject: [postgis-users] tweaking a query to use less memory 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
