Продолжаем соединять точки в контур

В предыдущем посте мы рассмотрели задачу превращения последовательности точек в LineString, а замкнутой ломаной в Polygon на основе WKT/WKB-представлений геометрических величин. Здесь мы рассмотрим решение той же задачи с использованием еще одного способа представления - GML.

Пусть имеем прежнюю таблицу точек #points (см. Скрипт 1 предыдущего поста)

 

select id, p.ToString() from #points

-------------------------------------------------------------------------------------

1 POINT (0 0)

2 POINT (1 1)

3 POINT (1 -1)

4 POINT (-1 -1)

5 POINT (-1 1)

Скрипт 1

 

из которых требуетсяполучить LINESTRING(0 0, 1 1, 1 -1, -1 -1, -1 1, 0 0). Наряду с текстовым (WKT) и бинарным (WKB)-представлениями для выражения геопространственных значений существует XML-вариант под названием GML (Geography Markup Language). Подобно методам STAsText() или AsTextZM(), он же ToString() в диалекте SQL Server, STGeomFromText() c его частностями STPointFromText(), STLineFromText() и др.; STAsBinary(), STGeomFromWKB() и его частностям; существуют методы для конвертации в/из GML: AsGml() / GeomFromGML().

Построение ломаной из таблицы точек с помощью GML

Рассмотрим GML-представление вышепомянутой ломаной:

 

declare @l geometry = geometry::STGeomFromText('LINESTRING(0 0, 1 1, 1 -1, -1 -1, -1 1)', 0)

select @l.AsGml()

-------------------------------------------------------------------------------------

<LineString xmlns="http://www.opengis.net/gml">

  <posList>0 0 1 1 1 -1 -1 -1 -1 1</posList>

</LineString>

Скрипт 2

 

Совершенно очевидно, как из таблицы Скрипт 1 получить XML Скрипт 2. Превращаем в каждой записи координаты точки в строку:

 

select id, p.ToString(), Format(p.STX, 'N8') + ' ' + Format(p.STY, 'N8') from #points

 

Рис.1

 

Складываем строки позаписьно

 

declare @s nvarchar(max) = (select ',' + Format(p.STX, 'N8') + ' ' + Format(p.STY, 'N8') from #points order by id for xml path(''))

select @s

 

Рис.2

 

И делаем требуемый XML по образу Скрипт 2:

 

declare @s nvarchar(max) = (select ' ' + Format(p.STX, 'N8') + ' ' + Format(p.STY, 'N8') from #points order by id for xml path(''))

declare @x xml = '<LineString xmlns="http://www.opengis.net/gml">

                   <posList>' + stuff(@s, 1, 1, '') + '</posList>

                 </LineString>'

select @x

 

Рис.3

 

который легким движением руки превращается в геометрию:

 

select geometry::GeomFromGml(@x, 0).ToString()

 

Рис.4

 

Построение многоугольника из таблицы точек с помощью GML

Совершенно аналогично вместо ломаной получить многоугольник, ограниченный создаваемым ею контуром. Посмотрим, как он выглядит в GML-представлении:

 

declare @l geometry = geometry::STGeomFromText('POLYGON((0 0, 1 1, 1 -1, -1 -1, -1 1, 0 0))', 0)

select @l.AsGml()

-------------------------------------------------------------------------------------

<Polygon xmlns="http://www.opengis.net/gml">

  <exterior>

    <LinearRing>

      <posList>0 0 1 1 1 -1 -1 -1 -1 1 0 0</posList>

    </LinearRing>

  </exterior>

</Polygon>

Скрипт 3

 

и создадим из таблицы Скрипт 1 такой же. Кроме тэгов необходимо иметь в виду еще одно изменение. Контур многоугольника должен быть замкнут, т.е. заканчиваться той же точкой, что и начинался. Отсюда set @s +=… Остальное понятно.

 

declare @s nvarchar(max) = (select Format(p.STX, 'N8') + ' ' + Format(p.STY, 'N8') + ' ' from #points order by id for xml path(''))

set @s += (select top 1 Format(p.STX, 'N8') + ' ' + Format(p.STY, 'N8') from #points order by id)

declare @x xml = '<Polygon xmlns="http://www.opengis.net/gml">

                    <exterior>

                      <LinearRing>

                        <posList>' + @s + '</posList>

                      </LinearRing>

                    </exterior>

                  </Polygon>'

select geometry::GeomFromGml(@x, 0).ToString()

 

Рис.5

 

Вариации способа

Получить начальную точку не запросом, а подстрокой

Можно взять начальную точку в конец не запросом из таблицы точек #points, а подстрокой из почти сформированной строки @s. Начальной точкой в ней будет все до второго пробела:

 

declare @s nvarchar(max) = (select Format(p.STX, 'N8') + ' ' + Format(p.STY, 'N8') + ' ' from #points order by id for xml path(''))

set @s += left(@s, charindex(' ', @s, charindex(' ', @s) + 1) - 1) --первая точка = левая часть до 2-го пробела

select @s

 

Рис.6

 

Получение многоугольника из замкнутой ломаной

Можно также взять готовый LineString (Рис.4) и превратить ее в Polygon, чтобы GML стал выглядеть, как в Скрипт 3. Жалко, в T-SQL нет возможности переименовывать узлы, только replace value of. Из-за этого XML проще построить заново, чем видоизменять структуру существующего.

 

declare @l geometry = geometry::STGeomFromText('LINESTRING(0 0, 1 1, 1 -1, -1 -1, -1 1)', 0)

declare @s varchar(max) = @l.AsGml().value('declare default element namespace "http://www.opengis.net/gml";

                                    (LineString/posList)[1]', 'varchar(max)') + ' ' +

                           @l.STPointN(1).AsGml().value('declare default element namespace "http://www.opengis.net/gml";

                                    (Point/pos)[1]', 'varchar(50)')

select geometry::GeomFromGml('<Polygon xmlns="http://www.opengis.net/gml">

                                <exterior>

                                   <LinearRing>

                                     <posList>' + @s + '</posList>

    </LinearRing>

                                </exterior>

                              </Polygon>', 0).ToString()

 

Рис.7

 

Здесь во втором операторе скрипта из LineString XML-запросом выбирается строчка с координатами и через пробел к ней добавляются координаты первой точки (STPointN(1)), которые тоже получаются XML-запросом (value()) из ее GML-представления (AsGml()). Затем строка с координатами вставляется в GML-описание многоугольника аналогично Рис.5, из которого методом GeomFromGml создается, собственно, геометрический объект.

Продолжение следует.

 

 

Алексей Шуленин

Comments

• Anonymous
  January 01, 2003
  msdn.microsoft.com/.../bb964712.aspx

• Anonymous
  January 01, 2003
  О, вот это уже интересно. Теперь уже можно всерьез думать о внедрении, а не только фантазировать :)

• Anonymous
  January 01, 2003
  Это был ответ на "кто что <...> планирует задействовать в составе своих решений". Уточню задачу. Дано два множества. Первой - набор прямоугольников, количество элементов - порядка сотен тысяч. Второе - набор полигонов, их - десятки. Задача (из области CCTV систем): найти процент геометрического пересечения каждого элемента из первого множества (прямоугольники) с какими-то элементами из второго (полигоны). Существующее решение реализовано на С++ на стороне клиента, а данные о геометрических фигурах хранятся в БД в виде BLOB данных. Это не гибкое решение, очевидно. Получается "толстый" клиент и плохая переносимость. После прочтения статьи хочется попробовать на SQL, но не ясно все ли там есть для решения задачи полностью на SQL. Кстати, ещё вопрос - все ли редакции SQL Server поддерживают эту функциональность или нужно будет поставлять только какой-нибудь SQL Server 2008 R2 Fast Track Data Warehouse?

• Anonymous
  January 01, 2003
  Представим, что у Вас есть две таблицы - с прямоугольниками и с полигонами (хотя прямоугольник тоже полигон). Насколько я понял, они абсолютно независимы. То есть мы устраиваем cross join множества прямоугольников на полигоны и STIntersection() для каждой пары. У кого-то оно пустое, у кого-то нет. Что делаем дальше? Что такое "процент геометрического пересечения"? Геопространственные расширения поддерживаются во всех редакциях, включая бесплатный Express.

• Anonymous
  January 01, 2003
  Так это нужно у вас спросить, уважаемые коллеги-читатели партнерского блога. В процессе нашего общения мы стараемся доносить информацию об интересных, на наш взгляд, особенностях и возможностях в различных областях SQL Server, Windows Azure, ... Чтобы общение носило двусторонний характер, не стесняйтесь рассказывать, кого и что заинтересовало, кто что задействовал или планирует задействовать в составе своих решений. Я пытаюсь по мере своих скромных возможностей проводить сравнение производительности, как Вы, Kirill, могли видеть в предыдущем посте "Превращение последовательности точек в геометрическую фигуру" или "Нарастающий итог - сравнение производительности" и т.д., но одно дело AdventureWorks, другое - реальные внедрения. Тут вам и карты в руки. В следующих постах я постараюсь сделать акцент на практической стороне вопроса, но очень надеюсь, что и вы будете не просто пассивными читателями, а будете активно делиться своим опытом.

• Anonymous
  January 01, 2003
  Я не очень понимаю, что означает "прямоугольник с произвольным набором полигонов". Ну да ладно. Насколько я понял, у Вас есть база геометрических объектов, и Вы хотите проверить на ней производительность запросов. Что мешает это сделать?

• Anonymous
  January 01, 2003
  Как я писал, пока могу только фантазировать, но хотелось бы проверить применимость данной технологии к практике. Например, организовать ретроспективный поиск для задач системы видео наблюдения. В задаче необходимо искать все пересечения прямоугольников с произвольным набором полигонов. Количество прямоугольников порядка сотен тысяч, количество полигонов - не более 10. Такой поиск должен производится не более секунды.

• Anonymous
  January 01, 2003
  The comment has been removed

• Anonymous
  September 11, 2011
  Интересно, но я пока только фантазировать могу как это на практике использовать. Насколько это быстро работает, есть примеры живых проектов?