当地理坐标在原始gps表中时如何获取旅行模式

a2mppw5e 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(276)

我在一个名为 labels 包含使用的旅行模式类型的开始和结束时间：

CREATE TABLE labels
(
    user_id integer NOT NULL,
    session_id int,
    start_timestamp timestamp with time zone NOT NULL,
    end_timestamp timestamp with time zone NOT NULL,
    travelmode text,
    PRIMARY KEY (user_id, start_timestamp, end_timestamp)
)

INSERT INTO labels (user_id,session_id,start_timestamp,end_timestamp,travelmode) 
 VALUES     (11,0,'2007-06-26 11:32:29+01','2007-06-26 11:40:29+01','bus'),
     (11,0,'2008-03-28 14:52:54+00','2008-03-28 15:59:59+00','train'),
      (11,0,'2008-03-28 16:00:00+00','2008-03-28 22:02:00+00','train'),
      (11,0,'2008-03-29 01:27:50+00','2008-03-29 15:59:59+00','train'),
      (11,0,'2008-03-29 16:00:00+00','2008-03-30 15:59:59+01','train'),
      (11,0,'2008-03-30 16:00:00+01','2008-03-31 03:13:11+01','train'),
      (11,0,'2008-03-31 04:17:59+01','2008-03-31 15:31:06+01','train'),
      (11,0,'2008-03-31 16:00:08+01','2008-03-31 16:09:01+01','taxi'),
      (11,0,'2008-03-31 17:26:04+01','2008-04-01 00:35:26+01','train')

在另一张table上 trajectories 是每个用户的gps采样（原始gps，采样间隔约为1-5秒）：

CREATE TABLE trajectories
(
    user_id int,
    session_id int NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    lat double precision NOT NULL,
    lon double precision NOT NULL,
    alt double precision,
    PRIMARY KEY (session_id, "timestamp")
)

INSERT INTO trajectories (user_id,session_id,timestamp,lat,lon,alt)  
 VALUES     (11,1002008,'2008-03-30 16:00:39+01',41.147205,95.457762,-777),
      (11,1002008,'2008-03-30 16:01:38+01',41.153458,95.444897,-777),
      (11,1002008,'2008-03-30 16:02:37+01',41.154867,95.429467,-777),
      (11,1002008,'2008-03-30 16:03:36+01',41.154075,95.413863,-777),
      (11,1002008,'2008-03-30 16:04:35+01',41.152223,95.398515,-777),
      (11,1002008,'2008-03-31 02:52:11+01',43.697033,87.57619,-777),
      (11,1002008,'2008-03-31 02:51:12+01',43.69425,87.579275,-777),
      (11,1002008,'2008-03-31 02:50:13+01',43.689312,87.587815,-777),
      (11,1002008,'2008-03-31 02:43:56+01',43.656445,87.634753,-777),
      (11,1002008,'2008-03-31 02:42:56+01',43.649275,87.638028,-777),
      (11,1002008,'2008-03-31 02:42:04+01',43.64454,87.63572,-777)

因为我对旅行方式的统计很感兴趣（每种旅行方式的计数） latitude 41.00 - 42.00 以及 longitude 87.5 - 95.30 只是，我得和 trajectories 包含 lat lon 信息，匹配 timestamp 如果它和 start/end_timestamp 的 labels 该用户的表。
我该怎么做？我加了这把小提琴。

sql postgresql aggregate-functions gps

来源：https://stackoverflow.com/questions/62552092/how-to-get-the-travel-mode-when-the-geographical-coordinates-is-in-the-raw-gps-t

1条答案

按热度按时间

z9gpfhce1#

这是您描述的查询：

select l.travelmode, count(*)
from trajectories t join
     labels l
     on t.user_id = l.user_id and
        t.timestamp between l.start_timestamp and l.end_timestamp
where t.lat between 41.00 and 42.00 and
      t.lon between 87.5 and 95.30 
group by l.travelmode;

但是，对于示例数据，这不会返回任何行，因为时间戳在不同的年份中。

赞(0）回复(0）举报 2021-07-24

我来回答

当地理坐标在原始gps表中时如何获取旅行模式

1条答案

相关问题

热门标签

最新问答