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

我对数据库管理系统还很陌生。我为每个用户在不同的csv文件中都有车辆的痕迹。格式：名称、时间戳、纬度、经度、随机ID。
例如：USER02008-10-2309:42:25441972.6942174428508.51172704942289
1）如何实现距离查询，要求在时间戳（t1）和时间戳（t2）之间看到的所有车辆的所有gps点在范围内（中心=纬度，经度；半径=r km）。
因为我在所有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）复制和构建数据库的最佳方法是什么，以便我的范围查询（如上所定义）高效地从数十亿个跟踪返回数据。
至少基本实现，这也行。
因为我是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：
这个问题有点小变化。我像使用python脚本一样将经纬度更改为UTM。

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

现在我有了这样的位置值（441972.6942174428508.5117），用UTM表示。
1）PostgreSQL表中UMT位置（米）的数据类型应该是什么？
2）TIMESTAMPTZ在我的postgresql版本中不可用。那么这种格式的正确数据类型应该是什么
2008-10-2309:42:25
.

最佳答案

如果有数十亿行，请使用table inheritance来加快查询性能和数据加载过程。
如注释中所述，首先将输入数据拆分为较小的数据集。
首先创建一个父表，然后再创建这么多子表。在示例中，我使用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)
);

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

-- 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。也就是说，在Bejiing中，您将使用srid32650

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

现在数据库中有数据，可以请求数据。
示例查询
在这个示例查询中，我请求坐标116.320157999940.004775000971（中国北京）周围100米半径内以及时间戳2016-01-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坐标，只需使用ST_SetSrid()并且不要强制转换到地理位置。

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

为什么继承？
主要是因为更好的表现。如果您有数百万行，那么使用继承时查询将更快，因为您将在单个表中存储十亿行。
您可以查询父表，并从所有子表（根据WHERE子句）返回结果。
你不需要知道你的数据在哪个子表中。表继承将为您执行此操作。（有关更多信息，请参见inheritance）
重要信息Postgis中的坐标是经度/纬度，也是x/y。在google地图和大多数地图web api中，坐标的表示顺序是相反的：经度/纬度（y/x）。使用正确的顺序！