AW: [mapserver-users] Mapserver layer selects

[Stephen Davies]

I agree that my problem is more PostGIS related than Mapserver but it did 
arise from a Mapserver session and this list is usually very helpful.

In addition, the constraint that seems to be in question is the extent 
constraint added by Mapserver.

Swip2 and swip3 are views:

benparts=# \d swip2
                View "public.swip2"
  Column  |            Type             | Modifiers
----------+-----------------------------+-----------
 rdate    | timestamp without time zone |
 state    | numeric                     |
 pid      | integer                     |
 location | text                        |
 geom     | text                        |
View definition:
 SELECT max(reading.rdate) AS rdate, sum(reading.rval) AS state, probe.id AS 
pid, max(probe.location) AS location, max(probe.geom::text) AS geom
   FROM probe, sensor, switch, reading
  WHERE probe.id = sensor.probe_id AND sensor.id = reading.sensor_id AND 
probe.switch_id = switch.id AND sensor.sensor_type::text = 'C'::text
  GROUP BY probe.id, reading.rdate
  ORDER BY max(reading.rdate) DESC;

benparts=# \d swip3
               View "public.swip3"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 id     | integer                     |
 zname  | text                        |
 pid    | integer                     |
 geom   | geometry                    |
 rdate  | timestamp without time zone |
View definition:
 SELECT DISTINCT zone.id, zone.name AS zname, probe.id AS pid, probe.geom, 
reading.rdate
   FROM reading, sensor, zone, probe, switch
  WHERE reading.sensor_id = sensor.id AND sensor.zone = zone.id AND 
zone.probe_id = probe.id AND probe.switch_id = switch.id AND 
sensor.sensor_type::text = 'C'::text AND reading.rval = 1::numeric
  ORDER BY zone.id, zone.name, probe.id, probe.geom, reading.rdate;

They are used to display irrigation probe and zone status in Mapserver layers.

The queries (should) return the same probe, switch and sensors - and without 
the extent constraint (as in query 3), they do.

Cheers and thanks,
Stephen
On Friday 13 February 2009 19:27:55 Eichner, Andreas - SID-NLKM wrote:
> Altough this is more PostgreSQL/PostGIS related and should go to there
> list but I tried it and...
> strange, I can not reproduce your problem. This is what I did:
>
> create table swip3 (
>   id int,
>   zname varchar(20),
>   pid int,
>   geom geometry,
>   rdate timestamp
> );
> insert into swip3 values ( 28, 'Zone 2', 607, geomfromewkb( decode(
> '0101000020BB1000007ADFF8DA335161406551D845D17541C0', 'hex')),
> '2009-02-12 13:30:00'::timestamp );
>
> create table probe (
>   int id,
>   int logger_id
> );
> insert into probe values ( 607, 1 );
>
> create table logger (
>   int id,
>   int client_id
> );
> insert into logger values ( 1, 120 );
>
> 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
>
> ----+--------+-----+----------------------------------------------------
> +---------------------
>  28 | Zone 2 | 607 | 0101000020BB1000007ADFF8DA335161406551D845D17541C0
>
> | 2009-02-12 13:30:00
>
> (1 Zeile)
>
> So I believe this has really nothing to do with MS.



-- 
=============================================================================
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