Перевернутые многоугольники? Как устранить правило левой руки SQL Server

На прошлой неделе мы рассмотрели, как легко импортировать данные GeoJSON в тип данных geography SQL Server.

Однако иногда ваши исходные данные не будут идеально отформатированы для пространственных типов данных SQL Server.

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

Смотрите видеоблог этой недели на моем канале YouTube.

Колорадо — это прямоугольник

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

Грубое обозначение координат широты и долготы четырех углов штата даст вам многоугольник, состоящий из следующих точек:

Или в формате GeoJSON (установленном равным переменной SQL) вы можете представить эти данные следующим образом:

DECLARE @Colorado nvarchar(max) = N'
{
 "type": "FeatureCollection",
 "features": [{
  "type": "Feature",
  "properties": {},
  "geometry": {
   "type": "Polygon",
   "coordinates": [
    [
     [-109.05005693435669,
      41.0006946252774
     ],
     [-102.05157816410065,
      41.002362600596015
     ],
     [-102.0421314239502,
      36.993139985820925
     ],
     [-109.04520750045776,
      36.99898824162522
     ],
 
     [-109.05005693435669,
      41.0006946252774
     ]
    ]
   ]
  }
 }]
}
'

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

Просмотр нашего полигона в Колорадо

Преобразование этого массива точек в тип данных geography SQL Server довольно прямолинейно:

SELECT
 geography::STPolyFromText(
  'POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))'
  ,4326) AS StateBoundary
FROM
 (
 SELECT 
  Long,
  Lat
 FROM
  OPENJSON(@Colorado, '$.features[0].geometry.coordinates[0]')
  WITH
   (
    Long varchar(100) '$[0]',
    Lat varchar(100) '$[1]'
   )
)d

Затем мы можем взглянуть на вкладку «Пространственные результаты» в SQL Server Management Studio и увидеть наш многоугольник Колорадо, нарисованный на карте. Вы можете заметить, что на этой картинке кое-что выглядит немного забавно:

ПОЧЕМУ ПЛОЩАДЬ МОЕГО МНОГОУГОЛЬНИКА ИНВЕРТИРОВАНА?!!??!

Проницательные глаза могут заметить, что SQL Server не затеняет область внутри многоугольника — вместо этого он затеняет все в мире, КРОМЕ внутренней части нашего многоугольника.

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

Правила левой/правой руки

Однако есть логическое объяснение тому, почему SQL Server затеняет не ту часть нашего полигона.

Тип данных geography SQL Server следует правилу левой руки при определении того, какая сторона многоугольника должна быть закрашена. Напротив, спецификация GeoJSON указывает, что объекты должны формироваться по правилу правой руки.

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

Поэтому, если мы нарисуем стрелки, указывающие в направлении координат, перечисленных в нашем GeoJSON, вы заметите, что мы создаем наш многоугольник в направлении по часовой стрелке:

Если вы представите, что идете по этой линии в указанном направлении, вы быстро поймете, почему SQL Server затеняет «внешнюю часть» многоугольника: следуя правилу левой руки, все, кроме штата Колорадо, считается внутренняя часть нашего многоугольника.

Изменение направления многоугольника

Итак, проблема здесь в том, что наши полигональные данные были закодированы в другом направлении, чем ожидает тип данных geography SQL Server.

Один из способов исправить это — исправить наши исходные данные, переупорядочив точки так, чтобы многоугольник рисовался в направлении против часовой стрелки:

-- Note: The middle three sets of points have been included in reverse order while the first/last point have stayed the same
DECLARE @ColoradoReversed nvarchar(max) = N'
{
 "type": "FeatureCollection",
 "features": [{
  "type": "Feature",
  "properties": {},
  "geometry": {
   "type": "Polygon",
   "coordinates": [
    [
     [-109.05005693435669,
      41.0006946252774
     ],
     [-109.04520750045776,
      36.99898824162522
     ],
     [-102.0421314239502,
      36.993139985820925
     ],
 
     [-102.05157816410065,
      41.002362600596015
     ],
 
     [-109.05005693435669,
      41.0006946252774
     ]
    ]
   ]
  }
 }]
}
'

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

Так как же решить эту проблему более эффективно?

Легко, используйте функцию SQL Server ReorientObject().

SELECT
 geography::STPolyFromText(
  'POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))'
  ,4326).ReorientObject() AS StateBoundary
FROM
 (
 SELECT 
  Long,
  Lat
 FROM
  OPENJSON(@Colorado, '$.features[0].geometry.coordinates[0]')
  WITH
   (
    Long varchar(100) '$[0]',
    Lat varchar(100) '$[1]'
   )
)d

ReorientObject() делает то, что мы делали вручную выше — она манипулирует порядком точек нашего многоугольника, чтобы изменить направление, в котором рисуется многоугольник.

Примечание: SQL использует другой порядок обращения точек с помощью ReorientObject(), чем тот, в котором мы обращали их выше. Однако конечный результат остается тем же.

Независимо от того, какой метод вы выберете, результаты будут одинаковыми: наш многоугольник Колорадо теперь нарисован в правильном направлении, и вкладка «Пространственные результаты» визуально подтверждает это для нас:

Первоначально опубликовано на сайте bertwagner.com 23 января 2018 г.