Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member199076
Participant
0 Kudos

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.   
2 Comments