Главная

Загружаем OpenStreetMap в MS SQL Server

На днях подкинули задачку по геокодированию объектов, а именно:
  • По строке адреса определить соответствующие ей географические координаты. Строка с адресом не нормализованна и потенциально может содержать ошибки.
  • Обратная задача. По геокординатам точки предоставить список всех имеющихся в базе объектов в эпсилон окрестности от этой точки.
Сервис по геокодированию должен работать только для российских адресов. Для решения задачи геокодирования можно воспользоваться известными географическими средствами, благо такие возможности имеются. К сожалению у большинства сервисов имеются ограничение на число запросов в сутки:
Каждый сервис реализует собственные алгоритмы и предоставляет определенный уровень достоверности результатов. Однако, предполагается, что объектов, геокодирование которых необходимо производить, будет больше, чем количество допустимых запросов и обработку необходимо производить в пакетном режиме.

В качестве возможного варианта решения, я решил попробовать реализовать собственный поиск, используя для этого открытую базу данных Open Street Map (OSM). В качестве инструмента для первоначальной реализации я выбрал MS SQL Server 2012. В последнем поддерживаются пространственные типы данных, а также имеется возможность создания пространственного индекса.

Для начала необходимо раздобыть дамп базы данных OSM. Сделать это можно двумя способами:
  1. Зайти на сайт OSM, выбрать меню "Экспорт" и задать необходимую область. Экспортируемый файл будет в формате .osm
  2. Использовать замечательный ресурс gis-lab. На данном сайте, с определенной периодичностью, выкладываются дампы различных слоев для России как целиком, так и с разбивкой по регионам. При этом данные можно скачать как в формате shape, так и в формате pbf и osm 
Первый вариант

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

Для загрузки данных в MS SQL Server можно воспользоваться следующими утилитами:
  • SQL Server Spatial tools (Shape2Sql) (Загружает shape-файл в указанную таблицу БД и создает по ней гео-индекс)
  • Osm2MsSql (Создает денормализованную БД по pbf файлу)
Для работы с Shape2Sql возможно придется установить пакет SQLSysClrTypes, после установки которого появится библиотека SqlServerSpatial.dll необходимая для работы данной тулзы.

Формат osm представляет xml-файл. XSD-схему данного файла можно найти здесь, а само описание файла здесь. Данный файл описывает следующие сущности (ссылка на документацию):
  • Node — точка с заданными координатами (lat, lon). Базовый элемент, все остальные элементы конструируются из него.
  • Way — отсортированный набор точек. Бывает двух типов:
    • ломанная — дороги, реки
    • полигон (замкнутая ломанная) — дома
  • Relation — составной объект, который состоит из нескольких элементов (Members), каждый из которых может быть объектом Node, Way или Realation. Каждый составной элемент(Member) может иметь свойство роли (Role), описывающую его назначение. Может использоваться для описания маршрутов транспорта, административных границ.
  • Tag — описательная характеристика, представляющая собой объект "ключ-значение", где ключ — имя тега, а значение — значение данного тега. Теги могут быть у каждого объекта. Полный список российских тегов можно найти здесь и с картинками.
При помощи osm2mssql произведем импорт файла osm. Время загрузки для данных Москвы составило около 5 минут.

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


Посмотрим, что же именно загрузилось. Для этого например найдем информацию о объектах на Большой Бронной улице в Москве.

Следующий запрос ищет объекты, которые должны подходить под наши критерии:

select top 100 * 
from [dbo].[tWay] W
inner join [dbo].[tWayTag] Tag 
    on Tag.WayId = W.Id
inner join [dbo].[tTagType] [Type]
    on [Type].Typ = Tag.Typ
where --[Type].Name = N'addr:street' and 
    Tag.Info like  N'Большая Бронная улица'

Результат данного запроса в SSMS

И результат данного запроса в Sql Spatial Query Visualizer


Последний более удобный, так как в нем отсутствует ограничение на количество отображаемых объектов.
Проанализируем данные только по сооружениям (ключевой тег building) и выберем самые популярные теги, которые встречаются у зданий. Для этого выберем только те теги, которые встречаются у зданий не менее 500 раз.
declare @quotedTags nvarchar(max) = '';
declare @bracketTags nvarchar(max) = '';
declare @sql nvarchar(max) = '';
declare @params nvarchar(max) = '';
declare @buildingTagId int;
declare @streetTagId int;
declare @houeseTagId int;

select @buildingTagId = ttt.Typ from [dbo].[tTagType] ttt where name = 'building'
select @streetTagId = ttt.Typ from [dbo].[tTagType] ttt where name = 'addr:street'
select @houeseTagId = ttt.Typ from [dbo].[tTagType] ttt where name = 'addr:housenumber'

select @bracketTags = stuff( (
    select  ', [' + name + ']' as [text()]
    from [dbo].[tTagType] tag
    inner join (
        select  t.Typ, count(*) as Cnt
        from [dbo].[tWay] w
        inner join [dbo].[tWayTag] t
            on t.WayId = w.Id
        where exists (
            select * from [dbo].[tWayTag] t2
            where t2.Typ = @buildingTagId and t2.WayId = w.Id
        )
        group by t.Typ
        having count(*) > 500
    ) pTag
        on tag.Typ = pTag.Typ
    order by Cnt desc
    for xml path(''), type).value('.','varchar(max)')
, 1, 1, '') ;
  
select @quotedTags = replace(replace(@bracketTags,']',''''),'[','''');
select @quotedTags, @bracketTags
Результат данного запроса:

'building', 'addr:housenumber', 'addr:street', 'building:levels', 'addr:city', 'addr:country', 'cladr:code', 'addr:postcode', 'name', 'amenity', 'addr:region', 'roof:shape', 'source', 'shop', 'building:colour', 'roof:material', 'building:use', 'addr:place', 'height', 'roof:colour', 'is_in:neighbourhood', 'website', 'man_made', 'power', 'fixme', 'phone', 'source:building:levels', 'addr:housenumber2', 'opening_hours', 'addr:street2', 'description', 'operator'
Создадим теперь более удобную таблицу с домами для которых проставлен адрес (улица, дом) и развернем их атрибуты при помощи операции PIVOT с 50 самыми популярными тегами. При этом обработаем только объекты у которых проставлена улица и номер дома.
if object_id('dbo.tObjects') is not null
   drop table dbo.tObjects

set @sql = '
select  
    W.line, pvt.* 
into dbo.tObjects 
from
( 
    select 
        W.Id as WayId, Tag.Info, Type.Name as TagName  
    from [dbo].[tWay] W
    left join [dbo].[tWayTag] Tag 
        on Tag.WayId = W.Id
    inner join [dbo].[tTagType] [Type]
        on [Type].Typ = Tag.Typ
    where 
    exists (
        select *
        from [dbo].[tWayTag] Tag2
        where Tag2.typ = @buildingTagId and Tag2.WayId = W.Id
    )
    and exists (
        select  WayId
        from [dbo].[tWayTag] Tag2
        where Tag2.typ = @houeseTagId and Tag2.WayId = W.Id
    )
    and exists (
        select  WayId
        from [dbo].[tWayTag] Tag2
        where Tag2.typ = @streetTagId and Tag2.WayId = W.Id
    )
    and Type.name in (' + @quotedTags + ')
)
as src
pivot
(
    max(src.Info) 
    for TagName in (' + @bracketTags + ') 
) as pvt
inner join [dbo].[tWay] W
    on W.Id = pvt.WayId';

print @sql;
SET @params = '@buildingTagId int, @houeseTagId int, @streetTagId int,'

exec sp_executesql @sql, @params, @buildingTagId = @buildingTagId, @houeseTagId = @houeseTagId, @streetTagId = @streetTagId
go
alter table dbo.TObjects add constraint PK_tObjects  primary key  clustered  (WayId)

Создадим гео-индекс

go
create spatial index [idx] ON [dbo].TObjects
(
 [line]
)
using  geography_auto_grid 
with (
 cells_per_object = 12, pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on
) on [primary]
go


ElastcicSearch
https://github.com/ncolomer/elasticsearch-osmosis-plugin
Отображение данных ElasticSearch
https://github.com/ncolomer/discovery
http://habrahabr.ru/hub/openstreetmap/




Интересные ссылки:
  • http://aboutsqlserver.com/2013/09/03/optimizing-sql-server-spatial-queries-with-bounding-box/
  • http://blogs.msdn.com/b/davidlean/archive/2008/10/27/sql-2008-spatial-samples-part-n-3-of-n-performance-improvement-methods.aspx
  • http://blogs.msdn.com/b/isaac/archive/2009/05/28/sql-server-spatial-indexing.aspx
  • http://boomphisto.blogspot.ru/2011/04/black-art-of-spatial-index-tuning-in.html
  • http://www.mssqltips.com/sql-server-tip-category/88/spatial-data-storage/
  • http://sqlspatialtools.codeplex.com/

24 февраля 2015 г.