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.

• simogeo: as a start I would actually use a different name with respect to the names or attributes in the tables you are joining, at least different casing. Hope that helps, Simone.

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

• Haynes: Thank you! This seems to have worked. I was missing the date() portion of the query.