Postgresql – Unable to send WHERE parameters to subquery

postgispostgresqlsubquery

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?

Best Answer

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).

PostGIS supports converting results to a different format. In your case, you want,

Moreover, it supports a ton of other Geometry Exports (current as of 2.5) too,

  • ST_AsBinary — Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEncodedPolyline — Returns an Encoded Polyline from a LineString geometry.
  • ST_AsEWKB — Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
  • ST_AsEWKT — Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • ST_AsGML — Return the geometry as a GML version 2 or 3 element.
  • ST_AsHEXEWKB — Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
  • ST_AsKML — Return the geometry as a KML element. Several variants. Default version=2, default maxdecimaldigits=15
  • ST_AsLatLonText — Return the Degrees, Minutes, Seconds representation of the given point.
  • ST_AsSVG — Returns a Geometry in SVG path data given a geometry or geography object.
  • ST_AsText — Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_AsTWKB — Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
  • ST_AsX3D — Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
  • ST_GeoHash — Return a GeoHash representation of the geometry.
  • ST_AsGeobuf — Return a Geobuf representation of a set of rows.
  • ST_AsMVTGeom — Transform a geometry into the coordinate space of a Mapbox Vector Tile.
  • ST_AsMVT — Return a Mapbox Vector Tile representation of a set of rows.
Related Question