Как лучше хранить список GPS-точек в PostgreSQL 9.6 с расширением postgis?

Question

[SteepZero]

Реализуем GPS-трекинг
Думаем как хранить маршруты - набор точек
Искать по точкам не планируется, нужно только выводить их и строить кривую в определенный момент

Разошлись во мнениях с коллегой:
1. Я предлагаю хранить каждую точку отдельной записью

2. Он предлагает хранить точки массивом (или в json-формате) в одной строке. Т.е. маршрут - одна строка в базе со всеми точками
Коллега аргументирует выбор такого формата тем, что если хранить точки в отдельных записях, база вырастет очень быстро и в дальнейшем при выборке нескольких тысяч точек из нескольких миллионов записей будет занимать много времени и ударит по производительности.

У меня вообще нет никаких аргументов в защиту своего варианта кроме принципов нормализации =)

Расскажите, пжлст, какой бы вариант выбрали вы и почему

Comments

[freeExec]

А как ваш коллега собрался из json трека выбирать несколько точек?

[SteepZero]

На самом деле нам пока и не нужно выбирать часть точек маршрута (в рамках текущего ТЗ)
И пока сложно представить понадобится ли нам отдельная сущность "точка"
Поэтому вариант коллеги на первый взгляд кажется обоснованным

[Сергей П]

SteepZero, Так как в итоге сделали-то? Понадобилось в результате то, чего не сделали?=)

Answer 1

[x67]

Вы упомянули постгис, соответственно нужно использовать его формат geom через st_makeline(). Ну или geojson в постгресовских jsonb полях.
Это оптимальные варианты. Второй предпочтительнее для быстрой отдачи клиентам, а первый для фильтрации или обработки в постгисе. Так как истинный геоджсон может содержать несколько геометрий и надо писать кастомную функцию для преобразования в постгисовскую геометрию, я бы хранил и обрабатывал в постгисе, а в геоджсон или другой нужный формат преобразовывал бы опосля

Хранить точки отдельно - дрочево при достаточно большом количестве точек. Да и сами точки без трека никакой полезной информации не несут ведь.

Answer 2

[Алексей Черемисин]

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

Comments

[Сергей П]

А если захочется чтобы точки как будто на волнах покачивались и кружились как снежинки?
Сарказм-сарказмом, но подход спорный.
Мало ли что захочется. Когда захочется, станет ясно чего хочется. Кстати интерсекция по терками в случае миллиардов точек - это довольно неприятная задача. Точки разных треков по одной и той же дороге всё же разные геометрически.
Пересекать треки в виде поли-линий куда проще постгисовскими методами, к тому же предварительно можно быстро проверить баунд-боксами могут треки пересечься или нет.

[Алексей Черемисин]

Сергей Паньков, у меня база из нескольких миллионов полигонов хранится в r-tree структурах. Для r-tree работают те же самые функции поиска и нахождения, перебирать по каждому массиву точек не нужно. Сравниваются bbox в виде структор r-tree. Все очень быстро работает. Правда у меня не постгрес, но постгрес умеет в r-tree точно.

Answer 3

[Виталий]

имхо виф рекурсив в купе со связкой ид, кей, парент_ид, маршрут_ид куда более удобны в обслуживании...

Answer 4

[Сергей П]

Некропостит.
Ваш вопрос довольно хорошо гуглится, так что добавлю свой вариант ответа.
Ключевой момент в том, что вы делаете трекинг. Для предметной области важны следующие сущности:

• Сессия - относительно непрерывный (без больших пропусков) участок траектории движения: границы сессии во времени, границы в пространстве, длительность сессии, пробег сессии, время простоя, время в движении, средняя скорость, максимальная скорость, медианная скорость.
  
• Точка трека - сообщение от GPS-трекера:
  
  • время,
    
  • координаты,
    
  • скорость,
    
  • уровень сигналов GPS и GSM,
    
  • уровень топлива,
    
  • датчики дверей,
    
  • датчики работы двигателя,
    
  • текущая точность GPS,
    
  • и т.д.
    
  
  
• Маршрут - информация о траектории без детализации профиля скорости. Длина, мат-ожидания времени пути для разных ТС...
  
• Точка маршрута.
  

Очевидно, что ввиду всего этого у вас никак не получится хранить точки треков в поли-линиях постгиса.
Зато можно кэшировать их агрегированную геометрию в этом формате для скорости и простоты работы. Но это самое кэширование имеет смысл только если вам эта оптимизация реально нужна (медленно отдаются треки, медленно и сложно джойнятся сессии...). Зачем делать то. что можно было бы не делать?
А на счет размера БД я бы на вашем месте и месте вашего коллеги вовсе бы не беспокоился. Сейчас хранилища дёшевы и масштабируемы, никогда не поздно чистить старые ненужные данные, Данные у вас отлично индексируются и фильтруются по времени, можно дампы старых данных сливать в холодное хранилище, вдруг приспичит когда-то сделать инфографику или болшой анализ.
Я бы пожалел отказываться от детальной инфы по точкам трека в пользу какой-то сомнительной преждевременной оптимизации.