I'm doing this sql query using geoservers sql views:

SELECT kmeans, count(*), ST_BUFFER(ST_CENTROID(ST_Collect(geom)), qrt(count(*))*1000  ) AS geom
   SELECT kmeans(ARRAY[ST_X(ST_CENTROID(geom)), ST_Y(ST_CENTROID(geom))], 10) OVER (), geom
      FROM data WHERE foo = %FOO%
AS ksub
GROUP BY kmeans
ORDER BY kmeans

This always returns the same regardless which bounding box is requestet via the wms. But I want to use the bounding box in my WHERE clause. I know that the user gets a different result then if there are more tiles used to dislpay a map but that will be ok for me.



  • Did you solve it?
    – pakman
    Commented Jun 10, 2015 at 23:46

1 Answer 1


I found some workaround to this as I was needing to do exactly the same thing, classify using kmeans inside the database depending on the features which are within the extent. I'm using openlayers.

Here is what I do. In javascript I've put a listener on precompose event in the map. Then I can calculate the extents (bbox) inside the javascript and change the request parameters of the TileWMS layer.

Here is the piece of code:

map.on('precompose', function() {
            var layer = map.getLayers().item(0);
            var view = map.getView();
            var center = view.getCenter();
            var resolution = view.getResolution();
            var size = map.getSize();
            var bbox = [
                center[0] - size[0] * resolution * 0.5,
                center[1] - size[1] * resolution * 0.5,
                center[0] + size[0] * resolution * 0.5,
                center[1] + size[1] * resolution * 0.5
            layer.getSource().params_.x1 = bbox[0];
            layer.getSource().params_.y1 = bbox[1];
            layer.getSource().params_.x2 = bbox[2];
            layer.getSource().params_.y2 = bbox[3];

Everytime the map is going to compose it updates the x1, y1, x2, y2 parameters, so I can use them in my SQL View.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.