Skip to Content

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 .

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Anonymous
    Hi! My name is Bjarne Anker and I work for AKVA group Software AS in Norway. We met in Frankfurt for the CAB meeting in april this year.  First of all, great work on this article! It’s nice to see how easy it is to create map data from the database and displaying as a layer on OSM.  We’ve started looking into the use of OSM, OpenLayers, GeoJSON and SQL Anywhere. For now, we’re using SQL Anywhere 11, so I’ve written a function to create valid GeoJSON from a table in the database.  My collegue has created a framework using OSM and the JSON library, which I’ve installed on my IIS 7.0 in Windows 7. Everything looks to be working great so far. The examples and maps are showing just fine.  But now I’ve created a webservice in my database (http://localhost:8080/WebDemo/GetGrossister/) which creates the GeoJSON data, and as you see it’s running on port 8080. IIS is running on port 80. When I try to display the points created in the webservice (syntax is verified against openlayers.org, which showes the points correctly), I get the following error: “Bad GeoJSON input string”.  I guess you have the same issues on your environment, and I suspect it to have something to do with the ports. Is this something you will adress in part 2 of this presentation?  Regards,  Bjarne
    (0) 
  2. Anonymous
    […] the first part of this series we showed how to generate a GeoJSON representation of spatial data we had stored in the database. […]
    (0) 
  3. Anonymous
    Hello Bjarne,  Good to hear from you!  My guess is that you are running into a problem with the browser’s limitation on the same-origin policy (http://en.wikipedia.org/wiki/Same_origin_policy). You are running into this problem because the application is being served off of a different port (80) than the data you are requesting (8080). For security reasons, the browser will not allow you to make Ajax requests to a different server than the page originated from.   Try using either debugging commands or Firebug and see if the Ajax request to your database web service is actually returning any data. My guess is that the browser is throwing an error that is not begin caught, and the OpenLayers library is being sent a blank GeoJSON string. A blank string is likely causing the “Bad GeoJSON input string” error.  – Eric
    (0) 

Leave a Reply