Funny you should ask this right now. I'm half way through implementing an
option in the PostGIS DataStore to specify exactly this.
You are correct, intersects does not use the GiST index, as far as I know,
and the && operator does. That's why the && operator is used in
conjunction with the intersects(), so as to make use of the index before
attempting the intersects operation.
The weakness of the && operator is that it is not exactly correct. As you
say, it does more or less the same thing. The difference is it just uses
the bounding box of the geometries, instead of the actual geometries,
causing it to be wrong is some cases.
But some people, like you I imagine, and like most WMSes, don't really
care so much about that. Which is exactly why I coded up the option (just
for the Bounding Box filter, may consider doing it for other filters, but
I'm not sure how I feel about that) to specify which one you want.
I finished the implementation in postgis gt2 2.0.x filter, I just need to
include it in the factory. If you can work off of cvs I can probably get
it up in a day or two (I'm on dial up at the moment, so things are
slower).
And I'd like feedback on the name you prefer, I'm thinking either
'loose-BBox' or 'strict-BBox' (both would be true/false, and have the
opposite meaning). When done users can specify this in the geoserver
admin interface.
The code for the filter is in the geotools subversion
(http://svn.geotools.org - in the 2.0.x branch) repository, you can get it
right away and set 'looseBbox' to true, build with maven and plug in to
GeoServer and it'll get the behaviour you'd like right away.
Chris
----- Original Message -----
From: "Peter Segerstedt" <petersegerstedt@anonymised.com>
To: <geoserver-devel@lists.sourceforge.net>
Sent: Thursday, September 16, 2004 1:10 PM
Subject: Re: [Geoserver-devel] Non-optimized SQL-statements?
Oops, I seem to have missed a '2' when I copied the output (I've
corrected
it below).
No, they are two identical pairs of conditions, actually I think all of
the
four conditions in variant 1 are equal which in that case means that
they
could all be replaced by one of them. The operation '&&' (overlaps) and
the
function 'intersects()' seems to do the same job, just that for some
reason,
'intersects()' in my case doesn't use the gist-index. The performance
(counted in time) differs by a factor 9 in my simple test./Peter
>From: Alexandre Florio <aflorio@anonymised.com>
>To: geoserver-devel@lists.sourceforge.net
>Subject: Re: [Geoserver-devel] Non-optimized SQL-statements?
>Date: Thu, 16 Sep 2004 09:52:53 -0300
>
>
> Aren't the queries different?
>
> In the first query you are intersecting with 2 geometries
(SRID=2400
>and 400), and in the second one, just with 1 geometry (SRID=2400).
>
>-- Alexandre
>
>
>Em Thu, 16 Sep 2004 13:57:38 +0200
>"Peter Segerstedt" <petersegerstedt@anonymised.com> escreveu:
>
> > # SELECT gid, AsText(force_2d("the_geom")), "shape_area"
> > # FROM "byggnader"
> > # WHERE (
> > # "the_geom" && GeometryFromText('POLYGON ((1332700 6174900,
>1332700
> > 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400)
> > # AND
> > # intersects("the_geom", GeometryFromText('POLYGON ((1332700
>6174900,
> > 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700
6174900))',
> > 2400))
> > # AND
> > # "the_geom" && GeometryFromText('POLYGON ((1332700 6174900,
>1332700
> > 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))',2400)
> > # AND
> > # intersects("the_geom", GeometryFromText('POLYGON ((1332700
>6174900,
> > 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700
6174900))',
>2400))
> > # )
> >
> >
> > When I evaluate the statement in psql using 'EXPLAIN ANALYZE' I get
the
> > following result:
> >
> >
> > # Index Scan using byggnader_gist_index on byggnader
(cost=0.00..652.08
> > rows=18 width=312) (actual time=0.000..16574.000 rows=9452 loops=1)
> > # Index Cond: (
> > # (the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700
> > 6181900,1339700 6181900,1339700 6174900,1332700
6174900))'::geometry)
> > # AND
> > # (the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700
> > 6181900,1339700 6181900,1339700 6174900,1332700
6174900))'::geometry)
> > # )
> > # Filter: (
> > # intersects(the_geom, 'SRID=2400;POLYGON((1332700
6174900,1332700
> > 6181900,1339700 6181900,1339700 6174900,1332700
6174900))'::geometry)
> > # AND
> > # intersects(the_geom, 'SRID=400;POLYGON((1332700
6174900,1332700
> > 6181900,1339700 6181900,1339700 6174900,1332700
6174900))'::geometry))
> > # Total runtime: 17326.000 ms
> >
> >
> > From my point of view it seems as if the following statement would
be
> > sufficient:
> >
> >
> > # SELECT gid, AsText(force_2d("the_geom")), "shape_area"
> > # FROM "byggnader"
> > # WHERE (
> > # "the_geom" && GeometryFromText('POLYGON ((1332700 6174900,
>1332700
> > 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400)
> > # )
> >
> >
> > ...which results in:
> >
> >
> > # Index Scan using byggnader_gist_index on byggnader
>(cost=0.00..32893.03
> > rows=8534 width=312) (actual time=0.000..2033.000 rows=9452 loops=1)
> > # Index Cond: (
> > # the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700
> > 6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry
> > # )
> > # Total runtime: 2123.000 ms
> >
> >
> > Am I totally wrong or is something happening here that maybe could
be
> > improved?
>
--