Create kml with time visualization using postGIS

PostGIS is a great tool to work with large spatial data. We can export a query as a txt file using the function COPY TO. Moreover, Postgres have XML-functions which allow you to write and read xml documents. With this functions you can create a KML document with time visualization. This means that using TimeSpan element we can visualize the spatial objects in a time animated way. The following example shows you how to create a kml document populated with points. Note that you need a column with a date/time data type.

COPY (
	SELECT
	/*begin kml spatial object*/
	xmlelement(name "Placemark",
		/*kml time description*/
		 xmlelement (name "TimeSpan",
			/*start date, when the feature will be shown*/
			xmlelement(name begin, b.date),
			/*end date when the feature will be hide*/
			xmlelement(name end,b.date)),
		 /*name of the feature object*/
		 xmlelement(name name, pointdescription),
		 /*feature tipe,point line or polygon */
		 xmlelement (name "Point",
			/*point coordinates*/
			xmlelement(name coordinates,a.x,',',a.y)))
	from tableA as a, tableB as b
	where a.id_a=b.id_b)
/*output path*/
TO '/tmp/output.kml';

Just one on step more to get the kml. You only need to add the header of the kml and the end (I didn’t manage to write it from Postgres). If you are a programmer will be easy to you to write a small code with python, java  or whatever to add those lines. Until then the esiest part is to open the kml doument with a txt editor and paste the following kml header:

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

Go to the end of the document and close the elements with this code:

</Document>
</kml>

Open the file with Google Earth and enjoy it.

Advertisement