[postgis-users] Spatial Join Performance
pramsey at refractions.net
Wed Nov 15 17:49:09 PST 2006
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.
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.
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.
> 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
> 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
> Am I approaching this correctly or is there something I am missing
> would speed up the query??
> Thanks for the help!!
> Eric Shuman
> postgis-users mailing list
> postgis-users at postgis.refractions.net
More information about the postgis-users