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?

>

--