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

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of David
Epstein
Sent: Friday, August 20, 2010 6:43 PM
To: postgis-users at postgis.refractions.net
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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users