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

[Nicklas Avén]

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: 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>