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

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

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



При этом необходимо выбрать формат экспорта «Данные (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:



Каждый узел может (необязательно) иметь любое количество тегов. В файле 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:



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



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