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.

3D points

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.


I'm not entirely sure what difference the units make - I assume the author of that page is suggesting that you "fake" the z dimension conventionally used for height with another unit, such as time. I don't think the units are important as you would just use substitute metres for seconds or minutes or unix time or whatever interval you like. Yes you may have to recode your data slightly to take advantage of it, but not if it is already in an integer format.

I think the question is a little bit broad as it is - have you tried implementing the 3d index and timing it as I can't see any barriers to doing that based on your use case? Your time field is already in an integer format, so can be indexed easily, and you have linked to an article that tells you both how to create the index and utilise it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.