Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm using postgres (postgis) and i want to perform a query that returns all rows which geometries intersect and have a timestamp within 1 hour from each other.

What would the SQL statement look like?

table looks like this:

id   { integer }    
name { character varying }
bounds { geometry }  
timestamp { timestamp with time zone }

// purposely left out bounds in the table example below (not necessary)

id   name       timestamp
---+----------+--------------------------------
1    one        "2010-09-24 21:10:39.515+00"
2    two        "2010-09-16 09:21:09.362+00"
3    three      "2010-07-08 00:00:46.549+00"
...

EDIT #1

Not including the intersect matching here is an example: for every row give me any other rows that are within 1 hour:

select * from myTable order by t

 id |               t               
----+-------------------------------
  9 | 2011-07-15 18:20:20.05+02
 10 | 2011-07-15 19:05:00.05+02
 11 | 2011-07-15 19:40:20.05+02
 13 | 2011-07-15 20:31:01.05+02
 14 | 2011-07-15 20:35:11.05+02
(5 rows)

result of needed query:

 id |  matchid |  origTime                  | matchTime
----+----------+----------------------------+------------------------------
  9 |   10     | 2011-07-15 18:20:20.05+02  | 2011-07-15 19:05:00.05+02
 10 |    9     | 2011-07-15 19:05:00.05+02  | 2011-07-15 18:20:20.05+02
 10 |   11     | 2011-07-15 19:05:00.05+02  | 2011-07-15 19:40:20.05+02
 11 |   10     | 2011-07-15 19:40:20.05+02  | 2011-07-15 19:05:00.05+02
 11 |   13     | 2011-07-15 19:40:20.05+02  | 2011-07-15 20:31:01.05+02
 11 |   14     | 2011-07-15 19:40:20.05+02  | 2011-07-15 20:35:11.05+02
 13 |   11     | 2011-07-15 20:31:01.05+02  | 2011-07-15 19:40:20.05+02
 13 |   14     | 2011-07-15 20:31:01.05+02  | 2011-07-15 20:35:11.05+02
 14 |   11     | 2011-07-15 20:35:11.05+02  | 2011-07-15 19:40:20.05+02
 14 |   13     | 2011-07-15 20:35:11.05+02  | 2011-07-15 20:31:01.05+02
(10 rows)
share|improve this question

1 Answer 1

up vote 3 down vote accepted

What about:

select
  m1.id as id,
  m2.id as match_id,
  m1.startDate as origTime,
  m2.startDate as matchTime
from
  myTable m1 inner join
  myTable m2 on st_intersects(m1.geom, m2.geom)
  and (m1.startDate - m2.startDate <= interval '1 hour'
       or -(m1.startDate - m2.startDate) <= interval '1 hour')
  and m1.id != m2.id
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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