2

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?

  • 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 '14 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 '14 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 '14 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 '14 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 '14 at 13:01
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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