How to get bbox of grouped geometries using postgis queries?

Question

I have a points table in my postgresql table.

CREATE TABLE my_points(
    gid serial PRIMARY KEY,
    created_on TIMESTAMP NOT NULL,
    geog geography(POINTZ,4326) 
);

So I want to get bounded boxes of updated data which grouped by created_on. The updated datas today are different locations.

For example table data is like this:

 gid      created_on             geog
 ------------------------------------
 1        08/15/2021 10:38:11    (1,2)
 2        08/15/2021 10:38:11    (2,2)
 3        08/15/2021 10:38:11    (3,2)
 4        08/15/2021 11:12:04    (1,2)
 5        08/15/2021 11:12:04    (2,4)

In this table there are two groups by date. 08/15/2021 10:38:11 has ids (1,2,3) and 08/15/2021 11:12:04 has ids (4,5

So I need a select query for two bounded boxes to gets grouped by created_on date.

I need a seelct query to find blue square geoemtries.

How can I select this?

I could be wrong but I think you really don't need to do this , visualization tools like Qgis will draw squares for you for given at least 2 points — eshirvana, Aug 15 '21 at 17:09
@eshirvana, I will use this boxes in web or other environments. — barteloma, Aug 16 '21 at 05:33

Jim Jones · Accepted Answer · 2021-08-16T14:48:16.823

Create a cluster of points with ST_Union and GROUP BY and then use either ST_Envelope or ST_Extent to draw the bounding box:

ST_Envelope

Returns the minimum bounding box for the supplied geometry, as a geometry:

SELECT 
  ST_Envelope(
    ST_Union(geog::geometry)) 
FROM my_points
GROUP BY created_on;

ST_Extent

Retrieves a BBOX of given geometry or group of geometries:

WITH j (created_on,geog) AS (
  SELECT 
    created_on, ST_Union(geog::geometry)
  FROM my_points
  GROUP BY created_on
)
SELECT ST_Extent(geog) FROM j
GROUP BY created_on;

Demo: db<>fiddle

CREATE TABLE my_points(
    gid serial PRIMARY KEY,
    created_on TIMESTAMP NOT NULL,
    geog geography(POINT,4326)
);

INSERT INTO my_points VALUES
(1,'2021-08-15 10:38:11','SRID=4326;POINT(-4.481927586167595 54.32254424440715)'),
(2,'2021-08-15 10:38:11','SRID=4326;POINT(-4.44759531077697 54.28408149183809)'),
(3,'2021-08-15 10:38:11','SRID=4326;POINT(-4.563638401597283 54.29169676415854)'),
(4,'2021-08-15 11:12:04','SRID=4326;POINT(-4.52449960765197 54.23234056232733)'),
(5,'2021-08-15 11:12:04','SRID=4326;POINT(-4.478494358628533 54.1893743942604)');

Result:

WITH j (created_on,geog) AS (
  SELECT 
    created_on, ST_Union(geog::geometry)
  FROM my_points
  GROUP BY created_on
)
SELECT ST_Extent(geog) FROM j
GROUP BY created_on;
                                   st_extent                                   
-------------------------------------------------------------------------------
 BOX(-4.563638401597283 54.28408149183809,-4.44759531077697 54.32254424440715)
 BOX(-4.52449960765197 54.1893743942604,-4.478494358628533 54.23234056232733)

This works but if group has 2 points, it returns LINESTRING. If group has 3 or more, ite returms Polygon. I want always return a bbox out of the group. — barteloma, Aug 16 '21 at 05:33

How to get bbox of grouped geometries using postgis queries?

1 Answers1

ST_Envelope

ST_Extent