在BigQuery,我想写一个查询,其计算的距离之间开始和结束日期为每个标识。 我不感兴趣的要点之间的那些日期范围。 我只需要抓住的纬度/经度的开始和结束的日期和计算欧几里的距离。
表1是这样的:
ID startdate enddate
A 2016-9-16 2016-10-9
A 2017-3-18 2017-4-9
表2有的坐标,并看起来是这样的:
ID Date Latitude Longitude
A 2016-9-16 40.76 -109.33
A 2016-9-17 40.72 -109.33
A 2016-10-9 40.75 -109.33
A 2017-3-18 40.81 -109.33
A 2017-4-8 40.83 -109.33
A 2017-4-9 40.96 -109.32
我希望结果会是这样的:
ID t1.startdate t2.Latitude t2.Longitude t1.enddate t2.Latitude t2.Longitude distance
A 2017-3-18 40.81 -109.33 2017-4-9 40.96 -109.32 150
A 2016-9-16 40.76 -109.33 2016-10-9 40.75 -109.33 200
这是接近正如我已经得到了,但由于可以有多个身份证,这是不匹配的正确的开始和结束日期,为每个ID:
SELECT
t1,t2,
ST_DISTANCE(Point1, Point2 ) as distance
from (
SELECT
ID,
st_geogpoint(Longitude,Latitude) as Point1
from `t2` AS t2 INNER JOIN
`t1` AS t1 ON t1.ID = t2.ID
WHERE t1.ID = t2.ID AND
t2.Date = t1.startdate ) t1,
(
SELECT
ID,
st_geogpoint(Longitude, Latitude) as Point2
from `t2` AS t2 INNER JOIN
`t1` AS t1 ON t1.ID = t2.ID
WHERE t1.ID = t2.ID AND
t2.Date = t1.enddate
) t2
WHERE t1.ID = t2.ID
目前的结果是这样的:
ID t1.startdate t2.Latitude t2.Longitude t1.enddate t2.Latitude t2.Longitude distance
A 2016-9-16 40.76 -109.33 2016-10-9 40.75 -109.33 150
A 2016-9-16 40.76 -109.33 2017-4-9 40.96 -109.32 250
A 2017-3-18 40.81 -109.33 2017-4-9 40.96 -109.32 200
A 2017-3-18 40.81 -109.33 2016-10-9 40.75 -109.33 250