Vector Solutions - Подключаем MapServer к MS SQL

28.02.2017 Подключаем MapServer к MS SQL

Вступление

Эта статья относится к вопросу разработок в области веб-ГИС продолжает тему по изучению и настройке MapServer. О том, что собой представляет MapServer и как его установить, вы можете узнать из предыдущей статьи. Итак, для работы нам необходим будет уже установленный и работающий экземпляр MapServer, а также наличие СУБД MS SQL Server 2008 и выше. В своем примере я использовал MS SQL Server 2012. Если вы будете использовать MS SQL Server 2008, то советую обратить внимание на определенные ограничения этой версии при работе с пространственными данными. Эти ограничения упомянуты в статье "Загрузка пространственных данных (spatial data) из shape-файлов в MS SQL".

Загрузка пространственных данных в MS SQL Server

MapServer может подключаться к MS SQL двумя способами:

Через тип подключения OGR
Через плагин - драйвер, создающий доступ к полям БД с пространственными данными, который скомпилирован в качестве плагина ("msplugin_mssql2008.dll").

Вначале рассмотрим способ подключения через OGR. Создать нужные таблицы и загрузить данные можно с помощью утилиты командной строки org2org и MSSQL Spatial driver. Эта утилита находится в директории ms4w\tools\gdal-ogr, где ms4w - директория, в которую был установлен MapServer. Запустим командную строку и перейдем в каталог ms4w\tools\gdal-ogr и запустим файл setenv.bat (который находится в каталоге ms4w) на выполнение. В моем случае это выглядит таким образом:

D:\ms4w\tools\gdal-ogr>..\..\setenv.bat

Выполнение данного файла установит нам необходимые переменные окружения для успешного запуска утилиты ogr2ogr. Далее загрузим и распакуем в директорию ms4w\Apache\htdocs\mydemo\shp файлы из архива с полигонами стран и файлы из архива с популярными местами планеты. Теперь запустим утилиту ogr2ogr с такими параметрами:

D:\ms4w\tools\gdal-ogr>ogr2ogr -f MSSQLSpatial -a_srs EPSG:4326 "MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" ..\..\Apache\htdocs\mydemo\shp\TM_WORLD_BORDERS_SIMPL-0.3.shp -lco spatial_index=no -nln tm_world_borders_simpl -progress

-f MSSQLSpatial - ключ -f позволяет указать формат данных. MSSQLSpatial - это пространственные данные MS SQL Server.
-a_srs EPSG:4326 - позволяет указать EPSG-идентификатор системы координат.
"MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" - строка подключения к MS SQL Server.
..\..\Apache\htdocs\mydemo\shp\TM_WORLD_BORDERS_SIMPL-0.3.shp - путь к shape-файлу с геоданными.
-lco spatial_index=no - указываем программе не создавать пространственный индекс.
-nln tm_world_borders_simpl - задаем имя создаваемой таблицы с пространственными данными.
-progress - отображать сведения о процессе работы программы.

Еще импортируем в нашу базу данных геоданные из файла ne_10m_populated_places.shp следующей командой:

D:\ms4w\tools\gdal-ogr>ogr2ogr -f MSSQLSpatial -a_srs EPSG:4326 "MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" ..\..\Apache\htdocs\mydemo\shp\ne_10m_populated_places.shp -lco spatial_index=no -nln ne_10m_populated_places -progress

Возможно, вам придется настроить строку подключения к вашему экземпляру MS SQL Server. Различные виды строк подключения можно найти, например, на сайте www.connectionstrings.com. Если все прошло успешно, то в нашей БД MySpatialDb должны появиться такие таблицы:

Импортированные геоданные в MS SQL Server

Сами пространственные данные находятся в таблицах tm_world_borders_simpl и ne_10m_populated_places. Если вы откроете структуру этих таблиц, то увидите, что по умолчанию утилита ogr2org создает поле с геоданными как тип geometry! Чтобы загрузить геоданные как тип geography, нужно к вышеуказанным командам добавить ключ -lco "GEOM_TYPE=geography" Остальные таблицы содержат метаданные. Таблица geometry_columns содержит ссылки на таблицы и поля, содержащие пространственные данные. А таблица spatial_ref_sys хранит информацию о системах координат.

Подключение к MS SQL через OGR

GDAL/OGR (поэтому и MapServer) могут получать доступ к пространственным таблицам в MS SQL 2008 и выше через MSSQLSpatial драйвер. Выполните показанную ниже команду, чтобы проверить, поддерживает ли имеющаяся у вас библиотека MS SQL Server.

D:\ms4w\tools\gdal-ogr>ogrinfo --formats | findstr "MSSQLSpatial"

Ответ должен содержать:

MSSQLSpatial -vector- (rw+): Microsoft SQL Server Spatial Database

командой findstr мы просто фильтруем вывод команды ogrinfo --formats. Без фильтрации мы можем посмотреть полный список поддерживаемых форматов данных. Теперь протестируем наши данные такими командами:

D:\ms4w\tools\gdal-ogr>ogrinfo "MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" tm_world_borders_simpl -summary

D:\ms4w\tools\gdal-ogr>ogrinfo "MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" ne_10m_populated_places -summary

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

MAP
    NAME "sample"
    EXTENT -180 -90 180 90 # Geographic
    SIZE 800 400
    IMAGECOLOR 220 221 239
  
    SYMBOL
      NAME 'circle'
      TYPE ELLIPSE
      POINTS 1 1 END
      FILLED TRUE
    END  
  
    LAYER
      NAME "world_poly"
      TYPE POLYGON
      STATUS ON
      ####
      CONNECTIONTYPE OGR
      CONNECTION "MSSQL:server=.\SQLEXPRESS;trusted_connection=yes;database=MySpatialDb;tables=tm_world_borders_simpl(ogr_geometry)"
      ####
      PROJECTION
        "init=epsg:4326"
      END
      CLASS
        NAME "The World"
        STYLE
          COLOR 240 240 240
          OUTLINECOLOR 100 100 100
        END
      END
      PROCESSING 'CLOSE_CONNECTION=DEFER'
    END # layer  
  
END # MAP

Часть параметров уже была описана в первой статье о MapServer. Рассмотрим только новые параметры, которые появились в нашем конфигурационном файле. Объект MAP содержит новый объект SYMBOL. Зачем мы создали новый объект SYMBOL будет рассказано немного позже. Определения символов могут содержаться в основном map-файле (в объекте MAP) или в отдельном файле, что более распространено. Определения символов в отдельном файле обозначаются с помощью ключевого слова SYMBOLSET, как часть объекта MAP. Т.е. включение файла с символами осуществляется следующей строкой:

MAP

#some content above
SYMBOLSET "../etc/symbols.txt"
#some content below
  
END # MAP

Этот рекомендованный подход является превосходным средством для повторного использования определений символов среди всех приложений. В MapServer существует 3 основных типа символов: маркеры, линии и наборы штриховок. Символ 0 является особым случаем для конкретных классов символов. Для точек символ 0 - это единственный пиксел, для штриховок (т.е. заливок полигонов) символ 0 - это сплошная заливка, а для линий - это линий с толщиной в 1px. Определения символов не содержат информации о цвете, цвет задается внутри объекта STYLE. Стилизация линий была перенесена в объект STYLE объекта CLASS в MapServer версии 5 и более недоступен в объекте SYMBOL. Для MapServer версий ниже 5 существует ограничение - максимум 64 определения символа в файле. Это ограничение можно изменить, отредактировав файл mapsymbol.h и изменив значение MS_MAXSYMBOLS в начале файла. Начиная с версии MapServer 5 нет ограничений на количество определяемых символов. Больше информации можно получить в руководстве по созданию картографических символов.

Далее разберем параметры объекта SYMBOL:

NAME - Псевдоним (алиас) для символа. Используется в объекте STYLE объекта CLASS.
TYPE - тип [ellipse|hatch|pixmap|svg|truetype|vector].
- ellipse - значения радиуса в направлениях x и y определяют эллипс.
- hatch - задают штриховые линии фигуры (полигона).
- pixmap - пользовательское изображение будет использоваться в качестве символа.
- svg - SVG-символ (SVG - scalable vector graphics, масштабируемая векторная графика). Требуется библиотека libsvg-cairo.
- truetype - использование шрифта TrueType, который определен в параметре FONTSET объекта MAP.
- vector - используется векторный рисунок для определения формы символа.
POINTS - обозначает начало последовательности точек, который определяют векторное изображение при указанном типе vector или определяют радиусы x и y при указанном типе ellipse. Конец этого блока определяется ключевым словом END. Значения x и y могут задаваться как десятичные числа. Максимальные значения x и y определяют ограничивающий контур символа. Размер (высота) символа задается в объекте STYLE. Можно создавать не соприкасающийся пути, вставив "-99 -99" в надлежащие места. Значение x увеличивается по вправо, а y - вниз. Для символов с TYPE ellipse равенство значений x и y создает окружность.
FILLED - TRUE или FALSE. Если TRUE, то символ будет залит цветом, который укажет пользователь в параметре COLOR объекта STYLE. По умолчанию - FALSE. Если TRUE, символы типа ellipse и vector будут трактоваться как полигоны. Цвет заливки указывается, используя параметр COLOR объекта STYLE, а контур - используя параметр OUTLINECOLOR объекта STYLE и WIDTH.

Объект LAYER содержит ряд новых параметров:

CONNECTIONTYPE - Тип подключения. Возможные значения: [contour|kerneldensity|local|ogr|oraclespatial|plugin|postgis|sde|union|uvraster|wfs|wms]. В данном случае мы выбрали тип OGR. OGR - это библиотека с открытым исходным кодом, которая обеспечивает чтение (а иногда и запись) различных форматов векторных данных, включая ESRI shape-файлы, MapInfo mid/mif и tab-форматы. На самом деле, OGR является частью GDAL-библиотеки. В нашем случае OGR обеспечивает доступ к пространственным данным, хранящимся в MS SQL Server.
CONNECTION - строка подключения к MS SQL. Необходимо также указать параметр "tables=", а также в скобках указать название поля, которое содержит геоданные.
PROJECTION - объект, позволяющий указать используемую систему координат.
PROCESSING - директива, указывающая на необходимость поддержания пула подключений к базе данных. Создание пула подключений позволяет процессу отрисовки карты использовать существующие подключения к геоданным, что приводит к экономии ресурсов и повышению производительности. "DEFER" - указывает на необходимость использования пула подключений. "ALWAYS" - после использования соединение всегда будет закрываться и больше не будет доступно для повторного использования.

После того как мы разобрались с новыми параметрами, назовем наш файл mssql.map и сохраним его в директорию ms4w\Apache\htdocs\mydemo. Если мы в браузере перейдем по адресу: http://localhost:8888/cgi-bin/mapserv.exe?map=../htdocs/mydemo/mssql.map&layer=world_poly&mode=map, то должны увидеть изображение с границами стран мира. О том, что означают различные параметры в строке запроса рассказано в предыдущей статье.

Изображение границ стран, созданное MapServer из векторных данных в таблице MS SQL Server

Подключение к MS SQL через плагин

Изменим наш сохраненный файл mssql.map, следующим образом:

MAP
    NAME "sample"
    EXTENT -180 -90 180 90 # Geographic
    SIZE 800 400
    IMAGECOLOR 220 221 239
  
    SYMBOL
      NAME 'circle'
      TYPE ELLIPSE
      POINTS 1 1 END
      FILLED TRUE
    END  
  
    LAYER
      NAME "world_poly"
      TYPE POLYGON
      STATUS ON
      ####
      CONNECTIONTYPE OGR
      CONNECTION "MSSQL:server=.\SQLEXPRESS;trusted_connection=yes;database=MySpatialDb;tables=tm_world_borders_simpl(ogr_geometry)"
      ####
      PROJECTION
        "init=epsg:4326"
      END
      CLASS
        NAME "The World"
        STYLE
          COLOR 240 240 240
          OUTLINECOLOR 100 100 100
        END
      END
      PROCESSING 'CLOSE_CONNECTION=DEFER'
    END # layer  

    LAYER
      NAME "pop_places"
      TYPE POINT
      STATUS ON
      ####
      CONNECTIONTYPE PLUGIN
      PLUGIN "msplugin_mssql2008.dll"
      CONNECTION "Server=.\SQLEXPRESS;Database=MySpatialDb;Integrated Security=true"
      DATA "ogr_geometry from tm_world_borders_simpl USING UNIQUE ogr_fid USING SRID=4326"  
      ####
      PROJECTION
        "init=epsg:4326"
      END
      CLASS
        NAME "Pop Places"
        STYLE
            COLOR 10 100 50
            SYMBOL 'circle'
            SIZE 2		
        END
      END
      PROCESSING 'CLOSE_CONNECTION=DEFER'
    END # layer 
  
END # MAP

Здесь мы добавили к уже существующему объекту LAYER, новый LAYER-объект, в котором изменили тип подключения на PLUGIN, а также объявили два новых параметра:

PLUGIN - содержит имя файла плагина.
DATA - используется для создания SELECT-запроса для выборки данных из таблицы MS SQL. Необходимо указать поле с типом geometry. В нашем случае - это ogr_geometry в таблице tm_world_borders_simpl. Таблица должна содержать ключевое поле (в нашем случае - ogr_fid) для обеспечения возможности произвольной выборки в дальнейших запросах. Кроме того, необходимо указать идентификатор пространственной ссылки SRID. Он используется при поиске объектов с использованием операций пересечения. Если опустить SRID, то драйвер будет использовать значение SRID=0 при выборке объектов.

Указанный в параметре PLUGIN файл плагина на ОС Windows будет искать в следующем порядке:

Директория, из которой загружается приложение
Текущая директория
Системная директория. Используйте функцию GetSystemDirectory, чтобы получить путь к данной директории
Системная директория для 16-битных программ
Директория Windows. Используйте функцию GetWindowsDirectory, чтобы получить путь к данной директории
Директории, которые перечислены в переменной окружения PATH

Также в объекте STYLE мы добавили параметр SYMBOL, который ссылается на наше объявление символа в объекте MAP. В результате пространственные данные из этого слоя должны быть отрисованы в виде окружности, размер которой можно задавать в параметре SIZE.

Чтобы запустить данный пример, я скопировал файл msplugin_mssql2008.dll из директории ms4w\Apache\specialplugins в директорию Windows. В моем случае - это C:\Windows. Далее, я перешел по ссылке http://localhost:8888/cgi-bin/mapserv.exe?map=../htdocs/mydemo/mssql.map&layer=world_poly&layer=pop_places&mode=map в браузере и получил вот такое изображение:

Изображение границ стран и популярных мест, созданное MapServer из векторных данных в таблице MS SQL Server

Сейчас загрузим наши пространственные данные в MS SQL как тип geography. Для этого выполнил две таких команды:

D:\ms4w\tools\gdal-ogr>ogr2ogr -f MSSQLSpatial -a_srs EPSG:4326 "MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" ..\..\Apache\htdocs\mydemo\shp\TM_WORLD_BORDERS_SIMPL-0.3.shp -lco spatial_index=no -nln tm_world_borders_simpl_geogr -lco GEOM_TYPE=geography -progress

D:\ms4w\tools\gdal-ogr>ogr2ogr -f MSSQLSpatial -a_srs EPSG:4326 "MSSQL:server=.\SQLEXPRESS;database=MySpatialDb;trusted_connection=yes" ..\..\Apache\htdocs\mydemo\shp\ne_10m_populated_places.shp -lco spatial_index=no -nln ne_10m_populated_places_geogr -lco GEOM_TYPE=geography -progress

Далее, изменим в втором объекте LAYER, параметр DATA на такое значение: "ogr_geometry(geography) from ne_10m_populated_places_geogr USING UNIQUE ogr_fid USING SRID=4326". Внеся это изменение, мы конфигурируем плагин для работы с геоданными в формате geography, а не geometry, как было ранее. Вносим изменения в файл mssql.map, сохраняем изменения и обновляем страницу в браузере. И видим вот такое сообщение об ошибке:

msDrawMap(): Image handling error. Failed to draw layer named 'pop_places'. msMSSQL2008LayerGetShape(): Query error. Error executing MSSQL2008 SQL statement: SELECT [ogr_geometry],convert(varchar(36), [ogr_fid]) from ne_10m_populated_places_geogr WHERE ogr_geometry.STIntersects(geography::STGeomFromText('POLYGON((-180.725563909774 -90,179.725563909774 -90,179.725563909774 90,-180.725563909774 90,-180.725563909774 -90))',4326)) = 1 -[Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": Microsoft.SqlServer.Types.GLArgumentException: 24206: The specified input cannot be accepted because it contains an edge with antipodal points. For information about using spatial methods with FullGlobe objects, see Types of Spatial Data in SQL Server Books Online. Microsoft.SqlServer.Types.GLArgumentException: at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode) at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai) at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai) at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid) .

Сообщение об ошибке говорит, что проблема возникла при отрисовке слоя 'pop_places', а сам источник проблемы заключается в том, что отрисовываемая область представляет собой весь мир. Для этого достаточно использовать вот такой запрос SELECT [ogr_geometry],convert(varchar(36), [ogr_fid]) from ne_10m_populated_places_geogr WHERE ogr_geometry.STIntersects(geography::STGeomFromText('FULLGLOBE',4326)) = 1. Однако драйвер заточен под MS SQL 2008, где поддержки объекта FULLGLOBE нет. Поэтому немного уменьшим размеры изображения для отрисовки:

MAP
    ...
    EXTENT -179 -89 179 89 # Geographic
    ...

END # MAP

Внеся эти изменения в файл mssql.map и обновив страницу в браузере, получим опять тот же самый рисунок. Теперь давайте попробуем отрисовать границы стран мира через плагин с использованием типа geography. Для этого внесем следующие изменения в первый LAYER-объект в файле mssql.map.

MAP
    ...
    LAYER
      ...
      ####
      CONNECTIONTYPE PLUGIN
      PLUGIN "msplugin_mssql2008.dll"
      CONNECTION "Server=.\SQLEXPRESS;Database=MySpatialDb;Integrated Security=true"
      DATA "ogr_geometry(geography) from tm_world_borders_simpl_geogr USING UNIQUE ogr_fid USING SRID=4326"
      ####
      ...
    END # layer 
    ...

END # MAP

Сохраняем изменения, обновляем и получаем еще одну ошибку:

msDrawMap(): Image handling error. Failed to draw layer named 'world_poly'. msMSSQL2008LayerGetShape(): Query error. Error executing MSSQL2008 SQL statement: SELECT [ogr_geometry],convert(varchar(36), [ogr_fid]) from tm_world_borders_simpl_geogr WHERE ogr_geometry.STIntersects(geography::STGeomFromText('POLYGON((-179 -89.3879849812265,179 -89.3879849812265,179 89.3879849812265,-179 89.3879849812265,-179 -89.3879849812265))',4326)) = 1 -[Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly. System.ArgumentException: at Microsoft.SqlServer.Types.SqlGeography.ThrowIfInvalid() at Microsoft.SqlServer.Types.SqlGeography.STIntersects(SqlGeography other) .

В данном случае проблема связана с невалидной геометрией. Нам предлагают исправить данную проблему вызвав метод MakeValid. Давайте так и поступим, выполнив следующий SQL-запрос в нашей БД, например, в Microsoft Sql Management Studio:

UPDATE tm_world_borders_simpl_geogr SET ogr_geometry = ogr_geometry.MakeValid() WHERE ogr_geometry.STIsValid() = 0

Обновляем нашу страницу в браузере и получаем вот такое изображение:

Изображение границ стран и популярных мест, созданное MapServer из векторных данных в таблице MS SQL Server как тип geography

Чтобы разобраться в чем же дело, я загрузил геоданные из shape-файла TM_WORLD_BORDERS_SIMPL.shp в БД с использованием своей утилиты shape2mssql, о которой в свое время была написана статья. Однако результат получился точно таким же, как на рисунке выше. Продолжая исследования проблемы, я заскочил к знакомой на работу и попытался подключиться к пространственным данным в MS SQL из ArcGis 10.2. Однако и здесь возникли проблемы с отрисовкой изображения, как показано на рисунке ниже:

Проблемы с рендерингом пространственных данных MS SQL в ArcMap

Ради интереса я открыл в ArcMap наш shape-файл M_WORLD_BORDERS_SIMPL-0.3.shp (который нормально отрисовался) и произвел экспорт в новую таблицу MS SQL Server, а потом подключил ее как новый слой в ArcMap. Все замечательно! Однако, ArcMap выгрузил данные как тип geometry.

Подводя итог, можно сказать, что на данном этапе пока лучше хранить свои пространственные геоданные как тип geometry, поскольку данное решение обеспечивает прекрасные результаты как с библиотекой GDAL, которая входит в состав MapServer, так и с ПО ArcGIS.

Файлы для загрузки:

TM_WORLD_BORDERS_SIMPL-0.3.zip (shape) - территориальные границы стран (полигоны).
ne_10m_populated_places.zip (shape) - наиболее посещаемые места (точечные объекты).