[postgis-users] Overlap Queries - speed
ealpert at digitalglobe.com
Mon Oct 3 14:11:37 PDT 2005
Bill I thought it was discovered that distance() was faster than
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bill
Sent: Monday, October 03, 2005 2:28 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Overlap Queries - speed
On Mon, 3 Oct 2005, Robert Burgholzer wrote:
> I have a question regarding performance, running postGIS 1.0.4 on
> postgreSQL 8.0.3. running on RedHat Enterprise ES, 1.113 GHz Inter
> Pentium III, 376 Megs of RAM
> I am performing an intersection calculation on two spatial tables, the
> tables are as follows:
> bmp_multiseg : 6579 records
> p5lrsegs : 2364 records
> select a.msid, b.lrseg, b.landseg, b.riverseg,
> area2d(intersection(a.the_geom, b.the_geom))/area2d(b.the_geom)
> bmp_multiseg as a, p5lrsegs as b where a.the_geom && b.the_geom;
> basically, there are several inputs in bmp_multiseg at a number of
> overlapping resolutions that I am distributing amongst the second
> table p5lrsegs, with an average of ~ 6 overlaps from bmp_multiseg to
> p5lrsegs, making a total of ~ 15,000 records in the resulting query.
> This query takes over an hour to complete with spatial indices on both
> tables geometry field.
> The geometries are not simple, they range in size from between 5-20K
> per row, perhaps this is part of the problem?
> Any insight would be appreciated. Even if you tell me that this is
> reasonable and I have to just suck it up, or look for another tool to
> do my calculations, those kinds of insights are good to know.
Well, one thing that might help would be to add an intersects() to your
where clause. If you shapes are very irregular, your && will cause many
false positives causing too many empty intersection shapes to be
(Someone correct me if I'm wrong).
So you might try:
select a.msid, b.lrseg, b.landseg, b.riverseg,
area2d(intersection(a.the_geom, b.the_geom))/area2d(b.the_geom) from
bmp_multiseg as a, p5lrsegs as b where a.the_geom && b.the_geom AND
Another thing (obviously) is to make sure that your indexes are being
used. Run a EXPLAIN on that query and post the results.
postgis-users mailing list postgis-users at postgis.refractions.net
More information about the postgis-users