[postgis-users] Mapserver layer selects

[Stephen Davies]

Could somebody please help me explain the results below.

The selects are cut-and-paste from postgresql log of cursor creation commands 
from mapserver.
Both refer to the same point entity and both have the same map extent 
constraint but the first query (correctly) returns one row while the second 
returns none.

However, removing the extent constraint from the second query to give the 
third, succeeds in returning the correct row.

Given that this is the only difference between queries 2 and three, one would 
have to assume that the point is outside the extent.

However, query 1 shows that the point is actually in the extent - as does 
eyeballing the geometries.

To further confuse me, increasing the extent in query 2 eventually results in 
the entity being returned.

Cheers and thanks,
Stephen Davies

benparts=# SELECT * from swip2 WHERE (pid in (select probe.id from probe where 
logger_id in (select id from logger where client_id=120)) and 
rdate='2009-02-12 13:30:00') and (geom && 
setSRID( 'BOX3D(138.5356633 -34.9225467,138.5397151 -34.9184949)'::BOX3D,4283) );
        rdate        | state  | pid | location |                        geom
---------------------+--------+-----+----------+----------------------------------------------------
 2009-02-12 13:30:00 | 13.000 | 607 | Oval     | 
0101000020BB1000007ADFF8DA335161406551D845D17541C0
(1 row)

benparts=# SELECT * from swip3 WHERE (pid in (select probe.id from probe where 
logger_id in (select id from logger where client_id=120)) and 
rdate='2009-02-12 13:30:00') and (geom && 
setSRID( 'BOX3D(138.5356633 -34.9225467,138.5397151 -34.9184949)'::BOX3D,4283) );
 id | zname | pid | geom | rdate
----+-------+-----+------+-------
(0 rows)

benparts=# SELECT * from swip3 WHERE (pid in (select probe.id from probe where 
logger_id in (select id from logger where client_id=120)) and 
rdate='2009-02-12 13:30:00');
 id | zname  | pid |                        geom                        |        
rdate
----+--------+-----+----------------------------------------------------+---------------------
 28 | Zone 2 | 607 | 0101000020BB1000007ADFF8DA335161406551D845D17541C0 | 
2009-02-12 13:30:00
(1 row)

-- 
=============================================================================
Stephen Davies Consulting P/L                             Voice: 08-8177 1595
Adelaide, South Australia.                                Fax  : 08-8177 0133
Computing & Network solutions.                            Mobile:040 304 0583
                                          VoIP:sip:1132210 at sip1.bbpglobal.com