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

[Santosh Gaikwad]

Hi Dane,

 

Can you send me the full script?  Were you the intern of Pacific
Biodiversity Institute, Winthrop, WA in 2004?  

 

Thanks & Regards,

 

Santosh Gaikwad

Senior Software Developer

Saama Technologies (India) Pvt. Ltd.,
Unit No.101-102, First floor,
Weikfield IT CITI INFO PARK, 
Weikfield estates, 
Pune Nagar Road,
Pune - 411 014. India
Phone : +91 20 66071397

Mobile: +91-9422005927
E-mail :Santosh.Gaikwad at saama.com

http://www.saama.com <http://www.saama.com> 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Dane
Springmeyer
Sent: Friday, November 09, 2007 3:28 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Write out KML for each database record?

 

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/20071112/54e22deb/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Santosh Gaikwad.vcf
Type: text/x-vcard
Size: 145 bytes
Desc: Santosh Gaikwad.vcf
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071112/54e22deb/attachment.vcf>