时间戳

首页 > 解决方案 > bigquery geojson 将同一用户的多行连接到单行

sql - bigquery geojson 将同一用户的多行连接到单行

问题描述

如何将来自同一用户的多行合并到一个 geojson 格式的行中，我尝试了 order by 但没有运气。这是我正在使用的示例代码，我也仅限于在此数据库上选择

SELECT rh.routeid, st_asgeojson(st_geogpoint(locs.lon, locs.lat)   
FROM demo.routebatches RB, demo.route R
cross join UNNEST(locations) as locs
where EXTRACT (date FROM TIMESTAMP_MILLIS (CAST(locs.date as INT64))) =  "2017-03-10" and rh.cycleID = 'aff9bb7b-3b92-4620-bc50-1152edefe04c'
order by routeID
limit 100

这给出了这个结果，其中来自同一 routeid 的多个 long 和 lats 不按 routeid 排序。我该如何解决这个问题？

Geojson 可以，但我也会采用这种格式，它来自超集中的 deck.gl 路径

超集deck.gl路径

大查询结果

标签： sqlgoogle-bigquerygisapache-superset

解决方案

这里有两个问题：

  1. 如何对路线内的位置进行排序。你写where multiple long and lats from the same routeid are not ordered by routeid- 但这是一个令人困惑的陈述。所有这些点 ( same routeid) 都具有相同的 routeid，那么如何对它们进行排序by routeid？您可能希望它们在 routeid 组中进行排序，但您需要一个不同的字段来对它们进行排序。

  2. 您需要将 routeid 中的所有点组合成一个线串，而不是将每个点分开。

此外，尚不清楚实际的输入模式是什么，以及 routebatches 表与路由表的关系如何，如果您澄清一下会有所帮助。字段是否locations属于 RB 或 R 表？在没有任何谓词的情况下连接 RB 和 R 表的方式使每个 RB 行与每个 R 行交叉连接。

看起来locations记录有一个date以毫秒为单位的字段-如果我们可以按此字段排序，我会使用类似

SELECT 
  rh.routeid, 
  st_asgeojson(st_makeline(
    array_agg(st_geogpoint(locs.lon, locs.lat) order by locs.date)))
FROM demo.route R cross join UNNEST(locations) as locs
where 
  EXTRACT (date FROM TIMESTAMP_MILLIS (CAST(locs.date as INT64))) =  "2017-03-10" 
  and rh.cycleID = 'aff9bb7b-3b92-4620-bc50-1152edefe04c'
GROUP BY routeID
order by routeID
limit 100

首先，我们按 routeId 分组，然后聚合 ( array_agg) 这条路线中的所有点，创建一个包含所有按时间戳排序的点的数组。st_makeline从此数组构建一个线串，然后您可以将其转换为 geojson。

此外，请参阅从公共 NOAA 数据创建类似线串的这篇文章： https ://mentin.medium.com/longest-hurricane-eeb6844d65ed

推荐阅读