Skip to Content
Author's profile photo Eric Farrar

SQL Anywhere Spatial Support and Google Maps

Earlier this month, as part of the Sybase Developer Days tour, I spent some time in Europe spreading the word about the next version of SQL Anywhere to listeners in Milan, Frankfurt, Amsterdam, London, Madrid, Stockholm, and Paris. To exhibit the new spatial capabilities in SQL Anywhere ‘Innsbruck’, I showed a demonstration of using SQL Anywhere to create a map overlay in Google Earth of the cities I was visiting on that tour. For this blog post I am going to recreate that demo, but using Google Maps instead. The end result of this tutorial will be to show how SQL Anywhere can be used to easily export spatial data for viewing in Google Maps.

Please note that this examples requires SQL Anywhere 12 RC1  (Build #2429) posted on May 20, 2010. Please visit the Innsbruck registration page for more details.

We will start by creating a simple table, DeveloperDays, that will hold the name and location of each city on the tour. Since we are dealing with such a large scale, we can represent each city as a single point representing the center of the city.
-- Create Table with Spatial Column
CREATE TABLE DeveloperDays (
 city_id INTEGER PRIMARY KEY,
 city LONG VARCHAR,
 point ST_POINT(SRID=4326)
);
Next, we can insert the seven cities that were visited into our table. Notice that this code sample also uses the new multi-insert support that allows us to add multiple rows in a single INSERT statement.

-- Insert Points into Table
INSERT INTO DeveloperDays(city_id, city, point) VALUES
 (1, 'Milan', NEW ST_Point (9.188141, 45.463600, 4326)),
 (2, 'Frankfurt', NEW ST_Point (8.680505, 50.111511, 4326)),
 (3, 'Amsterdam', NEW ST_Point (4.890935, 52.373801, 4326)),
 (4, 'London', NEW ST_Point (-0.12623, 51.500152, 4326)),
 (5, 'Madrid', NEW ST_Point (-3.70034, 40.416691, 4326)),
 (6, 'Stockholm', NEW ST_Point (18.06448, 59.332781, 4326)),
 (7, 'Paris', NEW ST_Point (2.350987, 48.856671, 4326));

We can write a query that connects the cities in the order of visit by a line using the LineStringAggr() function.

SELECT ST_LineString::ST_LineStringAggr(point) FROM DeveloperDays ORDER BY city_id;
http://iablog.sybase.com/efarrar/wp-content/uploads/2010/05/spatialviewer1.PNG
The results look correct, so we can proceed to export this result out to Google Maps. Google Maps uses a file format called the Keyhole Markup Language (KML) for adding overlays to its maps. In order to add an overlay to Google Maps, we will need to format our line string as a valid KML document. Fortunately, SQL Anywhere has built-in support for exporting KML-formatted strings. All spatial types have a function, ST_AsKML() that will output its contents as a KML geographic feature. We can use some of the additional XML functions in SQL Anywhere, along with the KML documentation to create a simple overlay. The code to generate the KML document, DeveloperDays.kml, is shown below.
BEGIN
  DECLARE RESULT XML;
  SELECT '<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2"
xmlns:gx="http://www.google.com/kml/ext/2.2"
xmlns:kml="http://www.opengis.net/kml/2.2"
xmlns:atom="http://www.w3.org/2005/Atom">
  <Document>' ||
  LIST(XMLGEN('
  <Placemark>
  <name>Day {$city_id}: {$city}</name>
  <Point>
  <coordinates>{$lon},{$lat},0</coordinates>
  </Point>
  </Placemark>', city_id, city, point.ST_Long() AS lon, point.ST_Lat() AS lat))  ||'
  <Placemark>' ||
  ST_LineString::ST_LineStringAggr(point).ST_AsKML() ||
' </Placemark>' ||
' </Document>
</kml>' INTO RESULT
FROM DeveloperDays ORDER BY city_id;
  CALL xp_write_file('DeveloperDays.kml', RESULT);
END;
Below is the resultant KML document, DeveloperDays.kml, produced by the query above.
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2"
  xmlns:gx="http://www.google.com/kml/ext/2.2"
  xmlns:kml="http://www.opengis.net/kml/2.2"
  xmlns:atom="http://www.w3.org/2005/Atom">
  <Document>
    <Placemark>
      <name>Day 1: Milan</name>
      <Point>
        <coordinates>9.188141,45.4636,0</coordinates>
      </Point>
    </Placemark>,
    <Placemark>
      <name>Day 2: Frankfurt</name>
      <Point>
        <coordinates>8.680505,50.111511,0</coordinates>
      </Point>
    </Placemark>,
    <Placemark>
      <name>Day 3: Amsterdam</name>
      <Point>
        <coordinates>4.890935,52.373801,0</coordinates>
      </Point>
    </Placemark>,
    <Placemark>
      <name>Day 4: London</name>
      <Point>
        <coordinates>-.12623,51.500152,0</coordinates>
      </Point>
    </Placemark>,
    <Placemark>
      <name>Day 5: Madrid</name>
      <Point>
        <coordinates>-3.70034,40.416691,0</coordinates>
      </Point>
    </Placemark>,
    <Placemark>
      <name>Day 6: Stockholm</name>
      <Point>
        <coordinates>18.06448,59.332781,0</coordinates>
      </Point>
    </Placemark>,
    <Placemark>
      <name>Day 7: Paris</name>
      <Point>
        <coordinates>2.350987,48.856671,0</coordinates>
      </Point>
    </Placemark>
    <Placemark>
      <LineString>
        <coordinates>
          9.188141,45.4636
          8.680505,50.111511
          4.890935,52.373801
          -.12623,51.500152
          -3.70034,40.416691
          18.06448,59.332780999999997
          2.350987,48.856670999999999
        </coordinates>
      </LineString>
    </Placemark>
  </Document>
</kml>
The last step is to load the KML document into Google Maps. One way to do this is to host the KML document at a publicly available URL, and simply “search” for that URL in Google Maps.   

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      [...] Spatial Data Support We have added comprehensive support for the storage, manipulation, and synchronization of spatial data. You can now store location information, and then access it in your database queries, allowing for very powerful applications. Spatial data and synch is supported on all SQL Anywhere supported platforms, including servers and handheld devices. Both Eric Farrar and myself have written about spatial support previously here and here. [...]
      Author's profile photo Former Member
      Former Member
      [...] here, here, and here). Several bloggers have posted about it as well (Eric Lai, Glenn Paulley, and Eric Farrar). Additionally, there are a couple of interesting papers describing some of the major [...]