Adding a SQL Parametric View based LayerΒΆ

The traditional way to use database backed data is to configure either a table or a database view as a new layer in GeoServer. Starting with GeoServer 2.1.0 the user can also create a new layer by specifying a raw SQL query, without the need to actually creating a view in the database. The SQL can also be parametrized, and parameter values passed in along with a WMS or WFS request.

Creating a plain SQL view

  1. In order to create an SQL view the administrator can go into the Add a new resource from the Layers page.

    ../_images/sqlviews_addlayer.png
  2. Upon selection of a database backed store a list of tables and views available for publication will appear, but at the bottom of if a new link, Configure new SQL view, will appear:

    ../_images/sqlviews_postgrestore.png
    ../_images/sqlviews_addsqllayer.png
  3. Selecting the link Configure new SQL view will open a new page where the SQL statement can be specified:

    ../_images/sqlviews_plainsql_params.png

    Plain SQL View configuration

    SELECT st.obs_year,
                                st.storm_num,
                                st.storm_name,
                                min(st.obs_datetime)
                                AS storm_start, max(st.obs_datetime)
                                AS storm_end, max(st.wind)
                                AS max_wind, st_makeline(st.geom)
                                AS the_route
    FROM ( SELECT storm_obs.storm_num,
                                storm_obs.storm_name,
                                storm_obs.wind,
                                storm_obs.press,
                                storm_obs.obs_datetime,
                                date_part('year'::text, storm_obs.obs_datetime)
                                AS obs_year, storm_obs.geom
           FROM storm_obs
          ORDER BY date_part('year'::text, storm_obs.obs_datetime),
                                        storm_obs.storm_num,
                                        storm_obs.obs_datetime) st
    GROUP BY st.obs_year, st.storm_num, st.storm_name
    ORDER BY st.obs_year, st.storm_num
    

    Note

    The query can be any SQL statement that can be validly executed as part of a subquery in the FROM clauses, that is select * from (<the sql view>) [as] vtable. This is true for most SQL statements, but specific syntax might be needed to call onto a stored procedure depending on the database. Also, all the columns returned by the SQL statement must have a name, in some databases aliasing is required when calling function names

  4. Once a valid SQL statement has been specified press the refresh link in the Attributes table to get a list of the feature type attributes:

    ../_images/sqlviews_plainsql_refresh.png

    Note

    GeoServer will do its best to figure out automatically the geometry type and the native srid, but they should always be double checked and eventually corrected. In particular having the right SRID (spatial reference id) is key to have spatial queries actually work. In many spatial databases the SRID is equal to the EPSG code for the specific spatial reference system, but that is not always true (e.g., Oracle has a number of non EPSG SRID codes).

  5. Specify a valid SRID.

    ../_images/sqlviews_plainsql_refresh_srid.png

    Forcing manually 4326 SRID in this case

    Note

    If stable feature ids are desired for the view’s features one or more column providing a unique identification for the features should be checked in the Indentifier column. Always make sure those attributes generate a actually unique key, or filtering and WFS clients will mishbehave.

  6. Once the query and the attribute details are set press Save and the usual new layer configuration page will show up. That page will have a link to a SQL view editor at the bottom of the Data tab:

    ../_images/sqlviews_plainsql_featuretype.png
  7. Make sure the CRS is EPSG:4326 and write manually (-180,-90,180,90) values in the Bounding Boxes sections. (Note: you can use the Compute from SRS bounds link if available)

    ../_images/sqlviews_plainsql_bbox.png
  8. Click Save.

At this point the new WMS Layer is being published with GeoServer.

Creating a parametric SQL view

Warning

As a rule of thumb use SQL parameter substitution only if the required functionality cannot be obtained with safer means, such as dynamic filtering (CQL filters) or SLD parameter substitution. Only use SQL parameters as a last resort, improperly validated parameters can open the door to SQL injection attacks.

A parametric SQL view is based on a SQL query containing parameters whose values can be dinamically provided along WMS or WFS requests. A parameter is bound by % signs, can have a default value, and should always have a validation regular expression.

  1. In order to create a parametric SQL view performs the steps 1 and 2 like before and then insert the following parameters:

    ../_images/sqlviews_parametricsql_params.png

    Parametric SQL View configuration

    SELECT date_part('year'::text, t1.obs_datetime) AS obs_year, t1.storm_num, t1.storm_name, t1.wind, t2.wind AS wind_end, t1.press, t2.press AS press_end, t1.obs_datetime, t2.obs_datetime AS obs_datetime_end, st_makeline(t1.geom, t2.geom) AS geom
    FROM storm_obs t1
    JOIN ( SELECT storm_obs.id, storm_obs.storm_num, storm_obs.storm_name, storm_obs.wind, storm_obs.press, storm_obs.obs_datetime, storm_obs.geom
               FROM storm_obs) t2 ON (t1.obs_datetime + '06:00:00'::interval) = t2.obs_datetime AND t1.storm_name::text = t2.storm_name::text
    WHERE
            date_part('year'::text, t1.obs_datetime) BETWEEN %MIN_OBS_YEAR% AND %MAX_OBS_YEAR%
    ORDER BY date_part('year'::text, t1.obs_datetime), t1.storm_num, t1.obs_datetime
    

    Note

    The query defines two parameters %MIN_OBS_YEAR% and %MAX_OBS_YEAR%.

  2. Click on the Guess parameters from SQL. GeoServer will automatically create fields with the parameters spcified in the view:

    ../_images/sqlviews_parametricsql_guess_params.png

    Note

    Always provide default values for each parameter in order to let the layer work properly and also be sure the regular expression for the values validation are correct.

    Examples of Regular Expressions:

    • ^[\d\.\+-eE]+$ will check that the parameter value is composed with valid elements for a floating point number, eventually in scientific notation, but will not check that the provided value is actually a valid floating point number
    • [^;']+ will check the parameter value does not contain quotes or semicolumn, preventing common sql injection attacks, without actually imposing much on the parameter value structure
  3. Fill in some default values for the parameters, so that GeoServer can run the query and inspect the results in the next steps. Set MAX_OBS_YEAR to 2020 and MIN_OBS_YEAR to 0.

  4. Refresh the attributes, check the Geometry SRID and publish the layer like before. Also assign the storm_track_interval style to the layer as Default Style.

    ../_images/sqlviews_parametricsql_publishing.png
  5. Click on the OpenLayers on the Layer Preview list for v_storm_track_interval layer.

  6. At a first glance you won’t see anything since the layer is using the default parameters for the observation years. Specify two years for the view adding this parameter at the end of the GetMap Request:

    &viewparams=MIN_OBS_YEAR:2000;MAX_OBS_YEAR:2000

    You should obtain a request like this:

http://localhost:8083/geoserver/geosolutions/wms?service=WMS&version=1.1.0&request=GetMap&layers=geosolutions:v_storm_track_interval&styles=&bbox=-180.0,-90.0,180.0,90.0&width=660&height=330&srs=EPSG:4326&format=application/openlayers&viewparams=MIN_OBS_YEAR:2000;MAX_OBS_YEAR:2000

Now you are able to see the hurricanes from the parametric view and also dynamically choose the observation years interval of interest.

../_images/sqlviews_parametricsql_preview.png

Parametric SQL View OL preview