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?
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:
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;
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)