gpt4 book ai didi

sql - 如何按两列聚合多个点并从中创建一个 LineString/MultiLineString

转载 作者:行者123 更新时间:2023-11-29 12:51:51 29 4
gpt4 key购买 nike

我有一个名为 locations 的表,其中包含这些行:

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

我如何提取这些信息:

每个用户----每天----几何列表(形状列)例如:

User1 in first day has list of geometry points. User1 in seccond day has list of geometry points and so on...

我通过 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 of users frequently repeated with other row.我该如何处理?

最后我想创建多折线每个用户 - 每天 - 多折线

最佳答案

可能有一百万种方法可以回答这个问题。这是其中之一:

考虑你的表结构..

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)');

.. 您可以汇总每个用户 + 日期的积分并使用 ST_MakeLine 创建一个 LINESTRING使用 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-529269fb14592018-08-28 00:00:00+02 的图形描述 enter image description here

以同样的方式,您可以使用 ST_Collect 创建一个 MULTIPOINT :

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)

enter image description here

编辑- 使用 CTE 每天为每个用户创建一组 LINESTRINGS(MULTILINESTRING) (又名 WITH 子句):

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

enter image description here

基本上您需要做的是对您需要的记录进行分组(在本例中为用户和日期)并使用您选择的聚合函数,例如ST_MergeLine , ST_Collect , ST_Union , ST_Multi等..

关于sql - 如何按两列聚合多个点并从中创建一个 LineString/MultiLineString,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52072643/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com