Hello,
I am using SQL Views in Geoserver on a PostGIS store. I am using Geoserver
version 2.16.2.
I need to pass a dynamic statement in the WHERE clause, but since the query
counts any combination of 15 columns to filter the points before counting
the number of points per polygon, normal parameters will not work. I am
trying to (a) understand how to use the :where_cluase: parameter in a SQL
view, and (b) more specifically how to send parameters to it from Leaflet
-- what is the correct syntax?
The SQL View Query is the following:
WITH selection AS (
SELECT a.id, count(b.geom) AS total
FROM boundary_poly a LEFT JOIN businesses b
ON st_contains(a.geom,b.geom)
WHERE deleted_at IS NULL :where_clause:
GROUP BY a.id)
SELECT a.id, a.geom, a.cdname,
CASE WHEN b.total IS NULL THEN 0
ELSE b.total END
FROM boundary_poly a FULL OUTER JOIN selection b
ON a.id = b.id
I am trying to send parameters to the :where_clause: from Leaflet like this
but it does not work;
var boundaries = L.tileLayer.wms(geoserver + '/geoserver/ows?', {
layers: 'workspace:boundaries',
format: 'image/png',
transparent: true,
tileSize: 2048,
viewparams: 'where_clause:wineries=1'
}).addTo(map);
It does not load but I get a 200 on the call. Eventually, I will need to
pass a WHERE clause that could be like any of these:
"p1=1 OR p2=1 ORp3=1"
"p7=1 OR p15=1"
"p5=1"
Any clarification on how to properly use the :where_clause: placeholder
would be a huge help!
Thanks,
Cliff
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
