Using SQL Anywhere with OpenLayers – Part 1
OpenLayers is a neat open source JavaScript library that allows you to put dynamic maps inside of any web page. You can see an example of an OpenLayers-powered map at the OpenStreetMap site. Being an JavaScript library, all data supplied to OpenLayers must go through web services. In the first part of this tutorial we will set up SQL Anywhere to deliver spatial data through its embedded HTTP web server to the OpenLayers-powered page. In the second part, we will hook up the OpenLayers gear and put it all together.
This tutorial will use the OpenStreetMap database that we created in the last post.
While OpenLayers supports many input formats including WKT, KML, and GML, for this tutorial we will use GeoJSON . As the name suggests, GeoJSON is a JSON-based format that allows you to encode both spatial and attribute data. A sample GeoJSON document is shown below.
{
"type":"Feature",
"id":"OpenLayers.Feature.Vector_314",
"properties":{},
"geometry":{
"type":"Point",
"coordinates":[97.03125, 39.7265625]
},
"crs":{
"type":"OGC",
"properties":{
"urn":"urn:ogc:def:crs:OGC:1.3:CRS84"
}
}
}For this example, we will create a web service that returns all of the roads coordinates, along with metadata about what type of road (super highway, street, etc) it is. This process is made simpler because SQL Anywhere contains built-in functions to turn spatial data into GeoJSON. All spatial data types have a ST_asGeoJSONmethod that will return its GeoJSON string representation.
SELECT ST_Geometry::ST_GeomFromText( 'LINESTRING(1 1,2 2,3 3)' );
-- >> LINESTRING(1 1, 2 2, 3 3)
SELECT ST_Geometry::ST_GeomFromText( 'LINESTRING(1 1,2 2,3 3)' ).ST_asGeoJSON();
-- >> { "type" : "LineString",
-- >> "coordinates": [[1,1],[2,2],[3,3]]
-- >> }Putting this all together, we can create a procedure sa_roads() that returns all the roads as GeoJSON.
CREATE PROCEDURE sa_roads()
BEGIN
CALL sa_set_http_header('Content-Type', 'text/plain');
SELECT STRING(
'{ "type": "FeatureCollection",
"features": [
{
"type": "Feature",
"geometry": ',
LIST(
STRING(geometry.ST_asGeoJSON(),',
"properties": {
"highway": "',
"value" ,
'"
}'),
'
},
{
"type": "Feature",
"geometry": '),
'
}]}')
FROM ways, waytags
WHERE ways.id = waytags.way_id AND
waytags."key" = 'highway';
END;Next, we will create a simple web service to serve this data.
CREATE SERVICE Roads
TYPE 'RAW'
AUTHORIZATION OFF
USER DBA
URL OFF
AS CALL sa_roads();Lastly, if we start up our server with the web server enabled (using the -xs switch), we can browse to our service at http://localhost/roads (your URL may be different if you started your web server on a port other than 80).
Here is a sample output based on the data set for Woodstock , Ontario .
