# 3D time dimension (x,y,t) [closed]

I'm working with data points (x,y) and timestamp t as a separate attribute in my table. (x, y) are in WGS 84 latitude,longitude format while timestamp is in Unix Time. For instance (71.8, -13.5) and 1420246483

I want to find if 2 points are close to each other both in terms of physical distance (meters) but also within a certain time. This is very simple using a query like

``````SELECT id, geom
FROM points p1, points p2
WHERE ST_DWITHIN(p1.geom::geography, p2.geom::geography, 1000)
AND (p2.timestamp > (p1.timestamp - 600) OR p2.timestamp < (p1.timestamp + 600))
``````

in which we transform our geometry to geography so that we can say 1000 is the distance in meters between the 2 points, and with attribute timestamp we could restrict to points that are max 10 minutes (600 seconds) away. The performance is not too bad but having millions of points it's not enough and I think it is possible to take advantage of PostGIS spatial indexes to achieve faster searches.

What I want to do now is to integrate time within the geometry points as (X,Y,time) to do a similar query which can improve performance. I read in the Boundless webpage about 3D/4D Index that with 3D index this is possible and particularly with time.

I quote: "Some of the fancy new 3D functions like ST_3DDistance can make use of the 3D index for fast searches in high dimensional spaces. I imagine folks working with XYT data may also find the new indexes useful".

I find hard to see how such implementation can be easy to use and useful. In fact functions such as ST_3DDistance or ST_3DDWithin only work with distances of units. This mean that meters and seconds can't go together so well, one would need to convert dimensions in some way to be able to write the query

``````SELECT id, geom
FROM points p1, points p2
WHERE ST_3DDWITHIN(p1.geom, p2.geom, #some_unit_number)
``````

I would like to know how experts in this wonderful community work with XYT data and find the new indexes useful as quoted above.

## closed as too broad by underdark♦May 22 '18 at 17:36

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.