Geoserver SQL Parameter View

Question

I'm trying to use Geoserver Parameter SQL views, but I'm having trouble getting geoserver to recognize the parameters (and I'm not able to follow the documentation - it's not very clear to me). Here's the code I'm working with so far, does anyone have any suggestions?

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table1.Date = %Date%

I'm trying to get geoserver to recognize each "Date" as a parameter - there are about 25 total dates, and I want the user to be able to select a date (or multiple dates) for display. The dates are in yyyy-mm-dd format.

Thanks for your help.

Answer 1

I have a table in PostGIS structured like this:

CREATE TABLE locations
(
  id integer NOT NULL,
  mp_id character varying(50),
  dt timestamp with time zone DEFAULT now(),
  the_geom geometry,
  CONSTRAINT pk_emp PRIMARY KEY (id)
)

For this table, I make the following Parametric View

SELECT id, mp_id, dt, the_geom
  FROM locations where Date(dt)=Date('%Dor%')

When you click on 'Guess Parameters from SQL, it will recognise Dor as the parameter. You should enter a default value. For my data, I entered 2014-08-19 The main change is in the regular expression. You need to enter ^[\w\d\s-]+$ this will recognize dates in yyyy-mm-dd format.

When you make a WMS request, be sure to send the parameters properly. I appended the following to the GET request: &viewparams=Dor:2014-08-12

By changing the date, I can see different data in the image sent by GetMap request on the WMS end point