I have a table called locations that has these rows:
id uuid NOT NULL, "deviceId" text COLLATE pg_catalog."default", "userId" uuid, "userName" text COLLATE pg_catalog."default", "creationDateTime" timestamp with time zone, shape geometry, CONSTRAINT id PRIMARY KEY (id)
Imagine My user register points per hours into this table at shape
column .When register the time of that point that is registered into table, saved into creationDateTime
column like this 2018-08-22 00:03:41.649+04:30
.
How can i extract this info :
each User ---- each day ---- list of geometry(shape column)
in example:
User1 in first day has list of geometry points. User1 in seccond day has list of geometry points and so on…
I made this query by mongo for same project:
{$project: { _id: 0, uId : "$UserId", dId : "$DeviceId", ts :"$CreationDateTime", point : "$Point" } }, {$group: { _id :{ did: "$dId", day: { $dayOfMonth: "$ts" } }, docs: { $push: "$$ROOT" } } }, { $sort:{"_id.day": -1} }
But how could i do that with postgresql? There is not exist this kind of aggregation on postgre and i am new on postgresql. This is my query :
(Select test1."deviceId",test1."shape", test1."creationDateTime" From (Select * from locations) as test1 Group By test1."deviceId",test1."shape",test1."creationDateTime" ORDER BY test1."creationDateTime")
This query did not suitable result and i know this query has a problem. deviceId of users frequently repeated with other row.How can i handle it?
At the end i want to create multi-poly-line per user - per day - multi poly line
Advertisement
Answer
There are probably a million ways to answer this question. Here is one of them:
Considering your table structure ..
CREATE TEMPORARY TABLE locations (id uuid, deviceId text COLLATE pg_catalog."default", userId uuid, userName text COLLATE pg_catalog."default", creationDateTime timestamp with time zone, shape geometry);
.. And these sample data ..
INSERT INTO locations (userId, creationDateTime, shape) VALUES ('d1166a84-ab66-11e8-98d0-529269fb1459',CURRENT_DATE,'POINT(-1.25 51.75)'), ('d1166a84-ab66-11e8-98d0-529269fb1459',CURRENT_DATE,'POINT(-1.15 52.96)'), ('d1166a84-ab66-11e8-98d0-529269fb1459',CURRENT_DATE,'POINT(-0.13 50.82)'), ('d1166a84-ab66-11e8-98d0-529269fb1459',CURRENT_DATE-1,'POINT(-2.22 53.48)'), ('d1166a84-ab66-11e8-98d0-529269fb1459',CURRENT_DATE-1,'POINT(-0.11 51.51)');
.. you can aggregate the points per user + date and create a LINESTRING
using ST_MakeLine
with a GROUP BY
:
SELECT userId, creationDateTime, ST_AsText(ST_MakeLine(shape)) FROM locations GROUP BY userId, creationDateTime ORDER BY creationDateTime; userid | creationdatetime | st_astext --------------------------------------+------------------------+------------------------------------------------- d1166a84-ab66-11e8-98d0-529269fb1459 | 2018-08-28 00:00:00+02 | LINESTRING(-2.22 53.48,-0.11 51.51) d1166a84-ab66-11e8-98d0-529269fb1459 | 2018-08-29 00:00:00+02 | LINESTRING(-1.25 51.75,-1.15 52.96,-0.13 50.82) (2 Zeilen)
Graphical depiction of user d1166a84-ab66-11e8-98d0-529269fb1459
at 2018-08-28 00:00:00+02
In the same fashion you can create a MULTIPOINT
using ST_Collect
:
SELECT userId, creationDateTime, ST_AsText(ST_Collect(shape)) FROM locations GROUP BY userId, creationDateTime ORDER BY creationDateTime; userid | creationdatetime | st_astext --------------------------------------+------------------------+------------------------------------------------- d1166a84-ab66-11e8-98d0-529269fb1459 | 2018-08-28 00:00:00+02 | MULTIPOINT(-2.22 53.48,-0.11 51.51) d1166a84-ab66-11e8-98d0-529269fb1459 | 2018-08-29 00:00:00+02 | MULTIPOINT(-1.25 51.75,-1.15 52.96,-0.13 50.82) (2 Zeilen)
EDIT– Create a set of LINESTRINGS
per day for each user (MULTILINESTRING
) using CTE
(aka WITH Clause):
WITH j AS ( SELECT userId, creationDateTime, ST_MakeLine(shape) AS shape FROM locations GROUP BY userId, creationDateTime) SELECT userId, ST_AsText(ST_Collect(shape)) FROM j GROUP BY userId userid | st_astext --------------------------------------+---------------------------------------------------------------------------------- d1166a84-ab66-11e8-98d0-529269fb1459 | MULTILINESTRING((-2.22 53.48,-0.11 51.51),(-1.25 51.75,-1.15 52.96,-0.13 50.82)) (1
Basically what you have to do is to group the records you need (in this case user and date) and use the aggregate function of your choice, e.g. ST_MergeLine
, ST_Collect
, ST_Union
, ST_Multi
, etc..