MySQL Spatial Extensions and Time Intervals

MySQL has supported GIS spatial extensions [1] for a good while now. Much has been said and many lances broken about how good or bad MySQL spatial extensions [2] are compared to other DBMSs, for example Postgres. Be that as it may - such an extension is available.

In broad strokes, Spatial Extensions are a set of data types and functions that allow working with points on a map, polygons, distances and other quantities needed on a geographic map in a comparatively convenient form (without using algebraic functions). According to the documentation [1], this extension can be used with both MyISAM and InnoDB databases, with one caveat - MyISAM also supports Spatial indexes, whereas InnoDB does not. I haven't verified this, but there's no reason to disbelieve it :)

Although the spatial extension is designed for geometric data, it is also very conveniently usable for comparing time intervals and other solutions that can be projected onto geometric planes.

The task:

There are several processes, each with a start and end time. And there is a search interval, which also has a start and end time. The task is to find those processes that intersect with the search interval.
Geometric interpretation:

Given that time can be interpreted as an integer (unix_timestamp), the flow of time forms values on the X axis. The Y axis represents processes. Since processes form projections onto the X axis with known start and end points - and the interval forms such a projection too - the task is to find the overlap (intersect) of these line segments.

1. Table.

CREATE TABLE `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`startDate` date DEFAULT NULL,
`endDate` date DEFAULT NULL,
`line` linestring NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `line` (`line`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

startDate and endDate are straightforward - Date data type. Two new things appear: 1. the data type - LINESTRING, and the spatial key - SPATIAL KEY.

2. Next we create the "line segments". Note that Spatial functions only work with numbers (not with dates), so they need to be converted to unix_timestamp.

Syntax: GeomFromText('POINT(58.0093006 25.1778021)')

UPDATE events
SET line = GeomFromText(
  CONCAT('LineString(', unix_timestamp(startDate), ' 0, ', unix_timestamp(endDate), ' 0)')
)

CONCAT is used here to "glue together" a text string consisting of text fragments and table fields (startDate, endDate). Note that one of the coordinates is 0.

3. The query.

SELECT
*
FROM events
WHERE MBRIntersects(line, GeomFromText('LineString(1272661200 0, 1273438800 0)'))

The numeric values are simply the timestamp values of the interval's start and end dates.

The query returns the following records: Process A, Process B and Process C.

[1] http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html
[2] http://laacz.lv/2009/11/11/ipiki-un-citi/