Postgis数据库:如何获取指定时间戳和指定区域之间的所有gps点

Question

我对DBMS完全陌生。我在每个用户的不同csv文件中都有车辆的痕迹。格式：名称、时间戳、纬度、经度、随机ID。例如：user02008-10-2009:42:2541197.26942174428508.51172704942289

1) 如何实现RANGE查询，该查询要求在时间戳(t1)和t2之间看到的所有车辆在范围内的所有gps点(中心=纬度，经度；半径=r公里)。

因为我在所有csv中都有数十亿行。我创建了一个基本的表

CREATE TABLE userDataBase1
(
gid serial NOT NULL,
name character varying(50),
time_stamp TIMESTAMPTZ // postgresql doesn't have this datatype
latitude numeric(12,8),// Don't know the data type for UTM points
longitude numeric(12,8),
pseudonym integer,
the_geom geometry
);

我应该直接这样抄吗？

copy landmarks(name,time_stamp,landmark,latitude,longitude) FROM '/local/path/to/Individual_Landmarks.csv' DELIMITERS ',' CSV HEADER;

2) 复制和构建数据库的最佳方式是什么，以便我的RANGE Query(如上所述)高效地从数十亿个跟踪中返回数据。

Atleast Basic实现也很好。

由于我是DBMS的新手。用小片段进行解释真的很有帮助。非常感谢！附言：我使用的是postgre9.5，postgis 2.2，windows 10，pgAdmin III

仅供参考：我已经通过Python脚本成功连接到数据库。

import psycopg2
conn = psycopg2.connect(database="postgis_unistuttgart", user="postgres", password="vishnu", host="127.0.0.1", port="5432")
print "Opened database successfully"

第1版：这个问题有点小变化。我已经将纬度和经度更改为UTM，就像使用python脚本一样。

import utm
import os
def gpsToUtm(latDeg,lonDeg):
#print "gpsToUtm:",latDeg,lonDeg
lat,lon,zoneNo,Zoneletter = utm.from_latlon(latDeg, lonDeg)    
return lat,lon

例如：现在我在UTM中有这样的位置值(441972.6942174428508.5117)。

1) PostgreSQL表中UMT位置(米)的数据类型应该是什么？2) TIMESTAMPTZ在我的postgresql版本中不可用。那么这种格式的正确数据类型应该是什么

2008-10-2309:42:25

。

Answer 1

如果您有数十亿行，请使用表继承来加快查询性能和数据加载过程。

正如评论中所说，首先将输入数据拆分为较小的数据集。您首先创建一个父表，然后创建多个子表作为输入文件的方式。在示例中，我使用landmarks_child_1作为表名。其他表可以命名为landmarks_child_2、landmarks_child_3等

-- Create a parent table landmarks
CREATE TABLE landmarks (
id serial primary key,
name text,
time_stamp timestamp,
landmark text,
latitude double precision,
longitude double precision,
geom geometry(Point, 4326)
);

现在创建并填充子表landmarks_child_1。对所有其他子表重复此步骤。

-- Create and fill the child table landmarks_child_1
CREATE TABLE landmarks_child_1 () INHERITS (landmarks);
ALTER TABLE landmarks_child_1 ADD PRIMARY KEY (id);   
-- create index for better performance.
CREATE INDEX landmarks_child_1_gist_geom ON landmarks_child_1 USING GIST (geom);        
CREATE INDEX landmarks_child_1_timestamp_index ON landmarks_child_1 ( time_stamp)

-- copy data
copy landmarks_child_1(name,time_stamp,landmark,latitude,longitude) FROM '/local/path/to/Individual_Landmarks.csv' DELIMITERS ',' CSV HEADER;
-- create postgis geometries based on longitude and latitude
UPDATE landmarks_child_1 SET geom = St_SetSrid(ST_Point(longitude, latitude),4326);

如果您有UTM坐标而不是全球长/滞后，只需更改srid即可。也就是说，在北京，你会使用srid 32650

UPDATE landmarks_child_1 SET geom = St_SetSrid(ST_Point(longitude, latitude),32650);

现在，您的数据库中有数据，可以请求数据。

样本查询

在这个示例查询中，我请求坐标116.3201579999940.004775000971(中国北京)周围100米半径内以及时间戳2016-01-01 01:00:00和2016-01-01 02:00:00(一小时)之间的所有点。

SELECT * FROM landmarks 
WHERE ST_DWithin(geom::geography, ST_Point(116.32015799999, 40.004775000971)::geography, 100)
AND time_stamp BETWEEN '2016-01-01 01:00:00'::timestamp AND '2016-01-01 02:00:00'::timestamp;

如果您有UTM坐标，只需使用strongetRid()，不要转换为地理坐标。

...
WHERE ST_DWithin(geom, ST_SetSrid(ST_Point(441972.694217,4428508.5117),32650), 100)
...

为什么要继承

主要是因为性能更好。如果您有数百万行，那么使用继承查询将更快，因为您将在一个表中存储10亿行。您可以查询父表，并将从所有子表返回结果(根据WHERE子句)。

您不需要知道您的数据在哪个子表中。表继承将为您做到这一点。(更多信息：请参阅继承)

重要信息Postgis中的坐标是经度/纬度，也是x/y。在谷歌地图和大多数地图web api中，坐标以相反的顺序表示：纬度/经度(y/x)。使用正确的顺序！

Answer 2

考虑到您有数十亿行，您最好使用这样的表：

CREATE TABLE userDataBase1
(
gid serial NOT NULL,
name character varying(50),
pseudonym integer,
the_geom geography
);

请注意，lat/long可以从geographic列中获得，因此无需将它们再次作为常规列存储在表中。要加载该表，如注释中所建议的，您需要批量加载到一个表中，然后从该表加载到永久表中，如下所示：

CREATE TEMPORARY TABLE load_table
(
name character varying(50),
ts timestamptz,
latitude numeric(12,8),
longitude numeric(12,8)
);
copy load_table FROM '/path/to/file' WITH CSV;

然后，您可以使用查询将数据从"load_table"复制到永久表中，例如：

INSERT INTO userDataBase1 (name, ts, the_geom) SELECT name, ts, st_setsrid(st_makepoint(longitude, latitude),4326)::geography FROM load_table;

一旦加载了数据，就可以创建一个gist索引来快速查找：

CREATE INDEX userDataBase1_the_geom_idx ON userDataBase1 USING GIST (the_geom);

加载数据并建立索引后，您可以发出查询来提取您感兴趣的记录：

SELECT * FROM userDataBase1 WHERE ts BETWEEN _timestamp1_ and _timestamp2_ AND st_dwithin(st_setsrid(st_makepoint(_longitutde_, _latitude_), 4326)::geography, the_geom), _meters_);

请注意，这将使用几何索引，并且期望由于该索引，结果受到足够的约束，因此可以简单地扫描区域内的所有记录以查找符合时间戳标准的记录。如果查询的时间戳部分在表上具有高度选择性，则可以使用在时间戳列上创建标准btree索引

CREATE INDEX userDataBase1_ts_idx ON userDataBase1 (ts);

然后，PostgreSQL将根据表的统计分析和传递到查询中的特定值，选择使用哪一个索引(或者，可能同时使用位图索引扫描)。