I am trying to figure out a way of producing a view of a layer in GeoServer, using an external SQL query.
By external I mean, for instance, a user defined query in a PHP from where all the parameters defined there would act as variables in the query.
By doing so, I would allow users of my web mapping application to select only what they intended to see.
Is this possible?
I am using PostgreSQL/PostGIS + GeoServer + OpenLayers and the front end webpage was developed using PHP.
EDIT 1 – I've been able to create the parametric view and pass a parameter value to the viewparams call successfully. However, when trying to pass a parameter as result of a variable value the layer polygons just wont load. I'm declaring a variable as var data = 10000000; and passing the variable to the viewparams as follows: viewparams:'volume:data'. The wms layer polygons wont load, but the getfeatureinfo works as if the viewparams wasn't defined because when i click on the map, i get the featureinfo pop-up with the corresponding info.
Any ideas?
Best Answer
Starting with GeoServer 2.1.0, layers can also be defined as SQL Views. SQL Views allow executing a custom SQL query on each request to the layer. This avoids the need to create a database view for complex queries.
Even more usefully, SQL View queries can be parameterized via string substitution. Parameter values can be supplied in both WMS and WFS requests. Default values can be supplied for parameters, and input values can be validated by Regular Expressions to eliminate the risk of SQL injection attacks.
See Here for the full documentation.
Please note warning regarding SQL Injection Attacks.