Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
A function can accept parameters and be used as a table expression in the from clause. Like in from the_function('Bounding Box'). Is that what you mean? BTW double quotes are used for identifiers not string values. Does ST_GeomFromText("Bounding box") really work? –  Clodoaldo Mar 24 at 12:39
    
Sorry, had to be more clear there. ST_GeomFromText takes a geometric value in text. This will look something like: "POLYGON(minx,maxx,miny,maxy)". –  kwarts Mar 24 at 12:42
    
And regarding the function, I'm not sure if I can use that? I can't change how Geoserver will retrieve it's data, I can't change the structure of its retrieval. Any ideas how I can still use a function then? –  kwarts Mar 24 at 12:45
1  
You mean Geoserver returns the whole command SELECT "count", "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding Box")? If that is the case then you will need a dynamically built query. If not explain how does the interaction with Geoserver works for those not familiar with it. Do it in the question. –  Clodoaldo Mar 24 at 12:53
    
I changed the question, I hope it's a bit more clear now. It basically boils down that Geoserver only knows one way to retrieve data, using the query I showed. So I have to find some way to give it a view that works. –  kwarts Mar 24 at 13:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.