Skip to main content

WKT to KML transformation

MySQL supports a few functions to convert geometry values between its internal format and either Well-Known Text (WKT) or Well-Known Binary (WKB) representations. An obvious missing feature is the possibility to retrieve data in the widely used GML format. The WKT format is defined in the OpenGIS Simple Features Implementation Specification for SQL and predates GML.

This SQL query retrieves geometry as WKT from a MySQL table:

SELECT iso2, name, AsText(geom) AS wkt FROM borders

I had to store country boundaries as multi-polygons in my database, since many countries consist of several islands. Some of the polygons are complex (i.e. contains holes) because of enclaves. Since the WKT and KML formats are very different, it is not straightforward to do a conversion of complex polygons. This is an example of how a WKT to KML conversion can be done in PHP:


// This function converts a representation of a
// MULTIPOLYGON from WKT to KML
function wkt2kml($wkt){

// Change coordinate format
$wkt = preg_replace("/([0-9\.\-]+) ([0-9\.\-]+),*/", "$1,$2,0 ", $wkt);

$wkt = substr($wkt, 15);
$wkt = substr($wkt, 0, -3);
$polygons = explode(')),((', $wkt);
$kml = '<MultiGeometry>' . PHP_EOL;

foreach ($polygons as $polygon) {
$kml .= '<Polygon>' . PHP_EOL;
$boundary = explode('),(', $polygon);
$kml .= '<outerBoundaryIs>' . PHP_EOL
. '<LinearRing>' . PHP_EOL
. '<coordinates> ' . $boundary[0] . '</coordinates>' . PHP_EOL
. '</LinearRing>' . PHP_EOL
. '</outerBoundaryIs>' . PHP_EOL;

for ($i=1; $i < count($boundary); $i++) {
$kml .= '<innerBoundaryIs>' . PHP_EOL
. '<LinearRing>' . PHP_EOL
. '<coordinates> ' . $boundary[$i] . '</coordinates>' . PHP_EOL
. '</LinearRing>' . PHP_EOL
. '</innerBoundaryIs>' . PHP_EOL;
}
$kml .= '</Polygon>' . PHP_EOL;
}
$kml .= '</MultiGeometry>' . PHP_EOL;
return $kml;
}

Parsing WKB instead of WKT might give a better performance.
Labels:

Comments

Anonymous said…
And to think, if you were using PostgreSQL/PostGIS, you could have just done AsKML(geom) :)
19 March 2008 at 05:42
Andrew Turner said…
Thanks for the great snippet. Want to move GeoPress to using WKT internally for storage and this will be useful.

In response to @nodes - the problem with your suggestion is the assumption that the dev has control over the database being used. For a plugin to other systems, this unfortunately isn't always the case. :)
4 April 2008 at 16:25
gorkemogut said…
Do you have any idea about handling this issue on .NET?
17 September 2012 at 17:02
Post a Comment

Popular posts from this blog

Creating a WebGL Earth with three.js

This blog post will show you how to create a WebGL Earth with three.js , a great JavaScript library which helps you to go 3D in the browser. I was surprised how easy it seemed when reading a blog post  by Jerome Etienne . So I decided to give it a try using earth textures  from one of my favourite cartographers, Tom Patterson . WebGL is a JavaScript API for rendering interactive 3D graphics in modern web browsers without the use of plug-ins. Three.js is built on top of WebGL, and allows you to create complex 3D scenes with a few lines of JavaScript. If your browser supports WebGL you should see a rotating Earth below: [ Fullscreen ] To be able to display something with three.js, you need three things: a scene, a camera and a renderer. var width  = window.innerWidth,     height = window.innerHeight; var scene = new THREE.Scene(); var camera = new THREE.PerspectiveCamera(45, width / height, 0.01, 1000); camera.position.z = 1.5; var rende...
11 comments
Read more

Thematic Mapping Engine

It's time to introduce the Thematic Mapping Engine (TME). In my previous blog posts, I've shown various techniques of how geobrowsers can be used for thematic mapping. The goal has been to explore the possibilites and to make these techniques available to a wider audience. The Tematic Mapping Engine provides an easy-to-use web interface where you can create visually appealing maps on-the-fly. So far only prism maps are supported, but other thematic mapping techniques will be added in the upcoming weeks. The engine returns a KMZ file that you can open in Google Earth or download to your computer. My primary data source is UNdata . The above visualisation is generated by TME ( download KMZ ) and shows child mortaility in the world ( UNdata ). The Thematic Mapping Engine is also an example of what you can achieve with open source tools and datasets in the public domain: A world border dataset is loaded into a MySQL database . The same database contains tables with statistics ...
45 comments
Read more

Creating 3D terrains with Cesium

Previously, I’ve used three.js to create 3D terrain maps in the browser ( 1 , 2 , 3 , 4 , 5 , 6 ). It worked great for smaller areas, but three.js doesn’t have built-in support for tiling and advanced LOD algorithms needed to render large terrains. So I decided to take Cesium for a spin. Cesium is a JavaScript library for creating 3D globes and 2D maps in the browser without a plugin. Like three.js, it uses WebGL for hardware-accelerated graphics. Cesium allows you to add your own terrain data, and this blog post will show you how. Impressed by the terrain rendering in @CesiumJS - with a 10m elevation model for Norway! Farewell Google Earth. pic.twitter.com/RQKvfu2hBb — Bjørn Sandvik (@thematicmapping) October 4, 2014 Compared to  the dying Google Earth plugin , it's quite complicated to get started with Cesium. The source code is well documented and the live coding Sandcastle is great, but there is a lack of tutorials  and my development slows down when ...
12 comments
Read more