Thursday 13 March 2008

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


Anonymous said...

And to think, if you were using PostgreSQL/PostGIS, you could have just done AsKML(geom) :)

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. :)

gorkemogut said...

Do you have any idea about handling this issue on .NET?