I have the following problem (in PostgreSQL in combination with PostGIS):
I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS related, so bear with me ;)
SELECT count(*), ST_Centroid(ST_Collect("geom")) AS "geom" FROM ( SELECT kmeans(ARRAY[ST_X("geom"), ST_Y("geom")], 5) OVER (), "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding box") --It's this line ) AS ksub GROUP BY kmeans ORDER BY kmeans;
Where "Bounding box" is a polygon.
This gives me a result. So far so good.
I use Geoserver which retrieves the data from the database based on a bounding box. It does so by requesting all the records using the following query:
SELECT "count", "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding Box");
Where "Bounding Box" is again a polygon.
I'm not able to change the structure of this query. The reason I need Geoserver is because it will convert the results to a different format (GeoJSON).
Is it possible to write a view that sends the WHERE parameters (or the value "Bounding box" to the subquery? It's not possible to leave the WHERE statement outside of the subquery because the subquery has to calculate on the bounding box. If you put the WHERE statement outside of the subquery, the subquery will calculate the clusters for all the data and then the WHERE statement will limit it to the bounding box. This is not what I want. Or perhaps somebody knows how to rewrite the query so the WHERE statement from Geoserver will succeed?