sql - 如何通过两列聚合多个点并从中创建 LineString / MultiLineString
问题描述
我有一个名为位置的表,其中包含以下行:
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)
想象一下,我的用户每小时将点数注册到该表的shape
列中。当注册该点的时间注册到表中时,creationDateTime
像这样保存到列2018-08-22 00:03:41.649+04:30
中。
我如何提取此信息:
each User ---- each day ---- list of geometry(shape column)
例如:
第一天的 User1 有几何点列表。第二天的用户1有几何点列表等等......
我通过 mongo 对同一个项目进行了此查询:
{$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}
}
但是我怎么能用 postgresql 做到这一点?postgre 上不存在这种聚合,我是 postgresql 的新手。这是我的查询:
(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")
这个查询没有合适的结果,我知道这个查询有问题。 用户的deviceId经常与其他行重复。我该如何处理?
最后我想创建多折线per user - per day - multi poly line
解决方案
可能有一百万种方法可以回答这个问题。这是其中之一:
考虑到您的表结构..
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);
..还有这些样本数据..
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)');
..您可以汇总每个用户+日期的积分并LINESTRING
使用以下方法创建ST_MakeLine
一个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)
d1166a84-ab66-11e8-98d0-529269fb1459
用户的图形描述2018-08-28 00:00:00+02
以同样的方式,您可以创建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)
编辑-使用(又名 WITH 子句)为每个用户()创建一组LINESTRINGS
每天:MULTILINESTRING
CTE
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
基本上你要做的就是对你需要的记录进行分组(在这种情况下是用户和日期)并使用你选择的聚合函数,例如ST_MergeLine
, ST_Collect
, ST_Union
,ST_Multi
等。
推荐阅读
- fortran - 分段错误 - 无效的内存引用,行数更多
- windows - Chocolatey 对 --override 参数感到困惑
- python-3.x - 如何从项目文件夹中导入所有 python 文件和方法
- laravel-5.3 - 在 laravel 项目中“compser install”时出错
- python-3.x - HttpAccessTokenRefreshError,通过 python gspread 连接到 google sheet API 时出现此错误
- qt - Opendds-3.13 发布构建失败
- rest - rest 和 Http 中“无状态”的含义
- angular - 如何使用角度从组件到模板选择选项按钮?
- cordova - Cordova:如何在启动时将应用程序方向锁定为横向而不是横向反转?
- database - 如果数据已更新,则阻止更新 MongoDB