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.
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)
);
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;
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;
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>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
38 | |
19 | |
13 | |
13 | |
10 | |
10 | |
10 | |
10 | |
8 | |
8 |