MySQL spatial extensions un laika intervāli

Jau labu laiku MySQL atbalsta GIS telpisko paplašinājumu (spatial extension) [1]. Daudz vārdu pateikts un daudz šķēpu lauzts par to, cik labs vai slikts ir MySQL spatial extension [2] salīdzinājumā ar citām DBVS, piemēram, Postgres. Lai vai kā – šāds paplašinājums ir pieejams.

Lielos vilcienos Spatial Extensions ir datu tipu un funkciju klāsts, kas ļauj salīdzinoši ērtā formā (neizmantojot algebriskās funkcijas) darboties ar punktiem kartē, poligoniem, attālumiem un citiem lielumiem, kas nu ģeogrāfiskajā kartē būtu vajadzīgi. Pēc dokumentācijas [1] izriet, ka šo paplašinājumu var izmantot gan MyISAM, gan InnoDB datubāzēm ar vienu piebildi, ka MyISAM atbalsta arī Spatial indeksus, savukārt InnoDB – nē. Pārbaudījis neesmu, bet nav pamats neticēt :)

Lai arī telpiskais paplašinājums ir domāts ģeometriskiem datiem, tas ļoti ērti izmantojamas arī laika intervālu salīdzināšanai un citiem risinājumiem, kurus var projicēt uz ģeometriskām plaknēm.

Uzdevums:

Ir vairāki procesi, kuriem ir sākuma un beigu laiks. Un ir meklēšanas intervāls, kuram arī ir sākuma un beigu laiks. Uzdevums ir atrast tos procesus, kuri krustojas ar meklēšanas intervālu.
Ģeometriskā interpretācija:

Ņemot vērā, ka laiku var interpretēt kā veselu skaitli (unix_timestamp), līdz ar ko laika ritējums veido vērtības uz X ass. Savukārt Y ass ir procesi. Tā kā procesi veido projekcijas uz X ass, ir zināms sākuma un beigu punkts un šādu projekciju veido arī intervāls, tad uzdevums ir atrast šo nogriežņu pārklāšanos (intersect).

1. Tabula.

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

ar startDate un endDate viss skaidrs - datu tips Date. Nāk divas jaunas lietas: 1. datu tips – LINESTRING un spatiālā atslēga – SPATIAL KEY.

2. Tālāk veidojam „nogriežņus”. Tiesa gan Spatiālās funkcijas strādā tikai ar skaitļiem (ar datumiem nestrādā), tos nepieciešams konvertēt uz unix_timestamp.

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

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

CONCAT šeit tiek izmantots lai „salipinātu” teksta rindu, kas sastāv no teksta fragmentiem un tabulas laukiem (startDate, endDate). Vēršu uzmanību, ka viena no koordinātēm ir 0.

3. Vaicājums.

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

Attiecīgi skaitliskās vērtības ir nekas cits kā intervāla sākuma un beigu datumu timestamp vērtības.

Vaicājums atgriež sekojošus ierakstus: Process A, Process B un 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/