[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.
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
More information about the postgis-users
mailing list