时间戳

首页 > 解决方案 > 如何从geojson中删除少于4个值的坐标

sql - 如何从geojson中删除少于4个值的坐标

问题描述

正如标题所说，我正在对存储在 bigquery 中的共享单车数据进行查询，我能够提取数据并以正确的顺序排列以显示在路径图中。在数据中，只有开始和结束经纬度协调，或者有时只开始经纬度，我如何删除少于 4 点的任何东西？这是代码，我也仅限于选择

SELECT 
 routeID , 
 json_extract(st_asgeojson(st_makeline( array_agg(st_geogpoint(locs.lon, locs.lat) order by locs.date))),'$.coordinates') as geo

FROM 
 howardcounty.routebatches
where  unlockedAt between {{start_date}} and {{end_date}} 
cross join UNNEST(locations) as locs
GROUP BY routeID
order by routeID
limit 10

为了清晰起见，还包括了一个屏幕截图坐标屏幕截图

标签： sqlgoogle-bigquerygis

解决方案

要在 a 之后应用条件group by，请使用 a having。对于一个简单的条件——路线是否至少有两个数据集？-- 可以使用这个查询：

With dummy as (
    Select 1 as routeID,  [struct(current_timestamp() as date, 1 as lon, 2 as lat),struct(current_timestamp() as date, 3 as lon, 4 as lat)] as locations 
    Union all select 2 as routeID,  [struct(current_timestamp() as date, 10 as lon, 20 as lat)]
       )


SELECT 
 routeID , count(locs.date) as amountcoord,
 json_extract(st_asgeojson(st_makeline( array_agg(st_geogpoint(locs.lon, locs.lat) order by locs.date))),'$.coordinates') as geo

FROM 
 #howardcounty.routebatches
dummy

#where  unlockedAt between {{start_date}} and {{end_date}} 
cross join UNNEST(locations) as locs
GROUP BY routeID
having count(locs.date)>1
order by routeID
limit 10

对于更复杂的，嵌套select可能会完成这项工作：

Select *
from (
 --- your code ---
) where length(geo)-length(replace(geo,"]","")) > 1+4

JSON 在您的代码中转换为字符串。如果]对 JSON 的结尾进行计数并减去 1，则计算内部数组。

推荐阅读