MySql has native support for GIS spatial extensions [1]. There are many articles related to how good or bad is MySql spatial extensions [2] compared to other DBMS, like Postgres. Anyhow, such extension is available.
Spatial Extensions is a set of data types and functions, helps easily (by not using geometrical mathematics) work with points in the map, polygons and distances and other units can be found in geographical maps. According to documentation [1] Spatial Extensions can be used as well in MyISAM as in InnoDB with a one note – InnoDB does not support spatial indexes. I have not checked if something is changed in the recent versions of MySql.
Even if Spatial Extensions are designed to work with geometrical data, they can be easily used for time intervals and for other solutions, where you can project points on geometrical planes.
Task:
There are many processes where you can assign start and end time. And there are search intervals where you can also define start and end time. Task is find out only those processes which overlaps with search interval.
Geometrical interpretation:
It is know, that time can be represented as integer (using unix_timestamp). That means, time flow can be represented as a values on X axis/ On Y axis we can place processes. Actually, task is to find out those processes, which intersect with the search interval.
1st 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 - the data type Date.Comes two new things: 1. data type - LINESTRING and spatial key - Spatial KEY.
2. Next, let us create line segments. Please keep in mind, that spatial functions work only with integers (they do not work with dates), these values should be converted using unix_timestamp function.
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 to glow text fragment and table field values (startDate, endDate). One coordinate (Y) is 0.
3 Query.
SELECT * FROM events WHERE MBRIntersects(line, GeomFromText('LineString(1272661200 0, 1273438800 0)'))
Numeric values is nothing more than start and end timestamp values for line segment.
Query returns 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/
» Comments