Структура базы данных для маршрутной энциклопедии

Сочинение на тему «какую форму хранения записей я бы выбрал для энциклопедии маршрутов городского/пригородного транспорта» в вольном стиле.

Больше всего при написании этого текста я руководствовался остатками познаний из области программирования, попутно переосмысливая для себя понятие «нормальная форма записи в базе данных».

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

2. Маршрутная энциклопедия должна быть, по сути, какой-то СУБД. Значит, все маршруты нужно заносить в базу данных. Нужно определиться с формой записи в такой базе данных, которую можно будет легко обрабатывать, и желательно, чтобы если что-то поменяется в СУБД, старой базой можно было бы продолжать пользоваться. Надо, чтобы в записи было поменьше ненужного и побольше нужного.

3. Что нам нужно в записи? По сути, нам нужны id, трассы маршрута и метаданные.
С id все вроде бы понятно: идентификатор себе, и ладно. Трасса маршрута представляет собой набор географических точек, которые %автобус% проезжает в последовательном порядке с пассажирами, причем часть из этих точек есть остановки (посадка-высадка). Каждая трасса начинается с начальной остановки и заканчивается конечной. Даже если маршрут кольцевой, можно найти какое-то место, где трасса условно начинается (откуда, скажем, обычно начинается выпуск машин на маршрут). Получается одна ломаная линия, которая идет от точки начальной остановки до точки конечной остановки (с направлением). Все остальное трассой не является. Также трассой не являются развороты транспорта (если перед этим на конечной всех пассажиров выгоняют. А если не выгоняют – то, в принципе, можно и считать разворот частью трассы. Но, все равно — «конечная» до разворота и «начальная» после — должны быть, и в реальной жизни есть, хотя бы формально).

Метаданные бывают такие, которые касаются одного маршрута во всех его вариациях, которые не зависят от того, по какой трассе поедет любой отдельно взятый %автобус% с таким номером. (Ярким представителем такого типа мета-информации является собственно номер маршрута). Есть метаданные, которые касаются только конкретной трассы (они в основном описывают время-стоимость-пространственные параметры поездки). Значит, их нужно показывать вместе с соответствующей трассой, а не относить к метаданным предыдущего типа.
Если так смотреть, структура БД вырисовывается такой: id; метаданные не зависимые от трассы; несколько наборов трасс с метаданными, которые касаются только их.

3+. Небольшая деталь. Статистическое большинство маршрутов, рельсового ли, безрельсового – все-таки потребуют ровно две трассы на один id – «туда» и «обратно». Этот факт не должен заставлять нас каким-либо образом ограничиваться в выборе формы записи в базе данных, но его стоит учесть при разработке интерфейса и поиска. Например если пользователь запрашивает какую-либо, но конкретную вариацию маршрута, чаще всего ему удобнее сразу видеть и прямую, и обратную трассу, тем более, что это визуально не захламляет карту.
(Если же отрисовать на карте все трассы маршрута сразу, пусть даже только в один конец – это захламляет карту многократно наведенными линиями и отвлекает внимание.)

3++. Бывают случаи маршрутов-полиморфов, когда, к примеру, каждый второй отправляющийся %автобус% имеет другую трассу маршрута (напривер, заезжает к какому-то массиву, или станции). Есть такой маршрут 365 в Москве – сам не был, но мне рассказали: хотя он и считается одним маршрутом, по сути под этим номером скрываются четыре трассы, пусть и с одинаковыми начальным и конечным пунктом. И наверняка о следовании по конкретному из них всегда предупреждает табличка на лобовом стекле автобуса. По сути, выполняются четыре разных маршрута: «365», «365 с заездом в А», «365 через В», «365 через А и В». Так как маршрута, по сути, четыре разных (и при работе поиска из окрестностей начальной остановки в пункт А должны выпасть только два из четырех результатов) – соответственно я предлагаю и хранить их под четырьмя разными id, тем более, что таких маршрутов подавляющее меньшинство. Опять же, юзабилити страдает не сильно, т.к. мы легко и непринужденно можем при выводе данных пользователю вывести ему и что-то вроде списка «похожих маршрутов».

4. Теперь, когда модель хранения маршрутов выбрана, можно подумать о мета-данных. Кроме того, что часть из них зависит от конкретной трассы, а часть – только от конкретного маршрута, их можно разбить еще на две группы – те, которые требуют ручного заполнения и те, которые (с целью хранения всех данных в стандартизированом, одинаковом виде) пользователям лучше не давать. Например — все географические данные (страна-субъект федерации-область-город) следует хранить в стандартном виде, иначе потом не получится быстро и гладко прикрутить тэги (подробнее в пункте 10). Удобно взять информацию в том виде, в каком ее отдает, для примера, google maps API. (Есть альтернативы вроде GEOS, выбрали гуглокарты только для примера).
Дальше я везде под словом «город» буду иметь в виду заодно и полный набор мета-категорий о нем — страна-округ-область-город.

5. Не стоит принудительно записывать маршрут в какой-то один город из каталога, иначе пригородные маршруты ставят очень много вопросов. Еще больше вопросов ставят маршруты между двумя городами-близнецами, вроде Днепропетровска и Днепродзержинска (только еще ближе). Но привязать маршруты хоть к чему-то нужно, иначе мы рискуем по запросу «трамвай тридцатый номер» получить трамвай 30 из Мск, Питера, Киева – и Хельсинки. Бывает и еще хуже: в пределах той же Москвы можно найти автобус № 1 из Зеленограда, автобус № 1 из Королёва и еще парочку первых автобусов! Нужно завести специальное поле для каждого id, которое бы подсказывало, откуда тот или иной автобус родом – что-то вроде «город, в котором назначен/утвержден маршрут» (полуавто – выбор из списка или клик по карте с обращением к google API). Образно говоря, поле можно назвать «порт приписки».

Чтобы мою мысль было проще понять, пример:
— в Нерезиновой есть маршруты с одинаковым номером: 1 (Королёв); 1c; 1 (Зеленоград); 1 (Химки). Для первого маршрута в это хитрое поле запишем «Королёв», для второго – «Москва», для 3го и 4го – «Зеленоград» и «Химки» соответственно. Когда пользователь будет искать дорогу из пункта А в пункт В, ему вместо ответа «автобус 1» мы подадим «автобус 1 (Королёв)», даже если А и В целиком и полностью находятся в Москве. Конечно, если точки «откуда» и «куда» поиска находятся обе в «городе приписки», то это поле можно и не выводить на экран. Кроме прочих применений, это поле – основа для каталогизации и средство борьбы с одинаковыми маршрутами в одном городе.

Еще пример: автобус 70 для города Севастополя, является пригородным, он проезжает немалую часть Севастополя, потом колесит по пригородам и оказывается в Форосе. Если кто-то будет искать маршрут в пределах Фороса, или из Фороса, и ему на выдачу попадет этот самый автобус номер 70, он увидит «70 (Севастополь)» и его полную трассу, что вряд ли помешает пользователю, а, скорее, даже поможет ему. (Утрирую: пользователь из Фороса может в один прекрасный момент получить выдачу из двух семидесятых маршрутов, и тут форма отображения «70 (Севастополь)»; «70 (Ялта)» его просто спасёт.)

6. Дальше я приведу список мета-данных, которые я счел нужными, и которые относятся ко всему маршруту сразу. Тут же и комментарии к ним.

Те поля, которые стоило бы заполнять с минимальной свободой пользователей, отмечу в скобках (авто) или (полуавто) – последнее предусматривает выбор из списка.

Метаданные, которые следует отнести ко всему маршруту сразу:
— «город приписки» — см. 5.
— вид транспорта (полуавто – выбор из списка) – один выпадающий список. Удобства пользователей ради можно разбить его на категории, к примеру: Рельсовый (метро, трамвай, городская электричка, фуникулёр, монорельс и прочая экзотика), Водный (паром или речной трамвай, кстати, привет от Севастополя), Наземный (автобус, троллейбус, маршрутное такси).
— номер маршрута – в принципе, это текстовое поле. Потому что: «422д»; автобус на время отмены трамвая «3-тр»; или даже «Аэроэкспресс» или «СкайБус». Но стоит ограничить его длину, или что-то вроде этого! Иначе базу данных (равно как и вывод маршрутов по отдельно взятому крупному городу) захламят длинные, ненумерованые названия пригородных и междугородных маршрутов («Орехово-Зуево — Спасокукоцко-Нарочненское»), и «каталог» маршрутов отдельно взятого мегаполиса нельзя будет смотреть без стона ужаса. И вообще, я лично придерживаюсь убеждения, что такие маршруты должны обслуживаться автовокзалами полностью, включая рисование их трасс на карте.
— перевозчик – по возможности полное (с формой собственности) название юр.лица-перевозчика. Кстати, тут использование тэгов тоже более чем уместно, вплоть до того, чтобы указывать не только тэг юр. лица перевозчика, но и АТП/автоколонны/депо, обслуживающей маршрут (актуально и очень интересно для мегаполисов с коммунальными транспортными предприятиями).
— стоимость проезда – стоимость маршрута часто фиксированная и не зависит от трассы следования – а только от перевозчика/номера маршрута. Но иногда бывает не так, например, когда обратный маршрут из-за, скажем, одностороннего движения на трассе, получается настолько длиннее, что частный перевозчик выставляет другую цену. Это редкость, так что стоимость – глобальное для маршрута поле. Автоподстановка валюты, основываясь на «городе приписки» – это, конечно, красиво. Но нужно предупредить пользователей, чтобы при создании маршрутов, у которых стоимость зависит от расстояния/зоны, они вписывали в поле минимум-максимум и/или оставляли комментарии об этом.
— общие особенности маршрута. – например, «маршрут обслуживается только новейшими автобусами МАЗ-107», или «на автобусах маршрута установлены турникеты».

7. Ненадолго оставим метаданные и займемся трассами маршрутов. Одна трасса, как мы уже определились, должна соответствовать одному рейсу %автобуса% в один конец. В принципе, точность, с которой нам отдает координаты веб-карта того же Гугля, когда мы ставим на нее точку, в нынешнее время вполне соизмерима с точностью позиционирования GPS (хоть и чуть хуже — она составляет единицы метров, и такую же примерно погрешность нам, увы, показывает совмещение карт из разных источников). Стоит пользоваться такими благами цивилизации и при рисовании нового маршрута – рисовать его с максимально доступной подробностью, со всеми тонкостями прохождения развязок и стороной улицы. Нет гигантской проблемы, если такой подробности не будет, но к ней стоит стремиться. Кроме важности этого для нормализации данных (нет ничего лучше, чем записать трассу без приколов, именно так, как она идет на самом деле) – самому же подчас интересно, где %автобус% поворачивает на этой развязке, и, соответственно, как попасть на его остановку! Кроме того, здорово успокаивает, попав в чужой город, ориентироваться хоть в чем-то досконально.

8. Согласно выбранной нами модели хранения данных, трассы маршрутов «туда» и «обратно» следует хранить недалеко друг от друга, хотя и обрабатывать как независимые объекты (трассы). Трассы маршрутов, которые претерпевают периодические (например, на выходные) изменения, а также трассы маршрутов, которые варьируются от рейса к рейсу (каждый первый автобус идет до А, а каждый второй – до Б), разносим в разные id. Это помогает упростить механизмы поиска и создания пар трасс «туда-обратно» для маршрутов. Опять же, можем поцепить на трассу какой-нибудь параметр вроде «кольцевой маршрут? Да/нет» или «маршрут в один конец?» и таким образом сообщить СУБД, что по этому id будем обрабатывать не пару трасс, а только одну.

9. Трасса маршрута – набор точек и прямых, которые их соединяют. Логичный и экономный способ хранения трасс — последовательностью точек. На каждой трассе есть как минимум две особые точки, они же — две остановки – начальная и конечная. Возможно, целесообразно хранить их названия, а заодно остальные остановки, вместе с трассой в списке координат. Тогда их легко обработать: если напротив пары координат стоит название, значит это – остановка, и ее нужно соответсвенно выделить при отрисовке, а при наведении курсора – показать ее название. Если же названия нет – это обычная точка пути, и выделять ее не нужно, а просто нарисовать через нее линии трассы. Дабы не мешать числа и текст, можно просто ставить какую-нибудь метку, например, порядковый номер остановки на трассе в метаданных. По крайней мере, делать остановкой такую же точку, из которых строится и весь маршрут, кажется мне очень логичным.

Можно развить идею такого хранения данных об точках пути и остановках: первой остановке можно присваивать номер 1, а последней – скажем, 0, или 9999. Например, маршрут 33 города Киев, Украина:

50.46611, 30.64351, 1 //это начальная остановка
50.46414, 30.63630, //это просто точка пути
50.46494, 30.63596, 2 //это остановка
50.46825, 30.63379, 3
50.47176, 30.63141,
{…} //еще n-цать точек пути и х остановок
50.48083, 30.62724, 
50.48251, 30.63317, 0 //это конечная

Это дает еще одно преимущество – по кнопке «Нарисовать обратный маршрут» можно сразу заполнить начальную (с тем же названием, что и бывшая конечная) и конечную (наоборот) остановки.

Функция для далекого будущего – учет мультимаршрутных остановок, когда одна остановка обслуживает несколько маршрутов. Вроде, кликнул на такую остановку – и всплывающая подсказка «тут останавливаются: …». Реализовать при рисовании, например, как «magnet» существующими остановками рисующейся трассы.

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

10. Теперь о геолокации и тегах городов. Допустим, пользователь маршрут успешно нарисовал и нажал кнопку «сохранить трассу маршрута». Теперь мы можем скормить нарисованные остановки Google maps API и проанализировать ответы на предмет принадлежности этих точек-остановок городам. Причем, даже если пользователь, создающий маршрут, за остановки еще не брался, две – начальная и конечная – уже по определению есть!

Исходя из ответа, автоматически создаем перекрестные ссылки маршрута на эти города (и если хоть один еще не существует – тут же создаем его в базе городов), а городов – на этот маршрут (так как он проходит и в этом городе!). Это и будет наше облако тэгов.

Тут же есть широкие возможности для «защиты от дурака» — если ни один из городов не оказался «городом приписки» из глобального для id# поля записи БД – скорее всего, сюда закралась ошибка.
Теперь «каталог маршрутов» для какого-то города покажет все маршруты, в которых встречается тэг с названием этого города.

11. Метаданные, которые следует отнести к конкретной трассе:
— облако тэгов городов, через которые следует маршрут – см. 10 и 3++ — почему именно к конкретной трассе/паре трасс.
— список названий остановок – в минимальном случае требуется заполнить хотя бы две. Поэтому такое заполнение должно производиться сразу при добавлении маршрута в базу. Первую и последнюю остановки можно средствами СУБД изымать каждый раз при обращении к трассе и отображать в необходимых местах, отдельно от полного списка или же с ним – или использовать при построении обратной трассы.
— интервал движения – простое текстовое поле для комментариев в вольной форме. Возможно, если когда-нибудь энциклопедия будет рассчитывать время поездки, да еще и правдоподобное, понадобится и это поле привести к стандартному виду для подсчета среднего времени ожидания – многие %автобусы% ходят в часы пик с совершенно другой регулярностью, нежели в иное время.
— длина трассы по карте (авторассчет).
— недельный график — согласно выбранной нами схеме, если маршрут меняет трассу движения, к примеру, на выходные, под него создается отдельный id. Поэтому при привязке к id конкретной трассы/пары трасс поле «недельный график» заполняется только тем временем, на которое %автобусы% маршрута едут именно по этой трассе/паре трасс!
— время работы – аналогично предыдущему пункту.
— интервал движения – аналогично предыдущим двум.
 

12. По сути БД, кажется, все. Теперь можно немножко времени посвятить вещам, которые я вроде бы трогать не собирался — интерфейсу добавления записи в базу.

p.s. Говорят, использование архитектуры «база данных без схемы» решает очень много проблем, так как любые нужные поля могут быть прикручены по мере необходимости их использования практически без побочных эффектов.

Автор: theodorthegreathe