1

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes...

I know Postgres is brilliant for grouping points based on their spatial values - that I can do - but is there a way I can group points by their 'closeness' in time. By this I mean, how would I go about identifying points that were created within x minutes of the first point.

To put it into some context, the first point could be the first Facebook status update immediately after Arsenal scored against Aston Villa. There would be a sharp rise in status updates immediately after the goal before gradually trailing off.

So..If I had a dataset of 50,000 records representing Facebook status updates each with a random time and then I edited 1000 of these to be within 10 minutes of the first post, how would I best approach this and what would the SQL look like?

The time format is arranged as YY:MM:DD HH:MM:SS.

So far I have been able to return those records which are close spatially and have a similar message, but adding in the time element so I have results that are close spatially, temporally and share the same topic has proved a little more difficult for me.

I've looked at the interval function but I am unsure of how exactly to deploy this in my SQL. Would it be that first I filter records by their spatial dispersal, then filter which are close in time and finally the similarity of their status?

My SQL looks something like this for now..

Creating the table...

CREATE TABLE FB_status (
id character varying (254),
status character varying (254),
longitude numeric,
latitude numeric,
posted_at character varying (254),
geom geometry(POINT));

My SQL thus far...

SELECT (a.status, b.status) 
FROM FB_status AS a 
JOIN FB_status AS b 
ON ST_Dwithin (a.geom, b.geom, 0.01)
WHERE similarity (a.status, b.status) > 0.5;
  • 1
    Please add the CREATE TABLE FB_status statement in the question. – ypercubeᵀᴹ Jan 14 '16 at 11:29
1

posted_at should certainly be data type timestamptz, not varchar.
(And id should be int or bigint.)

Assuming timestamptz for posted_at and leaving further performance optimization aside, you could make it work range types and the range overlap operator &&:

SELECT (a.status, b.status) 
FROM   FB_status a 
JOIN   FB_status b ON ST_Dwithin (a.geom, b.geom, 0.01)
WHERE  similarity (a.status, b.status) > 0.5
AND   tstzrange(a.posted_at, a.posted_at + interval '10 min')
   && tstzrange(b.posted_at, b.posted_at + interval '10 min');

One might argue it should really be:

   tstzrange(a.posted_at - interval '5 min', a.posted_at + interval '5 min')
&& tstzrange(b.posted_at - interval '5 min', b.posted_at + interval '5 min')

But the result is the same for a self-join.

In reality, you probably need to optimize performance with queries on top of (functional) GiST indexes using LATERAL joins ...

I would consider operating with a spacial or temporal raster (granulation) instead to largely simplify things. Using generate_series() and / or width_bucket().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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