title: Record clustering based on Place and Time in SQL
body: <p>I would like to know if any of you guys have written a query for record clustering based on overlapping time intervals AND locations.
  Data in my application is represented as individual events of a person being at any given location from start time to end time. Location is defined as latitude and longitude. During a day one person will have multiple different locations and start and end times. I need to get groups of persons who were at the same location and at the same time. One person will most likely be in several groups during a day.</p>
  
  <pre>
  <b>Example</b>:
  Person A can be with Person B at the office from 10 AM to 11 AM.
  Then Person A leaves the office for gym.
  There he is with Person C from 12 noon to 1PM.
  At 12:30 Person C leaves gym for the office.
  At 1:30PM I have Person B and C at the office.
  Persons B and C leave the office at 5PM.
  </pre>
  
  <p>In this example I have</p>
  
  <ul>
  <li>Cluster 1 (Person A and B at the office) from 10AM to 11AM,</li>
  <li>Cluster 2 (Person A and C at the gym) from 12 noon to 1PM, and</li>
  <li>Cluster 3 (Person B and C at the office) from 1:30PM to 5PM.</li>
  </ul>
  
  <p>The Location of each individual person will not match exactly to another person's location. I'm using SQL geography point type with the STBuffer of proximity threshold and check for STIntersects. I'm also joining the table on itself to check time overlaps. But i'm experiencing some weird behaviors when Person A gets clustered on itself without other person ever joining him.</p>
  
  <p>I'm wondering if there's a design pattern for handling situations like this. Ideally i would have the recordset grouped on "Overlapping Time Period" and "Centroid of an arbitrary geometry" but can't figure out how to get the overlapping time period and the arbitrary geometry.</p>
  
  <p>Any ideas are welcome and highly appreciated.</p>
  
  <p>P.S. writing a windows application is not an option unless it's the only way.</p>
  
  <p>EDIT: Failed to mention that locations of clustering is never known in advance. There can be indefinite number of locations where two or more of my customers may cluster. I don't know if clustering will happen in the office, gym, some park or at a bus station. Clustering location (i think ) will be the Centroid of a polygon represented by all congregated people's Latitudes and Longitudes.</p>