Postgis syntax error with WMS time request

[Dave Weaver]

On 13/06/07, Dave Weaver <dave.weaver at zen.co.uk> wrote:
> 
> Now I am trying to implement time support on a different machine (using the
> mapserv from the fgs distribution), but I am getting a strange error I don't
> fully understand:
> 
> ------------------------------------
> msDrawMap(): Image handling error. Failed to draw layer named 'sat_time_idx'.
> prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
> query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
>
image_time::text,asbinary(force_collection(force_2d(geometry)),'NDR'),image_path::text
> from satellite WHERE (image_time = 'date_trunc('minute', image_time) =
> '2007-06-12T11:00:00'') and (geometry &&amp;amp; setSRID( 'BOX3D(-20012461.4573608
> -44133305.4068053,20012461.4573608
> 44133305.4068064)'::BOX3D,find_srid('','satellite','geometry') ))'
> 
> Postgresql reports the error as 'ERROR:  syntax error at or near "minute" at
> character 178
> ------------------------------------

Well, I managed to solve this by simply downloading the lastest mapserver
source and re-building it. Perhaps it's a problem in the FGS distribution?

However, I'm still experiencing problems trying to display my time-based
imagery. Using a WMS request the resultant image is blank.

Using a Mapserver-style request, such as:
http://pb-vmap2:8080/cgi-bin/mapserv?map=map/satellite.map&mode=map&layers=satellite

and adding this line to my time index layer in the map file:
  FILTER "(date_trunc('minute',image_time) = '2007-06-12T11:00:00')"

gives me an image.

If I turn on debug output for the WMS request, I see that this Postgis query
is being executed:

query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
"image_time"::text,asbinary(force_collection(force_2d(geometry)),'NDR'),image_path::text
from satellite WHERE (date_trunc('minute', image_time) =
'2007-06-12T11:00:00') and (geometry &&amp; setSRID( 'BOX3D(-20012461.4573608
-44133305.4068053,20012461.4573608
44133305.4068064)'::BOX3D,find_srid('','satellite','geometry') ))

If I cut & paste that into pgsql I get 4 rows returned (my data consists of
images for several parts of the world), so it looks ok so far.

I suspect that the images are being filtered geographically in some way
after the postgis uery (does that happen?) so it might be down to my
geometry, but I've no idea how to find out what's wrong.

My images are being added to postgis with statements like this:

INSERT into satellite (image_path, image_time, geometry) values(
'/home/map/time_based_images/SATIR_NAMER_200706131000.png', '2007-06-13
10:00Z', GeometryFromText('POLYGON((-181.486 74.243, -28.5143852 74.2428069,
-28.5143852 -2.2428056, -181.486 -2.243, -181.486 74.243))',4326) )

where those numbers in the polygon are lat/long coordinates of the 4 corners
of the image - is there anything fundamentally wrong with the above?

Many thanks,
Dave.