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

Wed Oct 24 16:27:08 PDT 2007

Kyle,
Your php script worked perfectly. Thank you!

I've now written a SQL query that creates a relative http link within  
respective database rows that points to those kml files.

Finally a separate php script outputs all database records to a web  
page. From that page a one can now click on one of over a thousand  
sortable records to view the corresponding kml file (in my case these  
are watershed polygons of pacific salmon populations). There are  
likely better ways of doing this, but this is exactly what I was  
shooting for this time around.

Anyone that is interested can view the huge table here (http:// 
dev.riverchange.org/postgis2KMLtable.html) to see what I am getting at.

Thanks for your help!

Dane

ps. I've pasted the SQL query that embeds the html link, and the two  
php scripts BELOW in case anyone else is interested in hacking around  
with this approach.

---------------------------------
PORTION of SQL QUERY TO EMBED HTML LINK TO "GID" field

  select gid, '<a href=' || '"kml/' || gid || '.kml">' || population  
|| '</a>' as Link from salmon_populations;

---------------------------------
PHP SCRIPT TO WRITE LOTS OF KML FILES

<?php

$first_header = '<?xml version="1.0" encoding="UTF-8"?><kml  
xmlns="http://earth.google.com/kml/2.2"><Document><name>';

$second_header = '</name><Style id="sh_ylw- 
pushpin_copy0"><IconStyle><color>0a000000</color><scale>0</ 
scale><Icon><href>http://maps.google.com/mapfiles/kml/pushpin/ylw- 
pushpin.png</href></Icon><hotSpot x="20" y="2" xunits="pixels"  
yunits="pixels"/></IconStyle><LineStyle><color>cc20057b</ 
color><width>7</width></LineStyle><LabelStyle><color>e3ffffff</ 
color><scale>0.7</scale></LabelStyle><PolyStyle><color>99ffffff</ 
color><fill>0</fill></PolyStyle></Style><Style id="sn_ylw- 
pushpin_copy0"><IconStyle><color>0a000000</color><scale>0</ 
scale><Icon><href>http://maps.google.com/mapfiles/kml/pushpin/ylw- 
pushpin.png</href></Icon><hotSpot x="20" y="2" xunits="pixels"  
yunits="pixels"/></IconStyle><LineStyle><color>cc20057b</color><width>';

$poly_width = '7';

$third_header = '</width></LineStyle><LabelStyle><color>e3ffffff</ 
color><scale>0.7</scale></LabelStyle><PolyStyle><color>99ffffff</ 
color><fill>0</fill></PolyStyle></Style><StyleMap id="msn_ylw- 
pushpin_copy0"><Pair><key>normal</key><styleUrl>#sn_ylw- 
pushpin_copy0</styleUrl></Pair><Pair><key>highlight</ 
key><styleUrl>#sh_ylw-pushpin_copy0</styleUrl></Pair></ 
StyleMap><Placemark><name>';

$forth_header = '</name><styleUrl>#msn_ylw-pushpin_copy0</styleUrl>';

$footer = '</Placemark></Document></kml>';

$cstring = "host=localhost port=5432 dbname=postgis user=postgis  
password=postgis";
$conn = pg_connect($cstring);
$rows = pg_query("SELECT COUNT(gid) as count from salmon_pops");
$num_rows = pg_fetch_result($rows,'count');

for ($i=1 ; $i <= $num_rows ; $i++) {
	$row_names = pg_query("SELECT Population From salmon_pops Where gid= 
$i");
	$listed_name = pg_fetch_result($row_names,'Population');

	$query = pg_query("Copy (Select '$first_header' || '$listed_name' ||  
'$second_header' || '$poly_width' || '$third_header' ||  
'$listed_name' || '$forth_header' || askml(the_geom) || '$footer'  
 From salmon_pops Where gid=$i) TO '/Users/Shared/".$i. ".kml';");
}

// Free resultset
pg_free_result($rows);
pg_free_result($row_names);
pg_free_result($query);

// Closing connection
pg_close($conn);

?>

------------------------
PHP SCRIPT TO GENERATE A WEBPAGE TO VIEW RESULTS WITH LINK TO KML FILES
------------------------

Sample Sortable table of Pacific Northwest Salmon Populations
The "sorttable.js" allows dynamic row sorting by clicking a column 
title. Output is via php from postgis database
Click on a population name to view kml of population in google 
earth
</div>

<div align="center">
<?php
// Connecting, selecting database
$dbconn = pg_connect("host=localhost dbname=postgis user=postgis  
password=postgis")
     or die('Could not connect: ' . pg_last_error());

// Performing SQL query
$query = 'SELECT link, Status, Region, Ecoregion, State, ESU,  
Percent_Natural, Viability, Diversity, Round(Score), Round(Area) FROM  
salmon_pops order by Region, ESU';

$result = pg_query($query) or die('Query failed: ' . pg_last_error());

// Printing results in HTML
echo pg_query($result);
echo "<table cellpadding='2' class='sortable'>\n<tr><td>Population</td>
<td>Status</td>
<td>Region</td>
<td>State</td>
<td>ESU</td>
<td>Percent Natural</td>
<td>Viability</td>
<td>Diversity</td>
<td>Score</td>
<td>Population Area</td></tr>";
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
     echo "\t<tr>\n";
     foreach ($line as $col_value) {
         echo "\t\t<td>$col_value</td>\n";
     }
     echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
pg_free_result($result);

// Closing connection
pg_close($dbconn);
?>

</div>
</body>
</html>

------------------------------------------

On Oct 24, 2007, at 8:06 AM, Kyle Wilcox wrote:

> Life is to short to program in Java.
>
> Not the most elegant solution below, but works.  Note that column 'id'
> must be integer unique (pk or something).
>
> $header = "XML_HEADER_HERE";
> $footer = "XML_FOOTER_HERE";
> $cstring = "host=YOUR_HOST port=5432 dbname=YOUR_DATABASE user=USER
> password=PASSWORD";
> $conn = pg_connect($cstring);
> $rows = pg_query("SELECT COUNT(*) as count from YOUR_TABLE");
> $num_rows = pg_fetch_result($rows,'count');
> for ($i=0 ; $i <= $num_rows ; $i++) {
> 	$query = pg_query("Copy (Select '$header' || askml(the_geom) ||
> '$footer' From YOUR_TABLE Where id=$i) TO 'DATA_PATH/record_". 
> $i.".kml'; ");
> }
>
>
> Guido Lemoine wrote:
>> Dane,
>>
>> Why not drop PHP and learn Java instead? You can combine JDBC  
>> access to
>> your data base (simply looping through your resultset, which can have
>> askml() as
>> one of the columns) with Java's XML document processing (JDOM) to do
>> all you want to do.
>>
>> GL
>>
>> Dane Blakely Springmeyer wrote:
>>> Hello,
>>>
>>> I have been using the PostgreSQL COPY command and the Postgis  
>>> AsKml()
>>> function to write out a KML file for a single database record that
>>> matches a WHERE clause, which is very handy.
>>>
>>> Here is the generalized SQL query:
>>>
>>> Copy (SELECT 'XML HEADER HERE' || askml(the_geom) || 'XML FOOTER  
>>> HERE'
>>> from mytable where name = unique record) TO '/recordname.kml';
>>>
>>> I would like to do this for every database record, therefore  
>>> creating
>>> a single, independent KML file for every geometry row. I think this
>>> undoubtedly requires a bit of scripting to loop through every  
>>> database
>>> record and save to a new .kml file by running an individual SQL  
>>> query
>>> as many times are there are rows.
>>>
>>> I am learning PHP, but at this point turning query results into  
>>> arrays
>>> and then passing those back to another query is beyond my skill  
>>> level.
>>>
>>> Has anyone else used PHP, PL/PGSQL, or other approaches to try
>>> something like this?
>>>
>>> Dane
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>
> -- 
>
>  Kyle Wilcox
>  NOAA Chesapeake Bay Office
>  410 Severn Avenue
>  Suite 107A
>  Annapolis, MD 21403
>  office: (410) 295-3151
>  Kyle.Wilcox at noaa.gov
>
>  "It is from the wellspring of our despair and the places
>   that we are broken that we come to repair the world."
> 						- Murray Waas
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users