28.12.2016 Загрузка пространственных данных (spatial data) из shape-файлов в MS SQL

Лирическое всупление

СУБД MS SQL Server начиная с версии 2008 поддерживает работу с пространственными данными (spatial data), которые реализованы как пользовательские типы данных (UDT-types). MS SQL Server поддерживает 2 вида пространственных данных:

  • geometry - представляет данные в евклидовой (плоской) системе координат.
  • geography - представляет данные в геодезической (географической) системе координат.

Поддержка вышеуказанной СУБД пространственных данных открывает много полезных возможностей при разработке сайтов и других ГИС-приложений, взаимодействующих с картографической информацией: можно хранить данные в одном месте (централизовано), пользуясь преимуществами единой резервной копии и не беспокоясь за рассогласованность данных; получить все преимущества транзакций при извлечении и обновлении данных; создавать пространственные индексы для ускорения поиска, использовать удобство языка запросов SQL при манипуляции с данными и т.д.

Как вам, вероятно, известно MS SQL Server 2008 (или более поздние версии с уровнем совместимости 100 и ниже) накладывает некоторые ограничения на использование типа geography (взято из MSDN):

  • Любой экземпляр geography должен лежать в пределах одного полушария. И, как следствие, не допускается сохранение пространственных объектов больше размера полушария.
  • Любой экземпляр geography в представлении консорциума OGC Well-Known Text (WKT) или Well-Known Binary (WKB), порождающий объект больше полушария, приводит к возникновению исключения ArgumentException.
  • Методы типа данных geography, требующие указания двух экземпляров geography, такие как STIntersection(), STUnion(), STDifference() и STSymDifference(), возвратят NULL, если результаты методов не умещаются в одном полушарии. Метод STBuffer() также возвращает значение NULL, если выходные данные выходят за пределы одного полушария.

К счастью, в более поздних вресиях эти ограничения сняты и в моем случае использовался SQL Server 2012 Express Edition.

Получение пространственных данных из shape-файлов

Shape-файлы содержат геометрические и атрибутивные данные без сохранения информации о топологии. Этот формат хранения векторных данных (точек, линий, полигонов) был создан компанией ESRI для обмена информацией между своими программными продуктами, но приобрел широкую популярность и фактически является обменным форматом между различными геоинформационными системами (ГИС). Shape-файл состоит из:

  • Основного файла (.shp), каждая запись которого описывает фигуру со списком вершин.
  • Индексного файла (.shx) - каждая запись содержит смещение соответствующей записи основного файла от начала этого файла.
  • Файла dBASE (.dbf), который содержит атрибутивную информацию. Записи в файле должны располагаться в таком же порядке, как и в основном файле.

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

shape2sql
Окно программы spape2sql

В качестве тестовых данных для "закачки" в БД я использовал shape-файл TM_WORLD_BORDERS_SIMPL-0.3.zip с полигонами территориальных границ стран. Однако, в процессе импорта в таблицу MS SQL Server 2012 я получил предупреждение о том, что данные выходят за допустимые границы типа geography, а также ряд ошибок с сообщением о том, что значение не является допустимым экземпляром географического объекта. Возможно, ошибки связаны с ограничениями типа geography для MS SQL Server 2008, т.к. программа "заточена" под него. К сожалению, исходников я не обнаружил, равно как и других легковесных утилит для импорта геоданных. Скорей всего, есть решения от таких гигантов, как ESRI и иже с ними, входящие состав серьезных программных продуктов, но эта не та "весовая категория", и я их намеренно исключил из круга поиска.

shape2sql
Результат импорта утилиты spape2sql.
Невооруженным глазом заметно отсутствие ряда стран, в частности - России и Канады.

Чтобы глубоко не копать спецификацию shape-файла, решил поискать уже готовые библиотеки на C#. Достаточно быстро нашел подходящую библиотеку с открытым исходным кодом - Catfood.Shapefile. В загруженном архиве есть демо-пример использования библиотеки, который и взял за основу.

Я решил не заморачиваться с графическими интерфейсами и набросать простое консольное приложение, которое обозвал похожим образом - shape2mssql. Строка для запуска программы выглядит следующим образом:

shape2mssql.exe <filename.shp> [srid] [keyColumnName]

Здесь:

  • filename.shp - обязательный параметр: имя shape-файла (при этом файл должен быть в той же директории, что и программа) или путь к shape-файлу.
  • srid - необязательный параметр: идентификатор пространственной ссылки. Если не указан, то принимается по умолчанию значение 4326, что соответствует системе координат WGS84.
  • keyColumnName - необязательный параметр: имя поля для первичного ключа. Если не указан, то принимается значение по умолчанию Id

Пример запуска:

shape2mssql.exe map.shp 4269 ObjectId

Дополнительно в конфигурационном файле приложения shape2mssql.exe.config нужно указать строку подключения к БД, например, так:

<connectionStrings>
       <add name="SpatialDb" connectionString="Server=.\SQLEXPRESS;Database=MySpatialDb;Integrated Security=SSPI;" />
</connectionStrings>

Преобразование координат пространственных данных из shape-файла в объект типа SqlGeography (чтобы можно было работать с типом SqlGeography, нужно установить пакет Microsoft.SqlServer.Types (Spatial)) осуществляется c помощью класса SqlGeographyBuilder, который просто формирует нужные строки для создания геометрии, вызывая соответствующие методы STxxxFromText. Данный класс и реализация его публичных методов показана ниже:

namespace shape2mssql.Processing
{
    public class SqlGeographyBuilder
    {
        private int srid;
        private NumberFormatInfo nfi;

        public SqlGeographyBuilder(int srid)
        {
            this.nfi = new NumberFormatInfo() { CurrencyDecimalSeparator = "." };
            this.srid = srid;
        }

        public SqlGeography GetPoint(Point point)
        {
            return SqlGeography.Point(point.Y, point.X, srid);
        }

        public SqlGeography GetMultiPoint(Point[] points)
        {
            StringBuilder sb = new StringBuilder("MULTIPOINT (");
            for (int i = 0; i < points.Length; i++)
            {
                appendPointSegment(sb, points[i]);
                if (i < points.Length - 1)
                {
                    sb.Append(", ");
                }
            }
            sb.Append(")");
            return SqlGeography.STMPointFromText(new SqlChars(new SqlString(sb.ToString())), srid);
        }

        public SqlGeography GetPolygon(Point[] points)
        {
            StringBuilder sb = new StringBuilder("POLYGON ");
            sb.Append("(");
            appendPointsSegment(sb, points);
            sb.Append(")");
            SqlGeography polygon = SqlGeography.STPolyFromText(new SqlChars(new SqlString(sb.ToString())), srid).MakeValid();
            return getProperPolygon(polygon);
        }

        public SqlGeography GetMultiPolygon(Point[][] multiPoints)
        {
            StringBuilder sb = new StringBuilder("MULTIPOLYGON (");
            for (int i = 0; i < multiPoints.Length; i++)
            {
                sb.Append("(");
                appendPointsSegment(sb, multiPoints[i]);
                sb.Append(")");
                if (i < multiPoints.Length - 1)
                {
                    sb.Append(", ");
                }
            }
            sb.Append(")");
            SqlGeography polygon = SqlGeography.STMPolyFromText(new SqlChars(new SqlString(sb.ToString())), srid).MakeValid();
            return getProperPolygon(polygon);
        }

        public SqlGeography GetPolyLine(Point[] points)
        {
            StringBuilder sb = new StringBuilder("LINESTRING ");
            appendPointsSegment(sb, points);
            return SqlGeography.STLineFromText(new SqlChars(new SqlString(sb.ToString())), srid);
        }

        public SqlGeography GetMultiPolyLine(Point[][] multiPoints)
        {
            StringBuilder sb = new StringBuilder("MULTILINESTRING  (");
            for (int i = 0; i < multiPoints.Length; i++)
            {
                appendPointsSegment(sb, multiPoints[i]);
                if (i < multiPoints.Length - 1)
                {
                    sb.Append(", ");
                }
            }
            sb.Append(")");
            return SqlGeography.STMLineFromText(new SqlChars(new SqlString(sb.ToString())), srid);
        }

        // private methods goes below...
    }
}

Сам код достаточно прост, как хозяйственной мыло ) Класс SqlGeographyBuilder в аргументах своих публичных методов принимает класс shape2mssql.Entities.Point. Этот тип данных позволяет жестко не привязывать класс SqlGeographyBuilder к библиотеке Catfood.Shapefile, а именно к структуре Catfood.Shapefile.PointD. Поэтому, если подключить иную библиотеку доступа к shape-файлам, то этот класс изменять не придется, необходимо будет только написать свой метод для преобразования точек в тип shape2mssql.Entities.Point. Однако, вернемся к классу SqlGeographyBuilder. Единственный момент, на который стоит обратить внимание - это приватный метод getProperPolygon. Его код представлен ниже:

private SqlGeography getProperPolygon(SqlGeography polygon)
{
    SqlGeography invertedPolygon = polygon.ReorientObject();
    if (polygon.STArea() > invertedPolygon.STArea())
    {
        polygon = invertedPolygon;
    }
    return polygon;
}

Необходимостью его создания послужила следующая формальность: как MS SQL Server трактует полигоны. Представим, что есть область пространства, ограниченная замкнутой линией. Какую область необходимо учитывать: та, которая содержится внутри замкнутой линии или же все окружающее пространство за исключением области, ограниченной полигоном? Для решения этой дилеммы воспользуемся эвристическим подходом, который заключается в том, что область с меньшей площадью скорей всего будет правильным выбором. Таким образом, для всех полигонов производим инвертирование, проверяем условие, что площадь инвертированного полигона меньше площади начального полигона. Если условие выполняется, то далее используем уже этот инвертированный полигон.

Класс SqlDataAccess содержит код доступа к MS SQL Server и выполняет вставку геоданных и сопутствующей атрибутивной информации в таблицу. Имя таблицы получается из имени shape-файла (без расширения .shp). При создании параметра для команды вставки необходимо определить тип параметра как Udt. Фрагмент кода показан ниже:

cmd.Parameters.Add(new SqlParameter("@@" + GEOM_COLUMN_NAME, obj.Geography) 
{ 
    SqlDbType = System.Data.SqlDbType.Udt, 
    UdtTypeName = "Geography", 
    Value = obj.Geography 
});

Ссылку на загрузку исходного кода вы можете найти в конце данной статьи. Ну и напоследок приведу несколько примеров пространственных данных, импортированных в базу данных из shape-файлов с различным типом геометрии. Первый файл TM_WORLD_BORDERS_SIMPL-0.3.shp содержит полигоны границ стран мира. Поскольку данные имеют систему координат WGS84, то последние два параметра можно не указывать. Для импорта выполним команду: 

shape2mssql.exe TM_WORLD_BORDERS_SIMPL-0.3.shp

Получим всю выборку таким незамысловатым запросом:

SELECT * FROM [MySpatialDb].[dbo].[TM_WORLD_BORDERS_SIMPL-0.3]

И, перейдя на вкладку "Spatial results", увидим результаты в графическом представлении:

TM_WORLD_BORDERS_SIMPL-0.3 spatial results
Отображение пространственных данных из файла TM_WORLD_BORDERS_SIMPL-0.3.shp

Загрузим немного линий, выполнив программу со следующими параметрами.

shape2mssql.exe condivl020.shp 4269

В данном случае система координат отличается от WGS84, поэтому добавим параметр srid, который равен 4269 - североамериканская система координат (NAD) 1983г. И результат:

condivl020 spatial results
Отображение пространственных данных из файла condivl020.shp

Загрузим данные о наиболее посещаемых местах мира в виде точечных объектов.

shape2mssql.exe ne_10m_populated_places.shp

Количество объектов в файле оказалось равным 7343, а окно отображения пространственных результатов осилило отрисовку только 5000, о чем мы и были проинформированы. Но, тем не менее, картина достаточно показательна:

ne_10m_populated_places spatial results
Отображение пространственных данных из файла condivl020.shp

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

shape2mssql.exe map.shp 4326 ObjId

Получаем вот такую картину территориального делелния Замбии:

zambia spatial results
Отображение пространственных данных из файла map.shp

11.01.2017 Разбираемся с кодировкой (обновление)

В процессе импорта некоторых shape-файлов, атрибутивная информация которых содержит строки на русском языке, я обнаружил проблемы с кодировкой. Продемонстрирую данную проблему. Я нашел на просторах интернета и загрузил в свой MS SQL Server 2012 Express shape-файлы Белгородской области. Импорт прошел успешно, но в таблицах базы данных оказались строки такого вида:

Ошибка в кодировке при импорте атрибутивной информации из shape-файлов Белгородской области
Ошибка в кодировке при импорте атрибутивной информации из shape-файлов Белгородской области

С помощью популярной программы Notepad++ установил, что dbf-файлы оказались в кодировке UTF-8, в то время же как провайдер Microsoft.Jet.OLEDB.4.0 считывал их в кодировке cp866. Чтобы исправить ситуацию мне достаточно для строковых данных выполнять следующее преобразование:

if (config.DestEncoding != null && config.SourceEncoding != config.DestEncoding)
{
    byte[] bytes = config.SourceEncoding.GetBytes(strValue);
    strValue = config.DestEncoding.GetString(bytes);
}

В данном случае SourceEncoding - это cp866, а DestEncoding - это UTF-8. Чтобы учесть кодировку dbf-файла, в командную строку я добавил еще один опциональный параметр [encoding]. Теперь строка запуска программы с аргументами выглядит таким образом:

shape2mssql.exe [filename.shp] [srid] [keyColumnName] [encoding]

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

shape2mssql.exe boundary-polygon.shp 4326 Id UTF-8

мы получаем вот такую картину для атрибутов геоданных:

Исправление кодировки при импорте атрибутивной информации из shape-файлов Белгородской области
Исправление кодировки при импорте атрибутивной информации из shape-файлов Белгородской области

Дополнительные нововведения в программе

Помимо добавления еще одного аргумента, задающий кодировку файла, я вынес в файл конфигурации приложения значения параметров по умолчанию, т.е., если параметры командной строки [srid] [keyColumnName] [encoding] не указаны, то будут использоваться значения из файла конфигурации. Секция настроек выглядит таким образом:

<appSettings>
    <add key="Srid" value="4326" />
    <add key="KeyFieldName" value="Id" />
    <add key="SourceEncoding" value="cp866" />
    <!--<add key="DestEncoding" value="UTF-8" />-->
</appSettings>

Здесь:

  • Srid - идентификатор пространственной ссылки по умолчанию.
  • KeyFieldName - имя поля для первичного ключа по умолчанию.
  • SourceEncoding - кодировка, в которой считываются атрибутивные строковые данные драйвером dbf-файла.
  • DestEncoding - кодировка по умолчанию, в которую преобразуются строковая атрибутивная информация (если не указана, а также, если не указан соответствующий параметр командной строки, то перекодировка не происходит).

Еще одной новой фичей shape2mssql является пакетная загрузка shape-файлов в БД. Если теперь запустить программу без аргументов, то она ищет все shape-файлы в текущей директории и последовательно выполняет их обработку. Таким образом я загрузил в базу данных все геоданные из shape-файлов. С помощью запроса, приведенного ниже я сформировал демонстрационную выборку пространственных данных.

SELECT geom FROM [MySpatialDb].[dbo].[boundary-polygon]
UNION ALL
SELECT geom FROM [MySpatialDb].[dbo].[building-point]
UNION ALL
SELECT geom FROM [MySpatialDb].[dbo].[railway-line]
UNION ALL
SELECT geom FROM [MySpatialDb].[dbo].[surface-polygon]

В результате получилась вот такая картина:

Результат импорта пространственных данных Белгородской области из shape-файлов
Результат импорта пространственных данных Белгородской области из shape-файлов

Последние изменения касаются вывода информации на консоль в процессе работы программы. Теперь количество обработанных объектов отображается на той же строке. (Вместо Console.WriteLine("...") использую Console.Write("\r...") с указанием управляющего символа возврата каретки).

Скриншот работы программы shape2mssql
Скриншот работы программы shape2mssql

Ссылки для загрузки: