Well, there you have it. Thanx for pointing that out Chris. Goes to
show that there often many ways to accomplish the same thing in
PostGIS. I suppose it also really depends on the geometries (size,
type, etc). For the type of queries I often perform, checking distance
is sometimes faster than computing intersects(). This obviously isn't
the case for you. Good work. Glad to hear you're making progress.
For my own curiosity I timed a few similar queries as yours.
For two sets of ~3000 polygons,
using intersects() is significantly faster.
For two sets of ~5000 linestrings
using intersects() is 1.5 times faster.
For a set of ~300000 linestrings and ~300000 points
using distance()==0 is 1.5 times faster.
For a set of ~17000 polygons and 33000 points
using distance()==0 is 1.9 times faster.
Cheers,
Kevin
Chris Hermansen wrote:
Kevin makes some suggestions as to why I have empty geometry collections
and what is the quickest way to get rid of them. Based on his advice, I
thought I'd report some potentially useful info back to the list.
I have 11,228 polygons in "fc" and 4553 polygons in "results". I have a
late-model Core2 Duo based Dell running Ubuntu 7.04. This is
postgis1.3.1 and geos3.0.0RC4.
I have three test versions of a select statement that delivers the
intersection of "fc" and "results":
1. create temp table step1a as
select
map,standid,opening_id,intersection(fc.the_geom,results.the_geom)
as the_geom
from fc,results
where fc.the_geom && results.the_geom;
which takes 28.113 seconds on my machine
2. create temp table step1a as
select
map,standid,opening_id,intersection(fc.the_geom,results.the_geom)
as the_geom
from fc,results
where fc.the_geom && results.the_geom
and st_distance(fc.the_geom,results.the_geom) = 0.0;
which takes 4 minutes 5.903 seconds on my machine
3. create temp table step1a as
select
map,standid,opening_id,intersection(fc.the_geom,results.the_geom)
as the_geom
from fc,results
where st_intersects(fc.the_geom,results.the_geom);
which takes 26.203 seconds on my machine
Versions 2 and 3 both eliminate the empty geometry collections.
Kevin mentions (see below) that version 2 might be the fastest, but at
least in my case it is in fact the slowest of the bunch.
I hope the list readers find this useful!
Kevin, thanks a bunch for getting me on the right track!!!
Kevin Neufeld wrote:
Chris Hermansen wrote:
...
If I execute:
select geometryType(st_intersection(pblk.geom,pvri.geom))
from pblk,pvri
where pblk.geom && pvri.geom;
I get something quite different than what I expect. I expect to get
polygons, and sometimes multipolygons - which I assume arise when the
intersection process splits up polygons into several constituent parts.
But I also get a bunch of geometrycollections. And these
geometrycollections are apparently empty, for example, the first few
lines of output from the above select:
numgeometries | geometrytype ---------------+--------------------
| POLYGON
| POLYGON
0 | GEOMETRYCOLLECTION
0 | GEOMETRYCOLLECTION
| POLYGON
0 | GEOMETRYCOLLECTION
Are these supposed to be there? What conceivable purpose could they
serve?
Hi Chris,
Consider the cases where geometry bounding boxes intersect but the
geometries do not. In these cases, the intersection will be empty.
You probably want to add an additional filter to your query. "WHERE
pblk.geom && pvri.geom AND distance(pblk.geom, pvri.geom) = 0". (You
could alternatively use intersects(geometry, geoemetry), but
distance==0 is faster).
Does this help?
-- Kevin
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
