Using OpenStreetMap with SQL Server

It's no secret that OpenStreetMap is a young, dynamically developing project, in which, now you can find a lot of useful information. This information is already structured, therefore, with a little effort, it is possible to extract and filter absolutely everything that the soul can wish for.
The purpose of this post is to show how this information can be stored and retrieved using the SQL Server database server. SQL Server since the 2008 version allows you to store and process geometric and geographic data types. Therefore, it is suitable for this purpose, as well as possible.

To begin with, we will have to export the data in a form understandable for the database server. Fortunately, there is no need to reinvent the wheel. OpenStreetMap supports exporting to XML format which is understandable for most modern databases, although by mistake they are called OSM
Suppose that we want to see and process information about all banks in the city of St. Petersburg. To do this, you need to find the necessary information on the project and then click the "Export" button.



At the same time, it is necessary to select the “Data (OpenStreetMap XML)” export format
Unfortunately, and perhaps fortunately, sometimes, the number of objects in the selected area exceeds the limit. Then a saved osm file can come to the rescue, from the gis-lab.info resource .
')
If necessary, we select St. Petersburg and download the necessary osm file.
Now we can load the received data into the database. As you remember, we were going to do this in SQL Server, or rather, I will use SQL Server 2008R2.

First, we convert the data into a variable of xml type:

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

Then create tables of nodes (nodes) and their descriptions (nodestag)
First, first:
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)

Here's what the data looks like in SQL Server Management Studio:



Each node can (optionally) have any number of tags. In the XML source file, they are presented in the child element (s) of each We define them:

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)

Well, let's try to get something for which we all started it:

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 '%%'
)

Here’s how points are displayed in SSMS:



It is also easy to transfer the obtained data, for example, to Bing cards (I did this with the help of microsoft Report Builder).



I can only thank the guys with gis-lab.info And also regret that there is no separate testing ground for the city of Kiev. Such entities as the whole Ukraine are extremely difficult to roll ...