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 little effort, it is possible to extract and filter absolutely everything that only the soul could wish for.
The purpose of this post is to show how this information can be stored and retrieved using the SQL Server database server. Starting with the 2008 version, you can store and process geometric and geographic data types. Therefore, it is suitable for this purpose, as well as possible.

First, we will have to export the data in a format that is understandable to the database server. Fortunately, you don’t have to invent a bicycle. OpenStreetMap supports export to an XML format that is understandable for most modern databases, although due to a misunderstanding they call it OSM.
Suppose we want to view and process information about all banks in the city of St. Petersburg. To do this, find the necessary information on the project and then click the "Export" button.

image

At the same time, it is necessary to select the export format “Data (OpenStreetMap XML)”
Unfortunately, but maybe, fortunately, sometimes the number of objects in the selected section exceeds the limit. Then the osm file saved from the gis-lab.info resource can come to the rescue

If necessary, we select St. Petersburg and download the necessary osm file.
Now we can load the received data into the database. As you recall, we were going to do this in SQL Server, or rather, I will use SQL Server 2008R2.

First, convert the received data into an xml type variable: Next, create the tables of nodes (nodes) and their descriptions (nodestag) First, first: Here's how the received data looks 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. Define them: Well, let's try to get what we all started for: This is how the resulting points are displayed in SSMS:

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




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)




image



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 '%банк%'
)




image

Also, the data obtained can be easily transferred, for example, to Bing cards (I did this using microsoft Report Builder).

image

I can only thank the guys from gis-lab.info And also regret that there is no separate training ground for Kiev. Such entities as the whole of Ukraine are extremely difficult to toss and turn ...

Also popular now: