4

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!

Improve this question

1 Answer 1

Reset to default
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
;
Improve this answer
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? Commented Jun 2, 2021 at 16:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.