Re: [postgis-users] Spatial join not finishing

2012-02-29 Thread Jorge Gil
All, Problem fixed. Looking at the results from the Explain tool (thanks Paul!) it showed that it was taking too long because it was using a sequential id. I just had to create a spatial index on the geometry of the two tables to speed it up. I suspect many other queries I've been doing will

Re: [postgis-users] Spatial join not finishing

2012-02-29 Thread Jorge Gil
Dear Paul, Thanks for the tip, as you can see I'm a newbie... I've run Explain on the query and the result is this: UPDATE source.bag_new_buildings as aa SET units=bb.count FROM (SELECT cc.gid , count(*) from source.bag_new_buildings as cc, source.bag_new_landuse as dd where ST_Within(dd.the_

Re: [postgis-users] Spatial join not finishing

2012-02-29 Thread David Quinn
Just a thought (from a novice postgis user) - are there spatial indexes on both tables? I've forgotten to create these a couple of times and the time difference is enormous. -David On Wed, Feb 29, 2012 at 6:15 PM, Paul Ramsey wrote: > Without a query plan (explain ... ) there's not much people

Re: [postgis-users] Spatial join not finishing

2012-02-29 Thread Paul Ramsey
Without a query plan (explain ... ) there's not much people can do but scratch their chins sagely. P. On Wed, Feb 29, 2012 at 3:11 PM, Jorge Gil wrote: > Hi everyone, > > I've been trying to do a spatial join for a couple of days in different ways > and it never seems to finish. I've done someth

[postgis-users] Spatial join not finishing

2012-02-29 Thread Jorge Gil
Hi everyone, I've been trying to do a spatial join for a couple of days in different ways and it never seems to finish. I've done something identical before in a much larger set of the same data and it worked after a few hours. I have a polygons (buildings) table with 150,000 records and a po