Оптимальное решение для привязки координат к географическим объектам на MS SQL Server
 

Добрый день.

В организации проводятся некоторые измерения по различным районам. К данным по измерениям привязываются координаты с GPS. Данные агрегируются по долготам/широтам (с точностью до 4го знака) и храняться в таблицах на SQL Server. Далее для ведения статистики (отчетности) необходимо разбивать все позиции на различные группы (н-р, районы, округа, города, поселки, основные трассы). И тут встает вопрос, каким способом производить такое разбиение?

Изначально, когда разбивалось только на районы, алгоритм был такой:
в таблице на сервере создавались географические полигоны, и принадлежавшие полиганам позиции помечались идентификатором района.
Данных по измерениям очень большое количество, и поэтому такой способ занимал длительное время обработки.
Более того, так как сейчас необходимо разбиение на большее количество групп, очень сложно будет создать такие полигоны на сервере, и время обработки увеличится. (Изначально было всего 30-35 районов.)

На данный момент была мысль создать таблицу, содержащую весь список возможных координат (с шагом 0.0001) и привязать к каждой позиции информацию о принадлежности к группе. Записей в таблице получается около 1500000000, поэтому теперь сомневаюсь, что такой способ является "хорошим".

Подскажите, как реализовать такую задачу.
Буду рад любой информации.

Спасибо.

я немного далек от sql, но пять копеек вставлю (-
да, создать список, но к каждой позиции только данные измерений. Отдельно районы, округа и т.д. с указаниием диапазонов координат из таблицы

freese, спасибо.

Можно немного поподробнее. Не совсем понял, ваше представление - структуры всего этого.
Я думал что - то наподобие (см. вложение).

Ну и соответственно, ко всем необходимым таблицам по измерениям добавлять столбец PosId, которая берется из Position по совпадения Longitude, Latitude.

LilLoco, что за группы 1…N?

Iska, как бы уровни вложенности. Например: 1 группа- Москва, московская область; 2 группа - цао, вао, Ногинский район, Шатурский район; 3 группа - деревни, поселки; ну и так далее... То есть каждая группа будет отвечать за определенное разбиение на уровни.

LilLoco, с точки зрения теории это явно избыточно. Необходимо и достаточно держать в таблице только нижний уровень.

Мне не очень алгоритм понятен. Полигоны — это что суть, физически?

По всему, --- нечто подобное:
Полигонометрия

http://ru.wikipedia.org/wiki/Полигонометрия
ЗЫ Раньше узлами сетки служили тригопункты, а теперь ко всему можно привязаться...

Исправил...

географическая область. Н-р, Ногинский район.

А как тогда определять уровни вложенности? Н-р, г.Электросталь. Предположим, что это нижний уровень. Выше идут: Ногинский район, московская область, обслуживающее подразделение...

Я, наверное, перемудрил с вопросом. Имелось в виду, что набор записей с единым значением «Группа N» суть последовательность координат, дающих при последовательном соединении полигон, «накрывающий» замкнутую область, обозначающую физическую площадь «Группы N»? Я просто действительно пока не очень понимаю.

Нет, имелось ввиду Группа - это группы, на которые необходимо разбить снятые измерения, такие как подразделения, города, районы, деревни и т.д..

Для наглядности, прикладываю картинку.
Есть 4 группы - таблицы, на которые мы делим данные (Podrazd - подразделения, Oblast - области, Region - районы, City - города). Есть таблица Geo, которая содержит список всех возможных координат, где мы можем снимать измерения, с шагом 0.0001. Таких записей получается порядка 2 миллиардов.

Далее к таблицам измерений присоединять колонку PosId, которая будет идентификатором принадлежности измерения к "группе разбиения". Идея лезет в голову только такая пока что.

P.S. Чтобы немного уменьшить объем таблицы Geo, вложенность можно определять не в ней, а в таблицах-группах. Т.е. в таблице Oblast создать колонку Podrazd.podrazdid, в Region - Oblast.OblastId и так далее. Но это на данный момент не так сильно волнует. Основным вопросом для меня является правильно ли я мыслю, или этот вариант лучше вообще не использовать?

Спасибо.

LilLoco, скажи, а измерение до 4ого порядка, это сколько в метрах?

lxa85, я думаю порядка 4-5 метров. Это в московской области по широте.

Я думал о том, чтобы хранить координаты с шагом не 0.0001, а 0.001. Думаю погрешность будет невелика.

Отдельные таблицы, связанные отношениями один-ко-многим.

Не для того, чтобы уменьшить объём, а для того, чтобы нормализовать базу.

Iska, ну то есть, ход мыслей у меня более менее правильный?

LilLoco, в части проциированного мною в предыдущем посте — да. По остальному сказать не могу, поскольку для меня так и осталась непонятной предметная область целиком. В частности, непонятно Ваше решение с «создать таблицу, содержащую весь список возможных координат».

Расскажите об задаче наиболее подробным образом, чтобы было понятно человеку, впервые об этом узнавшем.

Другого на данный момент я не придумал. Поэтому прошу помощи.

Есть некая организация. Организация проводит тесты в различных местах, будь то города, села, деревни, шоссе, парки. Тесты проводятся как на автомобиле, так и пешком.

С оборудования снимаются файлы с данными, которые вносятся в базу данных SQL Server. По полученным данным, формируются таблицы (Измерения) для ведения статистики вида: id, longitude, latitude, столбцы с измерениями.
То есть, для каждой точки проезда/прохода, существует ряд измерений. Точка имеет свои координаты долготы/широты.

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

Для этого нам необходимо связать координаты из полученной таблицы (Измерения) с реальными географическими объектами.

Для этого и было предположение создать таблицу со всеми возможными координатами, в которой будет содержаться географическая информация по этим координатам. После чего сводить координаты из этой таблицы с координатами из таблицы (Измерения), тем самым получая измерения для определенных географических объектов.

Вот в принципе и вся задача.

Примерно ясно.

Понятно.

Откуда будут браться координаты реальных географических объектов? Какая-то база? Как именно они (координаты) из этой гипотетической базы определяют область объекта (накрытие)? Это как-то связано с упомянутыми полигонами?

Дополню Iska и разовью свою мысль. Допустим идет процесс пешей и автомобильной гамма съемки. Автомобиль, равно как и человек ходит по дорогам,иногд по лесам,полям,болотам. Вам доступны только его координаты,без описания окружения. Iska поднимает вопрос топопривязки полученных координат к реальной местности. С приборов можно получить трассу маршрута(удобно в плане дорог). Но для практического использования необходимо привязать полученные значения к карте. Возможно для вас целесообразние будет получить картографические сведения из специальных баз(если разработка официальная и поддерживается руководством) возможно они подскажут свое API для работы с картами.

Изначально, когда было разделение на небольшие возможные регионы (лишь несколько районов московской области), использовалась таблица, в столбце которой хранились полигоны этих районов и использовался тип данных - geometry. Далее осуществлялась проверка вхождения точки из таблицы измерения в полигон. таким образом точки соотносились с географическими областями.

Планируется, сделать геокодирование по всем точкам при помощи яндекс-карт, google-maps, 2gis, mapinfo. Каким именно сервисом пользоваться еще не знаю. Но выбор будет среди них. Другого под рукой ничего нет.

lxa85, некоторые сами «рисуют» карты: Waze: нам по пути, или История маленького, но очень гордого стартапа.

Ну, таково было и моё видение. Вы от этого решили уйти? Почему?

Где можно почитать — что это за тип данных в Microsoft SQL Server?

Когда я разбирался, читал все на MSDN.

Проверка вхождения точки в полигон, созданный типом geometry Sql Server занимает довольно долгое время. Притом, что было всего порядка 25-30 таких полигонов. С учетом, того, что сейчас необходимы полигоны не только такого количества групп, а намного больше, я думаю, время обработки намного увеличится. Кроме того, для создания этих полигонов, необходимо знать краевые точки всех этих географических объектов. Что так же добавляет трудности в создании таблицы с географическими объектами.

Спасибо, ясно.

LilLoco, почему бы не делать привязку к нижнеуровневому объекту не в момент выборки, а в момент добавления строки с измерением в таблицу?

Процесс добавления - закрытый. Добавление происходит при помощи ПО, поставляемого вместе с измерительным комплексом. Получается, что при добавлении мы никак не можем повлиять на структуры таблиц.

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

В любом случае необходим источник этих объектов. И вот в каком формате целесообразнее хранить эти объекты, на данный момент, является для меня все еще загадкой.

Ясно. Я думал, всё сугубо Ваше.

А как насчёт делать привязку в момент пересчёта? Есть же технологические перерывы какие-нибудь?

Например: Измерения[Id Измерения, Координаты, Измерения, Принадлежность] ⇚ один ко многим → Объекты[Id, Наименование, Подчинение (ссылка на Id в этой же самой таблице)] ← один ко многим ⇛ Полигоны[Id, Id Объекта, координата точки полигона, порядковый номер точки].

Схема базы данных:



(вторая таблица «Объекты» на схеме — фиктивная, только для того, чтобы показать связь, ссылку с одной записи таблицы на другую запись той же таблицы).

Так вот, в этой схеме поле «Принадлежность» (к объекту нижнего уровня) можно заполнять позже.

Пожалуй, таблицу Полигоны также стоит разбить на две подтаблицы: собственно Объект[Id, Наименование (что-то я забыл про это на схеме), Подчинение] и ТочкиПолигона[Id точки, координата точки полигона, порядковый номер точки, Id Полигона].

Да такой вариант вполне уместен. Перерывов предостаточно, в разумной степени естественно.

Iska, Спасибо большое за разъяснения. Буду пробовать.

Спасибо всем за помощь в этом, нелегком для меня, деле.

Тему пока отмечу решенной

LilLoco, я сейчас ещё подумал (да, бывает и такое — я думаю ;)) — таблицу Полигоны, что на схеме, надо обязательно разбить на две таблицы: один объект может состоять из нескольких полигонов, тогда как раз логично держать отдельно сами полигоны (как сущность) и отдельно, так же — как сущность, точки конкретного полигона.