Joining tables based on time and distance: PostGIS with PostgreSQL?

Question

I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small":

Small Table:
id  event_time                   group_id   item_id  position
1   '2018-06-21 18:35:01.631094+00' '123a'  1   '01010230...'
2   '2018-06-21 18:35:01.630881+00' '123a'  2   '01010044...'
3   '2018-06-18 10:35:01.630663+00' '321b'  1   '01015600...'
4   '2018-06-18 10:35:01.630305+00' '321b'  2   '01010031...'

I have another table (Table "Big") that has similar data columns (time, position, data1, data2 etc). The timestamps in this table continuous and are overlapping over the first table and there are 80+ million rows:

Big Table:
id  event_time                       Data1   position
1   '2018-06-21 18:45:01.631094+00' 'john'  '01013000...'
2   '2018-06-21 18:41:01.630881+00' 'joe'   '01016000...'
3   '2018-06-21 18:33:01.630663+00' 'john'  '01017000...'
4   '2018-06-21 18:30:01.630305+00' 'rory'  '01018000...'

I have geospatial and time indexes on the two tables.

What I would like to do is find the nearest matches between Big and Small and return data1, data2 and the differences in space and time. In short I'd like to find out that "john" is the best match to group '123a', item 2 and he was 100m and 2 minutes from it but 'rory' was closest to item 1 (5 minutes and 1 km or whatever).

I've tried a command similar to this but it is way too slow. It doesn't seem to be using the indexes.

SELECT 
  big.id, 
  small.id,
  st_distance(big.position, small.position) as pos_delta,
  (big.event_time, small.event_time) as time_delta,
  big.data1,
  small.item_id
FROM big, small 
WHERE
  (big.event_time - small.event_time) < '2 hours'
ORDER BY login_sar_vessel.position <-> login_pos_report.position
LIMIT 1

Is there a way to possible first select data from big table for 2 hours around the timestamp associated with the group_id, find the big rows with the smallest (big.position to small.position) distance and then repeat for each group_id? That seems a bit messy.

Oh, and the DB is postgres 9.6 with postgis 2.4.

Answer 1

I think you want something like this,

SELECT
  big.id,
  small.id,
  ST_Distance(big.geom, small.geom),
  big.event_time <-> small.event_time
FROM small
CROSS JOIN LATERAL (
  SELECT *
  FROM big
  WHERE (small.event_time <-> big.event_time) < '2 hours'::interval
  ORDER BY small.geom <-> big.geom
  OFFSET 0
  LIMIT 1
) AS big;

You can put both of these on both timestmap and your geom for extra joy using btree_gist

CREATE EXTENSION btree_gist;

CREATE INDEX ON big USING gist (event_time, geom);
CREATE INDEX ON small USING gist (event_time, geom);
VACUUM FULL ANALYZE big;
VACUUM FULL ANALYZE small;

Note: I would upgrade to PostgreSQL 10 so you'd have parallel stuff, as it'll make a major difference for this work load.

You may also consider BRIN indexes on the timestamp, and clustering by timestamp. And after that's done going to GIS. Or even creating a new indexed MATERIALIZED VIEW with only the last week of data or whatever.

Answer 2

Here is the option that I ended up using. It uses a couple common table expressions to cut the data down to the minimum amount needed. The idea being to fetch some "Big" data from before and some from after the "Small" timestamp. Then to sort and find the closest "Big" point, in time and space, to the "Small" point. It needs some work:

--Get Small info
WITH small_cut AS
(SELECT * FROM
small 
WHERE
group_id = 'abc123'
),

-- Get BIG info from before Small event 
big_before as
(SELECT DISTINCT ON (Data1)
big.data1
big.position,
big.event_time, 
(small_cut.event_time - big.event_time) as time_delta,
FROM
big,small_cut
WHERE
ST_Within(big.position, small_cut.bounding_geom)
AND
big.event_time > small_cut.event_timestamp - INTERVAL '1 Hours'  
AND
big.event_time < small_cut.event_timestamp
ORDER BY data1, big.event_time DESC
),

big_after as
(SELECT DISTINCT ON (Data1)
big.data1
big.position,
big.event_time, 
(small_cut.event_time - big.event_time) as time_delta,
FROM
big,small_cut
WHERE
ST_Within(big.position, small_cut.bounding_geom)
AND
big.event_time > small_cut.event_timestamp - INTERVAL '1 Hours'  
AND
big.event_time < small_cut.event_timestamp
ORDER BY data1, big.event_time DESC
)

SELECT DISTINCT ON (small.item_id)
* 
FROM Big, small_cut, big_before, big_after
WHERE
{some Id's are equal to other's}

I'm not sure how useful this is to other people since my DB structure has changed a bit and isn't 100% inline with the dummy data I've provided.