Using OpenStreetMap Data with SQL Anywhere

[former_member199076]

The OpenStreetMap project is a free, publicly editable map of the world that has been created in "wiki"-style by thousands of users. The basic idea is that if I share a map of what I know, and you share a map of what you know, then together we have a better map. The OpenStreetMap contains mapping information about roads, rivers, buildings, stop lights, bus routes just to name a few. The OpenStreetMap data is provided under the Creative Commons Attribution-ShareAlike 2.0 license.

You can download the data for any region in the world by visiting www.openstreetmap.com , and zooming into the region you are interested in. Choose the Export tab at the top, and select to export the data in OpenStreetMap XML Data format. This is the only format that will export the map with the vector data we need in order to load it into SQL Anywhere.

If you open up the file in a text editor, you will see that it is a giant XML file made up of nodes, tags, and ways. OpenStreetMap has no concept of points, linestrings, polgyons or any of the shapes that are used in SQL Anywhere. The reason for this is that OpenStreetMap uses a topological data model, whereas SQL Anywhere uses a simple shape model.

In a topological model, the data is comprised as a series of points (called nodes) that are identified by an id, a latitude, and a longitude. Complex structures are built by defining relationships (called ways) between nodes. For example, a way representing a road may be comprised of 10 nodes. Nodes can belong to zero or more ways. Key-Value attributes (called tags) can be assigned to both nodes and ways to further identify them. For example, a node may have a tag [Key=Railway, Value=Level Crossing], whereas a way may have a tag [Key=Highway, Value=Footpath]. A comprehensive list of the standard tags can be found here .

By comparison, SQL Anywhere (along with other spatial databases) use simple object geometries that include basic shapes such as points, linestrings, and polygons. In order to use the OpenStreetMap data inside SQL Anywhere, we need to convert the nodes into points, and the ways into linestrings. Since the OpenStreetMap data is XML, we can use the OPENXML() function to do most of the hard work. In the code sample below I have created four procedures to simplify working with the basic features of OpenStreetMap data:

• OSM_GetNodes: Return all the nodes as points
• OSM_GetNodeTags: Return all tags associated with nodes
• OSM_GetWays: Return all ways as linestrings
• OSM_GetWayTags: Return all tags associated with ways

Below are the queries to implement those procedures:

// Returns all the OSM ways as LineStrings

CREATE PROCEDURE OSM_GetWays(IN filename LONG VARCHAR)

RESULT (way_id BIGINT, geometry ST_LineString(SRID=4326))

BEGIN

 SELECT way_nodes.way_id, (ST_LineString::ST_LineStringAggr(location ORDER BY position)) FROM

 (SELECT * FROM OPENXML( xp_read_file(filename), '/osm/way/nd') WITH ( "way_id" BIGINT '../@id', "node_id" BIGINT '@ref', "position" INTEGER '@mp:position')

 ) way_nodes,

 (SELECT a.id node_id, new ST_Point(a.lon, a.lat, 4326) location from

 (SELECT * FROM OPENXML( xp_read_file(filename), '/osm/node') WITH ( "id" BIGINT '@id', lat FLOAT '@lat', lon FLOAT '@lon' )) a

 ) nodes

 WHERE

 way_nodes.node_id = nodes.node_id

 GROUP BY

 way_nodes.way_id;

END;



// Returns all the OSM nodes as Points

CREATE PROCEDURE OSM_GetNodes(IN filename LONG VARCHAR)

RESULT (node_id BIGINT, geometry ST_Point(SRID=4326))

BEGIN

 SELECT n.id, NEW ST_Point(n.lon, n.lat, 4326) FROM

 (SELECT * FROM OPENXML( XP_READ_FILE(filename), '/osm/node') WITH ( "id" BIGINT '@id', lat FLOAT '@lat', lon FLOAT '@lon' )

 ) n;

END;



// Returns all the tags on Nodes

CREATE PROCEDURE OSM_GetNodeTags(IN filename LONG VARCHAR)

RESULT (node_id BIGINT, "key" LONG VARCHAR, "value" LONG VARCHAR)

BEGIN

 SELECT id, k, v FROM OPENXML( XP_READ_FILE(filename), '/osm/node/tag') WITH ( "id" BIGINT '../@id', k LONG VARCHAR '@k', v LONG VARCHAR '@v' );

END;



// Returns all the tags on Ways

CREATE PROCEDURE OSM_GetWayTags(IN filename LONG VARCHAR)

RESULT (way_id BIGINT, "key" LONG VARCHAR, "value" LONG VARCHAR)

BEGIN

 SELECT id, k, v FROM OPENXML( XP_READ_FILE(filename), '/osm/way/tag') WITH ( "id" BIGINT '../@id', k LONG VARCHAR '@k', v LONG VARCHAR '@v' );

END;

The sample data for the queries below are for the area around Woodstock, Ontario (map). The XML file containing the data (Woodstock.osm) was obtained using the method described earlier in this post.

Using the OSM_GetWays procedure, we can view all of the data defined in the file including roads, highways, rivers, streams, railways, paths, etc.

SELECT * FROM OSM_GetWays('Woodstock.osm');

 

If we want to narrow our search, we can use the tag information (obtained with the OSM_GetWayTags procedure). For example, in this query we will limit it to ways that have a highway key defined. Since we did not specify a value, this query will include all types of highways including city streets, rural roads, and super highways.

SELECT * FROM OSM_GetWayTags('Woodstock.osm') way_tag,

 OSM_GetWays('Woodstock.osm') ways

WHERE "key" = 'highway' AND

 way_tag.way_id = ways.way_id;

 

In our last example, we will query for all ways that have a waterway key, with a value of stream. This will exclude other waterways such as rivers and lakes.

SELECT * FROM OSM_GetWayTags('Woodstock.osm') way_tag,

 OSM_GetWays('Woodstock.osm') ways

WHERE "key" = 'waterway' AND

 "value" = 'stream' AND

 way_tag.way_id = ways.way_id;

Just like Wikipedia, the information is incomplete and only as reliable as the people who contribute it, but the OpenStreetMap project contains a wealth of free, geographically diverse data to experiment with.