and David, don't forget to have a working spatial index in place for queries like this. st_dwithin needs it badly when there is a lot of geometries around.
/Nicklas On Fri, 2010-08-20 at 19:26 -0400, Paragon Corporation wrote: > 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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
