Базы данных для автотрекера

#база_данных #проектирование #mysql

Необходимо спроектировать базу данных для системы слежения за автомобилями. На автомобиле
стоит устройство слежения, которое отправляет свои координаты на сервер раз в N секунд.
 Работаю с MySQL 5ой версии. Опуская ненужные подробности, в моей голове родилось два
варианта:



Для хранения координат использовать одну таблицу с колонками:
[ID устройства(primary key)], [Долгота], [Широта], [Дата_Время]
Получается, что в одной таблице хранятся все треки для всех устройств.



Использовать отдельную таблицу для каждого трекаустройства и хранить в базе таблицу:
[ID устройства(primary key)], [Имя таблицы трека]
Запросы получаются сложнее, но каждый трек лежит в БД отдельно.


Внимание вопрос: какой вариант использовать лучше и почему? Или оба варианта не тру
и есть вариант номер три?    

Ответы

Ответ 1

Две таблицы. Первая - последние N точек для активных в данный момент устройств, где
N - достаточное число для отображения логического маршрута(для такси - один заказ).
Вторая - все точки для всех устройств. При отключении устройства оно пропадает из первой
таблицы.
Для каждого устройства создавать таблицу не надо.


Ответ 2

Отдельные таблицы — ни в коем случае. Не нарушайте смысл SQL :)
Зачем вам отдельные таблицы для однотипных объектов?
Вот когда записей будут многие миллионы, можно будет подумать о шардинге и разбить
на несколько таблиц, чтобы разместить данные по разным серверам; а пока пусть все в
одной таблице хранится.
Далее, чтобы спроектировать таблицу, нужно представлять, какие запросы у вас к ней
будут. Тут вам лучше знать, но для начала понятно, что:

Отдельной сущностью, уникальной в задаче, является пара ( объект, время ). Не может
быть две записи в одно время для одного автомобиля. Это и будет primary key.
Для каждого объекта нам нужен список координат не абы как, а упорядоченный в хронологическом
порядке, и частенько не весь, а в интервале «от и до». Тут спасибо MySQL, индекс для
primary key покрывает эту потребность
Координаты нам, вероятно, понадобятся для запросов вида «в каком квадрате находятся
какие автомобили». Здесь уже точно нужно знать, какие именно запросы будут… Но пока,
раз MySQL предоставляет функции OpenGIS, то почему бы ими не воспользоваться, и не
набросить на координаты spatial индекс? (правда, для этого тип таблицы должен быть
только MyISAM).

Получается так:
CREATE TABLE tracks
(
  id        INTEGER     NOT NULL,
  tm        TIMESTAMP   NOT NULL,
  coords    POINT       NOT NULL,

  PRIMARY KEY (id, tm),
  SPATIAL INDEX(coords)
) ENGINE = MyISAM;

Данные в такую таблицу вставляются вот так:
INSERT INTO tracks VALUES
(1, '2011-09-02 12:00:00', POINT(50.456257,30.511237)),
(1, '2011-09-02 12:00:01', POINT(50.456230,30.511425)),
(1, '2011-09-02 12:00:02', POINT(50.456244,30.510902));

И затем с ними можно делать много вкусного, например, проверить, какие автомобили
находились в заданном квадрате в определенное время:
SELECT id
  FROM tracks
  WHERE tm < (NOW() - INTERVAL 5 MINUTE)
    AND MBRWithin(
        coords,
        GeomFromText('Polygon((50.456736 30.510540, 50.455690 30.510540, 50.455690
30.513295, 50.456736 30.513295, 50.456736 30.510540))')
        ) = 1;

Ну а там уже надо брать реальные запросы и эксплейнить. Кстати, устройство слежения
что, не отдает скорость и направление движения? Тоже стоит их в базу записывать, чтобы
потом можно было удалить левые точки, когда GPS лагает.


Ответ 3

Если вам только обновлять надо координаты и не надо вести историю для каждого, то
лучше пользоваться первым вариантом. Если нужна история, то вторым, но базу захламите
быстро, если устройств много. Вот от этого и пляшите.