<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns0="urn:schemas-microsoft-com:office:smarttags">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="State"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="Street"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="address"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="country-region"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Lucida Grande";
panose-1:0 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue style='word-wrap: break-word;-khtml-nbsp-mode: space;
-khtml-line-break: after-white-space'>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Hi Dane,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Can you send me the full script? Were you
the intern of Pacific Biodiversity Institute, <st1:place w:st="on"><st1:City
w:st="on">Winthrop</st1:City>, <st1:State w:st="on">WA</st1:State></st1:place>
in 2004? <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Thanks & Regards,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<p class=MsoNormal><font size=2 color=navy face="Times New Roman"><span
style='font-size:10.0pt;color:navy'>Santosh Gaikwad</span></font><font
color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Times New Roman"><span
style='font-size:10.0pt;color:navy'>Senior Software Developer</span></font><font
color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=black face="Times New Roman"><span
style='font-size:10.0pt;color:black'>Saama Technologies (</span></font><font
size=2><span style='font-size:10.0pt'><ns0:place w:insAuthor="sgaikwad"
w:insDate="2007-11-12T12:26:00Z" w:endInsAuthor="sgaikwad"
w:endInsDate="2007-11-12T12:26:00Z"><ns0:country-region w:insAuthor="sgaikwad"
w:insDate="2007-11-12T12:26:00Z" w:endInsAuthor="sgaikwad"
w:endInsDate="2007-11-12T12:26:00Z"><st1:country-region w:st="on"><st1:place
w:st="on"><font color=black><span style='color:black'>India</span></font></st1:place></st1:country-region></ns0:country-region></ns0:place><font
color=black><span style='color:black'>) Pvt. Ltd.,<br>
</span></font><font color=navy><span style='color:navy'>Unit No.101-102, First
floor,<br>
Weikfield IT CITI INFO PARK, <br>
Weikfield estates, <br>
<st1:Street w:st="on"><st1:address w:st="on">Pune Nagar Road</st1:address></st1:Street>,<br>
Pune - 411 014. </span></font></span></font><font size=2 color=navy><span
lang=FR style='font-size:10.0pt;color:navy'>India<br>
Phone : +91 20 66071397</span></font><font color=navy><span lang=FR
style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Times New Roman"><span
lang=FR style='font-size:10.0pt;color:navy'>Mobile: +91-9422005927<br>
E-mail :Santosh.Gaikwad@saama.com</span></font><font color=navy><span lang=FR
style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Times New Roman"><span
style='font-size:10.0pt;color:navy'><a href="http://www.saama.com"><span
lang=FR>http://www.saama.com</span></a></span></font><span lang=FR><o:p></o:p></span></p>
</div>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Dane Springmeyer<br>
<b><span style='font-weight:bold'>Sent:</span></b> Friday, November 09, 2007
3:28 AM<br>
<b><span style='font-weight:bold'>To:</span></b> PostGIS Users Discussion<br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [postgis-users] Write
out KML for each database record?</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Hi Marc,<o:p></o:p></span></font></p>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>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.<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>As such I worked with the example here (<a
href="http://code.google.com/support/bin/answer.py?answer=69906&topic=11364">http://code.google.com/support/bin/answer.py?answer=69906&topic=11364</a>)
and modified the mysql functions to work for postgres and arrived
essentially at the exact example you just sent. Thanks!<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>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.<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>I've posted the generalized form of the script below. There are two
noteworthy additions for those interested:<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>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. <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>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.<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Also, if anyone is interested in playing with the full script just let
me know and I'll send it along.<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Cheers,<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Dane<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><?php<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>$conn= pg_connect($cstring);<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>//Form up KML Header and Styles<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>$kml_header .= 'lots of XML HERE if you like styling';<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>// Select all the rows in a table that match given criteria.<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>$sql = "SELECT *, askml(the_geom) as poly_geometry,
askml(centroid(the_geom)) as poly_centroid from table where = any_value";<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>$result = pg_exec($sql);<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>while ($row = pg_fetch_array($result)) {<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>//KML
Header<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
= $kml_header<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>//Key
Variable<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$name
= $row['id'] //where id is some unique in selected table id whether text or
number<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>//Form
up KML polygon<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '<Placemark>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '<name>' . $name . '</name>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '<styleUrl>#transbluePoly</styleUrl>'; <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= $row['poly_geometry'] ;<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '</Placemark>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>//Form
up KML label at centroid of polygon<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '<Placemark>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '<name>' . $name . '</name>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '<styleUrl>#label_map</styleUrl>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= $row['poly_centroid'] ;<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '</Placemark>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>//KML
Footer<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$kml
.= '</Document></kml>';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>//Use
PHP to write each kml to the local filesystem with respective id number<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>$filepath
= '/Users/Shared/kml/' . $name . '.kml';<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>file_put_contents("$filepath",
"$kml");<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>}<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span class=apple-tab-span><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>?><o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>On Nov 8, 2007, at 8:09 AM, Marc Compte wrote:<o:p></o:p></span></font></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><br>
<br>
<o:p></o:p></span></font></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>...</span></font><o:p></o:p></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>$conn=
pg_connect($cstring);</span></font><o:p></o:p></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>$sql="SELECT Id FROM
your_table";</span></font><o:p></o:p></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>$rs=pg_exec($sql);</span></font><o:p></o:p></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>while ($row=pg_fetch_array($rs))
{</span></font><o:p></o:p></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>$query =
pg_query("Copy (Select '$header' || askml(the_geom) || '$footer' From
YOUR_TABLE Where id=".$row["Id"].") TO
'DATA_PATH/record_".$row["Id"].".kml'; ");</span></font><o:p></o:p></p>
<p style='margin:0in;margin-bottom:.0001pt'><font size=1 face="Lucida Grande"><span
style='font-size:7.5pt;font-family:"Lucida Grande"'>}</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</div>
</body>
</html>