[postgis-users] Write out KML for each database record?

[Dane Springmeyer]

Hi Marc,

Exactly. You are perceptive- immediately after I got things working I  
realized that the way the Php FOR LOOP worked it would make it  
impossible to write out KML for records returned by a dynamically  
changing query.

As such I worked with the example here (http://code.google.com/ 
support/bin/answer.py?answer=69906&topic=11364) and modified the  
mysql functions to work for postgres and arrived essentially at the  
exact example you just sent. Thanks!

This approach is cool because it allows kml to be written for any  
number of rows returned by a query as long as some column in the  
database contains a numeric or string unique id.

I've posted the generalized form of the script below. There are two  
noteworthy additions for those interested:

One is the use of "askml(centroid(the_geom))" to grab the center  
point of each polygon, which is used in the while loop to create kml  
labels.

The other is the use of only a single postgis query. PHP loops  
through the results as an array and writes each kml to the local file  
system. I'm currently testing the speed of this method vs. using the  
COPY command. If you have any opinions about speed or shortfalls of  
having php write to the filesystem I would be interested to hear.

Also, if anyone is interested in playing with the full script just  
let me know and I'll send it along.

Cheers,

Dane



<?php

$conn= pg_connect($cstring);

//Form up KML Header and Styles
$kml_header .= 'lots of XML HERE if you like styling';

// Select all the rows in a table that match given criteria.
$sql = "SELECT *,  askml(the_geom) as poly_geometry,  askml(centroid 
(the_geom)) as poly_centroid from table where = any_value";
$result =  pg_exec($sql);
while ($row = pg_fetch_array($result)) {
	
	//KML Header
	$kml = $kml_header
	
	//Key Variable
	$name = $row['id'] //where id is some unique in selected table id  
whether text or number
	
	//Form up KML polygon
	$kml .= '<Placemark>';
	$kml .= '<name>' . $name . '</name>';
	$kml .= '<styleUrl>#transbluePoly</styleUrl>';
	$kml .= $row['poly_geometry'] ;
	$kml .= '</Placemark>';
	
	//Form up KML label at centroid of polygon
	$kml .= '<Placemark>';
	$kml .= '<name>' . $name . '</name>';
	$kml .= '<styleUrl>#label_map</styleUrl>';
	$kml .= $row['poly_centroid'] ;
	$kml .= '</Placemark>';
	
	//KML Footer
	$kml .= '</Document></kml>';
	
	//Use PHP to write each kml to the local filesystem with respective  
id number
	$filepath = '/Users/Shared/kml/' . $name . '.kml';
	file_put_contents("$filepath", "$kml");
	}
	
?>



On Nov 8, 2007, at 8:09 AM, Marc Compte wrote:

> ...
> $conn= pg_connect($cstring);
> $sql="SELECT Id FROM your_table";
> $rs=pg_exec($sql);
> while ($row=pg_fetch_array($rs)) {
> $query = pg_query("Copy (Select '$header' || askml(the_geom) ||  
> '$footer' From YOUR_TABLE Where id=".$row["Id"].") TO 'DATA_PATH/ 
> record_".$row["Id"].".kml'; ");
> }

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071108/73472e0c/attachment.html>