3

I need the coordinates of the bounding box as a GeoJSON from PostGIS.

To do the calculations on the PostgreSQL server I use:

SELECT  ST_Extent(l.position_geo) AS box (...)

The answer is as expected a Box:

BOX(8.5359520687 47.3553607386,8.5458103273 47.3652211268) 

So far so good.

But how can I convert this BOX into a GeoJSON for the transport to the client?

When I use :

SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(t.*)::json)
    ) AS geojson
from (

The geometry is missing.

There must be an easy way to form a simple GeoJSON with two points out of a BOX answer!

2

You need to cast the BOX3D back to a GEOMETRY for ST_AsGeoJSON to make sense of it:

SELECT JSONB_BUILD_OBJECT(
         'type',     'FeatureCollection',
         'features', JSONB_AGG(ST_AsGeoJSON(q.*)::JSONB)
       ) AS fc
FROM   (
    SELECT 1 AS id,
           ST_Extent(ST_Expand('POINT(0 0)'::GEOMETRY, 1))::GEOMETRY AS geom
) q
;

Note that ST_Extent is an aggregate function, so the sub-select would need to be properly grouped to enclose anything but all geometries that you select! It may be easier to use ST_Envelope on individual geometries from the sub-select; it also returns GEOMETRY directly:

SELECT JSONB_BUILD_OBJECT(
         'type',     'FeatureCollection',
         'features', JSONB_AGG(ST_AsGeoJSON(q.*)::JSONB)
       ) AS fc
FROM   (
    SELECT 1 AS id,
           ST_Envelope(ST_Expand('POINT(0 0)'::GEOMETRY, 1)) AS geom
) q
;

If a per-feature BBOX is needed, the non-RECORD signatures will include them as OGC standard into the GeoJSON; extending the example from @Encomiums (deleted) answer, this


SELECT JSONB_BUILD_OBJECT(
         'type',     'FeatureCollection',
         'features', JSONB_AGG(feature)
       ) AS fc
FROM   (
  SELECT JSONB_BUILD_OBJECT(
           'type',       'Feature',
           'id',         id,
           'geometry',   ST_AsGeoJSON(geom, options => 1)::JSONB,
           'properties', TO_JSONB(r.*) - 'geom' - 'id'
         ) AS feature
  FROM   (
    SELECT 1 AS id,
           'foo' AS name,
           ST_Envelope(ST_Expand('POINT(0 0)'::GEOMETRY, 1)) AS geom
  ) r
) q
;

would add a bbox member to each feature.


I wrote a set of custom aggregate functions a while back that directly returns valid FeatureCollections; while they center around the RECORD signature of ST_AsGeoJSON (not including a bbox member), they make the first examples a bit more convenient:

SELECT ST_AsFeatureCollection(q.*) AS geojson
FROM   (
    SELECT 1 AS id,
           ST_Envelope(ST_Expand('POINT(0 0)'::GEOMETRY, 1)) AS geom
) q
;
1
  • Thank you. My task is to calculate the FlyTo Point and the zoom level for Mapbox for a layer with GeoJSOn feeded points. I calculate the flyto point with avg(ST_X(l.position_geo)) as center_lng, avg(ST_Y(l.position_geo)) as center_lat. Now with the box i want to finde the right zoom level to see all the points. Mybe there is a easier way. Is there a postgis function to find the maximum distance between the furthermost points? – Baba Wanga Jun 2 at 16:02

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.