[postgis-users] Spatial Join Performance

[Paul Ramsey]

I am going to guess that your zoning polygons are fairly large and  
complex and that each one contains quite a large number of taxlots.  
Right?
In that case, you've re-found a known performance issue which we have  
promised to fix on any number of occasions, which is short-circuiting  
some simple cases like point-in-polygon to avoid the GEOS functions  
(which are robust and slow) and go for a simpler point-in-polygon  
test when it is applicable (as it is in your case).
Mark, if Eric confirms this case, can you file this as a bug and take  
a run at it in your AST.
P

On 15-Nov-06, at 3:09 PM, Eric Shuman wrote:

> Hi list, I'm a bit new doing PostGIS queries and have some  
> questions on
> optimizing the speed of spatial joins.
>
> Scenario:
> I have a tax lot polygon layer that I want to find out what zones  
> it falls
> into.
> There are about 100,000 tax lot shapes and 1000 zones.
>
> SELECT DISTINCT ON (taxlot,zone_) taxlot,zone_
> 	FROM taxlot,zoning
> 	WHERE taxlot != ''
> 		AND taxlot.the_geom && zoning.the_geom
> 		AND contains(zoning.the_geom,centroid(taxlot.the_geom))
>      ;
>
> I have created GIST indexes on both the zoning and taxlot tables  
> and when I
> run the query with EXPLAIN I get results suggesting the use of  
> indexes.
>
> ...
> Index Scan using taxlot_geom_index on taxlot  (cost=0.00..6.02 rows=1
> width=64)
> Index Cond: (taxlot.the_geom && "outer".the_geom)"
>
>
> I have been able to get a similar query to run on smaller data  
> sets, but so
> far I have tried to run this one for over 2 hours with out it  
> completing...
>
> Am I approaching this correctly or is there something I am missing  
> that
> would speed up the query??
>
>
> Thanks for the help!!
>
> ---------------
> Eric Shuman
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users