[postgis-users] Spatial Join Performance

Eric Shuman erics at ameri-title.com
Wed Nov 15 15:09:06 PST 2006

Hi list, I'm a bit new doing PostGIS queries and have some questions on
optimizing the speed of spatial joins.

I have a tax lot polygon layer that I want to find out what zones it falls
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
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

More information about the postgis-users mailing list