[GIS] Using a parametric SQL View in Geoserver

geoserver

In Geoserver i create parametric view:

SELECT *
FROM MyTable
LEFT JOIN Track On MyTable.SegmentID=Track.SegmentID
WHERE (DATEPART(yy, MyTable.Date)=%year%
AND DATEPART(mm, MyTable.Date)=%month%
AND DATEPART(dd, MyTable.Date)=%day%)

i set default values 2009-09-18

Now i go in layer preview pick my layer and see layer with features with Date=2009-09-18.
Now i want to see features with another date. In address line i add:

viewparams=year:2009

But still see same features. I try another dates but not help.
Whats wrong?

Best Answer

I dont know what heppens with my view but i delete all parameters except one and try it. And use viewparams parameter in Layer Preview i get features. After i add another parameters in view and my final view:

SELECT 
myTable.Segment_ID,
the_geom
FROM myTable
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
where (DATEPART(yy, myTable.Date) = %year%
AND DATEPART(mm, myTable.Date) = %mounth%
AND DATEPART(dd, myTable.Date) = %day%
AND DATEPART(hh, myTable.Date) = %hour%)

And Layer Preview address look like:

http://localhost:8080/geoserver/cite/wms?service=WMS&version=1.1.0&request=GetMap&
layers=cite:IntensityPram&styles=&
bbox=62.65931379022568,59.354842,77.56711981909412,63.919917987969896&
width=1077&height=330&srs=EPSG:4326&
viewparams=year:2009;mounth:09;day:18;hour:11&
format=application/openlayers

I think my problem was with Bouding Box. It was too small.

Related Question