Using OpenStreetMap Data with SQL Anywhere
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 [
Level Crossing], whereas a way may have a tag [
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;
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.