Использование OpenStreetMap совместно с SQL Server

Ни для кого не секрет, что OpenStreetMap — это молодой, динамически развивающийся проект, в котором, уже сейчас можно найти уйму полезной информации. Эта информация уже структурирована, поэтому приложив небольшие усилия, возможно извлекать и отфильтровывать абсолютно все, чего может только пожелать душа
Цель данного поста — показать, как эту информацию возможно хранить и извлекать с помощью сервера базы данных SQL Server. SQL Server начиная с 2008 версии позволяет хранить и обрабатывать геометрические и географические типы данных. Поэтому он подходит для этой цели, как нельзя лучше.

Для начала нам придется экспортировать данные в понятный для сервера баз данных вид. К счастью — велосипед изобретать не придется. OpenStreetMap поддерживает экспорт в понятный для большинства современных баз данных формат XML, хотя по недоразумению у них он зовется OSM
Предположим, что мы хотим посмотреть и обрабатывать информацию о всех банках города Санкт-Петербург. Для этого необходимо найти необходимую информацию на проекте и затем нажать кнопку «Экспорт».

image

При этом необходимо выбрать формат экспорта «Данные (OpenStreetMap XML)»
К сожалению, а может быть к счастью, порой, число объектов на выбранном участке превышает лимит. Тогда на помощь может прийти пред сохраненный osm файл, с ресурса gis-lab.info

Выбираем при необходимости Санкт-Петербург, и скачиваем необходимый osm файл
Теперь мы может загрузить полученные данные в базу данных. Как Вы помните мы собирались делать это в SQL Server, а если точнее я буду использовать SQL Server 2008R2.

Вначале конвертируем полученные данные в переменную типа xml:

DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(
BULK 'C:\tmp\stpeter.osm',
SINGLE_BLOB) AS x);


Создадим затем таблицы узлов (nodes) и их описаний (nodestag)
Вначале первую:
IF object_id('nodes') IS NOT NULL
DROP TABLE nodes

CREATE TABLE nodes (
nodeid int,
latitude float,
longitude float,
geog4326 geography
);
INSERT INTO nodes
SELECT
OSMnode.value('@id', 'int') AS nodeid,
OSMnode.value('@lat', 'float') AS latitude,
OSMnode.value('@lon', 'float') AS longitude,
geography::Point(OSMnode.value('@lat', 'float'), OSMnode.value('@lon', 'float'), 4326) AS geog4326
FROM
@x.nodes('/osm/node') AS OSM(OSMnode)


Вот, как полученные данные выглядят в SQL Server Management Studio:

image

Каждый узел может (необязательно) иметь любое количество тегов. В файле XML-источника, они представлены в дочерний элемент (ы) каждого Определим их:

IF object_id('nodetags') IS NOT NULL
DROP TABLE nodetags
CREATE TABLE nodetags (
nodeid int,
tagname varchar(32),
tagvalue varchar(32)
);
INSERT INTO nodetags
SELECT
OSMNode.e.value('(@id)[1]', 'int') AS 'NodeID',
OSMNodeTag.e.value('@k', 'nvarchar(32)') AS 'TagName',
OSMNodeTag.e.value('@v', 'nvarchar(32)') AS 'TagValue'
FROM
@x.nodes('/osm/node') AS OSMNode(e)
CROSS APPLY
OSMNode.e.nodes('tag') AS OSMNodeTag(e)


Ну и попробуем получить то, ради чего мы это все затевали:

SELECT
n.nodeid,
n.geog4326,
nt.TagValue
FROM nodes n
LEFT JOIN nodetags nt ON n.nodeid = nt.nodeid AND TagName in ('Name' ,'operator')
WHERE n.nodeid IN
(
SELECT nodeid from nodetags
where tagvalue like '%банк%'
)


Вот как полученные точки отображаются в SSMS:

image

Так же полученные данные легко перенести например на карты Bing (я делал это с помощью microsoft Report Builder).

image

Могу только поблагодарить ребят с gis-lab.info А так же пожалеть, что нет отдельного полигона для г. Киев. Такими сущностями, как целая Украина, крайне тяжело ворочать…
Share post

Comments 5

    +3
    Граница Киева: www.openstreetmap.org/browse/relation/421866
    Скрипт для генерации poly из границы: osm2mp.googlecode.com/svn/trunk/getbound.pl
    Затем osmosis --rx file=ukraine.osm --bp kiev.poly --wx file=kiev.osm
    Удачи!
      +1
      Это на самом деле отличная новость для меня! Спасибо
      –1
      Кстати говоря, OpenStreetMap в общем не такой уж и молодой проект, летом ему будет уже 7 лет. Но если речь о российской части проекта, то да, ей года 2-3.
        0
        Если не секрет, чем обусловлен выбор SQL Server?
        Мне казалось, postgres/postgis уже достаточно давно стал своеобразным стандартом для использования данных OSM.

        И да, OSMXML зовётся OSM, а не XML, по вполне очевидным причинам :)
          +1
          пожалуйста, приятно что пользуетесь :)

          Only users with full accounts can post comments. Log in, please.