[postgis-users] Spatial Join Performance

[Eric Shuman]

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